MIME-Version: 1.0
References: 
 <CAJTYsWUzok2+mvSYkbVUwq_SWWg-GdHqCuYumN82AU97SjwjCA@mail.gmail.com>
 <CAJTYsWVK6rw++WU94=gFk5ZR5ZXhT3BYOFxhm6tT9xOGG-drnA@mail.gmail.com>
In-Reply-To: 
 <CAJTYsWVK6rw++WU94=gFk5ZR5ZXhT3BYOFxhm6tT9xOGG-drnA@mail.gmail.com>
From: Alexandre Felipe <o.alexandre.felipe@gmail.com>
Date: Sun, 8 Mar 2026 17:17:44 +0000
Message-ID: 
 <CAE8JnxMXxJBs+U5HKkOfLd66vQHZ14XREHcEh-7_p-osaccFmQ@mail.gmail.com>
Subject: Re: tid_blockno() and tid_offset() accessor functions
To: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
Cc: pgsql-hackers@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000991466064c8677b0"
Archived-At: 
 <https://www.postgresql.org/message-id/CAE8JnxMXxJBs%2BU5HKkOfLd66vQHZ14XREHcEh-7_p-osaccFmQ%40mail.gmail.com>
Precedence: bulk

--000000000000991466064c8677b0
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

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.
I think it would be nice to also support
SELECT * FROM table WHERE tid_block(tid) BETWEEN b1 AND b2;

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?


Regards

On Sat, Mar 7, 2026 at 7:43=E2=80=AFPM Ayush Tiwari <ayushtiwari.slg01@gmai=
l.com>
wrote:

> Hello,
>
> Attaching a V2-patch post rebasing due to oid conflict with the latest
> main branch. In addition to that changing the sql function name for tid
> block number to tid_block and adding document related changes.
>
> Please review and let me know your thoughts.
>
> Regards,
> Ayush
>
> On Sat, 28 Feb 2026 at 00:29, 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 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`
>>
>> A couple of quick notes on the implementation I went for:
>> - `tid_blockno` returns `int8` since `BlockNumber` is `uint32` and could
>> overflow `int4`.
>> - `tid_offset` returns `int4` since `OffsetNumber` is `uint16`.
>> - Both are marked leakproof and strict.
>> - I used the `NoCheck` macros from `itemptr.h` so they safely handle
>> user-supplied literals like `(0,0)`.
>>
>> Please let me know what you think!
>>
>> Regards,
>> Ayush
>>
>

--000000000000991466064c8677b0
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">That was something I was surprised to learn, that we can c=
heck TID, do queries by TID intervals, but we can&#39;t get pages from TID,=
 when I was trying to analyse how many pages on average a certain query wou=
ld touch for different users.<div>I think it would be nice to also support=
=C2=A0</div><div>SELECT * FROM table WHERE tid_block(tid) BETWEEN b1 AND b2=
;</div><div><br><div>I wouldn&#39;t bother to support block number above 2^=
31 or block offsets above 2^15.</div></div><div><br>This test shows that it=
 assumes wrapping</div><div>-- (-1,0) wraps to blockno 4294967295<br>SELECT=
 tid_block(&#39;(-1,0)&#39;::tid);<br>=C2=A0tid_block =C2=A0<br>-----------=
-<br>=C2=A04294967295<br><br>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.</div><div>for tid_offset I don&#39;t think it is even possible. If th=
e maximum page size is limited to 2^15, must have a header and each offset =
has a line pointer aren&#39;t offsets limited to something smaller than 2^1=
3?<br></div><div><br></div><div><br></div><div>Regards</div></div><br><div =
class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">On Sat, Mar 7, =
2026 at 7:43=E2=80=AFPM Ayush Tiwari &lt;<a href=3D"mailto:ayushtiwari.slg0=
1@gmail.com" target=3D"_blank">ayushtiwari.slg01@gmail.com</a>&gt; wrote:<b=
r></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex=
;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">=
Hello,<br><br>Attaching a V2-patch post rebasing due to oid conflict with t=
he latest main branch. In addition to that changing the sql function name f=
or tid block number to tid_block and adding document related changes.<br><b=
r>Please=C2=A0review and let me know your thoughts.<br><br>Regards,<br>Ayus=
h</div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr"=
>On Sat, 28 Feb 2026 at 00:29, Ayush Tiwari &lt;<a href=3D"mailto:ayushtiwa=
ri.slg01@gmail.com" target=3D"_blank">ayushtiwari.slg01@gmail.com</a>&gt; w=
rote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0p=
x 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=
=3D"ltr">Hi hackers,<br><br>As of now we don&#39;t have any built-in way to=
 extract the block and offset components from a TID. When people need to gr=
oup by page (like for bloat analysis) or filter by specific blocks, they us=
ually end up using the `ctid::text::point` hack:<br><br>=C2=A0 =C2=A0 SELEC=
T (ctid::text::point)[0]::bigint AS blockno,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0=
 =C2=A0 =C2=A0(ctid::text::point)[1]::int =C2=A0 =C2=A0AS offset<br>=C2=A0 =
=C2=A0 FROM my_table;<br><br>This works, but it&#39;s pretty clunky, relies=
 on the text representation, and isn&#39;t great if you&#39;re trying to pa=
rse TIDs outside of SQL.<br><br>The attached patch adds two simple accessor=
 functions:<br>- `tid_blockno(tid) -&gt; bigint`<br>- `tid_offset(tid) -&gt=
; integer`<br><br>A couple of quick notes on the implementation I went for:=
<br>- `tid_blockno` returns `int8` since `BlockNumber` is `uint32` and coul=
d overflow `int4`.<br>- `tid_offset` returns `int4` since `OffsetNumber` is=
 `uint16`.<br>- Both are marked leakproof and strict.<br>- I used the `NoCh=
eck` macros from `itemptr.h` so they safely handle user-supplied literals l=
ike `(0,0)`.<br><br>Please let me know what you think!<br><br>Regards,<br>A=
yush</div>
</blockquote></div>
</blockquote></div>

--000000000000991466064c8677b0--