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

[Lawrence Jones]

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, , , true, NULL))
#else
if (!heap_fetch(rel, SnapshotAny, , ))
#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  wrote:

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

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

[Amit Kapila]

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

Postgres-native method to identify if a tuple is frozen

[Lawrence Jones]

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