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 →
  1. Add pg_column_toast_chunk_id().

Attachments

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>