public inbox for [email protected]
help / color / mirror / Atom feedFrom: Alexandre Felipe <[email protected]>
To: Ayush Tiwari <[email protected]>
Cc: [email protected]
Subject: Re: tid_blockno() and tid_offset() accessor functions
Date: Sun, 8 Mar 2026 17:17:44 +0000
Message-ID: <CAE8JnxMXxJBs+U5HKkOfLd66vQHZ14XREHcEh-7_p-osaccFmQ@mail.gmail.com> (raw)
In-Reply-To: <CAJTYsWVK6rw++WU94=gFk5ZR5ZXhT3BYOFxhm6tT9xOGG-drnA@mail.gmail.com>
References: <CAJTYsWUzok2+mvSYkbVUwq_SWWg-GdHqCuYumN82AU97SjwjCA@mail.gmail.com>
<CAJTYsWVK6rw++WU94=gFk5ZR5ZXhT3BYOFxhm6tT9xOGG-drnA@mail.gmail.com>
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 <[email protected]>
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 <[email protected]>
> 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
>>
>
view thread (18+ messages) latest in thread
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: tid_blockno() and tid_offset() accessor functions
In-Reply-To: <CAE8JnxMXxJBs+U5HKkOfLd66vQHZ14XREHcEh-7_p-osaccFmQ@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox