Re: tid_blockno() and tid_offset() accessor functions

On Fri, 13 Mar 2026 at 14:27, Peter Eisentraut <peter(at)eisentraut(dot)org> wrote:
>
> On 12.03.26 17:51, Masahiko Sawada wrote:
> > On Wed, Mar 11, 2026 at 2:50 PM Andres Freund <andres(at)anarazel(dot)de> wrote:
> >>
> >> Hi,
> >>
> >> On 2026-03-11 14:48:08 -0700, Masahiko Sawada wrote:
> >>> On Fri, Feb 27, 2026 at 10:59 AM Ayush Tiwari
> >>> <ayushtiwari(dot)slg01(at)gmail(dot)com> wrote:
> >>>>
> >>>> Hi hackers,
> >>>>
> >>>> As of now we don't have any built-in way to extract the block and offset components from a TID. When people need to group by page (like for bloat analysis) or filter by specific blocks, they usually end up using the `ctid::text::point` hack:
> >>>>
> >>>> SELECT (ctid::text::point)[0]::bigint AS blockno,
> >>>> (ctid::text::point)[1]::int AS offset
> >>>> FROM my_table;
> >>>>
> >>>> This works, but it's pretty clunky, relies on the text representation, and isn't great if you're trying to parse TIDs outside of SQL.
> >>>>
> >>>> The attached patch adds two simple accessor functions:
> >>>> - `tid_blockno(tid) -> bigint`
> >>>> - `tid_offset(tid) -> integer`
> >>>
> >>> How about adding the subscripting support for tid data type? For
> >>> example, ctid[0] returns bigint and ctid[1] returns int.
> >>
> >> That just seems less readable and harder to find to me. I think it'd also
> >> make the amount of required code noticeably larger?
> >
> > Yeah, using the dedicated functions would be more intuitive than using
> > magic numbers 1 and 2, and require less code.
>
> Also, you can use one-argument functions like field names, like
> tid.tid_blockno, so it's definitely more intuitive that way.

TIL.

As for naming; I'd personally prefer to have 'heap' included in the
names here (e.g. heaptid_blkno(tid) or heap_blkno[_of](tid)), because
not all AMs may map tid.blkno exactly to a block number in the main
fork. While PostgreSQL (in core) currently only knows about the heap
AM, we should probably keep clear of pretending that all tableAMs
produce TIDs that behave exactly like heap's do.

Matthias van de Meent
Databricks (https://www.databricks.com)