Re: Problem with accessing TOAST data in stored procedures

> 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