Postgres Pro
Downloads
Home   >   mailing lists

Thread: Postgres-native method to identify if a tuple is frozen

Postgres-native method to identify if a tuple is frozen

From
Lawrence Jones
Date:
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

Re: Postgres-native method to identify if a tuple is frozen

From
Amit Kapila
Date:
On Mon, Jul 20, 2020 at 9:07 PM Lawrence Jones <lawrence@gocardless.com> wrote:
>
>
> So we hit the question: how can we identify if a tuple is frozen? I know the tuple has both committed and aborted
hintbits 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?
>

I think the way to get that information is to use pageinspect
extension and use some query like below but you are right that you
need superuser privilege for that:

SELECT t_ctid, raw_flags, combined_flags
         FROM heap_page_items(get_raw_page('pg_class', 0)),
           LATERAL heap_tuple_infomask_flags(t_infomask, t_infomask2)
         WHERE t_infomask IS NOT NULL OR t_infomask2 IS NOT NULL;

-- 
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

Re: Postgres-native method to identify if a tuple is frozen

From
Lawrence Jones
Date:
Thanks for the help. I'd seen the heap_page_items functions, but wanted to avoid the superuser requirement and wondered if this was going to be a performant method of finding the freeze column (we're scanning some billions of rows).

Fwiw, we think we'll probably go with a tiny extension that exposes the frozen state exactly. For reference, this is the basic sketch:

Datum
frozen(PG_FUNCTION_ARGS)
{
Oid reloid = PG_GETARG_OID(0);
ItemPointer tid = PG_GETARG_ITEMPOINTER(1);
Relation rel;
HeapTupleData tuple;
Buffer buf;
int result;
// Open table and snapshot- ensuring we later close them
rel = heap_open(reloid, AccessShareLock);
// Initialise the tuple data with a tid that matches our input
ItemPointerCopy(tid, &(tuple.t_self));
#if PG_MAJOR < 12
if (!heap_fetch(rel, SnapshotAny, &tuple, &buf, true, NULL))
#else
if (!heap_fetch(rel, SnapshotAny, &tuple, &buf))
#endif
{
result = 3;
}
else
{
result = HeapTupleHeaderXminFrozen(tuple.t_data);
}
// Close any opened resources here
heap_close(rel, AccessShareLock);
ReleaseBuffer(buf);
PG_RETURN_INT32(result);
}

On Tue, 21 Jul 2020 at 13:22, Amit Kapila <amit.kapila16@gmail.com> wrote:
On Mon, Jul 20, 2020 at 9:07 PM Lawrence Jones <lawrence@gocardless.com> wrote:
>
>
> 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?
>

I think the way to get that information is to use pageinspect
extension and use some query like below but you are right that you
need superuser privilege for that:

SELECT t_ctid, raw_flags, combined_flags
         FROM heap_page_items(get_raw_page('pg_class', 0)),
           LATERAL heap_tuple_infomask_flags(t_infomask, t_infomask2)
         WHERE t_infomask IS NOT NULL OR t_infomask2 IS NOT NULL;

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com