Re: Problem with accessing TOAST data in stored procedures

On 19.02.2021 10:14, Pavel Stehule wrote:
>
>
> pá 19. 2. 2021 v 7:51 odesílatel Konstantin Knizhnik
> <k(dot)knizhnik(at)postgrespro(dot)ru <mailto: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?
>
Sorry, I do not understanf your suggestion.
How procedure_resowner can help to solve this problem?

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