aboutsummaryrefslogtreecommitdiff
path: root/src/test
diff options
context:
space:
mode:
authorTom Lane <tgl@sss.pgh.pa.us>2005-04-11 19:51:32 +0000
committerTom Lane <tgl@sss.pgh.pa.us>2005-04-11 19:51:32 +0000
commitfa57fd1c0a4b9dee0bbd932c20d773d56a88a78f (patch)
tree0be076c1a21005086da1bda3b958f7c46af29f82 /src/test
parentadd2c3f4d6b137b35c097354438779aacde2f1d9 (diff)
downloadpostgresql-fa57fd1c0a4b9dee0bbd932c20d773d56a88a78f.tar.gz
postgresql-fa57fd1c0a4b9dee0bbd932c20d773d56a88a78f.zip
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.
Diffstat (limited to 'src/test')
-rw-r--r--src/test/regress/expected/portals.out35
-rw-r--r--src/test/regress/sql/portals.sql43
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();