From: Nathan Bossart <nathandbossart@gmail.com>
To: Yugo NAGATA <nagata@sraoss.co.jp>
Cc: jian he <jian.universality@gmail.com>
Cc: Sergei Kornilov <sk@zsrv.org>
Cc: Nikita Malakhov <hukutoc@gmail.com>
Cc: pgsql-hackers@postgresql.org
Subject: Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
Date: Tue, 12 Mar 2024 22:07:17 -0500
Message-ID: <20240313030717.GA2507890@nathanxps13> (raw)
In-Reply-To: <20240312155119.9341cfd76f2732ac93ecb6a4@sraoss.co.jp>
References: <CACJufxGpqVg9j_Fb7aBRwricx1ZTV+vEQfUmwuVpNhrRyvcAdA@mail.gmail.com>
<20240201134524.d647b3d185e5252162eb0436@sraoss.co.jp>
<CACJufxHyDKcB+34JUDFJubO7GkLNNjZXxbf9jSnSdqVKS8T9+g@mail.gmail.com>
<20240205162823.6c705517ca6898e482b69b9e@sraoss.co.jp>
<20240307225617.GA615950@nathanxps13>
<20240308153155.4c4b2fee3ed1431203e41016@sraoss.co.jp>
<20240308221758.GB1060542@nathanxps13>
<20240309115718.deb9209b7f53883cc8e1b4b3@sraoss.co.jp>
<20240309145028.GB1111406@nathanxps13>
<20240312155119.9341cfd76f2732ac93ecb6a4@sraoss.co.jp>
I did some light editing to prepare this for commit.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.comAttachments:
[text/x-diff] v10-0001-Add-pg_column_toast_chunk_id.patch (6.0K, ../20240313030717.GA2507890@nathanxps13/2-v10-0001-Add-pg_column_toast_chunk_id.patch)
download | inline diff:
From 38c78bd92a7b4d4600e6f0dbe58283c21ea87d50 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Tue, 12 Mar 2024 22:04:25 -0500
Subject: [PATCH v10 1/1] Add pg_column_toast_chunk_id().
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 is useful for
identifying which values are actually TOASTed and for investigating
"unexpected chunk number" errors.
XXX: REQUIRES CATVERSION BUMP
Author: Yugo Nagata
Reviewed-by: Jian He
Discussion: https://postgr.es/m/20230329105507.d764497456eeac1ca491b5bd%40sraoss.co.jp
---
doc/src/sgml/func.sgml | 17 ++++++++
src/backend/utils/adt/varlena.c | 41 ++++++++++++++++++++
src/include/catalog/pg_proc.dat | 3 ++
src/test/regress/expected/misc_functions.out | 16 ++++++++
src/test/regress/sql/misc_functions.sql | 12 ++++++
5 files changed, 89 insertions(+)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgmlindex 0bb7aeb40e..fe9c1a38b9 100644--- a/doc/src/sgml/func.sgml+++ b/doc/src/sgml/func.sgml@@ -28552,6 +28552,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"/> for more information 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.cindex 543afb66e5..8d28dd42ce 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 un-TOASTed 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.datindex 291ed876fc..443ca854a6 100644--- a/src/include/catalog/pg_proc.dat+++ b/src/include/catalog/pg_proc.dat@@ -7447,6 +7447,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',
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.outindex d5f61dfad9..e0ba9fdafa 100644--- a/src/test/regress/expected/misc_functions.out+++ b/src/test/regress/expected/misc_functions.out@@ -703,3 +703,19 @@ SELECT has_function_privilege('regress_current_logfile',
(1 row)
DROP ROLE regress_current_logfile;
+-- pg_column_toast_chunk_id+CREATE TABLE test_chunk_id (a TEXT, b TEXT STORAGE EXTERNAL);+INSERT INTO test_chunk_id VALUES ('x', repeat('x', 8192));+SELECT t.relname AS toastrel FROM pg_class c+ LEFT JOIN pg_class t ON c.reltoastrelid = t.oid+ WHERE c.relname = 'test_chunk_id'+\gset+SELECT pg_column_toast_chunk_id(a) IS NULL,+ pg_column_toast_chunk_id(b) IN (SELECT chunk_id FROM pg_toast.:toastrel)+ FROM test_chunk_id;+ ?column? | ?column? +----------+----------+ t | t+(1 row)++DROP TABLE test_chunk_id;diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sqlindex 928b04db7f..ff5ed5edde 100644--- a/src/test/regress/sql/misc_functions.sql+++ b/src/test/regress/sql/misc_functions.sql@@ -265,3 +265,15 @@ GRANT pg_monitor TO regress_current_logfile;
SELECT has_function_privilege('regress_current_logfile',
'pg_current_logfile()', 'EXECUTE');
DROP ROLE regress_current_logfile;
++-- pg_column_toast_chunk_id+CREATE TABLE test_chunk_id (a TEXT, b TEXT STORAGE EXTERNAL);+INSERT INTO test_chunk_id VALUES ('x', repeat('x', 8192));+SELECT t.relname AS toastrel FROM pg_class c+ LEFT JOIN pg_class t ON c.reltoastrelid = t.oid+ WHERE c.relname = 'test_chunk_id'+\gset+SELECT pg_column_toast_chunk_id(a) IS NULL,+ pg_column_toast_chunk_id(b) IN (SELECT chunk_id FROM pg_toast.:toastrel)+ FROM test_chunk_id;+DROP TABLE test_chunk_id;--
2.25.1
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nathandbossart@gmail.com, nagata@sraoss.co.jp, jian.universality@gmail.com, sk@zsrv.org, hukutoc@gmail.com
Subject: Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
In-Reply-To: <20240313030717.GA2507890@nathanxps13>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox