tl;dr: we're looking for an easy way to ask if a tuple is frozen from within a SQL query
We're trying to build a validation process around our CCD, in an attempt to validate that all data inside of Postgres has made it into our secondary store.
Our plan is to build a small incremental process around daily snapshots of the database, scanning each table with something like:
-- $1: xid of transaction that occurred just before the previous day
-- TODO: Handle wraparound, defend against vacuum min frozen age, etc
select id from table where xmin > $1 and not frozen(tid);
We're hoping this can reliably detect new and modified tuples, and do it quickly, by sequentially scanning the table.
So we hit the question: how can we identify if a tuple is frozen? I know the tuple has both committed and aborted hint bits set, but accessing those bits seems to require superuser functions and are unlikely to be that fast.
Are there system columns (similar to xmin, tid, cid) that we don't know about?
Given this context, are we trying to do something you would think is a bad idea?
Thanks,
Lawrence