Re: tid_blockno() and tid_offset() accessor functions

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