PostgreSQL 9.4：如何根据LOB ID找到对应的所属表？

PostgreSQL 9.4本身不会自动追踪LOB与引用它的表之间的关联关系——pg_largeobject仅存储LOB的实际数据，而业务表只是用OID（即LOB ID）来引用它，系统层面没有记录这个OID被哪些表的字段使用。不过可以通过以下手动方式查找：

方法1：扫描所有含LOB引用字段的表 #

PostgreSQL中存储LOB的字段通常是oid（直接引用大对象）或bytea（存储小型LOB）。你可以先找出所有包含这类字段的表，再逐个查询是否存在目标LOB ID。

步骤1：获取所有含OID/bytea字段的表信息 #

执行以下SQL查询，得到所有可能存储LOB引用的表和字段：

SELECT 
    n.nspname AS schema_name,
    c.relname AS table_name,
    a.attname AS column_name,
    t.typname AS column_type
FROM 
    pg_class c
JOIN 
    pg_namespace n ON c.relnamespace = n.oid
JOIN 
    pg_attribute a ON c.oid = a.attrelid
JOIN 
    pg_type t ON a.atttypid = t.oid
WHERE 
    t.typname IN ('oid', 'bytea')
    AND a.attnum > 0
    AND NOT a.attisdropped
ORDER BY 
    schema_name, table_name;

步骤2：逐个查询表中是否存在目标LOB ID #

假设目标LOB ID是12345，针对查询结果中的每个表和字段，执行类似如下的SQL：

-- 替换成实际的schema、表名和字段名
SELECT * FROM public.target_table WHERE target_column = 12345;

方法2：优先检查活跃表缩小范围 #

如果数据库规模较大，可以先筛选出近期有读写操作的表，优先查询这些表以节省时间：

SELECT 
    n.nspname AS schema_name,
    c.relname AS table_name,
    p.n_live_tup AS row_count,
    p.last_autoanalyze AS last_analyzed_time
FROM 
    pg_class c
JOIN 
    pg_namespace n ON c.relnamespace = n.oid
JOIN 
    pg_stat_user_tables p ON c.relname = p.relname
WHERE 
    EXISTS (
        SELECT 1 FROM pg_attribute a 
        WHERE a.attrelid = c.oid 
        AND a.atttypid IN (SELECT oid FROM pg_type WHERE typname IN ('oid', 'bytea'))
        AND a.attnum > 0 AND NOT a.attisdropped
    )
ORDER BY 
    p.last_autoanalyze DESC;

注意事项 #

• 这种扫描方式在大型数据库中可能耗时较长；
• 如果LOB对应的所有业务记录已被删除，这个LOB会成为"孤儿"对象，此时无法找到所属表，可以用lo_unlink命令删除；
• PostgreSQL 9.5及以上版本新增了pg_largeobject_metadata表可辅助追踪LOB信息，但9.4不支持该特性。

内容的提问来源于stack exchange，提问作者Flumen