ORM framework sometimes doesn't set creation time for audit table. As a workaround I applied:
ALTER TABLE audit_log ALTER COLUMN created_on SET DEFAULT current_timestamp;
I suspect that this won't fix the problems that ORM framework causes: it could set column to
NULL so the default value is not triggered.
I cannot search for "broken" records with
WHERE created_on IS NULL returns lots of old record, indistinguishable from new. So I cannot check if the fix really helps.
My idea is to find some record after the fix with the known date, like (using xid trick):
SELECT MIN(CAST(CAST(xmin AS TEXT) AS BIGINT))
WHERE created_on > CURRENT_TIMESTAMP - INTERVAL '5 day';
and using the magical
xmin column find "newer" rogue
WHERE created_on IS NULL
AND CAST(CAST(xmin AS TEXT) AS BIGINT) > (
SELECT MIN(CAST(CAST(mintbl.xmin AS TEXT) AS BIGINT))
FROM audit_log mintbl
WHERE mintbl.created_on > CURRENT_TIMESTAMP - INTERVAL '5 day');
The query return nothing even if I have indirect indication that there are recent "broken" records. Probably I misunderstand the meaning of the special column
Could you help with Postgres magical columns which could be used to identify rows after some insert/update event in the past?
PS It is possible to implement stronger fix to workaround ORM problem: via insert/update trigger, still I'm interested in the original question: how can I find rows that are appeared after some known transaction in the past.