Re: Problem with accessing TOAST data in stored procedures

pá 19. 2. 2021 v 7:51 odesílatel Konstantin Knizhnik <
k(dot)knizhnik(at)postgrespro(dot)ru> napsal:

>
>
> 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;$$;
>

can you use new procedure_resowner?

Regards

Pavel

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