agora inbox for [email protected]help / color / mirror / Atom feed
[PATCH 5/8] (re)add wal_compression_method=lz4 8+ messages / 2 participants [nested] [flat]
* [PATCH 5/8] (re)add wal_compression_method=lz4 @ 2021-03-12 20:32 Justin Pryzby <[email protected]> 0 siblings, 0 replies; 8+ messages in thread From: Justin Pryzby @ 2021-03-12 20:32 UTC (permalink / raw) --- configure | 176 ++++++++++++++++++++++++ configure.ac | 26 ++++ src/backend/access/transam/xlog.c | 3 + src/backend/access/transam/xloginsert.c | 12 ++ src/backend/access/transam/xlogreader.c | 12 ++ src/include/access/xlog_internal.h | 1 + src/include/pg_config.h.in | 3 + src/tools/msvc/Solution.pm | 1 + 8 files changed, 234 insertions(+) diff --git a/configure b/configure index fad817bb38..9b590eb432 100755 --- a/configure +++ b/configure @@ -699,6 +699,9 @@ with_gnu_ld LD LDFLAGS_SL LDFLAGS_EX +LZ4_LIBS +LZ4_CFLAGS +with_lz4 with_zlib with_system_tzdata with_libxslt @@ -864,6 +867,7 @@ with_libxml with_libxslt with_system_tzdata with_zlib +with_lz4 with_gnu_ld with_ssl with_openssl @@ -891,6 +895,8 @@ ICU_LIBS XML2_CONFIG XML2_CFLAGS XML2_LIBS +LZ4_CFLAGS +LZ4_LIBS LDFLAGS_EX LDFLAGS_SL PERL @@ -1569,6 +1575,7 @@ Optional Packages: --with-system-tzdata=DIR use system time zone data in DIR --without-zlib do not use Zlib + --with-lz4 build with LZ4 support --with-gnu-ld assume the C compiler uses GNU ld [default=no] --with-ssl=LIB use LIB for SSL/TLS support (openssl) --with-openssl obsolete spelling of --with-ssl=openssl @@ -1596,6 +1603,8 @@ Some influential environment variables: XML2_CONFIG path to xml2-config utility XML2_CFLAGS C compiler flags for XML2, overriding pkg-config XML2_LIBS linker flags for XML2, overriding pkg-config + LZ4_CFLAGS C compiler flags for LZ4, overriding pkg-config + LZ4_LIBS linker flags for LZ4, overriding pkg-config LDFLAGS_EX extra linker flags for linking executables only LDFLAGS_SL extra linker flags for linking shared libraries only PERL Perl program @@ -8563,6 +8572,137 @@ fi +# +# LZ4 +# +{ $as_echo "$as_me:${as_lineno-$LINENO}: checking whether to build with LZ4 support" >&5 +$as_echo_n "checking whether to build with LZ4 support... " >&6; } + + + +# Check whether --with-lz4 was given. +if test "${with_lz4+set}" = set; then : + withval=$with_lz4; + case $withval in + yes) + +$as_echo "#define USE_LZ4 1" >>confdefs.h + + ;; + no) + : + ;; + *) + as_fn_error $? "no argument expected for --with-lz4 option" "$LINENO" 5 + ;; + esac + +else + with_lz4=no + +fi + + +{ $as_echo "$as_me:${as_lineno-$LINENO}: result: $with_lz4" >&5 +$as_echo "$with_lz4" >&6; } + + +if test "$with_lz4" = yes; then + +pkg_failed=no +{ $as_echo "$as_me:${as_lineno-$LINENO}: checking for liblz4" >&5 +$as_echo_n "checking for liblz4... " >&6; } + +if test -n "$LZ4_CFLAGS"; then + pkg_cv_LZ4_CFLAGS="$LZ4_CFLAGS" + elif test -n "$PKG_CONFIG"; then + if test -n "$PKG_CONFIG" && \ + { { $as_echo "$as_me:${as_lineno-$LINENO}: \$PKG_CONFIG --exists --print-errors \"liblz4\""; } >&5 + ($PKG_CONFIG --exists --print-errors "liblz4") 2>&5 + ac_status=$? + $as_echo "$as_me:${as_lineno-$LINENO}: \$? = $ac_status" >&5 + test $ac_status = 0; }; then + pkg_cv_LZ4_CFLAGS=`$PKG_CONFIG --cflags "liblz4" 2>/dev/null` + test "x$?" != "x0" && pkg_failed=yes +else + pkg_failed=yes +fi + else + pkg_failed=untried +fi +if test -n "$LZ4_LIBS"; then + pkg_cv_LZ4_LIBS="$LZ4_LIBS" + elif test -n "$PKG_CONFIG"; then + if test -n "$PKG_CONFIG" && \ + { { $as_echo "$as_me:${as_lineno-$LINENO}: \$PKG_CONFIG --exists --print-errors \"liblz4\""; } >&5 + ($PKG_CONFIG --exists --print-errors "liblz4") 2>&5 + ac_status=$? + $as_echo "$as_me:${as_lineno-$LINENO}: \$? = $ac_status" >&5 + test $ac_status = 0; }; then + pkg_cv_LZ4_LIBS=`$PKG_CONFIG --libs "liblz4" 2>/dev/null` + test "x$?" != "x0" && pkg_failed=yes +else + pkg_failed=yes +fi + else + pkg_failed=untried +fi + + + +if test $pkg_failed = yes; then + { $as_echo "$as_me:${as_lineno-$LINENO}: result: no" >&5 +$as_echo "no" >&6; } + +if $PKG_CONFIG --atleast-pkgconfig-version 0.20; then + _pkg_short_errors_supported=yes +else + _pkg_short_errors_supported=no +fi + if test $_pkg_short_errors_supported = yes; then + LZ4_PKG_ERRORS=`$PKG_CONFIG --short-errors --print-errors --cflags --libs "liblz4" 2>&1` + else + LZ4_PKG_ERRORS=`$PKG_CONFIG --print-errors --cflags --libs "liblz4" 2>&1` + fi + # Put the nasty error message in config.log where it belongs + echo "$LZ4_PKG_ERRORS" >&5 + + as_fn_error $? "Package requirements (liblz4) were not met: + +$LZ4_PKG_ERRORS + +Consider adjusting the PKG_CONFIG_PATH environment variable if you +installed software in a non-standard prefix. + +Alternatively, you may set the environment variables LZ4_CFLAGS +and LZ4_LIBS to avoid the need to call pkg-config. +See the pkg-config man page for more details." "$LINENO" 5 +elif test $pkg_failed = untried; then + { $as_echo "$as_me:${as_lineno-$LINENO}: result: no" >&5 +$as_echo "no" >&6; } + { { $as_echo "$as_me:${as_lineno-$LINENO}: error: in \`$ac_pwd':" >&5 +$as_echo "$as_me: error: in \`$ac_pwd':" >&2;} +as_fn_error $? "The pkg-config script could not be found or is too old. Make sure it +is in your PATH or set the PKG_CONFIG environment variable to the full +path to pkg-config. + +Alternatively, you may set the environment variables LZ4_CFLAGS +and LZ4_LIBS to avoid the need to call pkg-config. +See the pkg-config man page for more details. + +To get pkg-config, see <http://pkg-config.freedesktop.org/;. +See \`config.log' for more details" "$LINENO" 5; } +else + LZ4_CFLAGS=$pkg_cv_LZ4_CFLAGS + LZ4_LIBS=$pkg_cv_LZ4_LIBS + { $as_echo "$as_me:${as_lineno-$LINENO}: result: yes" >&5 +$as_echo "yes" >&6; } + +fi + LIBS="$LZ4_LIBS $LIBS" + CFLAGS="$LZ4_CFLAGS $CFLAGS" +fi + # # Assignments # @@ -13378,6 +13518,42 @@ fi fi +if test "$with_lz4" = yes; then + ac_save_CPPFLAGS=$CPPFLAGS + CPPFLAGS="$LZ4_CFLAGS $CPPFLAGS" + + # Verify we have LZ4's header files + for ac_header in lz4/lz4.h +do : + ac_fn_c_check_header_mongrel "$LINENO" "lz4/lz4.h" "ac_cv_header_lz4_lz4_h" "$ac_includes_default" +if test "x$ac_cv_header_lz4_lz4_h" = xyes; then : + cat >>confdefs.h <<_ACEOF +#define HAVE_LZ4_LZ4_H 1 +_ACEOF + +else + for ac_header in lz4.h +do : + ac_fn_c_check_header_mongrel "$LINENO" "lz4.h" "ac_cv_header_lz4_h" "$ac_includes_default" +if test "x$ac_cv_header_lz4_h" = xyes; then : + cat >>confdefs.h <<_ACEOF +#define HAVE_LZ4_H 1 +_ACEOF + +else + as_fn_error $? "lz4.h header file is required for LZ4" "$LINENO" 5 +fi + +done + +fi + +done + + + CPPFLAGS=$ac_save_CPPFLAGS +fi + if test "$with_gssapi" = yes ; then for ac_header in gssapi/gssapi.h do : diff --git a/configure.ac b/configure.ac index 0ed53571dd..2b3b5676eb 100644 --- a/configure.ac +++ b/configure.ac @@ -986,6 +986,21 @@ PGAC_ARG_BOOL(with, zlib, yes, [do not use Zlib]) AC_SUBST(with_zlib) +# +# LZ4 +# +AC_MSG_CHECKING([whether to build with LZ4 support]) +PGAC_ARG_BOOL(with, lz4, no, [build with LZ4 support], + [AC_DEFINE([USE_LZ4], 1, [Define to 1 to build with LZ4 support. (--with-lz4)])]) +AC_MSG_RESULT([$with_lz4]) +AC_SUBST(with_lz4) + +if test "$with_lz4" = yes; then + PKG_CHECK_MODULES(LZ4, liblz4) + LIBS="$LZ4_LIBS $LIBS" + CFLAGS="$LZ4_CFLAGS $CFLAGS" +fi + # # Assignments # @@ -1407,6 +1422,17 @@ failure. It is possible the compiler isn't looking in the proper directory. Use --without-zlib to disable zlib support.])]) fi +if test "$with_lz4" = yes; then + ac_save_CPPFLAGS=$CPPFLAGS + CPPFLAGS="$LZ4_CFLAGS $CPPFLAGS" + + # Verify we have LZ4's header files + AC_CHECK_HEADERS(lz4/lz4.h, [], + [AC_CHECK_HEADERS(lz4.h, [], [AC_MSG_ERROR([lz4.h header file is required for LZ4])])]) + + CPPFLAGS=$ac_save_CPPFLAGS +fi + if test "$with_gssapi" = yes ; then AC_CHECK_HEADERS(gssapi/gssapi.h, [], [AC_CHECK_HEADERS(gssapi.h, [], [AC_MSG_ERROR([gssapi.h header file is required for GSSAPI])])]) diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c index 8bae73b4ec..f33355e44a 100644 --- a/src/backend/access/transam/xlog.c +++ b/src/backend/access/transam/xlog.c @@ -186,6 +186,9 @@ const struct config_enum_entry wal_compression_options[] = { {"pglz", WAL_COMPRESSION_PGLZ, false}, #ifdef HAVE_LIBZ {"zlib", WAL_COMPRESSION_ZLIB, false}, +#endif +#ifdef USE_LZ4 + {"lz4", WAL_COMPRESSION_LZ4, false}, #endif {NULL, 0, false} }; diff --git a/src/backend/access/transam/xloginsert.c b/src/backend/access/transam/xloginsert.c index ee73bc3afd..38ac5f9e94 100644 --- a/src/backend/access/transam/xloginsert.c +++ b/src/backend/access/transam/xloginsert.c @@ -37,6 +37,10 @@ #include <zlib.h> #endif +#ifdef USE_LZ4 +#include "lz4.h" +#endif + /* Buffer size required to store a compressed version of backup block image */ #define PGLZ_MAX_BLCKSZ PGLZ_MAX_OUTPUT(BLCKSZ) @@ -884,6 +888,14 @@ XLogCompressBackupBlock(char *page, uint16 hole_offset, uint16 hole_length, } #endif +#ifdef USE_LZ4 + case WAL_COMPRESSION_LZ4: + len = LZ4_compress_fast(source, dest, orig_len, PGLZ_MAX_BLCKSZ, 1); + if (len == 0) + len = -1; + break; +#endif + default: /* * It should be impossible to get here for unsupported algorithms, diff --git a/src/backend/access/transam/xlogreader.c b/src/backend/access/transam/xlogreader.c index 143df55fcb..920c47e4d7 100644 --- a/src/backend/access/transam/xlogreader.c +++ b/src/backend/access/transam/xlogreader.c @@ -37,6 +37,10 @@ #include <zlib.h> #endif +#ifdef USE_LZ4 +#include "lz4.h" +#endif + static void report_invalid_record(XLogReaderState *state, const char *fmt,...) pg_attribute_printf(2, 3); static bool allocate_recordbuf(XLogReaderState *state, uint32 reclength); @@ -1558,6 +1562,8 @@ wal_compression_name(WalCompression compression) return "pglz"; case WAL_COMPRESSION_ZLIB: return "zlib"; + case WAL_COMPRESSION_LZ4: + return "lz4"; default: return "???"; } @@ -1607,6 +1613,12 @@ RestoreBlockImage(XLogReaderState *record, uint8 block_id, char *page) } #endif +#ifdef USE_LZ4 + case WAL_COMPRESSION_LZ4: + decomp_result = LZ4_decompress_safe(ptr, tmp.data, bkpb->bimg_len, BLCKSZ); + break; +#endif + default: report_invalid_record(record, "image at %X/%X is compressed with unsupported codec, block %d (%d/%s)", (uint32) (record->ReadRecPtr >> 32), diff --git a/src/include/access/xlog_internal.h b/src/include/access/xlog_internal.h index b80759ed45..0993de784e 100644 --- a/src/include/access/xlog_internal.h +++ b/src/include/access/xlog_internal.h @@ -328,6 +328,7 @@ typedef enum WalCompression { WAL_COMPRESSION_PGLZ = 0, WAL_COMPRESSION_ZLIB = 1, + WAL_COMPRESSION_LZ4 = 2, } WalCompression; extern const char *wal_compression_name(WalCompression compression); diff --git a/src/include/pg_config.h.in b/src/include/pg_config.h.in index 7a7cc21d8d..0a6422da4f 100644 --- a/src/include/pg_config.h.in +++ b/src/include/pg_config.h.in @@ -899,6 +899,9 @@ /* Define to 1 to build with LLVM based JIT support. (--with-llvm) */ #undef USE_LLVM +/* Define to 1 to build with LZ4 support (--with-lz4) */ +#undef USE_LZ4 + /* Define to select named POSIX semaphores. */ #undef USE_NAMED_POSIX_SEMAPHORES diff --git a/src/tools/msvc/Solution.pm b/src/tools/msvc/Solution.pm index a4f5cc4bdb..14605371bb 100644 --- a/src/tools/msvc/Solution.pm +++ b/src/tools/msvc/Solution.pm @@ -485,6 +485,7 @@ sub GenerateFiles USE_ICU => $self->{options}->{icu} ? 1 : undef, USE_LIBXML => undef, USE_LIBXSLT => undef, + USE_LZ4 => undef, USE_LDAP => $self->{options}->{ldap} ? 1 : undef, USE_LLVM => undef, USE_NAMED_POSIX_SEMAPHORES => undef, -- 2.17.0 --f0KYrhQ4vYSV2aJu Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="0006-Default-to-LZ4.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 -- 2021-03-12 20:32 [PATCH 5/8] (re)add wal_compression_method=lz4 Justin Pryzby <[email protected]> 2023-03-29 00:59 [PATCH v9] Add pg_column_toast_chunk_id function Yugo Nagata <[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]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox