Обсуждение: tid_blockno() and tid_offset() accessor functions

On 3/8/26 18:17, Alexandre Felipe wrote:
> That was something I was surprised to learn, that we can check TID, do
> queries by TID intervals, but we can't get pages from TID, when I was
> trying to analyse how many pages on average a certain query would touch
> for different users.

True. The conversion to "point" is the traditional way to do this, but
having functions to access the fields is cleared I think.

> I think it would be nice to also support 
> SELECT * FROM table WHERE tid_block(tid) BETWEEN b1 AND b2;
> 

Not sure. Functions are opaque for the scan, i.e. it can't treat it as a
scan key easily, because it could do anything. So this would require
teaching the TidScan that "tid_block" is a special case.

I believe this should be doable through "support procedures", which can
be attached to pg_proc entries. So tid_block would have a "prosupport"
pointing at a function, implementing SupportRequestIndexCondition. Which
would translate the clause on tid_block() to a range condition on the
underlying tid.

For inspiration see starts_with(), and text_starts_with_support support
procedure (or rather like_regex_support).

However, that seems out of scope for this initial patch.

> I wouldn't bother to support block number above 2^31 or block offsets
> above 2^15.
> 
> This test shows that it assumes wrapping
> -- (-1,0) wraps to blockno 4294967295
> SELECT tid_block('(-1,0)'::tid);
>  tid_block  
> ------------
>  4294967295
> 
> You could just stick with that, I am sure that someone with a table
> having more than 2B pages on a table will understand that.
> for tid_offset I don't think it is even possible. If the maximum page
> size is limited to 2^15, must have a header and each offset has a line
> pointer aren't offsets limited to something smaller than 2^13?
> 

No opinion. For displaying the bogus TID value (like "(-1,0)") it's
probably OK to show values that are a bit weird. If anything, we should
be more careful on input, it's too late for tid_block() to decide what
to do with an "impossible" TID value.

regards

-- 
Tomas Vondra

Hi,

On 2026-03-09 09:34:46 -0400, Greg Sabino Mullane wrote:
> On Sun, Mar 8, 2026 at 3:31 PM Tomas Vondra <tomas@vondra.me> wrote:
> 
> > No opinion. For displaying the bogus TID value (like "(-1,0)") it's
> > probably OK to show values that are a bit weird. If anything, we should
> > be more careful on input, it's too late for tid_block() to decide what to
> > do with an "impossible" TID value.
> >
> 
> This one doesn't sit right with me. I think it's not too late. No reason
> why tid_block cannot be stricter here than tid itself and complain. Other
> than that, the patch looks good to me.

I don't see any advantage in that. These functions are useful for inspecting
tid values that come from some source. When would you *ever* gain *anything*
from not being able to see the block / offset of a tid datum that you already
have?

This isn't an end user focused type / set of accessor functions were being
particularly careful about input validation will perhaps prevent users from
making mistakes...

Greetings,

Andres Freund

On Fri, Feb 27, 2026 at 10:59 AM Ayush Tiwari
<ayushtiwari.slg01@gmail.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
groupby 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
outsideof 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.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

Hi,

On 2026-03-11 14:48:08 -0700, Masahiko Sawada wrote:
> On Fri, Feb 27, 2026 at 10:59 AM Ayush Tiwari
> <ayushtiwari.slg01@gmail.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
groupby 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
TIDsoutside 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?

Greetings,

Andres

On Wed, Mar 11, 2026 at 2:50 PM Andres Freund <andres@anarazel.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.slg01@gmail.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
togroup 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
TIDsoutside 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.

Regards,

--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com

On 12.03.26 17:51, Masahiko Sawada wrote:
> On Wed, Mar 11, 2026 at 2:50 PM Andres Freund <andres@anarazel.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.slg01@gmail.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
togroup 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
TIDsoutside 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.

Hi,

