public inbox for [email protected]
help / color / mirror / Atom feedtid_blockno() and tid_offset() accessor functions
18+ messages / 8 participants
[nested] [flat]
* tid_blockno() and tid_offset() accessor functions
@ 2026-02-27 18:59 Ayush Tiwari <[email protected]>
2026-03-07 19:43 ` Re: tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
2026-03-11 21:48 ` Re: tid_blockno() and tid_offset() accessor functions Masahiko Sawada <[email protected]>
0 siblings, 2 replies; 18+ messages in thread
From: Ayush Tiwari @ 2026-02-27 18:59 UTC (permalink / raw)
To: pgsql-hackers
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
Attachments:
[application/octet-stream] 0001-Add-tid_blockno-and-tid_offset-accessor-functions.patch (7.8K, 3-0001-Add-tid_blockno-and-tid_offset-accessor-functions.patch)
download | inline diff:
From 02b652640e3eebeb575de1a1f36fb5013cdf0585 Mon Sep 17 00:00:00 2001
From: Ayush Tiwari <[email protected]>
Date: Fri, 27 Feb 2026 18:48:11 +0000
Subject: [PATCH v1] Add tid_blockno() and tid_offset() accessor functions
Add two new built-in SQL functions to extract the components of a tid
(tuple identifier) value:
tid_blockno(tid) -> bigint -- extract block number
tid_offset(tid) -> integer -- extract offset number
These provide a clean, efficient alternative to the common workaround
of ctid::text::point for decomposing TID values. The text-based hack
is fragile, inefficient, and unavailable outside of SQL contexts.
tid_blockno() returns int8 (bigint) because BlockNumber is uint32,
which exceeds the range of int4. tid_offset() returns int4 (integer)
because OffsetNumber is uint16, which fits safely in int4.
Both functions use the NoCheck accessor variants from itemptr.h,
are marked leakproof, and include regression tests covering typical
values, boundary conditions, NULL handling, and round-trip identity.
---
src/backend/utils/adt/tid.c | 31 +++++++++++
src/include/catalog/pg_proc.dat | 6 +++
src/test/regress/expected/opr_sanity.out | 2 +
src/test/regress/expected/tid.out | 66 ++++++++++++++++++++++++
src/test/regress/sql/tid.sql | 21 ++++++++
5 files changed, 126 insertions(+)
diff --git a/src/backend/utils/adt/tid.c b/src/backend/utils/adt/tid.c
index 07248b69e57..fbf29afac10 100644
--- a/src/backend/utils/adt/tid.c
+++ b/src/backend/utils/adt/tid.c
@@ -280,6 +280,37 @@ hashtidextended(PG_FUNCTION_ARGS)
}
+/*
+ * TID accessor functions
+ */
+
+/*
+ * tid_blockno - extract the block number from a TID
+ *
+ * Returns int8 because BlockNumber is uint32, which exceeds the range of int4.
+ */
+Datum
+tid_blockno(PG_FUNCTION_ARGS)
+{
+ ItemPointer tid = PG_GETARG_ITEMPOINTER(0);
+
+ PG_RETURN_INT64((int64) ItemPointerGetBlockNumberNoCheck(tid));
+}
+
+/*
+ * tid_offset - extract the offset number from a TID
+ *
+ * Returns int4 because OffsetNumber is uint16, which fits in int4.
+ */
+Datum
+tid_offset(PG_FUNCTION_ARGS)
+{
+ ItemPointer tid = PG_GETARG_ITEMPOINTER(0);
+
+ PG_RETURN_INT32((int32) ItemPointerGetOffsetNumberNoCheck(tid));
+}
+
+
/*
* Functions to get latest tid of a specified tuple.
*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index dac40992cbc..fc0fdc6cd1b 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -2747,6 +2747,12 @@
{ oid => '2796', descr => 'smaller of two',
proname => 'tidsmaller', prorettype => 'tid', proargtypes => 'tid tid',
prosrc => 'tidsmaller' },
+{ oid => '9949', descr => 'extract block number from tid',
+ proname => 'tid_blockno', proleakproof => 't', prorettype => 'int8',
+ proargtypes => 'tid', prosrc => 'tid_blockno' },
+{ oid => '9950', descr => 'extract offset number from tid',
+ proname => 'tid_offset', proleakproof => 't', prorettype => 'int4',
+ proargtypes => 'tid', prosrc => 'tid_offset' },
{ oid => '2233', descr => 'hash',
proname => 'hashtid', prorettype => 'int4', proargtypes => 'tid',
prosrc => 'hashtid' },
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 6ff4d7ee901..005d99058f6 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -887,6 +887,8 @@ oid8le(oid8,oid8)
oid8gt(oid8,oid8)
oid8ge(oid8,oid8)
btoid8cmp(oid8,oid8)
+tid_blockno(tid)
+tid_offset(tid)
-- Check that functions without argument are not marked as leakproof.
SELECT p1.oid::regprocedure
FROM pg_proc p1 JOIN pg_namespace pn
diff --git a/src/test/regress/expected/tid.out b/src/test/regress/expected/tid.out
index 083c83a1e1b..4e21014c81f 100644
--- a/src/test/regress/expected/tid.out
+++ b/src/test/regress/expected/tid.out
@@ -42,6 +42,57 @@ SELECT * FROM pg_input_error_info('(0,-1)', 'tid');
invalid input syntax for type tid: "(0,-1)" | | | 22P02
(1 row)
+-- tests for tid_blockno() and tid_offset()
+SELECT tid_blockno('(0,0)'::tid), tid_offset('(0,0)'::tid);
+ tid_blockno | tid_offset
+-------------+------------
+ 0 | 0
+(1 row)
+
+SELECT tid_blockno('(0,1)'::tid), tid_offset('(0,1)'::tid);
+ tid_blockno | tid_offset
+-------------+------------
+ 0 | 1
+(1 row)
+
+SELECT tid_blockno('(42,7)'::tid), tid_offset('(42,7)'::tid);
+ tid_blockno | tid_offset
+-------------+------------
+ 42 | 7
+(1 row)
+
+-- max values: blockno uint32 max, offset uint16 max
+SELECT tid_blockno('(4294967295,65535)'::tid), tid_offset('(4294967295,65535)'::tid);
+ tid_blockno | tid_offset
+-------------+------------
+ 4294967295 | 65535
+(1 row)
+
+-- (-1,0) wraps to blockno 4294967295
+SELECT tid_blockno('(-1,0)'::tid);
+ tid_blockno
+-------------
+ 4294967295
+(1 row)
+
+-- NULL handling (strict functions)
+SELECT tid_blockno(NULL::tid), tid_offset(NULL::tid);
+ tid_blockno | tid_offset
+-------------+------------
+ |
+(1 row)
+
+-- round-trip: blockno + offset reconstruct the original TID
+SELECT t, tid_blockno(t), tid_offset(t),
+ format('(%s,%s)', tid_blockno(t), tid_offset(t))::tid = t AS roundtrip_ok
+FROM (VALUES ('(0,0)'::tid), ('(1,42)'::tid), ('(4294967295,65535)'::tid)) AS v(t);
+ t | tid_blockno | tid_offset | roundtrip_ok
+--------------------+-------------+------------+--------------
+ (0,0) | 0 | 0 | t
+ (1,42) | 1 | 42 | t
+ (4294967295,65535) | 4294967295 | 65535 | t
+(3 rows)
+
-- tests for functions related to TID handling
CREATE TABLE tid_tab (a int);
-- min() and max() for TIDs
@@ -58,6 +109,21 @@ SELECT max(ctid) FROM tid_tab;
(0,2)
(1 row)
+-- tid_blockno() and tid_offset() with real table ctid
+SELECT ctid, tid_blockno(ctid), tid_offset(ctid) FROM tid_tab;
+ ctid | tid_blockno | tid_offset
+-------+-------------+------------
+ (0,1) | 0 | 1
+ (0,2) | 0 | 2
+(2 rows)
+
+-- use in WHERE clause
+SELECT ctid FROM tid_tab WHERE tid_blockno(ctid) = 0 AND tid_offset(ctid) = 1;
+ ctid
+-------
+ (0,1)
+(1 row)
+
TRUNCATE tid_tab;
-- Tests for currtid2() with various relation kinds
-- Materialized view
diff --git a/src/test/regress/sql/tid.sql b/src/test/regress/sql/tid.sql
index 2602e20eb5a..75370833cd0 100644
--- a/src/test/regress/sql/tid.sql
+++ b/src/test/regress/sql/tid.sql
@@ -16,6 +16,22 @@ SELECT pg_input_is_valid('(0,-1)', 'tid');
SELECT * FROM pg_input_error_info('(0,-1)', 'tid');
+-- tests for tid_blockno() and tid_offset()
+SELECT tid_blockno('(0,0)'::tid), tid_offset('(0,0)'::tid);
+SELECT tid_blockno('(0,1)'::tid), tid_offset('(0,1)'::tid);
+SELECT tid_blockno('(42,7)'::tid), tid_offset('(42,7)'::tid);
+-- max values: blockno uint32 max, offset uint16 max
+SELECT tid_blockno('(4294967295,65535)'::tid), tid_offset('(4294967295,65535)'::tid);
+-- (-1,0) wraps to blockno 4294967295
+SELECT tid_blockno('(-1,0)'::tid);
+-- NULL handling (strict functions)
+SELECT tid_blockno(NULL::tid), tid_offset(NULL::tid);
+-- round-trip: blockno + offset reconstruct the original TID
+SELECT t, tid_blockno(t), tid_offset(t),
+ format('(%s,%s)', tid_blockno(t), tid_offset(t))::tid = t AS roundtrip_ok
+FROM (VALUES ('(0,0)'::tid), ('(1,42)'::tid), ('(4294967295,65535)'::tid)) AS v(t);
+
+
-- tests for functions related to TID handling
CREATE TABLE tid_tab (a int);
@@ -24,6 +40,11 @@ CREATE TABLE tid_tab (a int);
INSERT INTO tid_tab VALUES (1), (2);
SELECT min(ctid) FROM tid_tab;
SELECT max(ctid) FROM tid_tab;
+
+-- tid_blockno() and tid_offset() with real table ctid
+SELECT ctid, tid_blockno(ctid), tid_offset(ctid) FROM tid_tab;
+-- use in WHERE clause
+SELECT ctid FROM tid_tab WHERE tid_blockno(ctid) = 0 AND tid_offset(ctid) = 1;
TRUNCATE tid_tab;
-- Tests for currtid2() with various relation kinds
--
2.45.4
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: tid_blockno() and tid_offset() accessor functions
2026-02-27 18:59 tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
@ 2026-03-07 19:43 ` Ayush Tiwari <[email protected]>
2026-03-08 17:17 ` Re: tid_blockno() and tid_offset() accessor functions Alexandre Felipe <[email protected]>
2026-03-13 16:24 ` Re: tid_blockno() and tid_offset() accessor functions Andres Freund <[email protected]>
1 sibling, 2 replies; 18+ messages in thread
From: Ayush Tiwari @ 2026-03-07 19:43 UTC (permalink / raw)
To: pgsql-hackers
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
>
Attachments:
[application/octet-stream] v2-0001-Add-tid_block-and-tid_offset-accessor-functions.patch (11.1K, 3-v2-0001-Add-tid_block-and-tid_offset-accessor-functions.patch)
download | inline diff:
From 92e3657d85b13355563ba4c447ddf89fcb4c4b3e Mon Sep 17 00:00:00 2001
From: Ayush Tiwari <[email protected]>
Date: Sat, 7 Mar 2026 18:27:36 +0000
Subject: [PATCH v2] Add tid_block() and tid_offset() accessor functions
Add two new built-in SQL functions to extract the components of a tid
(tuple identifier) value:
tid_block(tid) -> bigint -- extract block number
tid_offset(tid) -> integer -- extract offset number
These provide a clean, efficient alternative to the common workaround
of ctid::text::point for decomposing TID values. The text-based hack
is fragile, inefficient, and unavailable outside of SQL contexts.
tid_block() returns int8 (bigint) because BlockNumber is uint32,
which exceeds the range of int4. tid_offset() returns int4 (integer)
because OffsetNumber is uint16, which fits safely in int4.
Both functions use the NoCheck accessor variants from itemptr.h,
are marked leakproof, and include regression tests covering typical
values, boundary conditions, NULL handling, and round-trip identity.
---
doc/src/sgml/func/allfiles.sgml | 1 +
doc/src/sgml/func/func-tid.sgml | 73 ++++++++++++++++++++++++
doc/src/sgml/func/func.sgml | 1 +
src/backend/utils/adt/tid.c | 31 ++++++++++
src/include/catalog/pg_proc.dat | 6 ++
src/test/regress/expected/opr_sanity.out | 2 +
src/test/regress/expected/tid.out | 66 +++++++++++++++++++++
src/test/regress/sql/tid.sql | 21 +++++++
8 files changed, 201 insertions(+)
create mode 100644 doc/src/sgml/func/func-tid.sgml
diff --git a/doc/src/sgml/func/allfiles.sgml b/doc/src/sgml/func/allfiles.sgml
index ce11ef1d5d8..f5e3f008537 100644
--- a/doc/src/sgml/func/allfiles.sgml
+++ b/doc/src/sgml/func/allfiles.sgml
@@ -17,6 +17,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY func-formatting SYSTEM "func-formatting.sgml">
<!ENTITY func-datetime SYSTEM "func-datetime.sgml">
<!ENTITY func-enum SYSTEM "func-enum.sgml">
+<!ENTITY func-tid SYSTEM "func-tid.sgml">
<!ENTITY func-geometry SYSTEM "func-geometry.sgml">
<!ENTITY func-net SYSTEM "func-net.sgml">
<!ENTITY func-textsearch SYSTEM "func-textsearch.sgml">
diff --git a/doc/src/sgml/func/func-tid.sgml b/doc/src/sgml/func/func-tid.sgml
new file mode 100644
index 00000000000..6b3de14d093
--- /dev/null
+++ b/doc/src/sgml/func/func-tid.sgml
@@ -0,0 +1,73 @@
+ <sect1 id="functions-tid">
+ <title>TID Functions</title>
+
+ <indexterm zone="functions-tid">
+ <primary>TID</primary>
+ <secondary>functions</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>tid_block</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>tid_offset</primary>
+ </indexterm>
+
+ <para>
+ For the <type>tid</type> data type (described in <xref
+ linkend="datatype-oid"/>), <xref linkend="functions-tid-table"/>
+ shows the functions available for extracting the block number and
+ tuple offset. These functions are commonly used with the
+ <structfield>ctid</structfield> system column.
+ </para>
+
+ <table id="functions-tid-table">
+ <title><type>TID</type> Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para>
+ <para>
+ Example(s)
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>tid_block</function> ( <type>tid</type> )
+ <returnvalue>bigint</returnvalue>
+ </para>
+ <para>
+ Extracts the block number from a tuple identifier.
+ </para>
+ <para>
+ <literal>tid_block('(42,7)'::tid)</literal>
+ <returnvalue>42</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>tid_offset</function> ( <type>tid</type> )
+ <returnvalue>integer</returnvalue>
+ </para>
+ <para>
+ Extracts the tuple offset within the block from a tuple identifier.
+ </para>
+ <para>
+ <literal>tid_offset('(42,7)'::tid)</literal>
+ <returnvalue>7</returnvalue>
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
\ No newline at end of file
diff --git a/doc/src/sgml/func/func.sgml b/doc/src/sgml/func/func.sgml
index f351ef53f63..3fd21568b2e 100644
--- a/doc/src/sgml/func/func.sgml
+++ b/doc/src/sgml/func/func.sgml
@@ -59,6 +59,7 @@ repeat('Pg', 4) <returnvalue>PgPgPgPg</returnvalue>
&func-formatting;
&func-datetime;
&func-enum;
+&func-tid;
&func-geometry;
&func-net;
&func-textsearch;
diff --git a/src/backend/utils/adt/tid.c b/src/backend/utils/adt/tid.c
index 07248b69e57..d13c7cbe418 100644
--- a/src/backend/utils/adt/tid.c
+++ b/src/backend/utils/adt/tid.c
@@ -280,6 +280,37 @@ hashtidextended(PG_FUNCTION_ARGS)
}
+/*
+ * TID accessor functions
+ */
+
+/*
+ * tid_block - extract the block number from a TID
+ *
+ * Returns int8 because BlockNumber is uint32, which exceeds the range of int4.
+ */
+Datum
+tid_block(PG_FUNCTION_ARGS)
+{
+ ItemPointer tid = PG_GETARG_ITEMPOINTER(0);
+
+ PG_RETURN_INT64((int64) ItemPointerGetBlockNumberNoCheck(tid));
+}
+
+/*
+ * tid_offset - extract the offset number from a TID
+ *
+ * Returns int4 because OffsetNumber is uint16, which fits in int4.
+ */
+Datum
+tid_offset(PG_FUNCTION_ARGS)
+{
+ ItemPointer tid = PG_GETARG_ITEMPOINTER(0);
+
+ PG_RETURN_INT32((int32) ItemPointerGetOffsetNumberNoCheck(tid));
+}
+
+
/*
* Functions to get latest tid of a specified tuple.
*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 361e2cfffeb..08267dd4f81 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -2750,6 +2750,12 @@
{ oid => '2796', descr => 'smaller of two',
proname => 'tidsmaller', prorettype => 'tid', proargtypes => 'tid tid',
prosrc => 'tidsmaller' },
+{ oid => '9951', descr => 'extract block number from tid',
+ proname => 'tid_block', proleakproof => 't', prorettype => 'int8',
+ proargtypes => 'tid', prosrc => 'tid_block' },
+{ oid => '9952', descr => 'extract offset number from tid',
+ proname => 'tid_offset', proleakproof => 't', prorettype => 'int4',
+ proargtypes => 'tid', prosrc => 'tid_offset' },
{ oid => '2233', descr => 'hash',
proname => 'hashtid', prorettype => 'int4', proargtypes => 'tid',
prosrc => 'hashtid' },
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 6ff4d7ee901..cfdc6b1a17a 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -887,6 +887,8 @@ oid8le(oid8,oid8)
oid8gt(oid8,oid8)
oid8ge(oid8,oid8)
btoid8cmp(oid8,oid8)
+tid_block(tid)
+tid_offset(tid)
-- Check that functions without argument are not marked as leakproof.
SELECT p1.oid::regprocedure
FROM pg_proc p1 JOIN pg_namespace pn
diff --git a/src/test/regress/expected/tid.out b/src/test/regress/expected/tid.out
index 083c83a1e1b..3497a77688b 100644
--- a/src/test/regress/expected/tid.out
+++ b/src/test/regress/expected/tid.out
@@ -42,6 +42,57 @@ SELECT * FROM pg_input_error_info('(0,-1)', 'tid');
invalid input syntax for type tid: "(0,-1)" | | | 22P02
(1 row)
+-- tests for tid_block() and tid_offset()
+SELECT tid_block('(0,0)'::tid), tid_offset('(0,0)'::tid);
+ tid_block | tid_offset
+-----------+------------
+ 0 | 0
+(1 row)
+
+SELECT tid_block('(0,1)'::tid), tid_offset('(0,1)'::tid);
+ tid_block | tid_offset
+-----------+------------
+ 0 | 1
+(1 row)
+
+SELECT tid_block('(42,7)'::tid), tid_offset('(42,7)'::tid);
+ tid_block | tid_offset
+-----------+------------
+ 42 | 7
+(1 row)
+
+-- max values: blockno uint32 max, offset uint16 max
+SELECT tid_block('(4294967295,65535)'::tid), tid_offset('(4294967295,65535)'::tid);
+ tid_block | tid_offset
+------------+------------
+ 4294967295 | 65535
+(1 row)
+
+-- (-1,0) wraps to blockno 4294967295
+SELECT tid_block('(-1,0)'::tid);
+ tid_block
+------------
+ 4294967295
+(1 row)
+
+-- NULL handling (strict functions)
+SELECT tid_block(NULL::tid), tid_offset(NULL::tid);
+ tid_block | tid_offset
+-----------+------------
+ |
+(1 row)
+
+-- round-trip: blockno + offset reconstruct the original TID
+SELECT t, tid_block(t), tid_offset(t),
+ format('(%s,%s)', tid_block(t), tid_offset(t))::tid = t AS roundtrip_ok
+FROM (VALUES ('(0,0)'::tid), ('(1,42)'::tid), ('(4294967295,65535)'::tid)) AS v(t);
+ t | tid_block | tid_offset | roundtrip_ok
+--------------------+------------+------------+--------------
+ (0,0) | 0 | 0 | t
+ (1,42) | 1 | 42 | t
+ (4294967295,65535) | 4294967295 | 65535 | t
+(3 rows)
+
-- tests for functions related to TID handling
CREATE TABLE tid_tab (a int);
-- min() and max() for TIDs
@@ -58,6 +109,21 @@ SELECT max(ctid) FROM tid_tab;
(0,2)
(1 row)
+-- tid_block() and tid_offset() with real table ctid
+SELECT ctid, tid_block(ctid), tid_offset(ctid) FROM tid_tab;
+ ctid | tid_block | tid_offset
+-------+-----------+------------
+ (0,1) | 0 | 1
+ (0,2) | 0 | 2
+(2 rows)
+
+-- use in WHERE clause
+SELECT ctid FROM tid_tab WHERE tid_block(ctid) = 0 AND tid_offset(ctid) = 1;
+ ctid
+-------
+ (0,1)
+(1 row)
+
TRUNCATE tid_tab;
-- Tests for currtid2() with various relation kinds
-- Materialized view
diff --git a/src/test/regress/sql/tid.sql b/src/test/regress/sql/tid.sql
index 2602e20eb5a..c0a70be5cbd 100644
--- a/src/test/regress/sql/tid.sql
+++ b/src/test/regress/sql/tid.sql
@@ -16,6 +16,22 @@ SELECT pg_input_is_valid('(0,-1)', 'tid');
SELECT * FROM pg_input_error_info('(0,-1)', 'tid');
+-- tests for tid_block() and tid_offset()
+SELECT tid_block('(0,0)'::tid), tid_offset('(0,0)'::tid);
+SELECT tid_block('(0,1)'::tid), tid_offset('(0,1)'::tid);
+SELECT tid_block('(42,7)'::tid), tid_offset('(42,7)'::tid);
+-- max values: blockno uint32 max, offset uint16 max
+SELECT tid_block('(4294967295,65535)'::tid), tid_offset('(4294967295,65535)'::tid);
+-- (-1,0) wraps to blockno 4294967295
+SELECT tid_block('(-1,0)'::tid);
+-- NULL handling (strict functions)
+SELECT tid_block(NULL::tid), tid_offset(NULL::tid);
+-- round-trip: blockno + offset reconstruct the original TID
+SELECT t, tid_block(t), tid_offset(t),
+ format('(%s,%s)', tid_block(t), tid_offset(t))::tid = t AS roundtrip_ok
+FROM (VALUES ('(0,0)'::tid), ('(1,42)'::tid), ('(4294967295,65535)'::tid)) AS v(t);
+
+
-- tests for functions related to TID handling
CREATE TABLE tid_tab (a int);
@@ -24,6 +40,11 @@ CREATE TABLE tid_tab (a int);
INSERT INTO tid_tab VALUES (1), (2);
SELECT min(ctid) FROM tid_tab;
SELECT max(ctid) FROM tid_tab;
+
+-- tid_block() and tid_offset() with real table ctid
+SELECT ctid, tid_block(ctid), tid_offset(ctid) FROM tid_tab;
+-- use in WHERE clause
+SELECT ctid FROM tid_tab WHERE tid_block(ctid) = 0 AND tid_offset(ctid) = 1;
TRUNCATE tid_tab;
-- Tests for currtid2() with various relation kinds
--
2.43.0
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: tid_blockno() and tid_offset() accessor functions
2026-02-27 18:59 tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
2026-03-07 19:43 ` Re: tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
@ 2026-03-08 17:17 ` Alexandre Felipe <[email protected]>
2026-03-09 07:50 ` Re: tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
2026-03-09 13:34 ` Re: tid_blockno() and tid_offset() accessor functions Greg Sabino Mullane <[email protected]>
1 sibling, 2 replies; 18+ messages in thread
From: Alexandre Felipe @ 2026-03-08 17:17 UTC (permalink / raw)
To: Ayush Tiwari <[email protected]>; +Cc: pgsql-hackers
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
>>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: tid_blockno() and tid_offset() accessor functions
2026-02-27 18:59 tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
2026-03-07 19:43 ` Re: tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
2026-03-08 17:17 ` Re: tid_blockno() and tid_offset() accessor functions Alexandre Felipe <[email protected]>
@ 2026-03-09 07:50 ` Ayush Tiwari <[email protected]>
1 sibling, 0 replies; 18+ messages in thread
From: Ayush Tiwari @ 2026-03-09 07:50 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; +Cc: Alexandre Felipe <[email protected]>; pgsql-hackers
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 >= 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 — (-1,0) correctly shows 4294967295
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 <[email protected]> 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
>
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: tid_blockno() and tid_offset() accessor functions
2026-02-27 18:59 tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
2026-03-07 19:43 ` Re: tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
2026-03-08 17:17 ` Re: tid_blockno() and tid_offset() accessor functions Alexandre Felipe <[email protected]>
@ 2026-03-09 13:34 ` Greg Sabino Mullane <[email protected]>
2026-03-09 14:01 ` Re: tid_blockno() and tid_offset() accessor functions Andres Freund <[email protected]>
1 sibling, 1 reply; 18+ messages in thread
From: Greg Sabino Mullane @ 2026-03-09 13:34 UTC (permalink / raw)
To: Tomas Vondra <[email protected]>; +Cc: Alexandre Felipe <[email protected]>; Ayush Tiwari <[email protected]>; pgsql-hackers
On Sun, Mar 8, 2026 at 3:31 PM Tomas Vondra <[email protected]> wrote:
> 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.
>
This one doesn't sit right with me. I think it's not too late. No reason
why tid_block cannot be stricter here than tid itself and complain. Other
than that, the patch looks good to me.
Cheers,
Greg
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: tid_blockno() and tid_offset() accessor functions
2026-02-27 18:59 tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
2026-03-07 19:43 ` Re: tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
2026-03-08 17:17 ` Re: tid_blockno() and tid_offset() accessor functions Alexandre Felipe <[email protected]>
2026-03-09 13:34 ` Re: tid_blockno() and tid_offset() accessor functions Greg Sabino Mullane <[email protected]>
@ 2026-03-09 14:01 ` Andres Freund <[email protected]>
2026-03-11 13:50 ` Re: tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Andres Freund @ 2026-03-09 14:01 UTC (permalink / raw)
To: Greg Sabino Mullane <[email protected]>; +Cc: Tomas Vondra <[email protected]>; Alexandre Felipe <[email protected]>; Ayush Tiwari <[email protected]>; pgsql-hackers
Hi,
On 2026-03-09 09:34:46 -0400, Greg Sabino Mullane wrote:
> On Sun, Mar 8, 2026 at 3:31 PM Tomas Vondra <[email protected]> wrote:
>
> > 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.
> >
>
> This one doesn't sit right with me. I think it's not too late. No reason
> why tid_block cannot be stricter here than tid itself and complain. Other
> than that, the patch looks good to me.
I don't see any advantage in that. These functions are useful for inspecting
tid values that come from some source. When would you *ever* gain *anything*
from not being able to see the block / offset of a tid datum that you already
have?
This isn't an end user focused type / set of accessor functions were being
particularly careful about input validation will perhaps prevent users from
making mistakes...
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: tid_blockno() and tid_offset() accessor functions
2026-02-27 18:59 tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
2026-03-07 19:43 ` Re: tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
2026-03-08 17:17 ` Re: tid_blockno() and tid_offset() accessor functions Alexandre Felipe <[email protected]>
2026-03-09 13:34 ` Re: tid_blockno() and tid_offset() accessor functions Greg Sabino Mullane <[email protected]>
2026-03-09 14:01 ` Re: tid_blockno() and tid_offset() accessor functions Andres Freund <[email protected]>
@ 2026-03-11 13:50 ` Ayush Tiwari <[email protected]>
0 siblings, 0 replies; 18+ messages in thread
From: Ayush Tiwari @ 2026-03-11 13:50 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Greg Sabino Mullane <[email protected]>; Tomas Vondra <[email protected]>; Alexandre Felipe <[email protected]>; pgsql-hackers
Hi,
Thank you all for the reviews and discussion!
On the strictness question raised by Greg — I agree with Andres here. These
functions are meant for inspecting tid values that already exist, so
rejecting "impossible" values like (-1,0) would not be providing any real
benefit. I believe the tid input function is the appropriate place for any
validation, and these assessors should just faithfully report what's in the
datum.
Regards,
Ayush
On Mon, 9 Mar 2026 at 19:32, Andres Freund <[email protected]> wrote:
> Hi,
>
> On 2026-03-09 09:34:46 -0400, Greg Sabino Mullane wrote:
> > On Sun, Mar 8, 2026 at 3:31 PM Tomas Vondra <[email protected]> wrote:
> >
> > > 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.
> > >
> >
> > This one doesn't sit right with me. I think it's not too late. No reason
> > why tid_block cannot be stricter here than tid itself and complain. Other
> > than that, the patch looks good to me.
>
> I don't see any advantage in that. These functions are useful for
> inspecting
> tid values that come from some source. When would you *ever* gain
> *anything*
> from not being able to see the block / offset of a tid datum that you
> already
> have?
>
> This isn't an end user focused type / set of accessor functions were being
> particularly careful about input validation will perhaps prevent users from
> making mistakes...
>
> Greetings,
>
> Andres Freund
>
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: tid_blockno() and tid_offset() accessor functions
2026-02-27 18:59 tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
2026-03-07 19:43 ` Re: tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
@ 2026-03-13 16:24 ` Andres Freund <[email protected]>
1 sibling, 0 replies; 18+ messages in thread
From: Andres Freund @ 2026-03-13 16:24 UTC (permalink / raw)
To: Ayush Tiwari <[email protected]>; +Cc: pgsql-hackers
Hi,
On 2026-03-08 01:13:13 +0530, Ayush Tiwari wrote:
> From 92e3657d85b13355563ba4c447ddf89fcb4c4b3e Mon Sep 17 00:00:00 2001
> From: Ayush Tiwari <[email protected]>
> Date: Sat, 7 Mar 2026 18:27:36 +0000
> Subject: [PATCH v2] Add tid_block() and tid_offset() accessor functions
>
> Add two new built-in SQL functions to extract the components of a tid
> (tuple identifier) value:
>
> tid_block(tid) -> bigint -- extract block number
> tid_offset(tid) -> integer -- extract offset number
>
> These provide a clean, efficient alternative to the common workaround
> of ctid::text::point for decomposing TID values. The text-based hack
> is fragile, inefficient, and unavailable outside of SQL contexts.
>
> tid_block() returns int8 (bigint) because BlockNumber is uint32,
> which exceeds the range of int4. tid_offset() returns int4 (integer)
> because OffsetNumber is uint16, which fits safely in int4.
>
> Both functions use the NoCheck accessor variants from itemptr.h,
> are marked leakproof, and include regression tests covering typical
> values, boundary conditions, NULL handling, and round-trip identity.
> +++ b/doc/src/sgml/func/func-tid.sgml
> @@ -0,0 +1,73 @@
> + <sect1 id="functions-tid">
> + <title>TID Functions</title>
> +
> + <indexterm zone="functions-tid">
> + <primary>TID</primary>
> + <secondary>functions</secondary>
> + </indexterm>
> +
> + <indexterm>
> + <primary>tid_block</primary>
> + </indexterm>
> +
> + <indexterm>
> + <primary>tid_offset</primary>
> + </indexterm>
> +
> + <para>
> + For the <type>tid</type> data type (described in <xref
> + linkend="datatype-oid"/>), <xref linkend="functions-tid-table"/>
Seems odd to reference the datatype-oid, that's barely mentioning the tid type
and tid is not an oid like type either (like e.g. regtype is).
> + shows the functions available for extracting the block number and
> + tuple offset. These functions are commonly used with the
> + <structfield>ctid</structfield> system column.
> + </para>
I know some other places do that too, but "shows the functions" sounds odd to
me. I'd just say "These are listed in ..." or such.
I'd remove the "available for extracting the block number and tuple offset",
as that's bound to become inaccurate and just restates the table contents.
> --- a/doc/src/sgml/func/func.sgml
> +++ b/doc/src/sgml/func/func.sgml
> @@ -59,6 +59,7 @@ repeat('Pg', 4) <returnvalue>PgPgPgPg</returnvalue>
> &func-formatting;
> &func-datetime;
> &func-enum;
> +&func-tid;
> &func-geometry;
> &func-net;
> &func-textsearch;
I'd add it somewhere more alphabetically fitting. Unfortunately the list isn't
fully ordered right now, but no need to make it even worse...
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: tid_blockno() and tid_offset() accessor functions
2026-02-27 18:59 tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
@ 2026-03-11 21:48 ` Masahiko Sawada <[email protected]>
2026-03-11 21:50 ` Re: tid_blockno() and tid_offset() accessor functions Andres Freund <[email protected]>
1 sibling, 1 reply; 18+ messages in thread
From: Masahiko Sawada @ 2026-03-11 21:48 UTC (permalink / raw)
To: Ayush Tiwari <[email protected]>; +Cc: pgsql-hackers
On Fri, Feb 27, 2026 at 10:59 AM 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`
How about adding the subscripting support for tid data type? For
example, ctid[0] returns bigint and ctid[1] returns int.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: tid_blockno() and tid_offset() accessor functions
2026-02-27 18:59 tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
2026-03-11 21:48 ` Re: tid_blockno() and tid_offset() accessor functions Masahiko Sawada <[email protected]>
@ 2026-03-11 21:50 ` Andres Freund <[email protected]>
2026-03-12 16:51 ` Re: tid_blockno() and tid_offset() accessor functions Masahiko Sawada <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Andres Freund @ 2026-03-11 21:50 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; +Cc: Ayush Tiwari <[email protected]>; pgsql-hackers
Hi,
On 2026-03-11 14:48:08 -0700, Masahiko Sawada wrote:
> On Fri, Feb 27, 2026 at 10:59 AM 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`
>
> How about adding the subscripting support for tid data type? For
> example, ctid[0] returns bigint and ctid[1] returns int.
That just seems less readable and harder to find to me. I think it'd also
make the amount of required code noticeably larger?
Greetings,
Andres
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: tid_blockno() and tid_offset() accessor functions
2026-02-27 18:59 tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
2026-03-11 21:48 ` Re: tid_blockno() and tid_offset() accessor functions Masahiko Sawada <[email protected]>
2026-03-11 21:50 ` Re: tid_blockno() and tid_offset() accessor functions Andres Freund <[email protected]>
@ 2026-03-12 16:51 ` Masahiko Sawada <[email protected]>
2026-03-13 13:27 ` Re: tid_blockno() and tid_offset() accessor functions Peter Eisentraut <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Masahiko Sawada @ 2026-03-12 16:51 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Ayush Tiwari <[email protected]>; pgsql-hackers
On Wed, Mar 11, 2026 at 2:50 PM Andres Freund <[email protected]> wrote:
>
> Hi,
>
> On 2026-03-11 14:48:08 -0700, Masahiko Sawada wrote:
> > On Fri, Feb 27, 2026 at 10:59 AM 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`
> >
> > How about adding the subscripting support for tid data type? For
> > example, ctid[0] returns bigint and ctid[1] returns int.
>
> That just seems less readable and harder to find to me. I think it'd also
> make the amount of required code noticeably larger?
Yeah, using the dedicated functions would be more intuitive than using
magic numbers 1 and 2, and require less code.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: tid_blockno() and tid_offset() accessor functions
2026-02-27 18:59 tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
2026-03-11 21:48 ` Re: tid_blockno() and tid_offset() accessor functions Masahiko Sawada <[email protected]>
2026-03-11 21:50 ` Re: tid_blockno() and tid_offset() accessor functions Andres Freund <[email protected]>
2026-03-12 16:51 ` Re: tid_blockno() and tid_offset() accessor functions Masahiko Sawada <[email protected]>
@ 2026-03-13 13:27 ` Peter Eisentraut <[email protected]>
2026-03-13 17:08 ` Re: tid_blockno() and tid_offset() accessor functions Matthias van de Meent <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Peter Eisentraut @ 2026-03-13 13:27 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; Andres Freund <[email protected]>; +Cc: Ayush Tiwari <[email protected]>; pgsql-hackers
On 12.03.26 17:51, Masahiko Sawada wrote:
> On Wed, Mar 11, 2026 at 2:50 PM Andres Freund <[email protected]> wrote:
>>
>> Hi,
>>
>> On 2026-03-11 14:48:08 -0700, Masahiko Sawada wrote:
>>> On Fri, Feb 27, 2026 at 10:59 AM 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`
>>>
>>> How about adding the subscripting support for tid data type? For
>>> example, ctid[0] returns bigint and ctid[1] returns int.
>>
>> That just seems less readable and harder to find to me. I think it'd also
>> make the amount of required code noticeably larger?
>
> Yeah, using the dedicated functions would be more intuitive than using
> magic numbers 1 and 2, and require less code.
Also, you can use one-argument functions like field names, like
tid.tid_blockno, so it's definitely more intuitive that way.
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: tid_blockno() and tid_offset() accessor functions
2026-02-27 18:59 tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
2026-03-11 21:48 ` Re: tid_blockno() and tid_offset() accessor functions Masahiko Sawada <[email protected]>
2026-03-11 21:50 ` Re: tid_blockno() and tid_offset() accessor functions Andres Freund <[email protected]>
2026-03-12 16:51 ` Re: tid_blockno() and tid_offset() accessor functions Masahiko Sawada <[email protected]>
2026-03-13 13:27 ` Re: tid_blockno() and tid_offset() accessor functions Peter Eisentraut <[email protected]>
@ 2026-03-13 17:08 ` Matthias van de Meent <[email protected]>
2026-03-13 17:18 ` Re: tid_blockno() and tid_offset() accessor functions Greg Sabino Mullane <[email protected]>
2026-03-13 17:54 ` Re: tid_blockno() and tid_offset() accessor functions Andres Freund <[email protected]>
0 siblings, 2 replies; 18+ messages in thread
From: Matthias van de Meent @ 2026-03-13 17:08 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Masahiko Sawada <[email protected]>; Andres Freund <[email protected]>; Ayush Tiwari <[email protected]>; pgsql-hackers
On Fri, 13 Mar 2026 at 14:27, Peter Eisentraut <[email protected]> wrote:
>
> On 12.03.26 17:51, Masahiko Sawada wrote:
> > On Wed, Mar 11, 2026 at 2:50 PM Andres Freund <[email protected]> wrote:
> >>
> >> Hi,
> >>
> >> On 2026-03-11 14:48:08 -0700, Masahiko Sawada wrote:
> >>> On Fri, Feb 27, 2026 at 10:59 AM 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`
> >>>
> >>> How about adding the subscripting support for tid data type? For
> >>> example, ctid[0] returns bigint and ctid[1] returns int.
> >>
> >> That just seems less readable and harder to find to me. I think it'd also
> >> make the amount of required code noticeably larger?
> >
> > Yeah, using the dedicated functions would be more intuitive than using
> > magic numbers 1 and 2, and require less code.
>
> Also, you can use one-argument functions like field names, like
> tid.tid_blockno, so it's definitely more intuitive that way.
TIL.
As for naming; I'd personally prefer to have 'heap' included in the
names here (e.g. heaptid_blkno(tid) or heap_blkno[_of](tid)), because
not all AMs may map tid.blkno exactly to a block number in the main
fork. While PostgreSQL (in core) currently only knows about the heap
AM, we should probably keep clear of pretending that all tableAMs
produce TIDs that behave exactly like heap's do.
Matthias van de Meent
Databricks (https://www.databricks.com)
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: tid_blockno() and tid_offset() accessor functions
2026-02-27 18:59 tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
2026-03-11 21:48 ` Re: tid_blockno() and tid_offset() accessor functions Masahiko Sawada <[email protected]>
2026-03-11 21:50 ` Re: tid_blockno() and tid_offset() accessor functions Andres Freund <[email protected]>
2026-03-12 16:51 ` Re: tid_blockno() and tid_offset() accessor functions Masahiko Sawada <[email protected]>
2026-03-13 13:27 ` Re: tid_blockno() and tid_offset() accessor functions Peter Eisentraut <[email protected]>
2026-03-13 17:08 ` Re: tid_blockno() and tid_offset() accessor functions Matthias van de Meent <[email protected]>
@ 2026-03-13 17:18 ` Greg Sabino Mullane <[email protected]>
1 sibling, 0 replies; 18+ messages in thread
From: Greg Sabino Mullane @ 2026-03-13 17:18 UTC (permalink / raw)
To: Matthias van de Meent <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; Masahiko Sawada <[email protected]>; Andres Freund <[email protected]>; Ayush Tiwari <[email protected]>; pgsql-hackers
On Fri, Mar 13, 2026 at 1:08 PM Matthias van de Meent <
[email protected]> wrote:
> As for naming; I'd personally prefer to have 'heap' included in the names
> here (e.g. heaptid_blkno(tid) or heap_blkno[_of](tid)), because
> not all AMs may map tid.blkno exactly to a block number in the main fork.
All our docs and code mention blocks and offsets as the components of a
tid, so -1 on making things more obtuse.
--
Cheers,
Greg
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: tid_blockno() and tid_offset() accessor functions
2026-02-27 18:59 tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
2026-03-11 21:48 ` Re: tid_blockno() and tid_offset() accessor functions Masahiko Sawada <[email protected]>
2026-03-11 21:50 ` Re: tid_blockno() and tid_offset() accessor functions Andres Freund <[email protected]>
2026-03-12 16:51 ` Re: tid_blockno() and tid_offset() accessor functions Masahiko Sawada <[email protected]>
2026-03-13 13:27 ` Re: tid_blockno() and tid_offset() accessor functions Peter Eisentraut <[email protected]>
2026-03-13 17:08 ` Re: tid_blockno() and tid_offset() accessor functions Matthias van de Meent <[email protected]>
@ 2026-03-13 17:54 ` Andres Freund <[email protected]>
2026-03-14 09:01 ` Re: tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
1 sibling, 1 reply; 18+ messages in thread
From: Andres Freund @ 2026-03-13 17:54 UTC (permalink / raw)
To: Matthias van de Meent <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; Masahiko Sawada <[email protected]>; Ayush Tiwari <[email protected]>; pgsql-hackers
Hi,
On 2026-03-13 18:08:04 +0100, Matthias van de Meent wrote:
> As for naming; I'd personally prefer to have 'heap' included in the
> names here (e.g. heaptid_blkno(tid) or heap_blkno[_of](tid)), because
> not all AMs may map tid.blkno exactly to a block number in the main
> fork. While PostgreSQL (in core) currently only knows about the heap
> AM, we should probably keep clear of pretending that all tableAMs
> produce TIDs that behave exactly like heap's do.
Meh. As long as tids themselves are split like they are, without any
variability of the amount of space dedicated for either component, I don't see
any advantage in that.
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: tid_blockno() and tid_offset() accessor functions
2026-02-27 18:59 tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
2026-03-11 21:48 ` Re: tid_blockno() and tid_offset() accessor functions Masahiko Sawada <[email protected]>
2026-03-11 21:50 ` Re: tid_blockno() and tid_offset() accessor functions Andres Freund <[email protected]>
2026-03-12 16:51 ` Re: tid_blockno() and tid_offset() accessor functions Masahiko Sawada <[email protected]>
2026-03-13 13:27 ` Re: tid_blockno() and tid_offset() accessor functions Peter Eisentraut <[email protected]>
2026-03-13 17:08 ` Re: tid_blockno() and tid_offset() accessor functions Matthias van de Meent <[email protected]>
2026-03-13 17:54 ` Re: tid_blockno() and tid_offset() accessor functions Andres Freund <[email protected]>
@ 2026-03-14 09:01 ` Ayush Tiwari <[email protected]>
2026-04-05 19:30 ` Re: tid_blockno() and tid_offset() accessor functions Andres Freund <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Ayush Tiwari @ 2026-03-14 09:01 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Matthias van de Meent <[email protected]>; Peter Eisentraut <[email protected]>; Masahiko Sawada <[email protected]>; pgsql-hackers
Hi,
Thanks for the review!
Attaching a patch with all document changes, removed the cross-reference to
datatype-oid entirely. I've moved the &func-tid; entry in func.sgml to
directly follow &func-textsearch;, which fits better alphabetically, and
reworded the introductory paragraph to be much more concise, directly
pointing to the table.
Regards,
Ayush
On Fri, 13 Mar 2026 at 23:24, Andres Freund <[email protected]> wrote:
> Hi,
>
> On 2026-03-13 18:08:04 +0100, Matthias van de Meent wrote:
> > As for naming; I'd personally prefer to have 'heap' included in the
> > names here (e.g. heaptid_blkno(tid) or heap_blkno[_of](tid)), because
> > not all AMs may map tid.blkno exactly to a block number in the main
> > fork. While PostgreSQL (in core) currently only knows about the heap
> > AM, we should probably keep clear of pretending that all tableAMs
> > produce TIDs that behave exactly like heap's do.
>
> Meh. As long as tids themselves are split like they are, without any
> variability of the amount of space dedicated for either component, I don't
> see
> any advantage in that.
>
> Greetings,
>
> Andres Freund
>
Attachments:
[application/octet-stream] v3-0001-Add-tid_block-and-tid_offset-accessor-functions.patch (10.9K, 3-v3-0001-Add-tid_block-and-tid_offset-accessor-functions.patch)
download | inline diff:
From 9adfbf6d36320a8bebcef9b1c5383f13878025b5 Mon Sep 17 00:00:00 2001
From: Ayush Tiwari <[email protected]>
Date: Sat, 14 Mar 2026 08:51:25 +0000
Subject: [PATCH v3] Add tid_block() and tid_offset() accessor functions
Add two new built-in SQL functions to extract the components of a tid
(tuple identifier) value:
tid_block(tid) -> bigint -- extract block number
tid_offset(tid) -> integer -- extract offset number
These provide a clean, efficient alternative to the common workaround
of ctid::text::point for decomposing TID values. The text-based hack
is fragile, inefficient, and unavailable outside of SQL contexts.
tid_block() returns int8 (bigint) because BlockNumber is uint32,
which exceeds the range of int4. tid_offset() returns int4 (integer)
because OffsetNumber is uint16, which fits safely in int4.
Both functions use the NoCheck accessor variants from itemptr.h,
are marked leakproof, and include regression tests covering typical
values, boundary conditions, NULL handling, and round-trip identity.
---
doc/src/sgml/func/allfiles.sgml | 1 +
doc/src/sgml/func/func-tid.sgml | 70 ++++++++++++++++++++++++
doc/src/sgml/func/func.sgml | 1 +
src/backend/utils/adt/tid.c | 31 +++++++++++
src/include/catalog/pg_proc.dat | 6 ++
src/test/regress/expected/opr_sanity.out | 2 +
src/test/regress/expected/tid.out | 66 ++++++++++++++++++++++
src/test/regress/sql/tid.sql | 21 +++++++
8 files changed, 198 insertions(+)
create mode 100644 doc/src/sgml/func/func-tid.sgml
diff --git a/doc/src/sgml/func/allfiles.sgml b/doc/src/sgml/func/allfiles.sgml
index ce11ef1d5d8..f5e3f008537 100644
--- a/doc/src/sgml/func/allfiles.sgml
+++ b/doc/src/sgml/func/allfiles.sgml
@@ -17,6 +17,7 @@ Complete list of usable sgml source files in this directory.
<!ENTITY func-formatting SYSTEM "func-formatting.sgml">
<!ENTITY func-datetime SYSTEM "func-datetime.sgml">
<!ENTITY func-enum SYSTEM "func-enum.sgml">
+<!ENTITY func-tid SYSTEM "func-tid.sgml">
<!ENTITY func-geometry SYSTEM "func-geometry.sgml">
<!ENTITY func-net SYSTEM "func-net.sgml">
<!ENTITY func-textsearch SYSTEM "func-textsearch.sgml">
diff --git a/doc/src/sgml/func/func-tid.sgml b/doc/src/sgml/func/func-tid.sgml
new file mode 100644
index 00000000000..bcd3060087e
--- /dev/null
+++ b/doc/src/sgml/func/func-tid.sgml
@@ -0,0 +1,70 @@
+ <sect1 id="functions-tid">
+ <title>TID Functions</title>
+
+ <indexterm zone="functions-tid">
+ <primary>TID</primary>
+ <secondary>functions</secondary>
+ </indexterm>
+
+ <indexterm>
+ <primary>tid_block</primary>
+ </indexterm>
+
+ <indexterm>
+ <primary>tid_offset</primary>
+ </indexterm>
+
+ <para>
+ <xref linkend="functions-tid-table"/> lists functions for
+ the <type>tid</type> data type (tuple identifier).
+ </para>
+
+ <table id="functions-tid-table">
+ <title><type>TID</type> Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ Function
+ </para>
+ <para>
+ Description
+ </para>
+ <para>
+ Example(s)
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>tid_block</function> ( <type>tid</type> )
+ <returnvalue>bigint</returnvalue>
+ </para>
+ <para>
+ Extracts the block number from a tuple identifier.
+ </para>
+ <para>
+ <literal>tid_block('(42,7)'::tid)</literal>
+ <returnvalue>42</returnvalue>
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <function>tid_offset</function> ( <type>tid</type> )
+ <returnvalue>integer</returnvalue>
+ </para>
+ <para>
+ Extracts the tuple offset within the block from a tuple identifier.
+ </para>
+ <para>
+ <literal>tid_offset('(42,7)'::tid)</literal>
+ <returnvalue>7</returnvalue>
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
\ No newline at end of file
diff --git a/doc/src/sgml/func/func.sgml b/doc/src/sgml/func/func.sgml
index f351ef53f63..c9c231dd190 100644
--- a/doc/src/sgml/func/func.sgml
+++ b/doc/src/sgml/func/func.sgml
@@ -62,6 +62,7 @@ repeat('Pg', 4) <returnvalue>PgPgPgPg</returnvalue>
&func-geometry;
&func-net;
&func-textsearch;
+&func-tid;
&func-uuid;
&func-xml;
&func-json;
diff --git a/src/backend/utils/adt/tid.c b/src/backend/utils/adt/tid.c
index 07248b69e57..d13c7cbe418 100644
--- a/src/backend/utils/adt/tid.c
+++ b/src/backend/utils/adt/tid.c
@@ -280,6 +280,37 @@ hashtidextended(PG_FUNCTION_ARGS)
}
+/*
+ * TID accessor functions
+ */
+
+/*
+ * tid_block - extract the block number from a TID
+ *
+ * Returns int8 because BlockNumber is uint32, which exceeds the range of int4.
+ */
+Datum
+tid_block(PG_FUNCTION_ARGS)
+{
+ ItemPointer tid = PG_GETARG_ITEMPOINTER(0);
+
+ PG_RETURN_INT64((int64) ItemPointerGetBlockNumberNoCheck(tid));
+}
+
+/*
+ * tid_offset - extract the offset number from a TID
+ *
+ * Returns int4 because OffsetNumber is uint16, which fits in int4.
+ */
+Datum
+tid_offset(PG_FUNCTION_ARGS)
+{
+ ItemPointer tid = PG_GETARG_ITEMPOINTER(0);
+
+ PG_RETURN_INT32((int32) ItemPointerGetOffsetNumberNoCheck(tid));
+}
+
+
/*
* Functions to get latest tid of a specified tuple.
*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 361e2cfffeb..08267dd4f81 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -2750,6 +2750,12 @@
{ oid => '2796', descr => 'smaller of two',
proname => 'tidsmaller', prorettype => 'tid', proargtypes => 'tid tid',
prosrc => 'tidsmaller' },
+{ oid => '9951', descr => 'extract block number from tid',
+ proname => 'tid_block', proleakproof => 't', prorettype => 'int8',
+ proargtypes => 'tid', prosrc => 'tid_block' },
+{ oid => '9952', descr => 'extract offset number from tid',
+ proname => 'tid_offset', proleakproof => 't', prorettype => 'int4',
+ proargtypes => 'tid', prosrc => 'tid_offset' },
{ oid => '2233', descr => 'hash',
proname => 'hashtid', prorettype => 'int4', proargtypes => 'tid',
prosrc => 'hashtid' },
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 6ff4d7ee901..cfdc6b1a17a 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -887,6 +887,8 @@ oid8le(oid8,oid8)
oid8gt(oid8,oid8)
oid8ge(oid8,oid8)
btoid8cmp(oid8,oid8)
+tid_block(tid)
+tid_offset(tid)
-- Check that functions without argument are not marked as leakproof.
SELECT p1.oid::regprocedure
FROM pg_proc p1 JOIN pg_namespace pn
diff --git a/src/test/regress/expected/tid.out b/src/test/regress/expected/tid.out
index 083c83a1e1b..3497a77688b 100644
--- a/src/test/regress/expected/tid.out
+++ b/src/test/regress/expected/tid.out
@@ -42,6 +42,57 @@ SELECT * FROM pg_input_error_info('(0,-1)', 'tid');
invalid input syntax for type tid: "(0,-1)" | | | 22P02
(1 row)
+-- tests for tid_block() and tid_offset()
+SELECT tid_block('(0,0)'::tid), tid_offset('(0,0)'::tid);
+ tid_block | tid_offset
+-----------+------------
+ 0 | 0
+(1 row)
+
+SELECT tid_block('(0,1)'::tid), tid_offset('(0,1)'::tid);
+ tid_block | tid_offset
+-----------+------------
+ 0 | 1
+(1 row)
+
+SELECT tid_block('(42,7)'::tid), tid_offset('(42,7)'::tid);
+ tid_block | tid_offset
+-----------+------------
+ 42 | 7
+(1 row)
+
+-- max values: blockno uint32 max, offset uint16 max
+SELECT tid_block('(4294967295,65535)'::tid), tid_offset('(4294967295,65535)'::tid);
+ tid_block | tid_offset
+------------+------------
+ 4294967295 | 65535
+(1 row)
+
+-- (-1,0) wraps to blockno 4294967295
+SELECT tid_block('(-1,0)'::tid);
+ tid_block
+------------
+ 4294967295
+(1 row)
+
+-- NULL handling (strict functions)
+SELECT tid_block(NULL::tid), tid_offset(NULL::tid);
+ tid_block | tid_offset
+-----------+------------
+ |
+(1 row)
+
+-- round-trip: blockno + offset reconstruct the original TID
+SELECT t, tid_block(t), tid_offset(t),
+ format('(%s,%s)', tid_block(t), tid_offset(t))::tid = t AS roundtrip_ok
+FROM (VALUES ('(0,0)'::tid), ('(1,42)'::tid), ('(4294967295,65535)'::tid)) AS v(t);
+ t | tid_block | tid_offset | roundtrip_ok
+--------------------+------------+------------+--------------
+ (0,0) | 0 | 0 | t
+ (1,42) | 1 | 42 | t
+ (4294967295,65535) | 4294967295 | 65535 | t
+(3 rows)
+
-- tests for functions related to TID handling
CREATE TABLE tid_tab (a int);
-- min() and max() for TIDs
@@ -58,6 +109,21 @@ SELECT max(ctid) FROM tid_tab;
(0,2)
(1 row)
+-- tid_block() and tid_offset() with real table ctid
+SELECT ctid, tid_block(ctid), tid_offset(ctid) FROM tid_tab;
+ ctid | tid_block | tid_offset
+-------+-----------+------------
+ (0,1) | 0 | 1
+ (0,2) | 0 | 2
+(2 rows)
+
+-- use in WHERE clause
+SELECT ctid FROM tid_tab WHERE tid_block(ctid) = 0 AND tid_offset(ctid) = 1;
+ ctid
+-------
+ (0,1)
+(1 row)
+
TRUNCATE tid_tab;
-- Tests for currtid2() with various relation kinds
-- Materialized view
diff --git a/src/test/regress/sql/tid.sql b/src/test/regress/sql/tid.sql
index 2602e20eb5a..c0a70be5cbd 100644
--- a/src/test/regress/sql/tid.sql
+++ b/src/test/regress/sql/tid.sql
@@ -16,6 +16,22 @@ SELECT pg_input_is_valid('(0,-1)', 'tid');
SELECT * FROM pg_input_error_info('(0,-1)', 'tid');
+-- tests for tid_block() and tid_offset()
+SELECT tid_block('(0,0)'::tid), tid_offset('(0,0)'::tid);
+SELECT tid_block('(0,1)'::tid), tid_offset('(0,1)'::tid);
+SELECT tid_block('(42,7)'::tid), tid_offset('(42,7)'::tid);
+-- max values: blockno uint32 max, offset uint16 max
+SELECT tid_block('(4294967295,65535)'::tid), tid_offset('(4294967295,65535)'::tid);
+-- (-1,0) wraps to blockno 4294967295
+SELECT tid_block('(-1,0)'::tid);
+-- NULL handling (strict functions)
+SELECT tid_block(NULL::tid), tid_offset(NULL::tid);
+-- round-trip: blockno + offset reconstruct the original TID
+SELECT t, tid_block(t), tid_offset(t),
+ format('(%s,%s)', tid_block(t), tid_offset(t))::tid = t AS roundtrip_ok
+FROM (VALUES ('(0,0)'::tid), ('(1,42)'::tid), ('(4294967295,65535)'::tid)) AS v(t);
+
+
-- tests for functions related to TID handling
CREATE TABLE tid_tab (a int);
@@ -24,6 +40,11 @@ CREATE TABLE tid_tab (a int);
INSERT INTO tid_tab VALUES (1), (2);
SELECT min(ctid) FROM tid_tab;
SELECT max(ctid) FROM tid_tab;
+
+-- tid_block() and tid_offset() with real table ctid
+SELECT ctid, tid_block(ctid), tid_offset(ctid) FROM tid_tab;
+-- use in WHERE clause
+SELECT ctid FROM tid_tab WHERE tid_block(ctid) = 0 AND tid_offset(ctid) = 1;
TRUNCATE tid_tab;
-- Tests for currtid2() with various relation kinds
--
2.43.0
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: tid_blockno() and tid_offset() accessor functions
2026-02-27 18:59 tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
2026-03-11 21:48 ` Re: tid_blockno() and tid_offset() accessor functions Masahiko Sawada <[email protected]>
2026-03-11 21:50 ` Re: tid_blockno() and tid_offset() accessor functions Andres Freund <[email protected]>
2026-03-12 16:51 ` Re: tid_blockno() and tid_offset() accessor functions Masahiko Sawada <[email protected]>
2026-03-13 13:27 ` Re: tid_blockno() and tid_offset() accessor functions Peter Eisentraut <[email protected]>
2026-03-13 17:08 ` Re: tid_blockno() and tid_offset() accessor functions Matthias van de Meent <[email protected]>
2026-03-13 17:54 ` Re: tid_blockno() and tid_offset() accessor functions Andres Freund <[email protected]>
2026-03-14 09:01 ` Re: tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
@ 2026-04-05 19:30 ` Andres Freund <[email protected]>
2026-04-05 19:44 ` Re: tid_blockno() and tid_offset() accessor functions Álvaro Herrera <[email protected]>
0 siblings, 1 reply; 18+ messages in thread
From: Andres Freund @ 2026-04-05 19:30 UTC (permalink / raw)
To: Ayush Tiwari <[email protected]>; +Cc: Matthias van de Meent <[email protected]>; Peter Eisentraut <[email protected]>; Masahiko Sawada <[email protected]>; pgsql-hackers
Hi,
On 2026-03-14 14:31:32 +0530, Ayush Tiwari wrote:
> Attaching a patch with all document changes, removed the cross-reference to
> datatype-oid entirely. I've moved the &func-tid; entry in func.sgml to
> directly follow &func-textsearch;, which fits better alphabetically, and
> reworded the introductory paragraph to be much more concise, directly
> pointing to the table.
Pushed this after making some small changes (removed some IMO unnecessary
comments, replaced <type>TID</type> with <acronym/>, as TID is not the type's
name, ...).
I was reminded of this thread while in the process of doing some performance
validation that required getting all the block numbers from an index scan,
which required me to remember (ctid::text::point)[0], which I find to be very
ugly.
Closed the CF entry.
Thanks for the patch!
Greetings,
Andres Freund
^ permalink raw reply [nested|flat] 18+ messages in thread
* Re: tid_blockno() and tid_offset() accessor functions
2026-02-27 18:59 tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
2026-03-11 21:48 ` Re: tid_blockno() and tid_offset() accessor functions Masahiko Sawada <[email protected]>
2026-03-11 21:50 ` Re: tid_blockno() and tid_offset() accessor functions Andres Freund <[email protected]>
2026-03-12 16:51 ` Re: tid_blockno() and tid_offset() accessor functions Masahiko Sawada <[email protected]>
2026-03-13 13:27 ` Re: tid_blockno() and tid_offset() accessor functions Peter Eisentraut <[email protected]>
2026-03-13 17:08 ` Re: tid_blockno() and tid_offset() accessor functions Matthias van de Meent <[email protected]>
2026-03-13 17:54 ` Re: tid_blockno() and tid_offset() accessor functions Andres Freund <[email protected]>
2026-03-14 09:01 ` Re: tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
2026-04-05 19:30 ` Re: tid_blockno() and tid_offset() accessor functions Andres Freund <[email protected]>
@ 2026-04-05 19:44 ` Álvaro Herrera <[email protected]>
0 siblings, 0 replies; 18+ messages in thread
From: Álvaro Herrera @ 2026-04-05 19:44 UTC (permalink / raw)
To: Andres Freund <[email protected]>; +Cc: Ayush Tiwari <[email protected]>; Matthias van de Meent <[email protected]>; Peter Eisentraut <[email protected]>; Masahiko Sawada <[email protected]>; pgsql-hackers
Hello
On 2026-Apr-05, Andres Freund wrote:
> I was reminded of this thread while in the process of doing some performance
> validation that required getting all the block numbers from an index scan,
> which required me to remember (ctid::text::point)[0], which I find to be very
> ugly.
Thanks for this patch! I agree that the statu quo was sort of embarrasing :-)
Regards
--
Álvaro Herrera 48°01'N 7°57'E — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 18+ messages in thread
end of thread, other threads:[~2026-04-05 19:44 UTC | newest]
Thread overview: 18+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-02-27 18:59 tid_blockno() and tid_offset() accessor functions Ayush Tiwari <[email protected]>
2026-03-07 19:43 ` Ayush Tiwari <[email protected]>
2026-03-08 17:17 ` Alexandre Felipe <[email protected]>
2026-03-09 07:50 ` Ayush Tiwari <[email protected]>
2026-03-09 13:34 ` Greg Sabino Mullane <[email protected]>
2026-03-09 14:01 ` Andres Freund <[email protected]>
2026-03-11 13:50 ` Ayush Tiwari <[email protected]>
2026-03-13 16:24 ` Andres Freund <[email protected]>
2026-03-11 21:48 ` Masahiko Sawada <[email protected]>
2026-03-11 21:50 ` Andres Freund <[email protected]>
2026-03-12 16:51 ` Masahiko Sawada <[email protected]>
2026-03-13 13:27 ` Peter Eisentraut <[email protected]>
2026-03-13 17:08 ` Matthias van de Meent <[email protected]>
2026-03-13 17:18 ` Greg Sabino Mullane <[email protected]>
2026-03-13 17:54 ` Andres Freund <[email protected]>
2026-03-14 09:01 ` Ayush Tiwari <[email protected]>
2026-04-05 19:30 ` Andres Freund <[email protected]>
2026-04-05 19:44 ` Álvaro Herrera <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox