storing TG_RELID (oid) is not "portable" across backup/restore

[zam6ak]

Creating index on TG_RELID makes it small and neat to use but if I take a logical backup (pg_dump) of the DB and restore it on another server, the oid for schema.table changes thus making this unusable. unless I am missing something?

Related question on DBA StackExchange: https://dba.stackexchange.com/questions/180490/postgresql-do-regclass-oid-columns-persist-across-backups

Any ideas?

Thanks in advance!

[tbussmann]

Good point! So, after the restore of a dump, one could/should perform a (expensive) query like the following to make sure the relid column matches the oids of the current cluster. By dropping and recreating the index, the update should be HOT and thus more space efficient (and a bit faster as well):

DROP INDEX audit.logged_actions_relid_idx;
UPDATE audit.logged_actions
    SET relid = COALESCE(to_regclass(schema_name || '.' || table_name)::oid, 0)
    WHERE relid <> COALESCE(to_regclass(schema_name || '.' || table_name)::oid, 0);
CREATE INDEX logged_actions_relid_idx ON audit.logged_actions(relid);

One can detect the issue with a query like the following:

SELECT
    schema_name,
    table_name,
    COALESCE(to_regclass(schema_name || '.' || table_name)::oid, 0) AS relid_real,
    array_agg(DISTINCT relid) AS relid_audit,
    count(DISTINCT relid) AS relid_audit_count,
    count(*) AS rec_count_audit
FROM
    audit.logged_actions
GROUP BY
    schema_name,
    table_name
;