Fix interaction between materializing holdable cursors and firing

deferred triggers: either one can create more work for the other,
so we have to loop till it's all gone.  Per example from andrew@supernews.
Add a regression test to help spot trouble in this area in future.

2 files changed, 78 insertions, 0 deletions

diff --git a/src/test/regress/expected/portals.out b/src/test/regress/expected/portals.out
index a46a14ce80c..3f0e0cdd265 100644
--- a/src/test/regress/expected/portals.out
+++ b/src/test/regress/expected/portals.out
@@ -773,3 +773,38 @@ FETCH ALL FROM c;
 (15 rows)
 
 ROLLBACK;
+--
+-- Test behavior of both volatile and stable functions inside a cursor;
+-- in particular we want to see what happens during commit of a holdable
+-- cursor
+--
+create temp table tt1(f1 int);
+create function count_tt1_v() returns int8 as
+'select count(*) from tt1' language sql volatile;
+create function count_tt1_s() returns int8 as
+'select count(*) from tt1' language sql stable;
+begin;
+insert into tt1 values(1);
+declare c1 cursor for select count_tt1_v(), count_tt1_s();
+insert into tt1 values(2);
+fetch all from c1;
+ count_tt1_v | count_tt1_s 
+-------------+-------------
+           2 |           1
+(1 row)
+
+rollback;
+begin;
+insert into tt1 values(1);
+declare c2 cursor with hold for select count_tt1_v(), count_tt1_s();
+insert into tt1 values(2);
+commit;
+delete from tt1;
+fetch all from c2;
+ count_tt1_v | count_tt1_s 
+-------------+-------------
+           2 |           1
+(1 row)
+
+drop function count_tt1_v();
+drop function count_tt1_s();
diff --git a/src/test/regress/sql/portals.sql b/src/test/regress/sql/portals.sql
index c7e29c37868..da4e3b0e3ae 100644
--- a/src/test/regress/sql/portals.sql
+++ b/src/test/regress/sql/portals.sql
@@ -235,3 +235,46 @@ SELECT declares_cursor('AB%');
 FETCH ALL FROM c;
 
 ROLLBACK;
+
+--
+-- Test behavior of both volatile and stable functions inside a cursor;
+-- in particular we want to see what happens during commit of a holdable
+-- cursor
+--
+
+create temp table tt1(f1 int);
+
+create function count_tt1_v() returns int8 as
+'select count(*) from tt1' language sql volatile;
+
+create function count_tt1_s() returns int8 as
+'select count(*) from tt1' language sql stable;
+
+begin;
+
+insert into tt1 values(1);
+
+declare c1 cursor for select count_tt1_v(), count_tt1_s();
+
+insert into tt1 values(2);
+
+fetch all from c1;
+
+rollback;
+
+begin;
+
+insert into tt1 values(1);
+
+declare c2 cursor with hold for select count_tt1_v(), count_tt1_s();
+
+insert into tt1 values(2);
+
+commit;
+
+delete from tt1;
+
+fetch all from c2;
+
+drop function count_tt1_v();
+drop function count_tt1_s();