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)
--
Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company
