0

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.

3
  • 2
    For the first part, you can use a before trigger that fills in "missing" created_on values. Feb 19, 2023 at 13:28
  • 1
    Is there a limit to the perversity of your ORM? What if it updates the wrong row, of the wrong table, on a completely different database connection?
    – jjanes
    Feb 19, 2023 at 15:02
  • It is Spring + Hibernate and problems came from @CreatedDate - the following class level annotation was missed @EntityListeners(AuditingEntityListener.class). Investigation showed the problem had been resolved long ago. Old records indirectly appeared as the new ones but that perception was wrong. I needed proofs from DB side. Setting NOT NULL constraint on PROD was too dramatic change for detecting NULLs, maybe some logging triggers were better....
    – gavenkoa
    Feb 19, 2023 at 16:40

1 Answer 1

-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.

4
  • 1
    That is not reliable and purely coincidental. Transaction IDs wrap around, so a greater value could come from an older row. Feb 20, 2023 at 6:42
  • SELECT pg_column_size('123'::xid); returns 4, so in fact overlapping is quite possible. I assumed that MVCC relies on xid monotonicity, but I didn't study docs/sources. For a small table with low INSERT / UPDATE the overlapping might not happens in years. Audit table is such: append only! So the question if MVCC really assumes monotonicity of xmin.
    – gavenkoa
    Feb 20, 2023 at 10:25
  • Maybe I'm wrong. xmin is not a property of a record attached to a table but rather a global identity. So other highly active tables could result in xmin overlapping ((
    – gavenkoa
    Feb 20, 2023 at 10:32
  • 1
    It will work reliably if you have never had transaction ID wraparound yet. If SELECT pg_current_xact_id(); (available with recent PostgreSQL versions) gives you a value under 4294967296. Feb 20, 2023 at 13:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.