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

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(dot)kapila16(at)gmail(dot)com> wrote:

> On Mon, Jul 20, 2020 at 9:07 PM Lawrence Jones <lawrence(at)gocardless(dot)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
>