0002-fix-holdable-cursor-anomaly.patch

diff --git a/src/backend/commands/portalcmds.c b/src/backend/commands/portalcmds.c
index b8590d422b..3224261419 100644
--- a/src/backend/commands/portalcmds.c
+++ b/src/backend/commands/portalcmds.c
@@ -375,6 +375,15 @@ PersistHoldablePortal(Portal portal)
 		 * can be processed.  Otherwise, store only the not-yet-fetched rows.
 		 * (The latter is not only more efficient, but avoids semantic
 		 * problems if the query's output isn't stable.)
+		 *
+		 * In the no-scroll case, tuple indexes in the tuplestore will not
+		 * match the cursor's nominal position (portalPos).  Currently this
+		 * causes no difficulty because we only navigate in the tuplestore by
+		 * relative position, except for the tuplestore_skiptuples call below
+		 * and the tuplestore_rescan call in DoPortalRewind, both of which are
+		 * disabled for no-scroll cursors.  But someday we might need to track
+		 * the offset between the holdStore and the cursor's nominal position
+		 * explicitly.
 		 */
 		if (portal->cursorOptions & CURSOR_OPT_SCROLL)
 		{
@@ -382,10 +391,6 @@ PersistHoldablePortal(Portal portal)
 		}
 		else
 		{
-			/* Disallow moving backwards from here */
-			portal->atStart = true;
-			portal->portalPos = 0;
-
 			/*
 			 * If we already reached end-of-query, set the direction to
 			 * NoMovement to avoid trying to fetch any tuples.  (This check
@@ -443,10 +448,17 @@ PersistHoldablePortal(Portal portal)
 		{
 			tuplestore_rescan(portal->holdStore);
 
-			if (!tuplestore_skiptuples(portal->holdStore,
-									   portal->portalPos,
-									   true))
-				elog(ERROR, "unexpected end of tuple stream");
+			/*
+			 * In the no-scroll case, the start of the tuplestore is exactly
+			 * where we want to be, so no repositioning is wanted.
+			 */
+			if (portal->cursorOptions & CURSOR_OPT_SCROLL)
+			{
+				if (!tuplestore_skiptuples(portal->holdStore,
+										   portal->portalPos,
+										   true))
+					elog(ERROR, "unexpected end of tuple stream");
+			}
 		}
 	}
 	PG_CATCH();
diff --git a/src/test/regress/expected/portals.out b/src/test/regress/expected/portals.out
index d98c58d303..9da74876e1 100644
--- a/src/test/regress/expected/portals.out
+++ b/src/test/regress/expected/portals.out
@@ -781,6 +781,43 @@ SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
 (1 row)
 
 CLOSE foo25;
+BEGIN;
+DECLARE foo25ns NO SCROLL CURSOR WITH HOLD FOR SELECT * FROM tenk2;
+FETCH FROM foo25ns;
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+    8800 |       0 |   0 |    0 |   0 |      0 |       0 |      800 |         800 |      3800 |     8800 |   0 |    1 | MAAAAA   | AAAAAA   | AAAAxx
+(1 row)
+
+FETCH FROM foo25ns;
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+    1891 |       1 |   1 |    3 |   1 |     11 |      91 |      891 |        1891 |      1891 |     1891 | 182 |  183 | TUAAAA   | BAAAAA   | HHHHxx
+(1 row)
+
+COMMIT;
+FETCH FROM foo25ns;
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+    3420 |       2 |   0 |    0 |   0 |      0 |      20 |      420 |        1420 |      3420 |     3420 |  40 |   41 | OBAAAA   | CAAAAA   | OOOOxx
+(1 row)
+
+FETCH ABSOLUTE 4 FROM foo25ns;
+ unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 
+---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+---------
+    9850 |       3 |   0 |    2 |   0 |     10 |      50 |      850 |        1850 |      4850 |     9850 | 100 |  101 | WOAAAA   | DAAAAA   | VVVVxx
+(1 row)
+
+FETCH ABSOLUTE 4 FROM foo25ns; -- fail
+ERROR:  cursor can only scan forward
+HINT:  Declare it with SCROLL option to enable backward scan.
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+  name   |                              statement                              | is_holdable | is_binary | is_scrollable 
+---------+---------------------------------------------------------------------+-------------+-----------+---------------
+ foo25ns | DECLARE foo25ns NO SCROLL CURSOR WITH HOLD FOR SELECT * FROM tenk2; | t           | f         | f
+(1 row)
+
+CLOSE foo25ns;
 --
 -- ROLLBACK should close holdable cursors
 --
diff --git a/src/test/regress/sql/portals.sql b/src/test/regress/sql/portals.sql
index 2a098a43b9..eadf6ed942 100644
--- a/src/test/regress/sql/portals.sql
+++ b/src/test/regress/sql/portals.sql
@@ -229,6 +229,26 @@ SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
 
 CLOSE foo25;
 
+BEGIN;
+
+DECLARE foo25ns NO SCROLL CURSOR WITH HOLD FOR SELECT * FROM tenk2;
+
+FETCH FROM foo25ns;
+
+FETCH FROM foo25ns;
+
+COMMIT;
+
+FETCH FROM foo25ns;
+
+FETCH ABSOLUTE 4 FROM foo25ns;
+
+FETCH ABSOLUTE 4 FROM foo25ns; -- fail
+
+SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors;
+
+CLOSE foo25ns;
+
 --
 -- ROLLBACK should close holdable cursors
 --