From 942fdc60dfa5bff31b533e7ada15927f9f0bad77 Mon Sep 17 00:00:00 2001 From: Pallavi Sontakke Date: Thu, 16 Jun 2016 17:42:03 +0530 Subject: [PATCH] Separate out INSENSITIVE cursor Issue ( #34) Move issue in xl_known_bugs to track it separately. --- src/test/regress/expected/xl_known_bugs.out | 37 +++++++++ src/test/regress/expected/xl_limitations.out | 81 -------------------- src/test/regress/sql/xl_known_bugs.sql | 31 ++++++++ src/test/regress/sql/xl_limitations.sql | 20 ----- 4 files changed, 68 insertions(+), 101 deletions(-) diff --git a/src/test/regress/expected/xl_known_bugs.out b/src/test/regress/expected/xl_known_bugs.out index cfef50f969..bd210881a2 100644 --- a/src/test/regress/expected/xl_known_bugs.out +++ b/src/test/regress/expected/xl_known_bugs.out @@ -714,3 +714,40 @@ select pct, count(unique1) from (1 row) ------------------------------------------ +-- Issue #34 - Insensitive cursors +-- insensitive cursor would be insensitive to updates happening to the table +-- right now it is sensitive which is a bug (34) in Postgres-XL issue tracker sheet. +create table xl_PLine ( + slotname char(20), + phonenumber char(20), + comment text, + backlink char(20) +); +create unique index xl_PLine_n on xl_PLine using btree (slotname bpchar_ops); +insert into xl_PLine values ('PL.029', '-502', 'Fax first floor', 'PS.first.ta1'); +insert into xl_PLine values ('PL.030', '-367', '', 'PS.first.tb6'); +BEGIN; +declare xl_ins_cur INSENSITIVE CURSOR for select * from xl_Pline order by slotname desc; +FETCH FIRST xl_ins_cur; + slotname | phonenumber | comment | backlink +----------------------+----------------------+---------+---------------------- + PL.030 | -367 | | PS.first.tb6 +(1 row) + +delete from xl_Pline where slotname in ('PL.030'); +FETCH FIRST xl_ins_cur; + slotname | phonenumber | comment | backlink +----------------------+----------------------+-----------------+---------------------- + PL.030 | -367 | | PS.first.tb6 +(1 row) + +delete from xl_Pline where slotname in ('PL.029'); +FETCH FIRST xl_ins_cur; + slotname | phonenumber | comment | backlink +----------+-------------+---------+---------- + PL.030 | -367 | | PS.first.tb6 +(1 row) + +COMMIT; +drop table xl_PLine; +------------------------------------------ diff --git a/src/test/regress/expected/xl_limitations.out b/src/test/regress/expected/xl_limitations.out index 3f086c006c..1a5072c8b8 100755 --- a/src/test/regress/expected/xl_limitations.out +++ b/src/test/regress/expected/xl_limitations.out @@ -429,87 +429,6 @@ FETCH BACKWARD 2 xl_scroll_cursor1; DELETE FROM xl_Pline WHERE CURRENT OF xl_scroll_cursor1; ERROR: WHERE CURRENT OF clause not yet supported COMMIT; ---insensitive cursor would be insensitive to updates happening to the table --- right now it is sensitive which is a bug (34) in Postgres-XL issue tracker sheet. -BEGIN; -declare xl_ins_cursor INSENSITIVE CURSOR for select * from xl_Pline order by slotname desc; -FETCH FIRST xl_ins_cursor; - slotname | phonenumber | comment | backlink -----------------------+----------------------+-----------------+---------------------- - PL.029 | -502 | Fax first floor | PS.first.ta1 -(1 row) - -FETCH ABSOLUTE 5 xl_ins_cursor; - slotname | phonenumber | comment | backlink -----------------------+----------------------+---------+---------------------- - PL.025 | -367 | | PS.first.tb6 -(1 row) - -update xl_Pline set phonenumber='-503' where slotname = 'PL.029'; -insert into xl_PLine values ('PL.030', '-367', '', 'PS.first.tb6'); -FETCH FIRST xl_ins_cursor; - slotname | phonenumber | comment | backlink -----------------------+----------------------+---------+---------------------- - PL.030 | -367 | | PS.first.tb6 -(1 row) - -select xl_nodename_from_id(xc_node_id), * from xl_Pline where slotname in ('PL.030', 'PL.029') order by slotname; - xl_nodename_from_id | slotname | phonenumber | comment | backlink ----------------------+----------------------+----------------------+-----------------+---------------------- - datanode_1 | PL.029 | -503 | Fax first floor | PS.first.ta1 - datanode_1 | PL.030 | -367 | | PS.first.tb6 -(2 rows) - -delete from xl_Pline where slotname in ('PL.030'); -FETCH FIRST xl_ins_cursor; - slotname | phonenumber | comment | backlink -----------------------+----------------------+-----------------+---------------------- - PL.029 | -503 | Fax first floor | PS.first.ta1 -(1 row) - -delete from xl_Pline where slotname in ('PL.029'); -FETCH FIRST xl_ins_cursor; - slotname | phonenumber | comment | backlink -----------------------+----------------------+--------------+---------------------- - PL.028 | -501 | Fax entrance | PS.base.ta2 -(1 row) - -insert into xl_PLine values ('PL.029', '-367', '', 'PS.first.tb6'); -FETCH ALL FROM xl_ins_cursor; - slotname | phonenumber | comment | backlink -----------------------+----------------------+--------------+---------------------- - PL.027 | -367 | | PS.first.tb6 - PL.026 | -367 | | PS.first.tb6 - PL.025 | -367 | | PS.first.tb6 - PL.024 | -367 | | PS.first.tb6 - PL.023 | -367 | | PS.first.tb6 - PL.022 | -367 | | PS.first.tb6 - PL.021 | -365 | | PS.first.tb5 - PL.020 | -364 | | PS.first.tb3 - PL.019 | -363 | | PS.first.tb2 - PL.018 | -362 | | PS.first.tb1 - PL.017 | -139 | | PS.first.ta4 - PL.016 | -137 | | PS.first.ta3 - PL.015 | -134 | | PS.first.ta1 - PL.014 | -122 | | PS.base.tb6 - PL.013 | -122 | | PS.base.tb6 - PL.012 | -122 | | PS.base.tb6 - PL.011 | -122 | | PS.base.tb6 - PL.010 | -122 | | PS.base.tb6 - PL.009 | -121 | | PS.base.tb5 - PL.008 | -109 | | PS.base.tb4 - PL.007 | -108 | | PS.base.tb3 - PL.006 | -106 | | PS.base.tb2 - PL.005 | -104 | | PS.base.ta6 - PL.004 | -103 | | PS.base.ta5 - PL.003 | -102 | | PS.base.ta3 - PL.002 | -101 | | PS.base.ta2 - PL.001 | -0 | Central call | PS.base.ta1 -(27 rows) - -DELETE FROM xl_Pline WHERE CURRENT OF xl_ins_cursor; -ERROR: WHERE CURRENT OF clause not yet supported -COMMIT; --with hold cursor wold be available after the transaction that successfully committed it is gone BEGIN; declare xl_with_hold_cursor CURSOR WITH HOLD for select * from xl_Pline order by slotname; diff --git a/src/test/regress/sql/xl_known_bugs.sql b/src/test/regress/sql/xl_known_bugs.sql index d1fa0519df..093e0419e9 100644 --- a/src/test/regress/sql/xl_known_bugs.sql +++ b/src/test/regress/sql/xl_known_bugs.sql @@ -271,3 +271,34 @@ select pct, count(unique1) from group by pct; ------------------------------------------ + +-- Issue #34 - Insensitive cursors + +-- insensitive cursor would be insensitive to updates happening to the table +-- right now it is sensitive which is a bug (34) in Postgres-XL issue tracker sheet. + +create table xl_PLine ( + slotname char(20), + phonenumber char(20), + comment text, + backlink char(20) +); + +create unique index xl_PLine_n on xl_PLine using btree (slotname bpchar_ops); + +insert into xl_PLine values ('PL.029', '-502', 'Fax first floor', 'PS.first.ta1'); +insert into xl_PLine values ('PL.030', '-367', '', 'PS.first.tb6'); + +BEGIN; +declare xl_ins_cur INSENSITIVE CURSOR for select * from xl_Pline order by slotname desc; + +FETCH FIRST xl_ins_cur; +delete from xl_Pline where slotname in ('PL.030'); +FETCH FIRST xl_ins_cur; +delete from xl_Pline where slotname in ('PL.029'); +FETCH FIRST xl_ins_cur; +COMMIT; + +drop table xl_PLine; + +------------------------------------------ diff --git a/src/test/regress/sql/xl_limitations.sql b/src/test/regress/sql/xl_limitations.sql index 97387f6b64..cf795ea60b 100755 --- a/src/test/regress/sql/xl_limitations.sql +++ b/src/test/regress/sql/xl_limitations.sql @@ -223,26 +223,6 @@ FETCH BACKWARD 2 xl_scroll_cursor1; DELETE FROM xl_Pline WHERE CURRENT OF xl_scroll_cursor1; COMMIT; ---insensitive cursor would be insensitive to updates happening to the table --- right now it is sensitive which is a bug (34) in Postgres-XL issue tracker sheet. -BEGIN; -declare xl_ins_cursor INSENSITIVE CURSOR for select * from xl_Pline order by slotname desc; - -FETCH FIRST xl_ins_cursor; -FETCH ABSOLUTE 5 xl_ins_cursor; -update xl_Pline set phonenumber='-503' where slotname = 'PL.029'; -insert into xl_PLine values ('PL.030', '-367', '', 'PS.first.tb6'); -FETCH FIRST xl_ins_cursor; -select xl_nodename_from_id(xc_node_id), * from xl_Pline where slotname in ('PL.030', 'PL.029') order by slotname; -delete from xl_Pline where slotname in ('PL.030'); -FETCH FIRST xl_ins_cursor; -delete from xl_Pline where slotname in ('PL.029'); -FETCH FIRST xl_ins_cursor; -insert into xl_PLine values ('PL.029', '-367', '', 'PS.first.tb6'); -FETCH ALL FROM xl_ins_cursor; -DELETE FROM xl_Pline WHERE CURRENT OF xl_ins_cursor; -COMMIT; - --with hold cursor wold be available after the transaction that successfully committed it is gone BEGIN; -- 2.39.5