Re: Problem with accessing TOAST data in stored procedures

pá 19. 2. 2021 v 8:17 odesílatel Konstantin Knizhnik <
k(dot)knizhnik(at)postgrespro(dot)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
>
>