agora inbox for [email protected]help / color / mirror / Atom feed
[PATCH v6 05/10] doc: s/evade/avoid/ 8+ messages / 2 participants [nested] [flat]
* [PATCH v6 05/10] doc: s/evade/avoid/ @ 2020-04-06 22:16 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Justin Pryzby @ 2020-04-06 22:16 UTC (permalink / raw) --- src/backend/access/gin/README | 2 +- src/backend/utils/adt/jsonpath_exec.c | 2 +- 2 files changed, 2 insertions(+), 2 deletions(-) diff --git a/src/backend/access/gin/README b/src/backend/access/gin/README index 125a82219b..41d4e1e8a0 100644 --- a/src/backend/access/gin/README +++ b/src/backend/access/gin/README @@ -413,7 +413,7 @@ leftmost leaf of the tree. Deletion algorithm keeps exclusive locks on left siblings of pages comprising currently investigated path. Thus, if current page is to be removed, all required pages to remove both downlink and rightlink are already locked. That -evades potential right to left page locking order, which could deadlock with +avoids potential right to left page locking order, which could deadlock with concurrent stepping right. A search concurrent to page deletion might already have read a pointer to the diff --git a/src/backend/utils/adt/jsonpath_exec.c b/src/backend/utils/adt/jsonpath_exec.c index f146767bfc..2d2eb7d7a3 100644 --- a/src/backend/utils/adt/jsonpath_exec.c +++ b/src/backend/utils/adt/jsonpath_exec.c @@ -35,7 +35,7 @@ * executeItemOptUnwrapTarget() function have 'unwrap' argument, which indicates * whether unwrapping of array is needed. When unwrap == true, each of array * members is passed to executeItemOptUnwrapTarget() again but with unwrap == false - * in order to evade subsequent array unwrapping. + * in order to avoid subsequent array unwrapping. * * All boolean expressions (predicates) are evaluated by executeBoolItem() * function, which returns tri-state JsonPathBool. When error is occurred -- 2.17.0 --C+ts3FVlLX8+P6JN Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v6-0006-doc-Add-logical-replication-support-to-replicate-.patch" ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v6] Add pg_column_toast_chunk_id function @ 2023-03-29 00:59 Yugo Nagata <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Yugo Nagata @ 2023-03-29 00:59 UTC (permalink / raw) 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 6788ba8ef4..4116aaff7a 100644 --- a/doc/src/sgml/func.sgml +++ b/doc/src/sgml/func.sgml @@ -28502,6 +28502,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 29af4ce65d..9ab71646e3 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', diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out index 7c15477104..2d11f33fe6 100644 --- a/src/test/regress/expected/misc_functions.out +++ b/src/test/regress/expected/misc_functions.out @@ -683,3 +683,24 @@ SELECT gist_stratnum_identity(18::smallint); 18 (1 row) +-- 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', 2048)); +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 851dad90f4..7ab95a939e 100644 --- a/src/test/regress/sql/misc_functions.sql +++ b/src/test/regress/sql/misc_functions.sql @@ -254,3 +254,26 @@ FROM pg_walfile_name_offset('0/0'::pg_lsn + :segment_size - 1), -- test stratnum support functions SELECT gist_stratnum_identity(3::smallint); SELECT gist_stratnum_identity(18::smallint); + +-- 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', 2048)); + +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 --Multipart=_Thu__1_Feb_2024_13_45_24_+0900_Ax2kH1zymNzqiq2p-- ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v3] Add pg_column_toast_chunk_id function @ 2023-03-29 00:59 Yugo Nagata <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Yugo Nagata @ 2023-03-29 00:59 UTC (permalink / raw) 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 --Multipart=_Fri__26_Jan_2024_09_42_37_+0900_2+5eHmzdBJSefc3G-- ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v3] Add pg_column_toast_chunk_id function @ 2023-03-29 00:59 Yugo Nagata <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Yugo Nagata @ 2023-03-29 00:59 UTC (permalink / raw) 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 --Multipart=_Tue__30_Jan_2024_13_34_59_+0900_U/4uJiLxKkpoYH3v-- ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v7] Add pg_column_toast_chunk_id function @ 2023-03-29 00:59 Yugo Nagata <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Yugo Nagata @ 2023-03-29 00:59 UTC (permalink / raw) 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..a26f1e4ea0 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', 2048)); +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..f31f0897cd 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', 2048)); + +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 --Multipart=_Fri__8_Mar_2024_15_31_55_+0900_FC4Wz6zx./GJq1PU-- ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v8] Add pg_column_toast_chunk_id function @ 2023-03-29 00:59 Yugo Nagata <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Yugo Nagata @ 2023-03-29 00:59 UTC (permalink / raw) 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 | 27 +++++++++++++ src/test/regress/sql/misc_functions.sql | 24 ++++++++++++ 5 files changed, 112 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..722a01d726 100644 --- a/src/test/regress/expected/misc_functions.out +++ b/src/test/regress/expected/misc_functions.out @@ -703,3 +703,30 @@ 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 +SHOW block_size; \gset + block_size +------------ + 8192 +(1 row) + +INSERT INTO test_chunk_id(v1,v2) + VALUES (repeat('x', 1), repeat('x', (:block_size / 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..ad26d339ea 100644 --- a/src/test/regress/sql/misc_functions.sql +++ b/src/test/regress/sql/misc_functions.sql @@ -265,3 +265,27 @@ 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 +SHOW block_size; \gset +INSERT INTO test_chunk_id(v1,v2) + VALUES (repeat('x', 1), repeat('x', (:block_size / 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 --Multipart=_Sat__9_Mar_2024_11_57_18_+0900_jc6Fk34U744p9+qD-- ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v4] Add pg_column_toast_chunk_id function @ 2023-03-29 00:59 Yugo Nagata <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Yugo Nagata @ 2023-03-29 00:59 UTC (permalink / raw) 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 | 18 +++++++++ src/test/regress/sql/misc_functions.sql | 18 +++++++++ 5 files changed, 97 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', diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out index 9302134077..6a1fcc22f5 100644 --- a/src/test/regress/expected/misc_functions.out +++ b/src/test/regress/expected/misc_functions.out @@ -670,3 +670,21 @@ FROM pg_walfile_name_offset('0/0'::pg_lsn + :segment_size - 1), 0 | t (1 row) +-- 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); +INSERT INTO test_chunk_id VALUES ( + repeat('0123456789', 10), -- v1: small enough not to be TOASTed + repeat('0123456789', 100000)); -- v2: large enough to be TOASTed +DO $$ + DECLARE result text; + 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 d3dc591173..fe3b6c26c6 100644 --- a/src/test/regress/sql/misc_functions.sql +++ b/src/test/regress/sql/misc_functions.sql @@ -250,3 +250,21 @@ FROM pg_walfile_name_offset('0/0'::pg_lsn + :segment_size + 1), SELECT segment_number, file_offset = :segment_size - 1 FROM pg_walfile_name_offset('0/0'::pg_lsn + :segment_size - 1), pg_split_walfile_name(file_name); + +-- 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); +INSERT INTO test_chunk_id VALUES ( + repeat('0123456789', 10), -- v1: small enough not to be TOASTed + repeat('0123456789', 100000)); -- v2: large enough to be TOASTed +DO $$ + DECLARE result text; + 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 --Multipart=_Tue__30_Jan_2024_14_56_32_+0900_GDTJPGQgwtk.ZxyB-- ^ permalink raw reply [nested|flat] 8+ messages in thread
* [PATCH v9] Add pg_column_toast_chunk_id function @ 2023-03-29 00:59 Yugo Nagata <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Yugo Nagata @ 2023-03-29 00:59 UTC (permalink / raw) 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 --Multipart=_Tue__12_Mar_2024_15_51_19_+0700__bpvqBZp1Bbzr9VU-- ^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-03-29 00:59 UTC | newest] Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2020-04-06 22:16 [PATCH v6 05/10] doc: s/evade/avoid/ Justin Pryzby <[email protected]> 2023-03-29 00:59 [PATCH v6] Add pg_column_toast_chunk_id function Yugo Nagata <[email protected]> 2023-03-29 00:59 [PATCH v3] Add pg_column_toast_chunk_id function Yugo Nagata <[email protected]> 2023-03-29 00:59 [PATCH v3] Add pg_column_toast_chunk_id function Yugo Nagata <[email protected]> 2023-03-29 00:59 [PATCH v7] Add pg_column_toast_chunk_id function Yugo Nagata <[email protected]> 2023-03-29 00:59 [PATCH v8] Add pg_column_toast_chunk_id function Yugo Nagata <[email protected]> 2023-03-29 00:59 [PATCH v4] Add pg_column_toast_chunk_id function Yugo Nagata <[email protected]> 2023-03-29 00:59 [PATCH v9] Add pg_column_toast_chunk_id function Yugo Nagata <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox