On Tue, 2 Jan 2024 08:00:00 +0800 jian he <jian.universal...@gmail.com> wrote:
> On Mon, Nov 6, 2023 at 8:00 AM jian he <jian.universal...@gmail.com> wrote: > > > > minor doc issues. > > Returns the chunk id of the TOASTed value, or NULL if the value is not > > TOASTed. > > Should it be "chunk_id"? Thank you for your suggestion. As you pointed out, it is called "chunk_id" in the documentation, so I rewrote it and also added a link to the section where the TOAST table structure is explained. > > you may place it after pg_create_logical_replication_slot entry to > > make it look like alphabetical order. I've been thinking about where we should place the function in the doc, and I decided place it in the table of Database Object Size Functions because I think pg_column_toast_chunk_id also would assist understanding the result of size functions as similar to pg_column_compression; that is, those function can explain why a large value in size could be stored in a column. > > There is no test. maybe we can add following to > > src/test/regress/sql/misc.sql > > create table val(t text); > > INSERT into val(t) SELECT string_agg( > > chr((ascii('B') + round(random() * 25)) :: integer),'') > > FROM generate_series(1,2500); > > select pg_column_toast_chunk_id(t) is not null from val; > > drop table val; Thank you for the test proposal. However, if we add a test, I want to check that the chunk_id returned by the function exists in the TOAST table, and that it returns NULL if the values is not TOASTed. For the purpose, I wrote a test using a dynamic SQL since the table name of the TOAST table have to be generated from the main table's OID. > Hi > the main C function (pg_column_toast_chunk_id) I didn't change. > I added tests as mentioned above. > tests put it on src/test/regress/sql/misc.sql, i hope that's fine. > I placed pg_column_toast_chunk_id in "Table 9.99. Database Object > Location Functions" (below Table 9.98. Database Object Size > Functions). I could not find any change in your patch from my previous patch. Maybe, you attached wrong file. I attached a patch updated based on your review, including the documentation fixes and a test. What do you think about this it? Regards, Yugo Nagata -- Yugo NAGATA <nag...@sraoss.co.jp>
>From 97af1b2300ecf07a34923da87a9d84e6aa963956 Mon Sep 17 00:00:00 2001 From: Yugo Nagata <nag...@sraoss.co.jp> Date: Wed, 29 Mar 2023 09:59:25 +0900 Subject: [PATCH v3] Add pg_column_toast_chunk_id function This function returns the chunk_id of an on-disk TOASTed value, or NULL if the value is un-TOASTed or not on disk. This enables users to know which columns are actually TOASTed. This function is also useful to identify a problematic row when an error like "ERROR: unexpected chunk number ... (expected ...) for toast value" occurs. --- doc/src/sgml/func.sgml | 17 ++++++++++++++ src/backend/utils/adt/varlena.c | 41 +++++++++++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 3 +++ 3 files changed, 61 insertions(+) diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml index 210c7c0b02..2d82331323 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -28078,6 +28078,23 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>pg_column_toast_chunk_id</primary> + </indexterm> + <function>pg_column_toast_chunk_id</function> ( <type>"any"</type> ) + <returnvalue>oid</returnvalue> + </para> + <para> + Shows the <structfield>chunk_id</structfield> of an on-disk + <acronym>TOAST</acronym>ed value. Returns <literal>NULL</literal> + if the value is un-<acronym>TOAST</acronym>ed or not on-disk. + See <xref linkend="storage-toast-ondisk"/> for details about + <acronym>TOAST</acronym>. + </para></entry> + </row> + <row> <entry role="func_table_entry"><para role="func_signature"> <indexterm> diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c index 543afb66e5..84d36781a4 100644 --- a/src/backend/utils/adt/varlena.c +++ b/src/backend/utils/adt/varlena.c @@ -5105,6 +5105,47 @@ pg_column_compression(PG_FUNCTION_ARGS) PG_RETURN_TEXT_P(cstring_to_text(result)); } +/* + * Return the chunk_id of the on-disk TOASTed value. + * Return NULL if the value is unTOASTed or not on disk. + */ +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 58811a6530..1d4521ac1f 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -7454,6 +7454,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 on-disk 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', -- 2.25.1