From: | Lawrence Jones <lawrence(at)gocardless(dot)com> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Postgres-native method to identify if a tuple is frozen |
Date: | 2020-07-20 15:21:55 |
Message-ID: | CAA6U2pZx7=2YS9u_ZZYSiKR12tbPS81hh37SqEe8t3L_WSvrUw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hey all,
*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
From | Date | Subject | |
---|---|---|---|
Next Message | Georgios Kokolatos | 2020-07-20 15:36:44 | Re: [PATCH] Finally split StdRdOptions into HeapOptions and ToastOptions |
Previous Message | Peter Geoghegan | 2020-07-20 15:17:25 | Re: Binary support for pgoutput plugin |