Re: Problem with accessing TOAST data in stored procedures

On 18.02.2021 20:10, Pavel Stehule wrote:
> This has a negative impact on performance - and a lot of users use
> procedures without transaction control. So it doesn't look like a good
> solution.
>
> I am more concentrated on the Pg 14 release, where the work with SPI
> is redesigned, and I hope so this issue is fixed there. For older
> releases, I don't know. Is this issue related to Postgres or it is
> related to PgPro only? If it is related to community pg, then we
> should fix and we should accept not too good performance, because
> there is no better non invasive solution. If it is PgPro issue
> (because there are ATX support) you can fix it (or you can try
> backport the patch
> https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=ee895a655ce4341546facd6f23e3e8f2931b96bf
> ). You have more possibilities on PgPro code base.

Sorry, it is not PgPro specific problem and recent master suffers from
this bug as well.
In the original bug report there was simple scenario of reproducing the
problem:

CREATE TABLE toasted(id serial primary key, data text);
INSERT INTO toasted(data) VALUES((SELECT string_agg(random()::text,':')
FROM generate_series(1, 1000)));
INSERT INTO toasted(data) VALUES((SELECT string_agg(random()::text,':')
FROM generate_series(1, 1000)));
DO $$ DECLARE v_r record; BEGIN FOR v_r in SELECT data FROM toasted LOOP
INSERT INTO toasted(data) VALUES(v_r.data);COMMIT;END LOOP;END;$$;

--

Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company