pá 19. 2. 2021 v 9:08 odesílatel Konstantin Knizhnik < k.knizh...@postgrespro.ru> napsal:
> > > On 19.02.2021 10:47, Pavel Stehule wrote: > > > > pá 19. 2. 2021 v 8:39 odesílatel Konstantin Knizhnik < > 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> 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. > I have no problem with this. I have a problem with cycle implementation - when I iterate over some result, then this result should be consistent over all cycles. In other cases, the behaviour is not deterministic. > -- > Konstantin Knizhnik > Postgres Professional: http://www.postgrespro.com > The Russian Postgres Company > >
