pá 19. 2. 2021 v 8:17 odesílatel Konstantin Knizhnik < k.knizh...@postgrespro.ru> napsal:
> > I am sorry, maybe my reply was not (is not) correct - this issue was > reported four months ago, and now I think more about your words about ATX, > and I have no idea, how much it is related to community pg or to pgpro. > > I am sure so implementation of autonomous transactions is pretty hard, but > the described issue is related to PgPro implementation of ATX, and then it > should be fixed there. Disabling prefetching doesn't look like a good idea. > You try to fix the result, not the source of the problem - but I have not > any idea, what is possible and what not, because I don't know how PgPro ATX > is implemented. > > > I think there is some misunderstanding. > Sorry if I my explanation was not clear. > > This problem is not related with ATX and PgPro. Actually ATX correctly > handle this case (when iteration through query results cross transaction > commit). > It is the problem of transaction control in stored procedures in vanilla > Postgres and it is not yet resolved. > I refer to ATX in PgPro just as example of how this problem can be solved > with different transaction control model. > But this approach is not (IMHO) applicable to stored procedures. > > I do not think that this problem is so critical. > Not so many people are using stored procedures (which were added to the > Postgres not so long time ago), > not all of them are performing transaction control inside them and even > less of them interleave loop over query results with transactions commits. > But there are such people and we have received correspondent bug reports. > So I think it should be somehow fixed. > > I do not know good solution of the problem. > There are three possibilities: > 1. Disable prefetch > 2. Keep snapshot (which seems to be incorrect) > 3. Materialize prefetched tuples before commit (seems to be non-trivial) > > I am not sure if disabling prefetch for this case is the correct solution. Probably not if you got a new snapshot, then the cursor will be "sensitive", but other Postgres cursors are "insensitive". Implementation of materialization should not be very hard - you will do only copy tuples to some local buffers, but it doesn't say if the result will be correct, because you mix more snapshots. So keeping snapshots looks like a more correct solution - although there can be inconsistency against current snapshot, the result is very similar to full materialization. Regards Pavel > > -- > Konstantin Knizhnik > Postgres Professional: http://www.postgrespro.com > The Russian Postgres Company > >
