Postgres-native method to identify if a tuple is frozen

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

Responses

Browse pgsql-hackers by date

  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