From fa57fd1c0a4b9dee0bbd932c20d773d56a88a78f Mon Sep 17 00:00:00 2001
From: Tom Lane <tgl@sss.pgh.pa.us>
Date: Mon, 11 Apr 2005 19:51:32 +0000
Subject: 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.

---
 src/test/regress/expected/portals.out | 35 ++++++++++++++++++++++++++++
 src/test/regress/sql/portals.sql      | 43 +++++++++++++++++++++++++++++++++++
 2 files changed, 78 insertions(+)

(limited to 'src/test')

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();
-- 
cgit v1.2.3