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 по дате отправления: