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