Re: tid_blockno() and tid_offset() accessor functions

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 PM Ayush Tiwari <ayushtiwari(dot)slg01(at)gmail(dot)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(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`
>>
>> 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
>>
>