On 12.05.2021 05:42, Tom Lane wrote:
If you're in a position to apply the patch and see if it resolves your real non-simplified case, that would be very helpful. Also, this fix in principle will create a small performance penalty for FOR-loops in non-atomic contexts such as DO loops. It'd be interesting to know if the penalty is noticeable in your usage.
Provided I understood your comment in the code, the slight performance impact should be due to the disabled prefetching. That should not be an issue, but I have not yet tested this. What I have tested, I applied the patch to master and tested the actual code against the new build on a small testserver. This works for me.
However, I poked around a little bit, and this does not seem to solve all potential use cases, when I modify the simplified test by deferring the lookup to be done inside the loop as an extra lookup instead of doing it directly in the loop (whether that makes sense is another question) , then this still produces the error:
\echo test1 DO $$ DECLARE r record; t text; BEGIN FOR r in (SELECT i FROM test1) LOOP select txt into t from test1 where i=r.i; COMMIT; END LOOP; END; $$; \echo test2 DO $$ DECLARE r record; t text; BEGIN FOR r in (SELECT i FROM test2) LOOP select txt into t from test2 where i=r.i; COMMIT; END LOOP; END; $$; test1 DO test2 psql:snapshot_error.sql:38: ERROR: no known snapshots CONTEXT: PL/pgSQL function inline_code_block line 6 at FOR over SELECT rows
