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

Поиск
Список
Период
Сортировка
От Lawrence Jones
Тема Re: Postgres-native method to identify if a tuple is frozen
Дата
Msg-id CAA6U2pay5+o5vxdgw08uF39L3V1pmJcqDxFc0wMfv43+7B4vrQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Postgres-native method to identify if a tuple is frozen  (Amit Kapila <amit.kapila16@gmail.com>)
Список pgsql-hackers
Дерево обсуждения
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

В списке pgsql-hackers по дате отправления:

Предыдущее
От: Masahiko Sawada
Дата:
Сообщение: Re: Transactions involving multiple postgres foreign servers, take 2
Следующее
От: Masahiko Sawada
Дата:
Сообщение: Re: display offset along with block number in vacuum errors