Identifying recently inserted/updated records in Postgres

Question

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)) 
FROM audit_log
WHERE created_on > CURRENT_TIMESTAMP - INTERVAL '5 day'; 

and using the magical xmin column find "newer" rogue NULL rows:

SELECT * 
FROM audit_log
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 xmin...

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.

Answer 1

Seems my approach with MIN(xmin::TEXT:BIGINT) is working. My indirect indications were based on wrong assumptions, now I don't have doubts.

xmin holds the always incrementing ID of an instance of the raw, needed by MVCC. If you identify xmin of one record in a table:

• almost all previous transactions are with WHERE xmin::TEXT::BIGINT < ... (less then given)
• almost all subsequent transactions are with WHERE xmin::TEXT::BIGINT > ...

almost all because some concurrent transactions might have xmin in a close range but the order is not defined. Still those few transactions are not harmful for my analysis.

UPDATE Seems the cardinality of xid type is low, only 4 bytes: SELECT pg_column_size('123'::xid); returns 4.

Seems xmin is a globally incremented ID per transaction so some very active tables could cause xmin overlapping. After overlapping it is impossible to correlate data based on "fake" monotonicity of xmin.

In my case even DB is 5yo it is still near the limit for overlapping:

-- 1'705'652'544
select max(xmin::text::int) from audit_log;

So the method has limitations, not for production, but could be good enough in some cases for troubleshooting / forensic analysis.