pg.ddx.io pgsql-hackers@postgresql.org mailing list archive
help / color / mirror / Atom feedpg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
26+ messages / 5 participants
[nested] [flat]
* pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2023-03-29 01:55 Yugo NAGATA <nagata@sraoss.co.jp>
0 siblings, 1 reply; 26+ messages in thread
From: Yugo NAGATA @ 2023-03-29 01:55 UTC (permalink / raw)
To: pgsql-hackers
Hello,
Attached patch introduces a function pg_column_toast_chunk_id
that returns a chunk ID of a TOASTed value.
Recently, one of our clients needed a way to show which columns
are actually TOASTed because they would like to know how much
updates on the original table affects to its toast table
specifically with regard to auto VACUUM. We could not find a
function for this purpose in the current PostgreSQL, so I would
like propose pg_column_toast_chunk_id.
This function returns a chunk ID of a TOASTed value, or NULL
if the value is not TOASTed. Here is an example;
postgres=# \d val
Table "public.val"
Column | Type | Collation | Nullable | Default
--------+------+-----------+----------+---------
t | text | | |
postgres=# select length(t), pg_column_size(t), pg_column_compression(t), pg_column_toast_chunk_id(t), tableoid from val;
length | pg_column_size | pg_column_compression | pg_column_toast_chunk_id | tableoid
--------+----------------+-----------------------+--------------------------+----------
3 | 4 | | | 16388
3000 | 46 | pglz | | 16388
32000 | 413 | pglz | | 16388
305 | 309 | | | 16388
64000 | 64000 | | 16393 | 16388
(5 rows)
postgres=# select chunk_id, chunk_seq from pg_toast.pg_toast_16388;
chunk_id | chunk_seq
----------+-----------
16393 | 0
16393 | 1
16393 | 2
(snip)
16393 | 30
16393 | 31
16393 | 32
(33 rows)
This function is also useful to identify a problematic row when
an error like
"ERROR: unexpected chunk number ... (expected ...) for toast value"
occurs.
The patch is a just a concept patch and not including documentation
and tests.
What do you think about this feature?
Regards,
Yugo Nagata
--
Yugo NAGATA <nagata@sraoss.co.jp>
Attachments:
[text/x-diff] pg_column_toast_chunk_id.patch (2.2K, ../../20230329105507.d764497456eeac1ca491b5bd@sraoss.co.jp/2-pg_column_toast_chunk_id.patch)
download | inline diff:
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index 5778e3f0ef..4ddcf885cd 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -5070,6 +5070,47 @@ pg_column_compression(PG_FUNCTION_ARGS)
PG_RETURN_TEXT_P(cstring_to_text(result));
}
+/*
+ * Return the chunk id of the TOASTed value.
+ * Return NULL for unTOASTed value.
+ */
+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 7c358cff16..4c214f4c63 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7414,6 +7414,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 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',
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2023-07-05 14:49 Nikita Malakhov <hukutoc@gmail.com>
parent: Yugo NAGATA <nagata@sraoss.co.jp>
0 siblings, 1 reply; 26+ messages in thread
From: Nikita Malakhov @ 2023-07-05 14:49 UTC (permalink / raw)
To: Yugo NAGATA <nagata@sraoss.co.jp>; +Cc: pgsql-hackers
Hi!
I like the idea of having a standard function which shows a TOAST value ID
for a row. I've used my own to handle TOAST errors. Just, maybe, more
correct
name would be "...value_id", because you actually retrieve valueid field
from the TOAST pointer, and chunk ID consists of valueid + chunk_seq.
--
Regards,
Nikita Malakhov
Postgres Professional
The Russian Postgres Company
https://postgrespro.ru/
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2023-07-07 08:21 Yugo NAGATA <nagata@sraoss.co.jp>
parent: Nikita Malakhov <hukutoc@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Yugo NAGATA @ 2023-07-07 08:21 UTC (permalink / raw)
To: Nikita Malakhov <hukutoc@gmail.com>; +Cc: pgsql-hackers
Hi Nikita,
On Wed, 5 Jul 2023 17:49:20 +0300
Nikita Malakhov <hukutoc@gmail.com> wrote:
> Hi!
>
> I like the idea of having a standard function which shows a TOAST value ID
> for a row. I've used my own to handle TOAST errors. Just, maybe, more
> correct
> name would be "...value_id", because you actually retrieve valueid field
> from the TOAST pointer, and chunk ID consists of valueid + chunk_seq.
Thank you for your review!
Although, the retrieved field is "va_valueid" and it is called "value ID" in the
code, I chose the name "..._chunk_id" because I found the description in the
documentation as followings:
-------------
Every TOAST table has the columns chunk_id (an OID identifying the particular TOASTed value), chunk_seq (a sequence number for the chunk within its value), and chunk_data (the actual data of the chunk). A unique index on chunk_id and chunk_seq provides fast retrieval of the values. A pointer datum representing an out-of-line on-disk TOASTed value therefore needs to store the OID of the TOAST table in which to look and the OID of the specific value (its chunk_id)
-------------
https://www.postgresql.org/docs/devel/storage-toast.html
Here, chunk_id defined separately from chunk_seq. Therefore, I wonder
pg_column_toast_chunk_id would be ok. However, I don't insist on this
and I would be happy to change it if the other name is more natural for users.
Regards,
Yugo Nagata
>
> --
> Regards,
> Nikita Malakhov
> Postgres Professional
> The Russian Postgres Company
> https://postgrespro.ru/
--
Yugo NAGATA <nagata@sraoss.co.jp>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2023-07-07 08:30 Yugo NAGATA <nagata@sraoss.co.jp>
parent: Yugo NAGATA <nagata@sraoss.co.jp>
0 siblings, 1 reply; 26+ messages in thread
From: Yugo NAGATA @ 2023-07-07 08:30 UTC (permalink / raw)
To: Yugo NAGATA <nagata@sraoss.co.jp>; +Cc: Nikita Malakhov <hukutoc@gmail.com>; pgsql-hackers
On Fri, 7 Jul 2023 17:21:36 +0900
Yugo NAGATA <nagata@sraoss.co.jp> wrote:
> Hi Nikita,
>
> On Wed, 5 Jul 2023 17:49:20 +0300
> Nikita Malakhov <hukutoc@gmail.com> wrote:
>
> > Hi!
> >
> > I like the idea of having a standard function which shows a TOAST value ID
> > for a row. I've used my own to handle TOAST errors. Just, maybe, more
> > correct
> > name would be "...value_id", because you actually retrieve valueid field
> > from the TOAST pointer, and chunk ID consists of valueid + chunk_seq.
>
> Thank you for your review!
>
> Although, the retrieved field is "va_valueid" and it is called "value ID" in the
> code, I chose the name "..._chunk_id" because I found the description in the
> documentation as followings:
>
> -------------
> Every TOAST table has the columns chunk_id (an OID identifying the particular TOASTed value), chunk_seq (a sequence number for the chunk within its value), and chunk_data (the actual data of the chunk). A unique index on chunk_id and chunk_seq provides fast retrieval of the values. A pointer datum representing an out-of-line on-disk TOASTed value therefore needs to store the OID of the TOAST table in which to look and the OID of the specific value (its chunk_id)
> -------------
> https://www.postgresql.org/docs/devel/storage-toast.html
>
> Here, chunk_id defined separately from chunk_seq. Therefore, I wonder
> pg_column_toast_chunk_id would be ok. However, I don't insist on this
> and I would be happy to change it if the other name is more natural for users.
I attached v2 patch that contains the documentation fix.
Regards,
Yugo Nagata
>
> Regards,
> Yugo Nagata
>
> >
> > --
> > Regards,
> > Nikita Malakhov
> > Postgres Professional
> > The Russian Postgres Company
> > https://postgrespro.ru/
>
>
> --
> Yugo NAGATA <nagata@sraoss.co.jp>
>
>
--
Yugo NAGATA <nagata@sraoss.co.jp>
Attachments:
[text/x-diff] v2_pg_column_toast_chunk_id.patch (3.1K, ../../20230707173015.001c89f5a3f6f5cdacead169@sraoss.co.jp/2-v2_pg_column_toast_chunk_id.patch)
download | inline diff:
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5a47ce4343..c2c3156cd4 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -27608,6 +27608,20 @@ 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>
+ Returns the chunk id of the TOASTed value, or <literal>NULL</literal>
+ if the value is not TOASTed.
+ </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 884bfbc8ce..fe8788c1b1 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -5069,6 +5069,47 @@ pg_column_compression(PG_FUNCTION_ARGS)
PG_RETURN_TEXT_P(cstring_to_text(result));
}
+/*
+ * Return the chunk id of the TOASTed value.
+ * Return NULL for unTOASTed value.
+ */
+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 6996073989..0cacd0391d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7403,6 +7403,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 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',
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2023-07-10 18:39 Sergei Kornilov <sk@zsrv.org>
parent: Yugo NAGATA <nagata@sraoss.co.jp>
0 siblings, 1 reply; 26+ messages in thread
From: Sergei Kornilov @ 2023-07-10 18:39 UTC (permalink / raw)
To: Yugo NAGATA <nagata@sraoss.co.jp>; +Cc: Nikita Malakhov <hukutoc@gmail.com>; pgsql-hackers
Hello
My +1 to have such a function in core or in some contrib at least (pg_surgery? amcheck?).
In the past, more than once I needed to find a damaged tuple knowing only chunk id and toastrelid. This feature would help a lot.
regards, Sergei
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2023-11-06 00:00 jian he <jian.universality@gmail.com>
parent: Sergei Kornilov <sk@zsrv.org>
0 siblings, 1 reply; 26+ messages in thread
From: jian he @ 2023-11-06 00:00 UTC (permalink / raw)
To: Sergei Kornilov <sk@zsrv.org>; +Cc: Yugo NAGATA <nagata@sraoss.co.jp>; Nikita Malakhov <hukutoc@gmail.com>; pgsql-hackers
minor doc issues.
Returns the chunk id of the TOASTed value, or NULL if the value is not TOASTed.
Should it be "chunk_id"?
you may place it after pg_create_logical_replication_slot entry to
make it look like alphabetical order.
There is no test. maybe we can add following to src/test/regress/sql/misc.sql
create table val(t text);
INSERT into val(t) SELECT string_agg(
chr((ascii('B') + round(random() * 25)) :: integer),'')
FROM generate_series(1,2500);
select pg_column_toast_chunk_id(t) is not null from val;
drop table val;
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2024-01-02 00:00 jian he <jian.universality@gmail.com>
parent: jian he <jian.universality@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: jian he @ 2024-01-02 00:00 UTC (permalink / raw)
To: Sergei Kornilov <sk@zsrv.org>; +Cc: Yugo NAGATA <nagata@sraoss.co.jp>; Nikita Malakhov <hukutoc@gmail.com>; pgsql-hackers
On Mon, Nov 6, 2023 at 8:00 AM jian he <jian.universality@gmail.com> wrote:
>
> minor doc issues.
> Returns the chunk id of the TOASTed value, or NULL if the value is not TOASTed.
> Should it be "chunk_id"?
>
> you may place it after pg_create_logical_replication_slot entry to
> make it look like alphabetical order.
>
> There is no test. maybe we can add following to src/test/regress/sql/misc.sql
> create table val(t text);
> INSERT into val(t) SELECT string_agg(
> chr((ascii('B') + round(random() * 25)) :: integer),'')
> FROM generate_series(1,2500);
> select pg_column_toast_chunk_id(t) is not null from val;
> drop table val;
Hi
the main C function (pg_column_toast_chunk_id) I didn't change.
I added tests as mentioned above.
tests put it on src/test/regress/sql/misc.sql, i hope that's fine.
I placed pg_column_toast_chunk_id in "Table 9.99. Database Object
Location Functions" (below Table 9.98. Database Object Size
Functions).
Attachments:
[text/x-patch] v2_pg_column_toast_chunk_id (1).patch (3.2K, ../../CACJufxEUcJGsxVmGtdQ+N1SOs7mbZajmNmZHrScWi8ZAm5VnuA@mail.gmail.com/2-v2_pg_column_toast_chunk_id%20%281%29.patch)
download | inline diff:
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5a47ce4343..c2c3156cd4 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -27608,6 +27608,20 @@ 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>
+ Returns the chunk id of the TOASTed value, or <literal>NULL</literal>
+ if the value is not TOASTed.
+ </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 884bfbc8ce..fe8788c1b1 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -5069,6 +5069,47 @@ pg_column_compression(PG_FUNCTION_ARGS)
PG_RETURN_TEXT_P(cstring_to_text(result));
}
+/*
+ * Return the chunk id of the TOASTed value.
+ * Return NULL for unTOASTed value.
+ */
+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 6996073989..0cacd0391d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7403,6 +7403,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 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',
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2024-01-26 00:42 Yugo NAGATA <nagata@sraoss.co.jp>
parent: jian he <jian.universality@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Yugo NAGATA @ 2024-01-26 00:42 UTC (permalink / raw)
To: jian he <jian.universality@gmail.com>; +Cc: Sergei Kornilov <sk@zsrv.org>; Nikita Malakhov <hukutoc@gmail.com>; pgsql-hackers
On Tue, 2 Jan 2024 08:00:00 +0800
jian he <jian.universality@gmail.com> wrote:
> On Mon, Nov 6, 2023 at 8:00 AM jian he <jian.universality@gmail.com> wrote:
> >
> > minor doc issues.
> > Returns the chunk id of the TOASTed value, or NULL if the value is not TOASTed.
> > Should it be "chunk_id"?
Thank you for your suggestion. As you pointed out, it is called "chunk_id"
in the documentation, so I rewrote it and also added a link to the section
where the TOAST table structure is explained.
> > you may place it after pg_create_logical_replication_slot entry to
> > make it look like alphabetical order.
I've been thinking about where we should place the function in the doc,
and I decided place it in the table of Database Object Size Functions
because I think pg_column_toast_chunk_id also would assist understanding
the result of size functions as similar to pg_column_compression; that is,
those function can explain why a large value in size could be stored in
a column.
> > There is no test. maybe we can add following to src/test/regress/sql/misc.sql
> > create table val(t text);
> > INSERT into val(t) SELECT string_agg(
> > chr((ascii('B') + round(random() * 25)) :: integer),'')
> > FROM generate_series(1,2500);
> > select pg_column_toast_chunk_id(t) is not null from val;
> > drop table val;
Thank you for the test proposal. However, if we add a test, I want
to check that the chunk_id returned by the function exists in the
TOAST table, and that it returns NULL if the values is not TOASTed.
For the purpose, I wrote a test using a dynamic SQL since the table
name of the TOAST table have to be generated from the main table's OID.
> Hi
> the main C function (pg_column_toast_chunk_id) I didn't change.
> I added tests as mentioned above.
> tests put it on src/test/regress/sql/misc.sql, i hope that's fine.
> I placed pg_column_toast_chunk_id in "Table 9.99. Database Object
> Location Functions" (below Table 9.98. Database Object Size
> Functions).
I could not find any change in your patch from my previous patch.
Maybe, you attached wrong file. I attached a patch updated based
on your review, including the documentation fixes and a test.
What do you think about this it?
Regards,
Yugo Nagata
--
Yugo NAGATA <nagata@sraoss.co.jp>
Attachments:
[text/x-diff] v3-0001-Add-pg_column_toast_chunk_id-function.patch (4.1K, ../../20240126094237.cec181d85e05556533d79914@sraoss.co.jp/2-v3-0001-Add-pg_column_toast_chunk_id-function.patch)
download | inline diff:
From 97af1b2300ecf07a34923da87a9d84e6aa963956 Mon Sep 17 00:00:00 2001
From: Yugo Nagata <nagata@sraoss.co.jp>
Date: Wed, 29 Mar 2023 09:59:25 +0900
Subject: [PATCH v3] Add pg_column_toast_chunk_id function
This function returns the chunk_id of an on-disk TOASTed value, or
NULL if the value is un-TOASTed or not on disk. This enables users to
know which columns are actually TOASTed. This function is also useful
to identify a problematic row when an error like
"ERROR: unexpected chunk number ... (expected ...) for toast value"
occurs.
---
doc/src/sgml/func.sgml | 17 ++++++++++++++
src/backend/utils/adt/varlena.c | 41 +++++++++++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 3 +++
3 files changed, 61 insertions(+)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 210c7c0b02..2d82331323 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -28078,6 +28078,23 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_column_toast_chunk_id</primary>
+ </indexterm>
+ <function>pg_column_toast_chunk_id</function> ( <type>"any"</type> )
+ <returnvalue>oid</returnvalue>
+ </para>
+ <para>
+ Shows the <structfield>chunk_id</structfield> of an on-disk
+ <acronym>TOAST</acronym>ed value. Returns <literal>NULL</literal>
+ if the value is un-<acronym>TOAST</acronym>ed or not on-disk.
+ See <xref linkend="storage-toast-ondisk"/> for details about
+ <acronym>TOAST</acronym>.
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index 543afb66e5..84d36781a4 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -5105,6 +5105,47 @@ pg_column_compression(PG_FUNCTION_ARGS)
PG_RETURN_TEXT_P(cstring_to_text(result));
}
+/*
+ * Return the chunk_id of the on-disk TOASTed value.
+ * Return NULL if the value is unTOASTed or not on disk.
+ */
+Datum
+pg_column_toast_chunk_id(PG_FUNCTION_ARGS)
+{
+ int typlen;
+ struct varlena *attr;
+ struct varatt_external toast_pointer;
+
+ /* On first call, get the input type's typlen, and save at *fn_extra */
+ if (fcinfo->flinfo->fn_extra == NULL)
+ {
+ /* Lookup the datatype of the supplied argument */
+ Oid argtypeid = get_fn_expr_argtype(fcinfo->flinfo, 0);
+
+ typlen = get_typlen(argtypeid);
+ if (typlen == 0) /* should not happen */
+ elog(ERROR, "cache lookup failed for type %u", argtypeid);
+
+ fcinfo->flinfo->fn_extra = MemoryContextAlloc(fcinfo->flinfo->fn_mcxt,
+ sizeof(int));
+ *((int *) fcinfo->flinfo->fn_extra) = typlen;
+ }
+ else
+ typlen = *((int *) fcinfo->flinfo->fn_extra);
+
+ if (typlen != -1)
+ PG_RETURN_NULL();
+
+ attr = (struct varlena *) DatumGetPointer(PG_GETARG_DATUM(0));
+
+ if (!VARATT_IS_EXTERNAL_ONDISK(attr))
+ PG_RETURN_NULL();
+
+ VARATT_EXTERNAL_GET_POINTER(toast_pointer, attr);
+
+ PG_RETURN_OID(toast_pointer.va_valueid);
+}
+
/*
* string_agg - Concatenates values and returns string.
*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 58811a6530..1d4521ac1f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7454,6 +7454,9 @@
{ oid => '2121', descr => 'compression method for the compressed datum',
proname => 'pg_column_compression', provolatile => 's', prorettype => 'text',
proargtypes => 'any', prosrc => 'pg_column_compression' },
+{ oid => '8393', descr => 'chunk ID of on-disk TOASTed value',
+ proname => 'pg_column_toast_chunk_id', provolatile => 's', prorettype => 'oid',
+ proargtypes => 'any', prosrc => 'pg_column_toast_chunk_id' },
{ oid => '2322',
descr => 'total disk space usage for the specified tablespace',
proname => 'pg_tablespace_size', provolatile => 'v', prorettype => 'int8',
--
2.25.1
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2024-01-30 04:12 jian he <jian.universality@gmail.com>
parent: Yugo NAGATA <nagata@sraoss.co.jp>
0 siblings, 1 reply; 26+ messages in thread
From: jian he @ 2024-01-30 04:12 UTC (permalink / raw)
To: Yugo NAGATA <nagata@sraoss.co.jp>; +Cc: Sergei Kornilov <sk@zsrv.org>; Nikita Malakhov <hukutoc@gmail.com>; pgsql-hackers
On Fri, Jan 26, 2024 at 8:42 AM Yugo NAGATA <nagata@sraoss.co.jp> wrote:
>
> On Tue, 2 Jan 2024 08:00:00 +0800
> jian he <jian.universality@gmail.com> wrote:
>
> > On Mon, Nov 6, 2023 at 8:00 AM jian he <jian.universality@gmail.com> wrote:
> > >
> > > minor doc issues.
> > > Returns the chunk id of the TOASTed value, or NULL if the value is not TOASTed.
> > > Should it be "chunk_id"?
>
> Thank you for your suggestion. As you pointed out, it is called "chunk_id"
> in the documentation, so I rewrote it and also added a link to the section
> where the TOAST table structure is explained.
>
> > > you may place it after pg_create_logical_replication_slot entry to
> > > make it look like alphabetical order.
>
> I've been thinking about where we should place the function in the doc,
> and I decided place it in the table of Database Object Size Functions
> because I think pg_column_toast_chunk_id also would assist understanding
> the result of size functions as similar to pg_column_compression; that is,
> those function can explain why a large value in size could be stored in
> a column.
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>
v3 patch will place it on `Table 9.97. Replication Management Functions`
I agree with you. it should be placed after pg_column_compression. but
apply your patch, it will be at
> > > There is no test. maybe we can add following to src/test/regress/sql/misc.sql
> > > create table val(t text);
> > > INSERT into val(t) SELECT string_agg(
> > > chr((ascii('B') + round(random() * 25)) :: integer),'')
> > > FROM generate_series(1,2500);
> > > select pg_column_toast_chunk_id(t) is not null from val;
> > > drop table val;
>
> Thank you for the test proposal. However, if we add a test, I want
> to check that the chunk_id returned by the function exists in the
> TOAST table, and that it returns NULL if the values is not TOASTed.
> For the purpose, I wrote a test using a dynamic SQL since the table
> name of the TOAST table have to be generated from the main table's OID.
>
> > Hi
> > the main C function (pg_column_toast_chunk_id) I didn't change.
> > I added tests as mentioned above.
> > tests put it on src/test/regress/sql/misc.sql, i hope that's fine.
> > I placed pg_column_toast_chunk_id in "Table 9.99. Database Object
> > Location Functions" (below Table 9.98. Database Object Size
> > Functions).
>
> I could not find any change in your patch from my previous patch.
> Maybe, you attached wrong file. I attached a patch updated based
> on your review, including the documentation fixes and a test.
> What do you think about this it?
>
sorry, I had attached the wrong file.
but your v3 also has no tests, documentation didn't fix.
maybe you also attached the wrong file too?
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2024-01-30 04:34 Yugo NAGATA <nagata@sraoss.co.jp>
parent: jian he <jian.universality@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Yugo NAGATA @ 2024-01-30 04:34 UTC (permalink / raw)
To: jian he <jian.universality@gmail.com>; +Cc: Sergei Kornilov <sk@zsrv.org>; Nikita Malakhov <hukutoc@gmail.com>; pgsql-hackers
On Tue, 30 Jan 2024 12:12:31 +0800
jian he <jian.universality@gmail.com> wrote:
> On Fri, Jan 26, 2024 at 8:42 AM Yugo NAGATA <nagata@sraoss.co.jp> wrote:
> >
> > On Tue, 2 Jan 2024 08:00:00 +0800
> > jian he <jian.universality@gmail.com> wrote:
> >
> > > On Mon, Nov 6, 2023 at 8:00 AM jian he <jian.universality@gmail.com> wrote:
> > > >
> > > > minor doc issues.
> > > > Returns the chunk id of the TOASTed value, or NULL if the value is not TOASTed.
> > > > Should it be "chunk_id"?
> >
> > Thank you for your suggestion. As you pointed out, it is called "chunk_id"
> > in the documentation, so I rewrote it and also added a link to the section
> > where the TOAST table structure is explained.
> >
> > > > you may place it after pg_create_logical_replication_slot entry to
> > > > make it look like alphabetical order.
> >
> > I've been thinking about where we should place the function in the doc,
> > and I decided place it in the table of Database Object Size Functions
> > because I think pg_column_toast_chunk_id also would assist understanding
> > the result of size functions as similar to pg_column_compression; that is,
> > those function can explain why a large value in size could be stored in
> > a column.
>
> 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>
>
> v3 patch will place it on `Table 9.97. Replication Management Functions`
> I agree with you. it should be placed after pg_column_compression. but
> apply your patch, it will be at
>
>
> > > > There is no test. maybe we can add following to src/test/regress/sql/misc.sql
> > > > create table val(t text);
> > > > INSERT into val(t) SELECT string_agg(
> > > > chr((ascii('B') + round(random() * 25)) :: integer),'')
> > > > FROM generate_series(1,2500);
> > > > select pg_column_toast_chunk_id(t) is not null from val;
> > > > drop table val;
> >
> > Thank you for the test proposal. However, if we add a test, I want
> > to check that the chunk_id returned by the function exists in the
> > TOAST table, and that it returns NULL if the values is not TOASTed.
> > For the purpose, I wrote a test using a dynamic SQL since the table
> > name of the TOAST table have to be generated from the main table's OID.
> >
> > > Hi
> > > the main C function (pg_column_toast_chunk_id) I didn't change.
> > > I added tests as mentioned above.
> > > tests put it on src/test/regress/sql/misc.sql, i hope that's fine.
> > > I placed pg_column_toast_chunk_id in "Table 9.99. Database Object
> > > Location Functions" (below Table 9.98. Database Object Size
> > > Functions).
> >
> > I could not find any change in your patch from my previous patch.
> > Maybe, you attached wrong file. I attached a patch updated based
> > on your review, including the documentation fixes and a test.
> > What do you think about this it?
> >
>
> sorry, I had attached the wrong file.
> but your v3 also has no tests, documentation didn't fix.
> maybe you also attached the wrong file too?
>
Sorry, I also attached a wrong file.
Attached is the correct one.
Regards,
Yugo Nagata
--
Yugo NAGATA <nagata@sraoss.co.jp>
Attachments:
[text/x-diff] v3-0001-Add-pg_column_toast_chunk_id-function.patch (4.1K, ../../20240130133459.b1748c23998201476f49ca59@sraoss.co.jp/2-v3-0001-Add-pg_column_toast_chunk_id-function.patch)
download | inline diff:
From 97af1b2300ecf07a34923da87a9d84e6aa963956 Mon Sep 17 00:00:00 2001
From: Yugo Nagata <nagata@sraoss.co.jp>
Date: Wed, 29 Mar 2023 09:59:25 +0900
Subject: [PATCH v3] Add pg_column_toast_chunk_id function
This function returns the chunk_id of an on-disk TOASTed value, or
NULL if the value is un-TOASTed or not on disk. This enables users to
know which columns are actually TOASTed. This function is also useful
to identify a problematic row when an error like
"ERROR: unexpected chunk number ... (expected ...) for toast value"
occurs.
---
doc/src/sgml/func.sgml | 17 ++++++++++++++
src/backend/utils/adt/varlena.c | 41 +++++++++++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 3 +++
3 files changed, 61 insertions(+)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 210c7c0b02..2d82331323 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -28078,6 +28078,23 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_column_toast_chunk_id</primary>
+ </indexterm>
+ <function>pg_column_toast_chunk_id</function> ( <type>"any"</type> )
+ <returnvalue>oid</returnvalue>
+ </para>
+ <para>
+ Shows the <structfield>chunk_id</structfield> of an on-disk
+ <acronym>TOAST</acronym>ed value. Returns <literal>NULL</literal>
+ if the value is un-<acronym>TOAST</acronym>ed or not on-disk.
+ See <xref linkend="storage-toast-ondisk"/> for details about
+ <acronym>TOAST</acronym>.
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index 543afb66e5..84d36781a4 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -5105,6 +5105,47 @@ pg_column_compression(PG_FUNCTION_ARGS)
PG_RETURN_TEXT_P(cstring_to_text(result));
}
+/*
+ * Return the chunk_id of the on-disk TOASTed value.
+ * Return NULL if the value is unTOASTed or not on disk.
+ */
+Datum
+pg_column_toast_chunk_id(PG_FUNCTION_ARGS)
+{
+ int typlen;
+ struct varlena *attr;
+ struct varatt_external toast_pointer;
+
+ /* On first call, get the input type's typlen, and save at *fn_extra */
+ if (fcinfo->flinfo->fn_extra == NULL)
+ {
+ /* Lookup the datatype of the supplied argument */
+ Oid argtypeid = get_fn_expr_argtype(fcinfo->flinfo, 0);
+
+ typlen = get_typlen(argtypeid);
+ if (typlen == 0) /* should not happen */
+ elog(ERROR, "cache lookup failed for type %u", argtypeid);
+
+ fcinfo->flinfo->fn_extra = MemoryContextAlloc(fcinfo->flinfo->fn_mcxt,
+ sizeof(int));
+ *((int *) fcinfo->flinfo->fn_extra) = typlen;
+ }
+ else
+ typlen = *((int *) fcinfo->flinfo->fn_extra);
+
+ if (typlen != -1)
+ PG_RETURN_NULL();
+
+ attr = (struct varlena *) DatumGetPointer(PG_GETARG_DATUM(0));
+
+ if (!VARATT_IS_EXTERNAL_ONDISK(attr))
+ PG_RETURN_NULL();
+
+ VARATT_EXTERNAL_GET_POINTER(toast_pointer, attr);
+
+ PG_RETURN_OID(toast_pointer.va_valueid);
+}
+
/*
* string_agg - Concatenates values and returns string.
*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 58811a6530..1d4521ac1f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7454,6 +7454,9 @@
{ oid => '2121', descr => 'compression method for the compressed datum',
proname => 'pg_column_compression', provolatile => 's', prorettype => 'text',
proargtypes => 'any', prosrc => 'pg_column_compression' },
+{ oid => '8393', descr => 'chunk ID of on-disk TOASTed value',
+ proname => 'pg_column_toast_chunk_id', provolatile => 's', prorettype => 'oid',
+ proargtypes => 'any', prosrc => 'pg_column_toast_chunk_id' },
{ oid => '2322',
descr => 'total disk space usage for the specified tablespace',
proname => 'pg_tablespace_size', provolatile => 'v', prorettype => 'int8',
--
2.25.1
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2024-01-30 05:47 jian he <jian.universality@gmail.com>
parent: Yugo NAGATA <nagata@sraoss.co.jp>
0 siblings, 1 reply; 26+ messages in thread
From: jian he @ 2024-01-30 05:47 UTC (permalink / raw)
To: Yugo NAGATA <nagata@sraoss.co.jp>; +Cc: Sergei Kornilov <sk@zsrv.org>; Nikita Malakhov <hukutoc@gmail.com>; pgsql-hackers
On Tue, Jan 30, 2024 at 12:35 PM Yugo NAGATA <nagata@sraoss.co.jp> wrote:
>
>
> Sorry, I also attached a wrong file.
> Attached is the correct one.
I think you attached the wrong file again. also please name it as v4.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2024-01-30 05:56 Yugo NAGATA <nagata@sraoss.co.jp>
parent: jian he <jian.universality@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Yugo NAGATA @ 2024-01-30 05:56 UTC (permalink / raw)
To: jian he <jian.universality@gmail.com>; +Cc: Sergei Kornilov <sk@zsrv.org>; Nikita Malakhov <hukutoc@gmail.com>; pgsql-hackers
On Tue, 30 Jan 2024 13:47:45 +0800
jian he <jian.universality@gmail.com> wrote:
> On Tue, Jan 30, 2024 at 12:35 PM Yugo NAGATA <nagata@sraoss.co.jp> wrote:
> >
> >
> > Sorry, I also attached a wrong file.
> > Attached is the correct one.
> I think you attached the wrong file again. also please name it as v4.
Opps..sorry, again.
I attached the correct one, v4.
Regards,
Yugo Nagata
--
Yugo NAGATA <nagata@sraoss.co.jp>
Attachments:
[text/x-diff] v4-0001-Add-pg_column_toast_chunk_id-function.patch (6.3K, ../../20240130145632.d6609830bfd5139115f302cf@sraoss.co.jp/2-v4-0001-Add-pg_column_toast_chunk_id-function.patch)
download | inline diff:
From 8c0f9993c49d1d4ed1bb3e10ba26652da98cd41e Mon Sep 17 00:00:00 2001
From: Yugo Nagata <nagata@sraoss.co.jp>
Date: Wed, 29 Mar 2023 09:59:25 +0900
Subject: [PATCH v4] Add pg_column_toast_chunk_id function
This function returns the chunk_id of an on-disk TOASTed value, or
NULL if the value is un-TOASTed or not on disk. This enables users to
know which columns are actually TOASTed. This function is also useful
to identify a problematic row when an error like
"ERROR: unexpected chunk number ... (expected ...) for toast value"
occurs.
---
doc/src/sgml/func.sgml | 17 ++++++++
src/backend/utils/adt/varlena.c | 41 ++++++++++++++++++++
src/include/catalog/pg_proc.dat | 3 ++
src/test/regress/expected/misc_functions.out | 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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2024-01-30 06:57 jian he <jian.universality@gmail.com>
parent: Yugo NAGATA <nagata@sraoss.co.jp>
0 siblings, 1 reply; 26+ messages in thread
From: jian he @ 2024-01-30 06:57 UTC (permalink / raw)
To: Yugo NAGATA <nagata@sraoss.co.jp>; +Cc: Sergei Kornilov <sk@zsrv.org>; Nikita Malakhov <hukutoc@gmail.com>; pgsql-hackers
On Tue, Jan 30, 2024 at 1:56 PM Yugo NAGATA <nagata@sraoss.co.jp> wrote:
>
> I attached the correct one, v4.
>
+-- 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
select pg_size_pretty(100000::bigint);
return 98kb.
I think this is just too much, maybe I didn't consider the
implications of compression.
Anyway, I refactored the tests, making the toast value size be small.
I aslo refactor the doc.
pg_column_toast_chunk_id entry will be right after pg_column_compression entry.
You can check the screenshot.
Attachments:
[image/png] Screenshot from 2024-01-30 14-37-42.png (402.0K, ../../CACJufxGpqVg9j_Fb7aBRwricx1ZTV+vEQfUmwuVpNhrRyvcAdA@mail.gmail.com/2-Screenshot%20from%202024-01-30%2014-37-42.png)
download | view image
[text/x-patch] v5-0001-introduce-function-pg_column_toast_chunk_id.patch (6.5K, ../../CACJufxGpqVg9j_Fb7aBRwricx1ZTV+vEQfUmwuVpNhrRyvcAdA@mail.gmail.com/3-v5-0001-introduce-function-pg_column_toast_chunk_id.patch)
download | inline diff:
From 8c518db9cefe5b1d3155c61d819116a405f099ff Mon Sep 17 00:00:00 2001
From: jian he <jian.universality@gmail.com>
Date: Tue, 30 Jan 2024 14:49:16 +0800
Subject: [PATCH v5 1/1] introduce function, pg_column_toast_chunk_id
This function returns the chunk_id of an on-disk TOASTed value, or
NULL if the value is un-TOASTed or not on disk. This 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"
---
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 6788ba8e..4116aaff 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 543afb66..84d36781 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 29af4ce6..9ab71646 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 7c154771..e80049a9 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);
+-- v1: small enough not to be TOASTed
+-- v2: large enough to be TOASTed
+INSERT INTO test_chunk_id(v1,v2)
+SELECT repeat('0123456789', 10),
+ (SELECT string_agg(chr((ascii('B') + round(random() * 25)) :: integer),'')
+ FROM generate_series(1,2500));
+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 851dad90..3d6de868 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);
+
+-- v1: small enough not to be TOASTed
+-- v2: large enough to be TOASTed
+INSERT INTO test_chunk_id(v1,v2)
+SELECT repeat('0123456789', 10),
+ (SELECT string_agg(chr((ascii('B') + round(random() * 25)) :: integer),'')
+ FROM generate_series(1,2500));
+
+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;
\ No newline at end of file
--
2.34.1
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2024-02-01 04:45 Yugo NAGATA <nagata@sraoss.co.jp>
parent: jian he <jian.universality@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Yugo NAGATA @ 2024-02-01 04:45 UTC (permalink / raw)
To: jian he <jian.universality@gmail.com>; +Cc: Sergei Kornilov <sk@zsrv.org>; Nikita Malakhov <hukutoc@gmail.com>; pgsql-hackers
On Tue, 30 Jan 2024 14:57:20 +0800
jian he <jian.universality@gmail.com> wrote:
> On Tue, Jan 30, 2024 at 1:56 PM Yugo NAGATA <nagata@sraoss.co.jp> wrote:
> >
> > I attached the correct one, v4.
> >
>
> +-- 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
>
> select pg_size_pretty(100000::bigint);
> return 98kb.
>
> I think this is just too much, maybe I didn't consider the
> implications of compression.
> Anyway, I refactored the tests, making the toast value size be small.
Actually the data is compressed and the size is much smaller,
but I agree with you it is better not to generate large data unnecessarily.
I rewrote the test to disallow compression in the toast data using
"ALTER TABLE ... SET STORAGE EXTERNAL". In this case, any text larger
than 2k will be TOASTed on disk without compression, and it makes the
test simple, not required to use string_agg.
>
> I aslo refactor the doc.
> pg_column_toast_chunk_id entry will be right after pg_column_compression entry.
> You can check the screenshot.
I found the document order was not changed between my patch and yours.
In both, pg_column_toast_chunk_id entry is right after
pg_column_compression.
Here is a updated patch, v6.
Regards,
Yugo Nagata
--
Yugo NAGATA <nagata@sraoss.co.jp>
Attachments:
[text/x-diff] v6-0001-Add-pg_column_toast_chunk_id-function.patch (6.5K, ../../20240201134524.d647b3d185e5252162eb0436@sraoss.co.jp/2-v6-0001-Add-pg_column_toast_chunk_id-function.patch)
download | inline diff:
From dfec0fdb52c6aad8846b5c984e111dc8b2985e1a Mon Sep 17 00:00:00 2001
From: Yugo Nagata <nagata@sraoss.co.jp>
Date: Wed, 29 Mar 2023 09:59:25 +0900
Subject: [PATCH v6] Add pg_column_toast_chunk_id function
This function returns the chunk_id of an on-disk TOASTed value, or
NULL if the value is un-TOASTed or not on disk. This enables users to
know which columns are actually TOASTed. This function is also useful
to identify a problematic row when an error like
"ERROR: unexpected chunk number ... (expected ...) for toast value"
occurs.
---
doc/src/sgml/func.sgml | 17 ++++++++
src/backend/utils/adt/varlena.c | 41 ++++++++++++++++++++
src/include/catalog/pg_proc.dat | 3 ++
src/test/regress/expected/misc_functions.out | 21 ++++++++++
src/test/regress/sql/misc_functions.sql | 23 +++++++++++
5 files changed, 105 insertions(+)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2024-02-01 09:59 jian he <jian.universality@gmail.com>
parent: Yugo NAGATA <nagata@sraoss.co.jp>
0 siblings, 1 reply; 26+ messages in thread
From: jian he @ 2024-02-01 09:59 UTC (permalink / raw)
To: Yugo NAGATA <nagata@sraoss.co.jp>; +Cc: Sergei Kornilov <sk@zsrv.org>; Nikita Malakhov <hukutoc@gmail.com>; pgsql-hackers
On Thu, Feb 1, 2024 at 12:45 PM Yugo NAGATA <nagata@sraoss.co.jp> wrote:
>
> Here is a updated patch, v6.
v6 patch looks good.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2024-02-05 07:28 Yugo NAGATA <nagata@sraoss.co.jp>
parent: jian he <jian.universality@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Yugo NAGATA @ 2024-02-05 07:28 UTC (permalink / raw)
To: jian he <jian.universality@gmail.com>; +Cc: Sergei Kornilov <sk@zsrv.org>; Nikita Malakhov <hukutoc@gmail.com>; pgsql-hackers
On Thu, 1 Feb 2024 17:59:56 +0800
jian he <jian.universality@gmail.com> wrote:
> On Thu, Feb 1, 2024 at 12:45 PM Yugo NAGATA <nagata@sraoss.co.jp> wrote:
> >
> > Here is a updated patch, v6.
>
> v6 patch looks good.
Thank you for your review and updating the status to RwC!
Regards,
Yugo Nagata
--
Yugo NAGATA <nagata@sraoss.co.jp>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2024-03-07 22:56 Nathan Bossart <nathandbossart@gmail.com>
parent: Yugo NAGATA <nagata@sraoss.co.jp>
0 siblings, 1 reply; 26+ messages in thread
From: Nathan Bossart @ 2024-03-07 22:56 UTC (permalink / raw)
To: Yugo NAGATA <nagata@sraoss.co.jp>; +Cc: jian he <jian.universality@gmail.com>; Sergei Kornilov <sk@zsrv.org>; Nikita Malakhov <hukutoc@gmail.com>; pgsql-hackers
On Mon, Feb 05, 2024 at 04:28:23PM +0900, Yugo NAGATA wrote:
> On Thu, 1 Feb 2024 17:59:56 +0800
> jian he <jian.universality@gmail.com> wrote:
>> v6 patch looks good.
>
> Thank you for your review and updating the status to RwC!
I think this one needs a (pretty trivial) rebase. I spent a few minutes
testing it out and looking at the code, and it seems generally reasonable
to me. Do you think it's worth adding something like a
pg_column_toast_num_chunks() function that returns the number of chunks for
the TOASTed value, too?
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2024-03-08 06:31 Yugo NAGATA <nagata@sraoss.co.jp>
parent: Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Yugo NAGATA @ 2024-03-08 06:31 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: jian he <jian.universality@gmail.com>; Sergei Kornilov <sk@zsrv.org>; Nikita Malakhov <hukutoc@gmail.com>; pgsql-hackers
On Thu, 7 Mar 2024 16:56:17 -0600
Nathan Bossart <nathandbossart@gmail.com> wrote:
> On Mon, Feb 05, 2024 at 04:28:23PM +0900, Yugo NAGATA wrote:
> > On Thu, 1 Feb 2024 17:59:56 +0800
> > jian he <jian.universality@gmail.com> wrote:
> >> v6 patch looks good.
> >
> > Thank you for your review and updating the status to RwC!
>
> I think this one needs a (pretty trivial) rebase. I spent a few minutes
> testing it out and looking at the code, and it seems generally reasonable
Thank you for your review.
I've attached a rebased patch.
> to me. Do you think it's worth adding something like a
> pg_column_toast_num_chunks() function that returns the number of chunks for
> the TOASTed value, too?
If we want to know the number of chunks of a specified chunk_id,
we can get this by the following query.
postgres=# SELECT id, (SELECT count(*) FROM pg_toast.pg_toast_16384 WHERE chunk_id = id)
FROM (SELECT pg_column_toast_chunk_id(v) AS id FROM t);
id | count
-------+-------
16389 | 3
16390 | 287
(2 rows)
However, if there are needs for getting such information in a
simpler way, it might be worth making a new function.
Regards,
Yugo Nagata
> --
> Nathan Bossart
> Amazon Web Services: https://aws.amazon.com
--
Yugo NAGATA <nagata@sraoss.co.jp>
Attachments:
[text/x-diff] v7-0001-Add-pg_column_toast_chunk_id-function.patch (6.5K, ../../20240308153155.4c4b2fee3ed1431203e41016@sraoss.co.jp/2-v7-0001-Add-pg_column_toast_chunk_id-function.patch)
download | inline diff:
From 7cd0a1cbeae11dea4f4020c71cd0a4550e17b26b Mon Sep 17 00:00:00 2001
From: Yugo Nagata <nagata@sraoss.co.jp>
Date: Wed, 29 Mar 2023 09:59:25 +0900
Subject: [PATCH v7] Add pg_column_toast_chunk_id function
This function returns the chunk_id of an on-disk TOASTed value, or
NULL if the value is un-TOASTed or not on disk. This enables users to
know which columns are actually TOASTed. This function is also useful
to identify a problematic row when an error like
"ERROR: unexpected chunk number ... (expected ...) for toast value"
occurs.
---
doc/src/sgml/func.sgml | 17 ++++++++
src/backend/utils/adt/varlena.c | 41 ++++++++++++++++++++
src/include/catalog/pg_proc.dat | 3 ++
src/test/regress/expected/misc_functions.out | 21 ++++++++++
src/test/regress/sql/misc_functions.sql | 23 +++++++++++
5 files changed, 105 insertions(+)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 0bb7aeb40e..3169e2aeb7 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -28552,6 +28552,23 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_column_toast_chunk_id</primary>
+ </indexterm>
+ <function>pg_column_toast_chunk_id</function> ( <type>"any"</type> )
+ <returnvalue>oid</returnvalue>
+ </para>
+ <para>
+ Shows the <structfield>chunk_id</structfield> of an on-disk
+ <acronym>TOAST</acronym>ed value. Returns <literal>NULL</literal>
+ if the value is un-<acronym>TOAST</acronym>ed or not on-disk.
+ See <xref linkend="storage-toast-ondisk"/> for details about
+ <acronym>TOAST</acronym>.
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index 543afb66e5..84d36781a4 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -5105,6 +5105,47 @@ pg_column_compression(PG_FUNCTION_ARGS)
PG_RETURN_TEXT_P(cstring_to_text(result));
}
+/*
+ * Return the chunk_id of the on-disk TOASTed value.
+ * Return NULL if the value is unTOASTed or not on disk.
+ */
+Datum
+pg_column_toast_chunk_id(PG_FUNCTION_ARGS)
+{
+ int typlen;
+ struct varlena *attr;
+ struct varatt_external toast_pointer;
+
+ /* On first call, get the input type's typlen, and save at *fn_extra */
+ if (fcinfo->flinfo->fn_extra == NULL)
+ {
+ /* Lookup the datatype of the supplied argument */
+ Oid argtypeid = get_fn_expr_argtype(fcinfo->flinfo, 0);
+
+ typlen = get_typlen(argtypeid);
+ if (typlen == 0) /* should not happen */
+ elog(ERROR, "cache lookup failed for type %u", argtypeid);
+
+ fcinfo->flinfo->fn_extra = MemoryContextAlloc(fcinfo->flinfo->fn_mcxt,
+ sizeof(int));
+ *((int *) fcinfo->flinfo->fn_extra) = typlen;
+ }
+ else
+ typlen = *((int *) fcinfo->flinfo->fn_extra);
+
+ if (typlen != -1)
+ PG_RETURN_NULL();
+
+ attr = (struct varlena *) DatumGetPointer(PG_GETARG_DATUM(0));
+
+ if (!VARATT_IS_EXTERNAL_ONDISK(attr))
+ PG_RETURN_NULL();
+
+ VARATT_EXTERNAL_GET_POINTER(toast_pointer, attr);
+
+ PG_RETURN_OID(toast_pointer.va_valueid);
+}
+
/*
* string_agg - Concatenates values and returns string.
*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 291ed876fc..443ca854a6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7447,6 +7447,9 @@
{ oid => '2121', descr => 'compression method for the compressed datum',
proname => 'pg_column_compression', provolatile => 's', prorettype => 'text',
proargtypes => 'any', prosrc => 'pg_column_compression' },
+{ oid => '8393', descr => 'chunk ID of on-disk TOASTed value',
+ proname => 'pg_column_toast_chunk_id', provolatile => 's', prorettype => 'oid',
+ proargtypes => 'any', prosrc => 'pg_column_toast_chunk_id' },
{ oid => '2322',
descr => 'total disk space usage for the specified tablespace',
proname => 'pg_tablespace_size', provolatile => 'v', prorettype => 'int8',
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index d5f61dfad9..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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2024-03-08 22:17 Nathan Bossart <nathandbossart@gmail.com>
parent: Yugo NAGATA <nagata@sraoss.co.jp>
0 siblings, 1 reply; 26+ messages in thread
From: Nathan Bossart @ 2024-03-08 22:17 UTC (permalink / raw)
To: Yugo NAGATA <nagata@sraoss.co.jp>; +Cc: jian he <jian.universality@gmail.com>; Sergei Kornilov <sk@zsrv.org>; Nikita Malakhov <hukutoc@gmail.com>; pgsql-hackers
On Fri, Mar 08, 2024 at 03:31:55PM +0900, Yugo NAGATA wrote:
> On Thu, 7 Mar 2024 16:56:17 -0600
> Nathan Bossart <nathandbossart@gmail.com> wrote:
>> to me. Do you think it's worth adding something like a
>> pg_column_toast_num_chunks() function that returns the number of chunks for
>> the TOASTed value, too?
>
> If we want to know the number of chunks of a specified chunk_id,
> we can get this by the following query.
>
> postgres=# SELECT id, (SELECT count(*) FROM pg_toast.pg_toast_16384 WHERE chunk_id = id)
> FROM (SELECT pg_column_toast_chunk_id(v) AS id FROM t);
Good point. Overall, I think this patch is in decent shape, so I'll aim to
commit it sometime next week.
> +{ 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' },
Note to self: this change requires a catversion bump.
> +INSERT INTO test_chunk_id(v1,v2)
> + VALUES (repeat('x', 1), repeat('x', 2048));
Is this guaranteed to be TOASTed for all possible page sizes?
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2024-03-09 02:57 Yugo NAGATA <nagata@sraoss.co.jp>
parent: Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Yugo NAGATA @ 2024-03-09 02:57 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: jian he <jian.universality@gmail.com>; Sergei Kornilov <sk@zsrv.org>; Nikita Malakhov <hukutoc@gmail.com>; pgsql-hackers
On Fri, 8 Mar 2024 16:17:58 -0600
Nathan Bossart <nathandbossart@gmail.com> wrote:
> On Fri, Mar 08, 2024 at 03:31:55PM +0900, Yugo NAGATA wrote:
> > On Thu, 7 Mar 2024 16:56:17 -0600
> > Nathan Bossart <nathandbossart@gmail.com> wrote:
> >> to me. Do you think it's worth adding something like a
> >> pg_column_toast_num_chunks() function that returns the number of chunks for
> >> the TOASTed value, too?
> >
> > If we want to know the number of chunks of a specified chunk_id,
> > we can get this by the following query.
> >
> > postgres=# SELECT id, (SELECT count(*) FROM pg_toast.pg_toast_16384 WHERE chunk_id = id)
> > FROM (SELECT pg_column_toast_chunk_id(v) AS id FROM t);
>
> Good point. Overall, I think this patch is in decent shape, so I'll aim to
> commit it sometime next week.
Thank you.
>
> > +{ 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' },
>
> Note to self: this change requires a catversion bump.
>
> > +INSERT INTO test_chunk_id(v1,v2)
> > + VALUES (repeat('x', 1), repeat('x', 2048));
>
> Is this guaranteed to be TOASTed for all possible page sizes?
Should we use block_size?
SHOW block_size \gset
INSERT INTO test_chunk_id(v1,v2)
VALUES (repeat('x', 1), repeat('x', (:block_size / 4)));
I think this will work in various page sizes.
I've attached a patch in which the test is updated.
Regards,
Yugo Nagata
>
> --
> Nathan Bossart
> Amazon Web Services: https://aws.amazon.com
--
Yugo NAGATA <nagata@sraoss.co.jp>
Attachments:
[text/x-diff] v8-0001-Add-pg_column_toast_chunk_id-function.patch (6.6K, ../../20240309115718.deb9209b7f53883cc8e1b4b3@sraoss.co.jp/2-v8-0001-Add-pg_column_toast_chunk_id-function.patch)
download | inline diff:
From c3b99418d1ae3a8235db9bedd95e7d6ac1556f90 Mon Sep 17 00:00:00 2001
From: Yugo Nagata <nagata@sraoss.co.jp>
Date: Wed, 29 Mar 2023 09:59:25 +0900
Subject: [PATCH v8] Add pg_column_toast_chunk_id function
This function returns the chunk_id of an on-disk TOASTed value, or
NULL if the value is un-TOASTed or not on disk. This enables users to
know which columns are actually TOASTed. This function is also useful
to identify a problematic row when an error like
"ERROR: unexpected chunk number ... (expected ...) for toast value"
occurs.
---
doc/src/sgml/func.sgml | 17 ++++++++
src/backend/utils/adt/varlena.c | 41 ++++++++++++++++++++
src/include/catalog/pg_proc.dat | 3 ++
src/test/regress/expected/misc_functions.out | 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
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2024-03-09 14:50 Nathan Bossart <nathandbossart@gmail.com>
parent: Yugo NAGATA <nagata@sraoss.co.jp>
0 siblings, 1 reply; 26+ messages in thread
From: Nathan Bossart @ 2024-03-09 14:50 UTC (permalink / raw)
To: Yugo NAGATA <nagata@sraoss.co.jp>; +Cc: jian he <jian.universality@gmail.com>; Sergei Kornilov <sk@zsrv.org>; Nikita Malakhov <hukutoc@gmail.com>; pgsql-hackers
On Sat, Mar 09, 2024 at 11:57:18AM +0900, Yugo NAGATA wrote:
> On Fri, 8 Mar 2024 16:17:58 -0600
> Nathan Bossart <nathandbossart@gmail.com> wrote:
>> Is this guaranteed to be TOASTed for all possible page sizes?
>
> Should we use block_size?
>
> SHOW block_size \gset
> INSERT INTO test_chunk_id(v1,v2)
> VALUES (repeat('x', 1), repeat('x', (:block_size / 4)));
>
> I think this will work in various page sizes.
WFM
> +SHOW block_size; \gset
> + block_size
> +------------
> + 8192
> +(1 row)
I think we need to remove the ';' so that the output of the query is not
saved in the ".out" file. With that change, this test passes when Postgres
is built with --with-blocksize=32. However, many other unrelated tests
begin failing, so I guess this fix isn't tremendously important.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2024-03-12 08:51 Yugo NAGATA <nagata@sraoss.co.jp>
parent: Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Yugo NAGATA @ 2024-03-12 08:51 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: jian he <jian.universality@gmail.com>; Sergei Kornilov <sk@zsrv.org>; Nikita Malakhov <hukutoc@gmail.com>; pgsql-hackers
On Sat, 9 Mar 2024 08:50:28 -0600
Nathan Bossart <nathandbossart@gmail.com> wrote:
> On Sat, Mar 09, 2024 at 11:57:18AM +0900, Yugo NAGATA wrote:
> > On Fri, 8 Mar 2024 16:17:58 -0600
> > Nathan Bossart <nathandbossart@gmail.com> wrote:
> >> Is this guaranteed to be TOASTed for all possible page sizes?
> >
> > Should we use block_size?
> >
> > SHOW block_size \gset
> > INSERT INTO test_chunk_id(v1,v2)
> > VALUES (repeat('x', 1), repeat('x', (:block_size / 4)));
> >
> > I think this will work in various page sizes.
>
> WFM
>
> > +SHOW block_size; \gset
> > + block_size
> > +------------
> > + 8192
> > +(1 row)
>
> I think we need to remove the ';' so that the output of the query is not
> saved in the ".out" file. With that change, this test passes when Postgres
> is built with --with-blocksize=32. However, many other unrelated tests
> begin failing, so I guess this fix isn't tremendously important.
I rewrote the patch to use current_setting('block_size') instead of SHOW
and \gset as other tests do. Although some tests are failing with block_size=32,
I wonder it is a bit better to use "block_size" instead of the constant
to make the test more general to some extent.
Regards,
Yugo Nagata
>
> --
> Nathan Bossart
> Amazon Web Services: https://aws.amazon.com
--
Yugo NAGATA <nagata@sraoss.co.jp>
Attachments:
[text/x-diff] v9-0001-Add-pg_column_toast_chunk_id-function.patch (6.6K, ../../20240312155119.9341cfd76f2732ac93ecb6a4@sraoss.co.jp/2-v9-0001-Add-pg_column_toast_chunk_id-function.patch)
download | inline diff:
From 5b3be1ca6f8d8bafc1d9ce7bea252f364c9c09a9 Mon Sep 17 00:00:00 2001
From: Yugo Nagata <nagata@sraoss.co.jp>
Date: Wed, 29 Mar 2023 09:59:25 +0900
Subject: [PATCH v9] Add pg_column_toast_chunk_id function
This function returns the chunk_id of an on-disk TOASTed value, or
NULL if the value is un-TOASTed or not on disk. This enables users to
know which columns are actually TOASTed. This function is also useful
to identify a problematic row when an error like
"ERROR: unexpected chunk number ... (expected ...) for toast value"
occurs.
---
doc/src/sgml/func.sgml | 17 ++++++++
src/backend/utils/adt/varlena.c | 41 ++++++++++++++++++++
src/include/catalog/pg_proc.dat | 3 ++
src/test/regress/expected/misc_functions.out | 21 ++++++++++
src/test/regress/sql/misc_functions.sql | 23 +++++++++++
5 files changed, 105 insertions(+)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 0bb7aeb40e..3169e2aeb7 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -28552,6 +28552,23 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_column_toast_chunk_id</primary>
+ </indexterm>
+ <function>pg_column_toast_chunk_id</function> ( <type>"any"</type> )
+ <returnvalue>oid</returnvalue>
+ </para>
+ <para>
+ Shows the <structfield>chunk_id</structfield> of an on-disk
+ <acronym>TOAST</acronym>ed value. Returns <literal>NULL</literal>
+ if the value is un-<acronym>TOAST</acronym>ed or not on-disk.
+ See <xref linkend="storage-toast-ondisk"/> for details about
+ <acronym>TOAST</acronym>.
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index 543afb66e5..84d36781a4 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -5105,6 +5105,47 @@ pg_column_compression(PG_FUNCTION_ARGS)
PG_RETURN_TEXT_P(cstring_to_text(result));
}
+/*
+ * Return the chunk_id of the on-disk TOASTed value.
+ * Return NULL if the value is unTOASTed or not on disk.
+ */
+Datum
+pg_column_toast_chunk_id(PG_FUNCTION_ARGS)
+{
+ int typlen;
+ struct varlena *attr;
+ struct varatt_external toast_pointer;
+
+ /* On first call, get the input type's typlen, and save at *fn_extra */
+ if (fcinfo->flinfo->fn_extra == NULL)
+ {
+ /* Lookup the datatype of the supplied argument */
+ Oid argtypeid = get_fn_expr_argtype(fcinfo->flinfo, 0);
+
+ typlen = get_typlen(argtypeid);
+ if (typlen == 0) /* should not happen */
+ elog(ERROR, "cache lookup failed for type %u", argtypeid);
+
+ fcinfo->flinfo->fn_extra = MemoryContextAlloc(fcinfo->flinfo->fn_mcxt,
+ sizeof(int));
+ *((int *) fcinfo->flinfo->fn_extra) = typlen;
+ }
+ else
+ typlen = *((int *) fcinfo->flinfo->fn_extra);
+
+ if (typlen != -1)
+ PG_RETURN_NULL();
+
+ attr = (struct varlena *) DatumGetPointer(PG_GETARG_DATUM(0));
+
+ if (!VARATT_IS_EXTERNAL_ONDISK(attr))
+ PG_RETURN_NULL();
+
+ VARATT_EXTERNAL_GET_POINTER(toast_pointer, attr);
+
+ PG_RETURN_OID(toast_pointer.va_valueid);
+}
+
/*
* string_agg - Concatenates values and returns string.
*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 291ed876fc..443ca854a6 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -7447,6 +7447,9 @@
{ oid => '2121', descr => 'compression method for the compressed datum',
proname => 'pg_column_compression', provolatile => 's', prorettype => 'text',
proargtypes => 'any', prosrc => 'pg_column_compression' },
+{ oid => '8393', descr => 'chunk ID of on-disk TOASTed value',
+ proname => 'pg_column_toast_chunk_id', provolatile => 's', prorettype => 'oid',
+ proargtypes => 'any', prosrc => 'pg_column_toast_chunk_id' },
{ oid => '2322',
descr => 'total disk space usage for the specified tablespace',
proname => 'pg_tablespace_size', provolatile => 'v', prorettype => 'int8',
diff --git a/src/test/regress/expected/misc_functions.out b/src/test/regress/expected/misc_functions.out
index d5f61dfad9..ff25e967e2 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -703,3 +703,24 @@ SELECT has_function_privilege('regress_current_logfile',
(1 row)
DROP ROLE regress_current_logfile;
+-- Test pg_column_toast_chunk_id:
+-- Check if the returned chunk_id exists in the TOAST table
+CREATE TABLE test_chunk_id (v1 text, v2 text);
+-- Use uncompressed toast data
+ALTER TABLE test_chunk_id ALTER COLUMN v2 SET STORAGE EXTERNAL;
+-- v1: small enough not to be TOASTed
+-- v2: large enough to be TOASTed
+INSERT INTO test_chunk_id(v1,v2)
+ VALUES (repeat('x', 1), repeat('x', current_setting('block_size')::int / 4));
+DO $$
+ DECLARE result text default 'not_ok';
+ BEGIN
+ EXECUTE format(
+ 'SELECT ''ok'' FROM test_chunk_id
+ WHERE pg_column_toast_chunk_id(v1) IS NULL AND
+ pg_column_toast_chunk_id(v2) IN (SELECT chunk_id FROM pg_toast.pg_toast_%s)',
+ regclass('test_chunk_id')::int::text) INTO result;
+ RAISE INFO '%', result;
+END; $$;
+INFO: ok
+DROP TABLE test_chunk_id;
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index 928b04db7f..840f3564a1 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -265,3 +265,26 @@ GRANT pg_monitor TO regress_current_logfile;
SELECT has_function_privilege('regress_current_logfile',
'pg_current_logfile()', 'EXECUTE');
DROP ROLE regress_current_logfile;
+
+-- Test pg_column_toast_chunk_id:
+-- Check if the returned chunk_id exists in the TOAST table
+CREATE TABLE test_chunk_id (v1 text, v2 text);
+-- Use uncompressed toast data
+ALTER TABLE test_chunk_id ALTER COLUMN v2 SET STORAGE EXTERNAL;
+
+-- v1: small enough not to be TOASTed
+-- v2: large enough to be TOASTed
+INSERT INTO test_chunk_id(v1,v2)
+ VALUES (repeat('x', 1), repeat('x', current_setting('block_size')::int / 4));
+
+DO $$
+ DECLARE result text default 'not_ok';
+ BEGIN
+ EXECUTE format(
+ 'SELECT ''ok'' FROM test_chunk_id
+ WHERE pg_column_toast_chunk_id(v1) IS NULL AND
+ pg_column_toast_chunk_id(v2) IN (SELECT chunk_id FROM pg_toast.pg_toast_%s)',
+ regclass('test_chunk_id')::int::text) INTO result;
+ RAISE INFO '%', result;
+END; $$;
+DROP TABLE test_chunk_id;
--
2.25.1
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2024-03-13 03:07 Nathan Bossart <nathandbossart@gmail.com>
parent: Yugo NAGATA <nagata@sraoss.co.jp>
0 siblings, 1 reply; 26+ messages in thread
From: Nathan Bossart @ 2024-03-13 03:07 UTC (permalink / raw)
To: Yugo NAGATA <nagata@sraoss.co.jp>; +Cc: jian he <jian.universality@gmail.com>; Sergei Kornilov <sk@zsrv.org>; Nikita Malakhov <hukutoc@gmail.com>; pgsql-hackers
I did some light editing to prepare this for commit.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
Attachments:
[text/x-diff] v10-0001-Add-pg_column_toast_chunk_id.patch (6.0K, ../../20240313030717.GA2507890@nathanxps13/2-v10-0001-Add-pg_column_toast_chunk_id.patch)
download | inline diff:
From 38c78bd92a7b4d4600e6f0dbe58283c21ea87d50 Mon Sep 17 00:00:00 2001
From: Nathan Bossart <nathan@postgresql.org>
Date: Tue, 12 Mar 2024 22:04:25 -0500
Subject: [PATCH v10 1/1] Add pg_column_toast_chunk_id().
This function returns the chunk_id of an on-disk TOASTed value, or
NULL if the value is un-TOASTed or not on-disk. This is useful for
identifying which values are actually TOASTed and for investigating
"unexpected chunk number" errors.
XXX: REQUIRES CATVERSION BUMP
Author: Yugo Nagata
Reviewed-by: Jian He
Discussion: https://postgr.es/m/20230329105507.d764497456eeac1ca491b5bd%40sraoss.co.jp
---
doc/src/sgml/func.sgml | 17 ++++++++
src/backend/utils/adt/varlena.c | 41 ++++++++++++++++++++
src/include/catalog/pg_proc.dat | 3 ++
src/test/regress/expected/misc_functions.out | 16 ++++++++
src/test/regress/sql/misc_functions.sql | 12 ++++++
5 files changed, 89 insertions(+)
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 0bb7aeb40e..fe9c1a38b9 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -28552,6 +28552,23 @@ postgres=# SELECT '0/0'::pg_lsn + pd.segment_number * ps.setting::int + :offset
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>pg_column_toast_chunk_id</primary>
+ </indexterm>
+ <function>pg_column_toast_chunk_id</function> ( <type>"any"</type> )
+ <returnvalue>oid</returnvalue>
+ </para>
+ <para>
+ Shows the <structfield>chunk_id</structfield> of an on-disk
+ <acronym>TOAST</acronym>ed value. Returns <literal>NULL</literal>
+ if the value is un-<acronym>TOAST</acronym>ed or not on-disk. See
+ <xref linkend="storage-toast"/> for more information about
+ <acronym>TOAST</acronym>.
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index 543afb66e5..8d28dd42ce 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -5105,6 +5105,47 @@ pg_column_compression(PG_FUNCTION_ARGS)
PG_RETURN_TEXT_P(cstring_to_text(result));
}
+/*
+ * Return the chunk_id of the on-disk TOASTed value. Return NULL if the value
+ * is un-TOASTed or not on-disk.
+ */
+Datum
+pg_column_toast_chunk_id(PG_FUNCTION_ARGS)
+{
+ int typlen;
+ struct varlena *attr;
+ struct varatt_external toast_pointer;
+
+ /* On first call, get the input type's typlen, and save at *fn_extra */
+ if (fcinfo->flinfo->fn_extra == NULL)
+ {
+ /* Lookup the datatype of the supplied argument */
+ Oid argtypeid = get_fn_expr_argtype(fcinfo->flinfo, 0);
+
+ typlen = get_typlen(argtypeid);
+ if (typlen == 0) /* should not happen */
+ elog(ERROR, "cache lookup failed for type %u", argtypeid);
+
+ fcinfo->flinfo->fn_extra = MemoryContextAlloc(fcinfo->flinfo->fn_mcxt,
+ sizeof(int));
+ *((int *) fcinfo->flinfo->fn_extra) = typlen;
+ }
+ else
+ typlen = *((int *) fcinfo->flinfo->fn_extra);
+
+ if (typlen != -1)
+ PG_RETURN_NULL();
+
+ attr = (struct varlena *) DatumGetPointer(PG_GETARG_DATUM(0));
+
+ if (!VARATT_IS_EXTERNAL_ONDISK(attr))
+ PG_RETURN_NULL();
+
+ VARATT_EXTERNAL_GET_POINTER(toast_pointer, attr);
+
+ PG_RETURN_OID(toast_pointer.va_valueid);
+}
+
/*
* string_agg - Concatenates values and returns string.
*
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.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..e0ba9fdafa 100644
--- a/src/test/regress/expected/misc_functions.out
+++ b/src/test/regress/expected/misc_functions.out
@@ -703,3 +703,19 @@ SELECT has_function_privilege('regress_current_logfile',
(1 row)
DROP ROLE regress_current_logfile;
+-- pg_column_toast_chunk_id
+CREATE TABLE test_chunk_id (a TEXT, b TEXT STORAGE EXTERNAL);
+INSERT INTO test_chunk_id VALUES ('x', repeat('x', 8192));
+SELECT t.relname AS toastrel FROM pg_class c
+ LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
+ WHERE c.relname = 'test_chunk_id'
+\gset
+SELECT pg_column_toast_chunk_id(a) IS NULL,
+ pg_column_toast_chunk_id(b) IN (SELECT chunk_id FROM pg_toast.:toastrel)
+ FROM test_chunk_id;
+ ?column? | ?column?
+----------+----------
+ t | t
+(1 row)
+
+DROP TABLE test_chunk_id;
diff --git a/src/test/regress/sql/misc_functions.sql b/src/test/regress/sql/misc_functions.sql
index 928b04db7f..ff5ed5edde 100644
--- a/src/test/regress/sql/misc_functions.sql
+++ b/src/test/regress/sql/misc_functions.sql
@@ -265,3 +265,15 @@ GRANT pg_monitor TO regress_current_logfile;
SELECT has_function_privilege('regress_current_logfile',
'pg_current_logfile()', 'EXECUTE');
DROP ROLE regress_current_logfile;
+
+-- pg_column_toast_chunk_id
+CREATE TABLE test_chunk_id (a TEXT, b TEXT STORAGE EXTERNAL);
+INSERT INTO test_chunk_id VALUES ('x', repeat('x', 8192));
+SELECT t.relname AS toastrel FROM pg_class c
+ LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
+ WHERE c.relname = 'test_chunk_id'
+\gset
+SELECT pg_column_toast_chunk_id(a) IS NULL,
+ pg_column_toast_chunk_id(b) IN (SELECT chunk_id FROM pg_toast.:toastrel)
+ FROM test_chunk_id;
+DROP TABLE test_chunk_id;
--
2.25.1
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2024-03-13 06:09 Yugo NAGATA <nagata@sraoss.co.jp>
parent: Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Yugo NAGATA @ 2024-03-13 06:09 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: jian he <jian.universality@gmail.com>; Sergei Kornilov <sk@zsrv.org>; Nikita Malakhov <hukutoc@gmail.com>; pgsql-hackers
On Tue, 12 Mar 2024 22:07:17 -0500
Nathan Bossart <nathandbossart@gmail.com> wrote:
> I did some light editing to prepare this for commit.
Thank you. I confirmed the test you improved and I am fine with that.
Regards,
Yugo Nagata
>
> --
> Nathan Bossart
> Amazon Web Services: https://aws.amazon.com
--
Yugo NAGATA <nagata@sraoss.co.jp>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2024-03-14 16:10 Nathan Bossart <nathandbossart@gmail.com>
parent: Yugo NAGATA <nagata@sraoss.co.jp>
0 siblings, 1 reply; 26+ messages in thread
From: Nathan Bossart @ 2024-03-14 16:10 UTC (permalink / raw)
To: Yugo NAGATA <nagata@sraoss.co.jp>; +Cc: jian he <jian.universality@gmail.com>; Sergei Kornilov <sk@zsrv.org>; Nikita Malakhov <hukutoc@gmail.com>; pgsql-hackers
On Wed, Mar 13, 2024 at 01:09:18PM +0700, Yugo NAGATA wrote:
> On Tue, 12 Mar 2024 22:07:17 -0500
> Nathan Bossart <nathandbossart@gmail.com> wrote:
>> I did some light editing to prepare this for commit.
>
> Thank you. I confirmed the test you improved and I am fine with that.
Committed.
--
Nathan Bossart
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value
@ 2024-03-18 07:44 Yugo NAGATA <nagata@sraoss.co.jp>
parent: Nathan Bossart <nathandbossart@gmail.com>
0 siblings, 0 replies; 26+ messages in thread
From: Yugo NAGATA @ 2024-03-18 07:44 UTC (permalink / raw)
To: Nathan Bossart <nathandbossart@gmail.com>; +Cc: jian he <jian.universality@gmail.com>; Sergei Kornilov <sk@zsrv.org>; Nikita Malakhov <hukutoc@gmail.com>; pgsql-hackers
On Thu, 14 Mar 2024 11:10:42 -0500
Nathan Bossart <nathandbossart@gmail.com> wrote:
> On Wed, Mar 13, 2024 at 01:09:18PM +0700, Yugo NAGATA wrote:
> > On Tue, 12 Mar 2024 22:07:17 -0500
> > Nathan Bossart <nathandbossart@gmail.com> wrote:
> >> I did some light editing to prepare this for commit.
> >
> > Thank you. I confirmed the test you improved and I am fine with that.
>
> Committed.
Thank you!
>
> --
> Nathan Bossart
> Amazon Web Services: https://aws.amazon.com
--
Yugo NAGATA <nagata@sraoss.co.jp>
^ permalink raw reply [nested|flat] 26+ messages in thread
end of thread, other threads:[~2024-03-18 07:44 UTC | newest]
Thread overview: 26+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-03-29 01:55 pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value Yugo NAGATA <nagata@sraoss.co.jp>
2023-07-05 14:49 ` Nikita Malakhov <hukutoc@gmail.com>
2023-07-07 08:21 ` Yugo NAGATA <nagata@sraoss.co.jp>
2023-07-07 08:30 ` Yugo NAGATA <nagata@sraoss.co.jp>
2023-07-10 18:39 ` Sergei Kornilov <sk@zsrv.org>
2023-11-06 00:00 ` jian he <jian.universality@gmail.com>
2024-01-02 00:00 ` jian he <jian.universality@gmail.com>
2024-01-26 00:42 ` Yugo NAGATA <nagata@sraoss.co.jp>
2024-01-30 04:12 ` jian he <jian.universality@gmail.com>
2024-01-30 04:34 ` Yugo NAGATA <nagata@sraoss.co.jp>
2024-01-30 05:47 ` jian he <jian.universality@gmail.com>
2024-01-30 05:56 ` Yugo NAGATA <nagata@sraoss.co.jp>
2024-01-30 06:57 ` jian he <jian.universality@gmail.com>
2024-02-01 04:45 ` Yugo NAGATA <nagata@sraoss.co.jp>
2024-02-01 09:59 ` jian he <jian.universality@gmail.com>
2024-02-05 07:28 ` Yugo NAGATA <nagata@sraoss.co.jp>
2024-03-07 22:56 ` Nathan Bossart <nathandbossart@gmail.com>
2024-03-08 06:31 ` Yugo NAGATA <nagata@sraoss.co.jp>
2024-03-08 22:17 ` Nathan Bossart <nathandbossart@gmail.com>
2024-03-09 02:57 ` Yugo NAGATA <nagata@sraoss.co.jp>
2024-03-09 14:50 ` Nathan Bossart <nathandbossart@gmail.com>
2024-03-12 08:51 ` Yugo NAGATA <nagata@sraoss.co.jp>
2024-03-13 03:07 ` Nathan Bossart <nathandbossart@gmail.com>
2024-03-13 06:09 ` Yugo NAGATA <nagata@sraoss.co.jp>
2024-03-14 16:10 ` Nathan Bossart <nathandbossart@gmail.com>
2024-03-18 07:44 ` Yugo NAGATA <nagata@sraoss.co.jp>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox