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 <nag...@sraoss.co.jp>
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c index 5778e3f0ef..4ddcf885cd 100644 --- a/src/backend/utils/adt/varlena.c +++ b/src/backend/utils/adt/varlena.c @@ -5070,6 +5070,47 @@ pg_column_compression(PG_FUNCTION_ARGS) PG_RETURN_TEXT_P(cstring_to_text(result)); } +/* + * Return the chunk id of the TOASTed value. + * Return NULL for unTOASTed value. + */ +Datum +pg_column_toast_chunk_id(PG_FUNCTION_ARGS) +{ + int typlen; + struct varlena *attr; + struct varatt_external toast_pointer; + + /* On first call, get the input type's typlen, and save at *fn_extra */ + if (fcinfo->flinfo->fn_extra == NULL) + { + /* Lookup the datatype of the supplied argument */ + Oid argtypeid = get_fn_expr_argtype(fcinfo->flinfo, 0); + + typlen = get_typlen(argtypeid); + if (typlen == 0) /* should not happen */ + elog(ERROR, "cache lookup failed for type %u", argtypeid); + + fcinfo->flinfo->fn_extra = MemoryContextAlloc(fcinfo->flinfo->fn_mcxt, + sizeof(int)); + *((int *) fcinfo->flinfo->fn_extra) = typlen; + } + else + typlen = *((int *) fcinfo->flinfo->fn_extra); + + if (typlen != -1) + PG_RETURN_NULL(); + + attr = (struct varlena *) DatumGetPointer(PG_GETARG_DATUM(0)); + + if (!VARATT_IS_EXTERNAL_ONDISK(attr)) + PG_RETURN_NULL(); + + VARATT_EXTERNAL_GET_POINTER(toast_pointer, attr); + + PG_RETURN_OID(toast_pointer.va_valueid); +} + /* * string_agg - Concatenates values and returns string. * diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index 7c358cff16..4c214f4c63 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -7414,6 +7414,9 @@ { oid => '2121', descr => 'compression method for the compressed datum', proname => 'pg_column_compression', provolatile => 's', prorettype => 'text', proargtypes => 'any', prosrc => 'pg_column_compression' }, +{ oid => '8393', descr => 'chunk ID of TOASTed value', + proname => 'pg_column_toast_chunk_id', provolatile => 's', prorettype => 'oid', + proargtypes => 'any', prosrc => 'pg_column_toast_chunk_id' }, { oid => '2322', descr => 'total disk space usage for the specified tablespace', proname => 'pg_tablespace_size', provolatile => 'v', prorettype => 'int8',