Re: pg_column_toast_chunk_id: a function to get a chunk ID of a TOASTed value

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>