Re: ALTER TABLE uses a bistate but not for toast tables

Hi!

Found this discussion for our experiments with TOAST, I'd have to check it
under [1].
I'm not sure, what behavior is expected when the main table is unpinned,
bulk insert
to the TOAST table is in progress, and the second query with a heavy bulk
insert to
the same TOAST table comes in?

Thank you!

[1]
https://www.postgresql.org/message-id/flat/224711f9-83b7-a307-b17f-4457ab73aa0a(at)sigaev(dot)ru

On Sun, Nov 27, 2022 at 11:15 PM Justin Pryzby <pryzby(at)telsasoft(dot)com> wrote:

> On Wed, Sep 07, 2022 at 10:48:39AM +0200, Drouvot, Bertrand wrote:
> > Hi,
> >
> > On 6/22/22 4:38 PM, Justin Pryzby wrote:
> > > ATRewriteTable() calls table_tuple_insert() with a bistate, to avoid
> clobbering
> > > and polluting the buffers.
> > >
> > > But heap_insert() then calls
> > > heap_prepare_insert() >
> > > heap_toast_insert_or_update >
> > > toast_tuple_externalize >
> > > toast_save_datum >
> > > heap_insert(toastrel, toasttup, mycid, options, NULL /* without
> bistate:( */);
> >
> > What do you think about creating earlier a new dedicated bistate for the
> > toast table?
>
> Yes, but I needed to think about what data structure to put it in...
>
> Here, I created a 2nd bistate for toast whenever creating a bistate for
> heap. That avoids the need to add arguments to tableam's
> table_tuple_insert(), in addition to the 6 other functions in the call
> stack.
>
> I also updated rewriteheap.c to handle the same problem in CLUSTER:
>
> postgres=# DROP TABLE t; CREATE TABLE t AS SELECT i,
> repeat((5555555+i)::text, 123456)t FROM generate_series(1,9999)i;
> postgres=# VACUUM FULL VERBOSE t ; SELECT COUNT(1), datname,
> coalesce(c.relname,b.relfilenode::text), d.relname FROM pg_buffercache b
> LEFT JOIN pg_class c ON b.relfilenode=pg_relation_filenode(c.oid) LEFT JOIN
> pg_class d ON d.reltoastrelid=c.oid LEFT JOIN pg_database db ON
> db.oid=b.reldatabase GROUP BY 2,3,4 ORDER BY 1 DESC LIMIT 22;
>
> Unpatched:
> 5000 | postgres | pg_toast_96188840 | t
> => 40MB of shared buffers
>
> Patched:
> 2048 | postgres | pg_toast_17097 | t
>
> Note that a similar problem seems to exist in COPY ... but I can't see
> how to fix that one.
>
> --
> Justin
>

--
Regards,
Nikita Malakhov
Postgres Professional
https://postgrespro.ru/