On Sat, 9 Mar 2024 08:50:28 -0600
Nathan Bossart <nathandboss...@gmail.com> wrote:
> On Sat, Mar 09, 2024 at 11:57:18AM +0900, Yugo NAGATA wrote:
> > On Fri, 8 Mar 2024 16:17:58 -0600
> > Nathan Bossart <nathandboss...@gmail.com> wrote:
> >> Is this guaranteed to be TOASTed for all possible page sizes?
> >
> > Should we use block_size?
> >
> > SHOW block_size \gset
> > INSERT INTO test_chunk_id(v1,v2)
> > VALUES (repeat('x', 1), repeat('x', (:block_size / 4)));
> >
> > I think this will work in various page sizes.
>
> WFM
>
> > +SHOW block_size; \gset
> > + block_size
> > +------------
> > + 8192
> > +(1 row)
>
> I think we need to remove the ';' so that the output of the query is not
> saved in the ".out" file. With that change, this test passes when Postgres
> is built with --with-blocksize=32. However, many other unrelated tests
> begin failing, so I guess this fix isn't tremendously important.
I rewrote the patch to use current_setting('block_size') instead of SHOW
and \gset as other tests do. Although some tests are failing with block_size=32,
I wonder it is a bit better to use "block_size" instead of the constant
to make the test more general to some extent.
Regards,
Yugo Nagata
>
> --
> Nathan Bossart
> Amazon Web Services: https://aws.amazon.com
--
Yugo NAGATA <nag...@sraoss.co.jp>
>From 5b3be1ca6f8d8bafc1d9ce7bea252f364c9c09a9 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 v9] 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 ++
src/test/regress/expected/misc_functions.out | 21 ++++++++++
src/test/regress/sql/misc_functions.sql | 23 +++++++++++
5 files changed, 105 insertions(+)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 0bb7aeb40e..3169e2aeb7 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-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 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.out
index d5f61dfad9..ff25e967e2 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -703,3 +703,24 @@ SELECT has_function_privilege('regress_current_logfile',
(1 row)
DROP ROLE regress_current_logfile;
+-- Test pg_column_toast_chunk_id:
+-- Check if the returned chunk_id exists in the TOAST table
+CREATE TABLE test_chunk_id (v1 text, v2 text);
+-- Use uncompressed toast data
+ALTER TABLE test_chunk_id ALTER COLUMN v2 SET STORAGE EXTERNAL;
+-- v1: small enough not to be TOASTed
+-- v2: large enough to be TOASTed
+INSERT INTO test_chunk_id(v1,v2)
+ VALUES (repeat('x', 1), repeat('x', current_setting('block_size')::int / 4));
+DO $$
+ DECLARE result text default 'not_ok';
+ BEGIN
+ EXECUTE format(
+ 'SELECT ''ok'' FROM test_chunk_id
+ WHERE pg_column_toast_chunk_id(v1) IS NULL AND
+ pg_column_toast_chunk_id(v2) IN (SELECT chunk_id FROM pg_toast.pg_toast_%s)',
+ regclass('test_chunk_id')::int::text) INTO result;
+ RAISE INFO '%', result;
+END; $$;
+INFO: ok
+DROP TABLE test_chunk_id;
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index 928b04db7f..840f3564a1 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -265,3 +265,26 @@ GRANT pg_monitor TO regress_current_logfile;
SELECT has_function_privilege('regress_current_logfile',
'pg_current_logfile()', 'EXECUTE');
DROP ROLE regress_current_logfile;
+
+-- Test pg_column_toast_chunk_id:
+-- Check if the returned chunk_id exists in the TOAST table
+CREATE TABLE test_chunk_id (v1 text, v2 text);
+-- Use uncompressed toast data
+ALTER TABLE test_chunk_id ALTER COLUMN v2 SET STORAGE EXTERNAL;
+
+-- v1: small enough not to be TOASTed
+-- v2: large enough to be TOASTed
+INSERT INTO test_chunk_id(v1,v2)
+ VALUES (repeat('x', 1), repeat('x', current_setting('block_size')::int / 4));
+
+DO $$
+ DECLARE result text default 'not_ok';
+ BEGIN
+ EXECUTE format(
+ 'SELECT ''ok'' FROM test_chunk_id
+ WHERE pg_column_toast_chunk_id(v1) IS NULL AND
+ pg_column_toast_chunk_id(v2) IN (SELECT chunk_id FROM pg_toast.pg_toast_%s)',
+ regclass('test_chunk_id')::int::text) INTO result;
+ RAISE INFO '%', result;
+END; $$;
+DROP TABLE test_chunk_id;
--
2.25.1
