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

[2ndquadrant]

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

[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
;