pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
Yugo Nagata <nagata@sraoss.co.jp>
From: Yugo NAGATA <nagata@sraoss.co.jp>
To: pgsql-hackers@postgresql.org
Date: 2023-03-29T01:55:07Z
Lists: pgsql-hackers
Commits
Same data as JSON:
GET /api/v1/messages/:b64id/commits
the thread's linked commits as JSON, with link sources.
API reference →
-
Add pg_column_toast_chunk_id().
- d1162cfda885 17.0 landed
Attachments
- pg_column_toast_chunk_id.patch (text/x-diff) patch
Hello,
Attached patch introduces a function pg_column_toast_chunk_id
that returns a chunk ID of a TOASTed value.
Recently, one of our clients needed a way to show which columns
are actually TOASTed because they would like to know how much
updates on the original table affects to its toast table
specifically with regard to auto VACUUM. We could not find a
function for this purpose in the current PostgreSQL, so I would
like propose pg_column_toast_chunk_id.
This function returns a chunk ID of a TOASTed value, or NULL
if the value is not TOASTed. Here is an example;
postgres=# \d val
Table "public.val"
Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
t | text | | |
postgres=# select length(t), pg_column_size(t), pg_column_compression(t), pg_column_toast_chunk_id(t), tableoid from val;
length | pg_column_size | pg_column_compression | pg_column_toast_chunk_id | tableoid
--------+----------------+-----------------------+--------------------------+----------
3 | 4 | | | 16388
3000 | 46 | pglz | | 16388
32000 | 413 | pglz | | 16388
305 | 309 | | | 16388
64000 | 64000 | | 16393 | 16388
(5 rows)
postgres=# select chunk_id, chunk_seq from pg_toast.pg_toast_16388;
chunk_id | chunk_seq
----------+-----------
16393 | 0
16393 | 1
16393 | 2
(snip)
16393 | 30
16393 | 31
16393 | 32
(33 rows)
This function is also useful to identify a problematic row when
an error like
"ERROR: unexpected chunk number ... (expected ...) for toast value"
occurs.
The patch is a just a concept patch and not including documentation
and tests.
What do you think about this feature?
Regards,
Yugo Nagata
--
Yugo NAGATA <nagata@sraoss.co.jp>