postgresql pageinspect what does t_infomask, t_infomask2 comparison with integers really mean?

create or replace function heap_page(relname text, pageno integer)
    returns
    table ( ctid tid,state text,xmin text,xmax text,hhu text,hot text,t_ctid tid)
    as $
    select
    (pageno,lp)::text::tid as ctid,
    case lp_flags
        when 0 then 'unused'
        when 1 then 'normal'
        when 2 then 'redirect to '|| lp_off
        when 3 then 'dead'
    end as state,
    t_xmin || case
    when (t_infomask & 256) > 0 then 'c'
    when (t_infomask & 512) > 0 then 'a'
    else ''
    end as xmin,
    t_xmax || case
    when (t_infomask & 1024) >0 then 'c'
    when (t_infomask & 2048) >0 then 'a'
    else ''
    end as xmax,
    case when (t_infomask2 & 16384) >0 then 't' end as hhu,
    case when (t_infomask2 & 32768) >0 then 't' end as hot,
    t_ctid
    from heap_page_items
    (get_raw_page(relname, pageno))
    order by lp
    $ language sql;

https://www.postgresql.org/docs/current/pageinspect.html

https://www.postgresql.org/docs/current/functions-math.html

https://doxygen.postgresql.org/htup__details_8h_source.html

I can understand the manual bitwise operator & example.

But here I don't understand all the comparisons t_infomask and t_infomask2 with variant integer.

You can find that information in src/include/access/htup_details.h.

About t_infomask:
/*
* information stored in t_infomask:
*/
#define HEAP_HASNULL 0x0001 / has null attribute(s) /
#define HEAP_HASVARWIDTH 0x0002 / has variable-width attribute(s) /
#define HEAP_HASEXTERNAL 0x0004 / has external stored attribute(s) /
#define HEAP_HASOID_OLD 0x0008 / has an object-id field /
#define HEAP_XMAX_KEYSHR_LOCK 0x0010 / xmax is a key-shared locker /
#define HEAP_COMBOCID 0x0020 / t_cid is a combo CID /
#define HEAP_XMAX_EXCL_LOCK 0x0040 / xmax is exclusive locker /
#define HEAP_XMAX_LOCK_ONLY 0x0080 / xmax, if valid, is only a locker /

/ xmax is a shared locker /
#define HEAP_XMAX_SHR_LOCK (HEAP_XMAX_EXCL_LOCK | HEAP_XMAX_KEYSHR_LOCK)

#define HEAP_LOCK_MASK (HEAP_XMAX_SHR_LOCK | HEAP_XMAX_EXCL_LOCK | \
HEAP_XMAX_KEYSHR_LOCK)
#define HEAP_XMIN_COMMITTED 0x0100 / t_xmin committed /
#define HEAP_XMIN_INVALID 0x0200 / t_xmin invalid/aborted /
#define HEAP_XMIN_FROZEN (HEAP_XMIN_COMMITTED|HEAP_XMIN_INVALID)
#define HEAP_XMAX_COMMITTED 0x0400 / t_xmax committed /
#define HEAP_XMAX_INVALID 0x0800 / t_xmax invalid/aborted /
#define HEAP_XMAX_IS_MULTI 0x1000 / t_xmax is a MultiXactId /
#define HEAP_UPDATED 0x2000 / this is UPDATEd version of row /
#define HEAP_MOVED_OFF 0x4000 /* moved to another place by pre-9.0
* VACUUM FULL; kept for binary
upgrade support /
#define HEAP_MOVED_IN 0x8000 /* moved from another place by pre-9.0
* VACUUM FULL; kept for binary
upgrade support /
#define HEAP_MOVED (HEAP_MOVED_OFF | HEAP_MOVED_IN)


About t_infomask2:
/*
* information stored in t_infomask2:
*/
#define HEAP_NATTS_MASK 0x07FF / 11 bits for number of attributes /
/ bits 0x1800 are available /
#define HEAP_KEYS_UPDATED 0x2000 /* tuple was updated and key cols
modified, or tuple deleted /
#define HEAP_HOT_UPDATED 0x4000 / tuple was HOT-updated /
#define HEAP_ONLY_TUPLE 0x8000 / this is heap-only tuple /

#define HEAP2_XACT_MASK 0xE000 / visibility-related bits /


So t_infomask & 256 means that the transaction stored in xmin is marked committed, and t_infomask & 512 means that that the transaction stored in xmin is rolled back or invalid. t_infomask & 1024 and t_infomask & 2048 are the same for xmax.

These are so called “hint bits” that provide a shortcut to commit log information about the visibility of the tuple.

t_infomask2 & 16384 means that the tuple was updated with a HOT update, and t_infomask2 & 32768 means that the tuple is a heap-only tuple.

StackExchange Database Administrators Q#308360, answer score: 3