On 19.02.2021 10:47, Pavel Stehule wrote:
pá 19. 2. 2021 v 8:39 odesílatel Konstantin Knizhnik
<k.knizh...@postgrespro.ru <mailto:k.knizh...@postgrespro.ru>> napsal:
On 19.02.2021 10:14, Pavel Stehule wrote:
pá 19. 2. 2021 v 7:51 odesílatel Konstantin Knizhnik
<k.knizh...@postgrespro.ru <mailto:k.knizh...@postgrespro.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?
This is just an idea - I think the most correct with zero performance
impact is keeping snapshot, and this can be stored in procedure_resowner.
The fundamental question is if we want or allow more snapshots per
query. The implementation is a secondary issue.
I wonder if it is correct from logical point of view.
If we commit transaction in stored procedure, then we actually
implicitly start new transaction.
And new transaction should have new snapshot. Otherwise its behavior
will change.
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
