Allow committing inside cursor loop

Previously, committing or aborting inside a cursor loop was prohibited
because that would close and remove the cursor.  To allow that,
automatically convert such cursors to holdable cursors so they survive
commits or rollbacks.  Portals now have a new state "auto-held", which
means they have been converted automatically from pinned.  An auto-held
portal is kept on transaction commit or rollback, but is still removed
when returning to the main loop on error.

This supports all languages that have cursor loop constructs: PL/pgSQL,
PL/Python, PL/Perl.

Reviewed-by: Ildus Kurbangaliev <i.kurbangaliev@postgrespro.ru>

1 files changed, 36 insertions, 0 deletions

diff --git a/src/pl/plpython/sql/plpython_transaction.sql b/src/pl/plpython/sql/plpython_transaction.sql
index b337d4e3006..33b37e5b7fc 100644
--- a/src/pl/plpython/sql/plpython_transaction.sql
+++ b/src/pl/plpython/sql/plpython_transaction.sql
@@ -99,6 +99,23 @@ $$;
 
 SELECT * FROM test1;
 
+-- check that this doesn't leak a holdable portal
+SELECT * FROM pg_cursors;
+
+
+-- error in cursor loop with commit
+TRUNCATE test1;
+
+DO LANGUAGE plpythonu $$
+for row in plpy.cursor("SELECT * FROM test2 ORDER BY x"):
+    plpy.execute("INSERT INTO test1 (a) VALUES (12/(%s-2))" % row['x'])
+    plpy.commit()
+$$;
+
+SELECT * FROM test1;
+
+SELECT * FROM pg_cursors;
+
 
 -- rollback inside cursor loop
 TRUNCATE test1;
@@ -111,6 +128,25 @@ $$;
 
 SELECT * FROM test1;
 
+SELECT * FROM pg_cursors;
+
+
+-- first commit then rollback inside cursor loop
+TRUNCATE test1;
+
+DO LANGUAGE plpythonu $$
+for row in plpy.cursor("SELECT * FROM test2 ORDER BY x"):
+    plpy.execute("INSERT INTO test1 (a) VALUES (%s)" % row['x'])
+    if row['x'] % 2 == 0:
+        plpy.commit()
+    else:
+        plpy.rollback()
+$$;
+
+SELECT * FROM test1;
+
+SELECT * FROM pg_cursors;
+
 
 DROP TABLE test1;
 DROP TABLE test2;