MIME-Version: 1.0
References: 
 <CAJTYsWUzok2+mvSYkbVUwq_SWWg-GdHqCuYumN82AU97SjwjCA@mail.gmail.com>
 <CAJTYsWVK6rw++WU94=gFk5ZR5ZXhT3BYOFxhm6tT9xOGG-drnA@mail.gmail.com>
 <CAE8JnxMXxJBs+U5HKkOfLd66vQHZ14XREHcEh-7_p-osaccFmQ@mail.gmail.com>
 <57388743-e380-4145-8b77-86ed23b062de@vondra.me>
In-Reply-To: <57388743-e380-4145-8b77-86ed23b062de@vondra.me>
From: Ayush Tiwari <ayushtiwari.slg01@gmail.com>
Date: Mon, 9 Mar 2026 13:20:13 +0530
Message-ID: 
 <CAJTYsWW0tQcRp6FTeOkTscoMytJ4=GEa4YTQyKJw62KL3fbrVA@mail.gmail.com>
Subject: Re: tid_blockno() and tid_offset() accessor functions
To: Tomas Vondra <tomas@vondra.me>
Cc: Alexandre Felipe <o.alexandre.felipe@gmail.com>,
 pgsql-hackers@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000c72ecc064c92a76a"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJTYsWW0tQcRp6FTeOkTscoMytJ4%3DGEa4YTQyKJw62KL3fbrVA%40mail.gmail.com>
Precedence: bulk

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

Hello,

Thanks for the review!

On the return types: I chose int8 for tid_block() deliberately because
BlockNumber is uint32. If we used int4, block numbers >=3D 2^31 would
silently appear as negative values, which seems worse than using the wider
type. PostgreSQL already uses bigint to represent uint32 values in other
catalog/system functions (e.g., pg_control_checkpoint). The wrapping test
actually demonstrates exactly this =E2=80=94 (-1,0) correctly shows 4294967=
295
rather than -1.

For tid_offset(), int4 is the natural safe mapping for uint16
(OffsetNumber). You're right that practical offsets are well below 2^13,
but int4 costs nothing extra and is consistent.

Happy to hear other opinions on the type choices though!

Regards,
Ayush

On Mon, 9 Mar 2026 at 01:01, Tomas Vondra <tomas@vondra.me> wrote:

> 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
>
>

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

<div dir=3D"ltr">Hello,<br><br>Thanks for the review!<br><br>On the return =
types: I chose int8 for tid_block() deliberately because BlockNumber is uin=
t32. If we used int4, block numbers &gt;=3D 2^31 would silently appear as n=
egative values, which seems worse than using the wider type. PostgreSQL alr=
eady uses bigint to represent uint32 values in other catalog/system functio=
ns (e.g., pg_control_checkpoint). The wrapping test actually demonstrates e=
xactly this =E2=80=94 (-1,0) correctly shows 4294967295 rather than -1.<br>=
<br>For tid_offset(), int4 is the natural safe mapping for uint16 (OffsetNu=
mber). You&#39;re right that practical offsets are well below 2^13, but int=
4 costs nothing extra and is consistent.<br><br>Happy to hear other opinion=
s on the type choices though!<br><br>Regards,<br>Ayush</div><br><div class=
=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail_attr=
">On Mon, 9 Mar 2026 at 01:01, Tomas Vondra &lt;<a href=3D"mailto:tomas@von=
dra.me">tomas@vondra.me</a>&gt; wrote:<br></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">On 3/8/26 18:17, Alexandre Felipe wrote:<br>
&gt; That was something I was surprised to learn, that we can check TID, do=
<br>
&gt; queries by TID intervals, but we can&#39;t get pages from TID, when I =
was<br>
&gt; trying to analyse how many pages on average a certain query would touc=
h<br>
&gt; for different users.<br>
<br>
True. The conversion to &quot;point&quot; is the traditional way to do this=
, but<br>
having functions to access the fields is cleared I think.<br>
<br>
&gt; I think it would be nice to also support=C2=A0<br>
&gt; SELECT * FROM table WHERE tid_block(tid) BETWEEN b1 AND b2;<br>
&gt; <br>
<br>
Not sure. Functions are opaque for the scan, i.e. it can&#39;t treat it as =
a<br>
scan key easily, because it could do anything. So this would require<br>
teaching the TidScan that &quot;tid_block&quot; is a special case.<br>
<br>
I believe this should be doable through &quot;support procedures&quot;, whi=
ch can<br>
be attached to pg_proc entries. So tid_block would have a &quot;prosupport&=
quot;<br>
pointing at a function, implementing SupportRequestIndexCondition. Which<br=
>
would translate the clause on tid_block() to a range condition on the<br>
underlying tid.<br>
<br>
For inspiration see starts_with(), and text_starts_with_support support<br>
procedure (or rather like_regex_support).<br>
<br>
However, that seems out of scope for this initial patch.<br>
<br>
&gt; I wouldn&#39;t bother to support block number above 2^31 or block offs=
ets<br>
&gt; above 2^15.<br>
&gt; <br>
&gt; This test shows that it assumes wrapping<br>
&gt; -- (-1,0) wraps to blockno 4294967295<br>
&gt; SELECT tid_block(&#39;(-1,0)&#39;::tid);<br>
&gt; =C2=A0tid_block =C2=A0<br>
&gt; ------------<br>
&gt; =C2=A04294967295<br>
&gt; <br>
&gt; You could just stick with that, I am sure that someone with a table<br=
>
&gt; having more than 2B pages on a table will understand that.<br>
&gt; for tid_offset I don&#39;t think it is even possible. If the maximum p=
age<br>
&gt; size is limited to 2^15, must have a header and each offset has a line=
<br>
&gt; pointer aren&#39;t offsets limited to something smaller than 2^13?<br>
&gt; <br>
<br>
No opinion. For displaying the bogus TID value (like &quot;(-1,0)&quot;) it=
&#39;s<br>
probably OK to show values that are a bit weird. If anything, we should<br>
be more careful on input, it&#39;s too late for tid_block() to decide what<=
br>
to do with an &quot;impossible&quot; TID value.<br>
<br>
regards<br>
<br>
-- <br>
Tomas Vondra<br>
<br>
</blockquote></div>

--000000000000c72ecc064c92a76a--