On 2026-03-08 01:13:13 +0530, Ayush Tiwari wrote:
> From 92e3657d85b13355563ba4c447ddf89fcb4c4b3e Mon Sep 17 00:00:00 2001
> From: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
> Date: Sat, 7 Mar 2026 18:27:36 +0000
> Subject: [PATCH v2] Add tid_block() and tid_offset() accessor functions
>
> Add two new built-in SQL functions to extract the components of a tid
> (tuple identifier) value:
>
>   tid_block(tid) -> bigint   -- extract block number
>   tid_offset(tid)  -> integer  -- extract offset number
>
> These provide a clean, efficient alternative to the common workaround
> of ctid::text::point for decomposing TID values. The text-based hack
> is fragile, inefficient, and unavailable outside of SQL contexts.
>
> tid_block() returns int8 (bigint) because BlockNumber is uint32,
> which exceeds the range of int4. tid_offset() returns int4 (integer)
> because OffsetNumber is uint16, which fits safely in int4.
>
> Both functions use the NoCheck accessor variants from itemptr.h,
> are marked leakproof, and include regression tests covering typical
> values, boundary conditions, NULL handling, and round-trip identity.


> +++ b/doc/src/sgml/func/func-tid.sgml
> @@ -0,0 +1,73 @@
> + <sect1 id="functions-tid">
> +  <title>TID Functions</title>
> +
> +  <indexterm zone="functions-tid">
> +   <primary>TID</primary>
> +   <secondary>functions</secondary>
> +  </indexterm>
> +
> +  <indexterm>
> +   <primary>tid_block</primary>
> +  </indexterm>
> +
> +  <indexterm>
> +   <primary>tid_offset</primary>
> +  </indexterm>
> +
> +  <para>
> +   For the <type>tid</type> data type (described in <xref
> +   linkend="datatype-oid"/>), <xref linkend="functions-tid-table"/>

Seems odd to reference the datatype-oid, that's barely mentioning the tid type
and tid is not an oid like type either (like e.g. regtype is).


> +   shows the functions available for extracting the block number and
> +   tuple offset.  These functions are commonly used with the
> +   <structfield>ctid</structfield> system column.
> +  </para>

I know some other places do that too, but "shows the functions" sounds odd to
me. I'd just say "These are listed in ..." or such.

I'd remove the "available for extracting the block number and tuple offset",
as that's bound to become inaccurate and just restates the table contents.



> --- a/doc/src/sgml/func/func.sgml
> +++ b/doc/src/sgml/func/func.sgml
> @@ -59,6 +59,7 @@ repeat('Pg', 4) <returnvalue>PgPgPgPg</returnvalue>
>  &func-formatting;
>  &func-datetime;
>  &func-enum;
> +&func-tid;
>  &func-geometry;
>  &func-net;
>  &func-textsearch;

I'd add it somewhere more alphabetically fitting. Unfortunately the list isn't
fully ordered right now, but no need to make it even worse...


Greetings,

Andres Freund

On Fri, 13 Mar 2026 at 14:27, Peter Eisentraut <peter@eisentraut.org> wrote:
>
> On 12.03.26 17:51, Masahiko Sawada wrote:
> > On Wed, Mar 11, 2026 at 2:50 PM Andres Freund <andres@anarazel.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.slg01@gmail.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
togroup 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
TIDsoutside 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)

Hi,

On 2026-03-13 18:08:04 +0100, Matthias van de Meent wrote:
> 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.

Meh. As long as tids themselves are split like they are, without any
variability of the amount of space dedicated for either component, I don't see
any advantage in that.

Greetings,

Andres Freund

Hi,

On 2026-03-14 14:31:32 +0530, Ayush Tiwari wrote:
> Attaching a patch with all document changes, removed the cross-reference to
> datatype-oid entirely. I've moved the &func-tid; entry in func.sgml to
> directly follow &func-textsearch;, which fits better alphabetically, and
> reworded the introductory paragraph to be much more concise, directly
> pointing to the table.

Pushed this after making some small changes (removed some IMO unnecessary
comments, replaced <type>TID</type> with <acronym/>, as TID is not the type's
name, ...).

I was reminded of this thread while in the process of doing some performance
validation that required getting all the block numbers from an index scan,
which required me to remember (ctid::text::point)[0], which I find to be very
ugly.

Closed the CF entry.

Thanks for the patch!

Greetings,

Andres Freund

Hello

On 2026-Apr-05, Andres Freund wrote:

> I was reminded of this thread while in the process of doing some performance
> validation that required getting all the block numbers from an index scan,
> which required me to remember (ctid::text::point)[0], which I find to be very
> ugly.

Thanks for this patch!  I agree that the statu quo was sort of embarrasing :-)

Regards

-- 
Álvaro Herrera               48°01'N 7°57'E  —  https://www.EnterpriseDB.com/