Repository: incubator-hawq Updated Branches: refs/heads/master a5a2e6d13 -> 513388129
HAWQ-834. Refactor goh_portals checkinstall cases using new test framework Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq/commit/51338812 Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/51338812 Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/51338812 Branch: refs/heads/master Commit: 513388129f2b60539f84e4dbc2f57a46b624c4bf Parents: a5a2e6d Author: Yi <[email protected]> Authored: Tue Aug 16 11:49:17 2016 +1000 Committer: Yi <[email protected]> Committed: Tue Aug 16 11:49:17 2016 +1000 ---------------------------------------------------------------------- src/test/feature/query/ans/portal-basic.ans | 578 +++++++++++++++++++++++ src/test/feature/query/sql/portal-basic.sql | 189 ++++++++ src/test/feature/query/test_portal.cpp | 42 ++ src/test/regress/expected/goh_portals.out | 541 --------------------- src/test/regress/known_good_schedule | 1 - src/test/regress/sql/goh_portals.sql | 206 -------- 6 files changed, 809 insertions(+), 748 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/51338812/src/test/feature/query/ans/portal-basic.ans ---------------------------------------------------------------------- diff --git a/src/test/feature/query/ans/portal-basic.ans b/src/test/feature/query/ans/portal-basic.ans new file mode 100644 index 0000000..6778f33 --- /dev/null +++ b/src/test/feature/query/ans/portal-basic.ans @@ -0,0 +1,578 @@ +BEGIN; +BEGIN +DECLARE foo1 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; +DECLARE CURSOR +DECLARE foo2 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; +DECLARE CURSOR +DECLARE foo3 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; +DECLARE CURSOR +DECLARE foo4 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; +DECLARE CURSOR +DECLARE foo5 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; +DECLARE CURSOR +DECLARE foo6 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; +DECLARE CURSOR +DECLARE foo7 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; +DECLARE CURSOR +DECLARE foo8 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; +DECLARE CURSOR +DECLARE foo9 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; +DECLARE CURSOR +DECLARE foo10 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; +DECLARE CURSOR +DECLARE foo11 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; +DECLARE CURSOR +DECLARE foo12 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; +DECLARE CURSOR +DECLARE foo13 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; +DECLARE CURSOR +DECLARE foo14 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; +DECLARE CURSOR +DECLARE foo15 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; +DECLARE CURSOR +DECLARE foo16 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; +DECLARE CURSOR +DECLARE foo17 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; +DECLARE CURSOR +DECLARE foo18 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; +DECLARE CURSOR +DECLARE foo19 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; +DECLARE CURSOR +DECLARE foo20 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; +DECLARE CURSOR +DECLARE foo21 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; +DECLARE CURSOR +DECLARE foo22 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; +DECLARE CURSOR +DECLARE foo23 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; +DECLARE CURSOR +FETCH 1 in foo1; + a | b | c | d +---+---+---+--- + 1 | 2 | 3 | 4 +(1 row) + +FETCH 2 in foo2; + a | b | c | d +---+---+---+--- + 1 | 2 | 3 | 4 + 2 | 4 | 6 | 8 +(2 rows) + +FETCH 3 in foo3; + a | b | c | d +---+---+---+---- + 1 | 2 | 3 | 4 + 2 | 4 | 6 | 8 + 3 | 6 | 9 | 12 +(3 rows) + +FETCH 4 in foo4; + a | b | c | d +---+---+----+---- + 1 | 2 | 3 | 4 + 2 | 4 | 6 | 8 + 3 | 6 | 9 | 12 + 4 | 8 | 12 | 16 +(4 rows) + +FETCH 5 in foo5; + a | b | c | d +---+----+----+---- + 1 | 2 | 3 | 4 + 2 | 4 | 6 | 8 + 3 | 6 | 9 | 12 + 4 | 8 | 12 | 16 + 5 | 10 | 15 | 20 +(5 rows) + +FETCH 6 in foo6; + a | b | c | d +---+----+----+---- + 1 | 2 | 3 | 4 + 2 | 4 | 6 | 8 + 3 | 6 | 9 | 12 + 4 | 8 | 12 | 16 + 5 | 10 | 15 | 20 + 6 | 12 | 18 | 24 +(6 rows) + +FETCH 7 in foo7; + a | b | c | d +---+----+----+---- + 1 | 2 | 3 | 4 + 2 | 4 | 6 | 8 + 3 | 6 | 9 | 12 + 4 | 8 | 12 | 16 + 5 | 10 | 15 | 20 + 6 | 12 | 18 | 24 + 7 | 14 | 21 | 28 +(7 rows) + +FETCH 8 in foo8; + a | b | c | d +---+----+----+---- + 1 | 2 | 3 | 4 + 2 | 4 | 6 | 8 + 3 | 6 | 9 | 12 + 4 | 8 | 12 | 16 + 5 | 10 | 15 | 20 + 6 | 12 | 18 | 24 + 7 | 14 | 21 | 28 + 8 | 16 | 24 | 32 +(8 rows) + +FETCH 9 in foo9; + a | b | c | d +---+----+----+---- + 1 | 2 | 3 | 4 + 2 | 4 | 6 | 8 + 3 | 6 | 9 | 12 + 4 | 8 | 12 | 16 + 5 | 10 | 15 | 20 + 6 | 12 | 18 | 24 + 7 | 14 | 21 | 28 + 8 | 16 | 24 | 32 + 9 | 18 | 27 | 36 +(9 rows) + +FETCH 10 in foo10; + a | b | c | d +----+----+----+---- + 1 | 2 | 3 | 4 + 2 | 4 | 6 | 8 + 3 | 6 | 9 | 12 + 4 | 8 | 12 | 16 + 5 | 10 | 15 | 20 + 6 | 12 | 18 | 24 + 7 | 14 | 21 | 28 + 8 | 16 | 24 | 32 + 9 | 18 | 27 | 36 + 10 | 20 | 30 | 40 +(10 rows) + +FETCH 11 in foo11; + a | b | c | d +----+----+----+---- + 1 | 2 | 3 | 4 + 2 | 4 | 6 | 8 + 3 | 6 | 9 | 12 + 4 | 8 | 12 | 16 + 5 | 10 | 15 | 20 + 6 | 12 | 18 | 24 + 7 | 14 | 21 | 28 + 8 | 16 | 24 | 32 + 9 | 18 | 27 | 36 + 10 | 20 | 30 | 40 + 11 | 22 | 33 | 44 +(11 rows) + +FETCH 12 in foo12; + a | b | c | d +----+----+----+---- + 1 | 2 | 3 | 4 + 2 | 4 | 6 | 8 + 3 | 6 | 9 | 12 + 4 | 8 | 12 | 16 + 5 | 10 | 15 | 20 + 6 | 12 | 18 | 24 + 7 | 14 | 21 | 28 + 8 | 16 | 24 | 32 + 9 | 18 | 27 | 36 + 10 | 20 | 30 | 40 + 11 | 22 | 33 | 44 + 12 | 24 | 36 | 48 +(12 rows) + +FETCH 13 in foo13; + a | b | c | d +----+----+----+---- + 1 | 2 | 3 | 4 + 2 | 4 | 6 | 8 + 3 | 6 | 9 | 12 + 4 | 8 | 12 | 16 + 5 | 10 | 15 | 20 + 6 | 12 | 18 | 24 + 7 | 14 | 21 | 28 + 8 | 16 | 24 | 32 + 9 | 18 | 27 | 36 + 10 | 20 | 30 | 40 + 11 | 22 | 33 | 44 + 12 | 24 | 36 | 48 + 13 | 26 | 39 | 52 +(13 rows) + +FETCH 14 in foo14; + a | b | c | d +----+----+----+---- + 1 | 2 | 3 | 4 + 2 | 4 | 6 | 8 + 3 | 6 | 9 | 12 + 4 | 8 | 12 | 16 + 5 | 10 | 15 | 20 + 6 | 12 | 18 | 24 + 7 | 14 | 21 | 28 + 8 | 16 | 24 | 32 + 9 | 18 | 27 | 36 + 10 | 20 | 30 | 40 + 11 | 22 | 33 | 44 + 12 | 24 | 36 | 48 + 13 | 26 | 39 | 52 + 14 | 28 | 42 | 56 +(14 rows) + +FETCH 15 in foo15; + a | b | c | d +----+----+----+---- + 1 | 2 | 3 | 4 + 2 | 4 | 6 | 8 + 3 | 6 | 9 | 12 + 4 | 8 | 12 | 16 + 5 | 10 | 15 | 20 + 6 | 12 | 18 | 24 + 7 | 14 | 21 | 28 + 8 | 16 | 24 | 32 + 9 | 18 | 27 | 36 + 10 | 20 | 30 | 40 + 11 | 22 | 33 | 44 + 12 | 24 | 36 | 48 + 13 | 26 | 39 | 52 + 14 | 28 | 42 | 56 + 15 | 30 | 45 | 60 +(15 rows) + +FETCH 16 in foo16; + a | b | c | d +----+----+----+---- + 1 | 2 | 3 | 4 + 2 | 4 | 6 | 8 + 3 | 6 | 9 | 12 + 4 | 8 | 12 | 16 + 5 | 10 | 15 | 20 + 6 | 12 | 18 | 24 + 7 | 14 | 21 | 28 + 8 | 16 | 24 | 32 + 9 | 18 | 27 | 36 + 10 | 20 | 30 | 40 + 11 | 22 | 33 | 44 + 12 | 24 | 36 | 48 + 13 | 26 | 39 | 52 + 14 | 28 | 42 | 56 + 15 | 30 | 45 | 60 + 16 | 32 | 48 | 64 +(16 rows) + +FETCH 17 in foo17; + a | b | c | d +----+----+----+---- + 1 | 2 | 3 | 4 + 2 | 4 | 6 | 8 + 3 | 6 | 9 | 12 + 4 | 8 | 12 | 16 + 5 | 10 | 15 | 20 + 6 | 12 | 18 | 24 + 7 | 14 | 21 | 28 + 8 | 16 | 24 | 32 + 9 | 18 | 27 | 36 + 10 | 20 | 30 | 40 + 11 | 22 | 33 | 44 + 12 | 24 | 36 | 48 + 13 | 26 | 39 | 52 + 14 | 28 | 42 | 56 + 15 | 30 | 45 | 60 + 16 | 32 | 48 | 64 + 17 | 34 | 51 | 68 +(17 rows) + +FETCH 18 in foo18; + a | b | c | d +----+----+----+---- + 1 | 2 | 3 | 4 + 2 | 4 | 6 | 8 + 3 | 6 | 9 | 12 + 4 | 8 | 12 | 16 + 5 | 10 | 15 | 20 + 6 | 12 | 18 | 24 + 7 | 14 | 21 | 28 + 8 | 16 | 24 | 32 + 9 | 18 | 27 | 36 + 10 | 20 | 30 | 40 + 11 | 22 | 33 | 44 + 12 | 24 | 36 | 48 + 13 | 26 | 39 | 52 + 14 | 28 | 42 | 56 + 15 | 30 | 45 | 60 + 16 | 32 | 48 | 64 + 17 | 34 | 51 | 68 + 18 | 36 | 54 | 72 +(18 rows) + +FETCH 19 in foo19; + a | b | c | d +----+----+----+---- + 1 | 2 | 3 | 4 + 2 | 4 | 6 | 8 + 3 | 6 | 9 | 12 + 4 | 8 | 12 | 16 + 5 | 10 | 15 | 20 + 6 | 12 | 18 | 24 + 7 | 14 | 21 | 28 + 8 | 16 | 24 | 32 + 9 | 18 | 27 | 36 + 10 | 20 | 30 | 40 + 11 | 22 | 33 | 44 + 12 | 24 | 36 | 48 + 13 | 26 | 39 | 52 + 14 | 28 | 42 | 56 + 15 | 30 | 45 | 60 + 16 | 32 | 48 | 64 + 17 | 34 | 51 | 68 + 18 | 36 | 54 | 72 + 19 | 38 | 57 | 76 +(19 rows) + +FETCH 20 in foo20; + a | b | c | d +----+----+----+---- + 1 | 2 | 3 | 4 + 2 | 4 | 6 | 8 + 3 | 6 | 9 | 12 + 4 | 8 | 12 | 16 + 5 | 10 | 15 | 20 + 6 | 12 | 18 | 24 + 7 | 14 | 21 | 28 + 8 | 16 | 24 | 32 + 9 | 18 | 27 | 36 + 10 | 20 | 30 | 40 + 11 | 22 | 33 | 44 + 12 | 24 | 36 | 48 + 13 | 26 | 39 | 52 + 14 | 28 | 42 | 56 + 15 | 30 | 45 | 60 + 16 | 32 | 48 | 64 + 17 | 34 | 51 | 68 + 18 | 36 | 54 | 72 + 19 | 38 | 57 | 76 + 20 | 40 | 60 | 80 +(20 rows) + +FETCH 21 in foo21; + a | b | c | d +----+----+----+---- + 1 | 2 | 3 | 4 + 2 | 4 | 6 | 8 + 3 | 6 | 9 | 12 + 4 | 8 | 12 | 16 + 5 | 10 | 15 | 20 + 6 | 12 | 18 | 24 + 7 | 14 | 21 | 28 + 8 | 16 | 24 | 32 + 9 | 18 | 27 | 36 + 10 | 20 | 30 | 40 + 11 | 22 | 33 | 44 + 12 | 24 | 36 | 48 + 13 | 26 | 39 | 52 + 14 | 28 | 42 | 56 + 15 | 30 | 45 | 60 + 16 | 32 | 48 | 64 + 17 | 34 | 51 | 68 + 18 | 36 | 54 | 72 + 19 | 38 | 57 | 76 + 20 | 40 | 60 | 80 + 21 | 42 | 63 | 84 +(21 rows) + +FETCH 22 in foo22; + a | b | c | d +----+----+----+---- + 1 | 2 | 3 | 4 + 2 | 4 | 6 | 8 + 3 | 6 | 9 | 12 + 4 | 8 | 12 | 16 + 5 | 10 | 15 | 20 + 6 | 12 | 18 | 24 + 7 | 14 | 21 | 28 + 8 | 16 | 24 | 32 + 9 | 18 | 27 | 36 + 10 | 20 | 30 | 40 + 11 | 22 | 33 | 44 + 12 | 24 | 36 | 48 + 13 | 26 | 39 | 52 + 14 | 28 | 42 | 56 + 15 | 30 | 45 | 60 + 16 | 32 | 48 | 64 + 17 | 34 | 51 | 68 + 18 | 36 | 54 | 72 + 19 | 38 | 57 | 76 + 20 | 40 | 60 | 80 + 21 | 42 | 63 | 84 + 22 | 44 | 66 | 88 +(22 rows) + +FETCH 23 in foo23; + a | b | c | d +----+----+----+---- + 1 | 2 | 3 | 4 + 2 | 4 | 6 | 8 + 3 | 6 | 9 | 12 + 4 | 8 | 12 | 16 + 5 | 10 | 15 | 20 + 6 | 12 | 18 | 24 + 7 | 14 | 21 | 28 + 8 | 16 | 24 | 32 + 9 | 18 | 27 | 36 + 10 | 20 | 30 | 40 + 11 | 22 | 33 | 44 + 12 | 24 | 36 | 48 + 13 | 26 | 39 | 52 + 14 | 28 | 42 | 56 + 15 | 30 | 45 | 60 + 16 | 32 | 48 | 64 + 17 | 34 | 51 | 68 + 18 | 36 | 54 | 72 + 19 | 38 | 57 | 76 + 20 | 40 | 60 | 80 + 21 | 42 | 63 | 84 + 22 | 44 | 66 | 88 + 23 | 46 | 69 | 92 +(23 rows) + +CLOSE foo1; +CLOSE CURSOR +CLOSE foo2; +CLOSE CURSOR +CLOSE foo3; +CLOSE CURSOR +CLOSE foo4; +CLOSE CURSOR +CLOSE foo5; +CLOSE CURSOR +CLOSE foo6; +CLOSE CURSOR +CLOSE foo7; +CLOSE CURSOR +CLOSE foo8; +CLOSE CURSOR +CLOSE foo9; +CLOSE CURSOR +CLOSE foo10; +CLOSE CURSOR +CLOSE foo11; +CLOSE CURSOR +CLOSE foo12; +CLOSE CURSOR +-- leave some cursors open, to test that auto-close works. +-- record this in the system view as well (don't query the time field there +-- however) +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name; + name | statement | is_holdable | is_binary | is_scrollable +-------+----------------------------------------------------------------+-------------+-----------+--------------- + foo13 | DECLARE foo13 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; | f | f | f + foo14 | DECLARE foo14 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; | f | f | f + foo15 | DECLARE foo15 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; | f | f | f + foo16 | DECLARE foo16 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; | f | f | f + foo17 | DECLARE foo17 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; | f | f | f + foo18 | DECLARE foo18 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; | f | f | f + foo19 | DECLARE foo19 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; | f | f | f + foo20 | DECLARE foo20 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; | f | f | f + foo21 | DECLARE foo21 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; | f | f | f + foo22 | DECLARE foo22 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; | f | f | f + foo23 | DECLARE foo23 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; | f | f | f +(11 rows) + +END; +COMMIT +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name; + name | statement | is_holdable | is_binary | is_scrollable +------+-----------+-------------+-----------+--------------- +(0 rows) + +-- +-- NO SCROLL disallows backward fetching +-- +BEGIN; +BEGIN +DECLARE foo24 NO SCROLL CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; +DECLARE CURSOR +FETCH 1 FROM foo24; + a | b | c | d +---+---+---+--- + 1 | 2 | 3 | 4 +(1 row) + +FETCH BACKWARD 1 FROM foo24; -- should fail +psql:/tmp/TestQueryPortal_TestBasic1.sql:143: ERROR: backward scan is not supported in this version of Greenplum Database +END; +ROLLBACK +-- +-- Cursors outside transaction blocks +-- +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name; + name | statement | is_holdable | is_binary | is_scrollable +------+-----------+-------------+-----------+--------------- +(0 rows) + +BEGIN; +BEGIN +DECLARE foo25 CURSOR WITH HOLD FOR SELECT * FROM test2 ORDER BY 1,2,3,4; +DECLARE CURSOR +FETCH FROM foo25; + a | b | c | d +---+---+---+--- + 1 | 2 | 3 | 4 +(1 row) + +FETCH FROM foo25; + a | b | c | d +---+---+---+--- + 2 | 4 | 6 | 8 +(1 row) + +COMMIT; +COMMIT +FETCH FROM foo25; + a | b | c | d +---+---+---+---- + 3 | 6 | 9 | 12 +(1 row) + +--FETCH BACKWARD FROM foo25; +--FETCH ABSOLUTE -1 FROM foo25; +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name; + name | statement | is_holdable | is_binary | is_scrollable +-------+--------------------------------------------------------------------------+-------------+-----------+--------------- + foo25 | DECLARE foo25 CURSOR WITH HOLD FOR SELECT * FROM test2 ORDER BY 1,2,3,4; | t | f | f +(1 row) + +CLOSE foo25; +CLOSE CURSOR +-- +-- ROLLBACK should close holdable cursors +-- +BEGIN; +BEGIN +DECLARE foo26 CURSOR WITH HOLD FOR SELECT * FROM test1 ORDER BY 1,2,3,4; +DECLARE CURSOR +ROLLBACK; +ROLLBACK +-- should fail +FETCH FROM foo26; +psql:/tmp/TestQueryPortal_TestBasic1.sql:185: ERROR: cursor "foo26" does not exist +-- Create a cursor with the BINARY option and check the pg_cursors view +BEGIN; +BEGIN +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name; + name | statement | is_holdable | is_binary | is_scrollable +------+-----------+-------------+-----------+--------------- +(0 rows) + +DECLARE bc BINARY CURSOR FOR SELECT * FROM test1; +DECLARE CURSOR +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name; + name | statement | is_holdable | is_binary | is_scrollable +------+---------------------------------------------------+-------------+-----------+--------------- + bc | DECLARE bc BINARY CURSOR FOR SELECT * FROM test1; | f | t | f +(1 row) + +ROLLBACK; +ROLLBACK http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/51338812/src/test/feature/query/sql/portal-basic.sql ---------------------------------------------------------------------- diff --git a/src/test/feature/query/sql/portal-basic.sql b/src/test/feature/query/sql/portal-basic.sql new file mode 100644 index 0000000..448ca79 --- /dev/null +++ b/src/test/feature/query/sql/portal-basic.sql @@ -0,0 +1,189 @@ +BEGIN; + +DECLARE foo1 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; + +DECLARE foo2 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; + +DECLARE foo3 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; + +DECLARE foo4 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; + +DECLARE foo5 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; + +DECLARE foo6 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; + +DECLARE foo7 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; + +DECLARE foo8 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; + +DECLARE foo9 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; + +DECLARE foo10 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; + +DECLARE foo11 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; + +DECLARE foo12 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; + +DECLARE foo13 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; + +DECLARE foo14 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; + +DECLARE foo15 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; + +DECLARE foo16 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; + +DECLARE foo17 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; + +DECLARE foo18 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; + +DECLARE foo19 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; + +DECLARE foo20 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; + +DECLARE foo21 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; + +DECLARE foo22 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; + +DECLARE foo23 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; + +FETCH 1 in foo1; + +FETCH 2 in foo2; + +FETCH 3 in foo3; + +FETCH 4 in foo4; + +FETCH 5 in foo5; + +FETCH 6 in foo6; + +FETCH 7 in foo7; + +FETCH 8 in foo8; + +FETCH 9 in foo9; + +FETCH 10 in foo10; + +FETCH 11 in foo11; + +FETCH 12 in foo12; + +FETCH 13 in foo13; + +FETCH 14 in foo14; + +FETCH 15 in foo15; + +FETCH 16 in foo16; + +FETCH 17 in foo17; + +FETCH 18 in foo18; + +FETCH 19 in foo19; + +FETCH 20 in foo20; + +FETCH 21 in foo21; + +FETCH 22 in foo22; + +FETCH 23 in foo23; + + +CLOSE foo1; + +CLOSE foo2; + +CLOSE foo3; + +CLOSE foo4; + +CLOSE foo5; + +CLOSE foo6; + +CLOSE foo7; + +CLOSE foo8; + +CLOSE foo9; + +CLOSE foo10; + +CLOSE foo11; + +CLOSE foo12; + +-- leave some cursors open, to test that auto-close works. + +-- record this in the system view as well (don't query the time field there +-- however) +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name; + +END; + +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name; + +-- +-- NO SCROLL disallows backward fetching +-- + +BEGIN; + +DECLARE foo24 NO SCROLL CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; + +FETCH 1 FROM foo24; + +FETCH BACKWARD 1 FROM foo24; -- should fail + +END; + +-- +-- Cursors outside transaction blocks +-- + + +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name; + +BEGIN; + +DECLARE foo25 CURSOR WITH HOLD FOR SELECT * FROM test2 ORDER BY 1,2,3,4; + +FETCH FROM foo25; + +FETCH FROM foo25; + +COMMIT; + +FETCH FROM foo25; + +--FETCH BACKWARD FROM foo25; + +--FETCH ABSOLUTE -1 FROM foo25; + +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name; + +CLOSE foo25; + +-- +-- ROLLBACK should close holdable cursors +-- + +BEGIN; + +DECLARE foo26 CURSOR WITH HOLD FOR SELECT * FROM test1 ORDER BY 1,2,3,4; + +ROLLBACK; + +-- should fail +FETCH FROM foo26; + +-- Create a cursor with the BINARY option and check the pg_cursors view +BEGIN; +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name; +DECLARE bc BINARY CURSOR FOR SELECT * FROM test1; +SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name; +ROLLBACK; \ No newline at end of file http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/51338812/src/test/feature/query/test_portal.cpp ---------------------------------------------------------------------- diff --git a/src/test/feature/query/test_portal.cpp b/src/test/feature/query/test_portal.cpp new file mode 100644 index 0000000..49697f0 --- /dev/null +++ b/src/test/feature/query/test_portal.cpp @@ -0,0 +1,42 @@ +#include <pwd.h> +#include <sys/types.h> +#include <unistd.h> +#include <vector> +#include <stdio.h> +#include <stdlib.h> +#include <errno.h> +#include <iostream> + +#include "lib/command.h" +#include "lib/data_gen.h" +#include "lib/hawq_config.h" +#include "lib/sql_util.h" + +#include "gtest/gtest.h" + +class TestQueryPortal : public ::testing::Test { + public: + TestQueryPortal() {} + ~TestQueryPortal() {} +}; + +TEST_F(TestQueryPortal, TestBasic1) { + hawq::test::SQLUtility util; + // prepare + util.execute("ALTER RESOURCE QUEUE pg_default WITH (active_statements=30)"); + util.execute("DROP TABLE IF EXISTS test1 CASCADE"); + util.execute("DROP TABLE IF EXISTS test2 CASCADE"); + util.execute("CREATE TABLE test1 (a INT, b INT, c INT, d INT)"); + util.execute("CREATE TABLE test2 (a INT, b INT, c INT, d INT)"); + util.execute("INSERT INTO test1 SELECT x, 2*x, 3*x, 4*x FROM generate_series(1,1000) x"); + util.execute("INSERT INTO test2 SELECT x, 2*x, 3*x, 4*x FROM generate_series(1,1000) x"); + + // test + util.execSQLFile("query/sql/portal-basic.sql", + "query/ans/portal-basic.ans"); + + // clean up + util.execute("DROP TABLE test1"); + util.execute("DROP TABLE test2"); + util.execute("ALTER RESOURCE QUEUE pg_default WITH (active_statements=20)"); +} http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/51338812/src/test/regress/expected/goh_portals.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/goh_portals.out b/src/test/regress/expected/goh_portals.out deleted file mode 100755 index cc64a9e..0000000 --- a/src/test/regress/expected/goh_portals.out +++ /dev/null @@ -1,541 +0,0 @@ --- --- Cursor regression tests --- --- setup -ALTER RESOURCE QUEUE pg_default WITH (active_statements=30); -CREATE TABLE test1 (a int, b int, c int, d int); -CREATE TABLE test2 (a int, b int, c int, d int); -INSERT INTO test1 SELECT x, 2 * x, 3 * x, 4 * x FROM generate_series(1, 1000) x; -INSERT INTO test2 SELECT x, 2 * x, 3 * x, 4 * x FROM generate_series(1, 1000) x; -BEGIN; -DECLARE foo1 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; -DECLARE foo2 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; -DECLARE foo3 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; -DECLARE foo4 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; -DECLARE foo5 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; -DECLARE foo6 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; -DECLARE foo7 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; -DECLARE foo8 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; -DECLARE foo9 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; -DECLARE foo10 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; -DECLARE foo11 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; -DECLARE foo12 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; -DECLARE foo13 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; -DECLARE foo14 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; -DECLARE foo15 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; -DECLARE foo16 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; -DECLARE foo17 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; -DECLARE foo18 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; -DECLARE foo19 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; -DECLARE foo20 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; -DECLARE foo21 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; -DECLARE foo22 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; -DECLARE foo23 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; -FETCH 1 in foo1; - a | b | c | d ----+---+---+--- - 1 | 2 | 3 | 4 -(1 row) - -FETCH 2 in foo2; - a | b | c | d ----+---+---+--- - 1 | 2 | 3 | 4 - 2 | 4 | 6 | 8 -(2 rows) - -FETCH 3 in foo3; - a | b | c | d ----+---+---+---- - 1 | 2 | 3 | 4 - 2 | 4 | 6 | 8 - 3 | 6 | 9 | 12 -(3 rows) - -FETCH 4 in foo4; - a | b | c | d ----+---+----+---- - 1 | 2 | 3 | 4 - 2 | 4 | 6 | 8 - 3 | 6 | 9 | 12 - 4 | 8 | 12 | 16 -(4 rows) - -FETCH 5 in foo5; - a | b | c | d ----+----+----+---- - 1 | 2 | 3 | 4 - 2 | 4 | 6 | 8 - 3 | 6 | 9 | 12 - 4 | 8 | 12 | 16 - 5 | 10 | 15 | 20 -(5 rows) - -FETCH 6 in foo6; - a | b | c | d ----+----+----+---- - 1 | 2 | 3 | 4 - 2 | 4 | 6 | 8 - 3 | 6 | 9 | 12 - 4 | 8 | 12 | 16 - 5 | 10 | 15 | 20 - 6 | 12 | 18 | 24 -(6 rows) - -FETCH 7 in foo7; - a | b | c | d ----+----+----+---- - 1 | 2 | 3 | 4 - 2 | 4 | 6 | 8 - 3 | 6 | 9 | 12 - 4 | 8 | 12 | 16 - 5 | 10 | 15 | 20 - 6 | 12 | 18 | 24 - 7 | 14 | 21 | 28 -(7 rows) - -FETCH 8 in foo8; - a | b | c | d ----+----+----+---- - 1 | 2 | 3 | 4 - 2 | 4 | 6 | 8 - 3 | 6 | 9 | 12 - 4 | 8 | 12 | 16 - 5 | 10 | 15 | 20 - 6 | 12 | 18 | 24 - 7 | 14 | 21 | 28 - 8 | 16 | 24 | 32 -(8 rows) - -FETCH 9 in foo9; - a | b | c | d ----+----+----+---- - 1 | 2 | 3 | 4 - 2 | 4 | 6 | 8 - 3 | 6 | 9 | 12 - 4 | 8 | 12 | 16 - 5 | 10 | 15 | 20 - 6 | 12 | 18 | 24 - 7 | 14 | 21 | 28 - 8 | 16 | 24 | 32 - 9 | 18 | 27 | 36 -(9 rows) - -FETCH 10 in foo10; - a | b | c | d -----+----+----+---- - 1 | 2 | 3 | 4 - 2 | 4 | 6 | 8 - 3 | 6 | 9 | 12 - 4 | 8 | 12 | 16 - 5 | 10 | 15 | 20 - 6 | 12 | 18 | 24 - 7 | 14 | 21 | 28 - 8 | 16 | 24 | 32 - 9 | 18 | 27 | 36 - 10 | 20 | 30 | 40 -(10 rows) - -FETCH 11 in foo11; - a | b | c | d -----+----+----+---- - 1 | 2 | 3 | 4 - 2 | 4 | 6 | 8 - 3 | 6 | 9 | 12 - 4 | 8 | 12 | 16 - 5 | 10 | 15 | 20 - 6 | 12 | 18 | 24 - 7 | 14 | 21 | 28 - 8 | 16 | 24 | 32 - 9 | 18 | 27 | 36 - 10 | 20 | 30 | 40 - 11 | 22 | 33 | 44 -(11 rows) - -FETCH 12 in foo12; - a | b | c | d -----+----+----+---- - 1 | 2 | 3 | 4 - 2 | 4 | 6 | 8 - 3 | 6 | 9 | 12 - 4 | 8 | 12 | 16 - 5 | 10 | 15 | 20 - 6 | 12 | 18 | 24 - 7 | 14 | 21 | 28 - 8 | 16 | 24 | 32 - 9 | 18 | 27 | 36 - 10 | 20 | 30 | 40 - 11 | 22 | 33 | 44 - 12 | 24 | 36 | 48 -(12 rows) - -FETCH 13 in foo13; - a | b | c | d -----+----+----+---- - 1 | 2 | 3 | 4 - 2 | 4 | 6 | 8 - 3 | 6 | 9 | 12 - 4 | 8 | 12 | 16 - 5 | 10 | 15 | 20 - 6 | 12 | 18 | 24 - 7 | 14 | 21 | 28 - 8 | 16 | 24 | 32 - 9 | 18 | 27 | 36 - 10 | 20 | 30 | 40 - 11 | 22 | 33 | 44 - 12 | 24 | 36 | 48 - 13 | 26 | 39 | 52 -(13 rows) - -FETCH 14 in foo14; - a | b | c | d -----+----+----+---- - 1 | 2 | 3 | 4 - 2 | 4 | 6 | 8 - 3 | 6 | 9 | 12 - 4 | 8 | 12 | 16 - 5 | 10 | 15 | 20 - 6 | 12 | 18 | 24 - 7 | 14 | 21 | 28 - 8 | 16 | 24 | 32 - 9 | 18 | 27 | 36 - 10 | 20 | 30 | 40 - 11 | 22 | 33 | 44 - 12 | 24 | 36 | 48 - 13 | 26 | 39 | 52 - 14 | 28 | 42 | 56 -(14 rows) - -FETCH 15 in foo15; - a | b | c | d -----+----+----+---- - 1 | 2 | 3 | 4 - 2 | 4 | 6 | 8 - 3 | 6 | 9 | 12 - 4 | 8 | 12 | 16 - 5 | 10 | 15 | 20 - 6 | 12 | 18 | 24 - 7 | 14 | 21 | 28 - 8 | 16 | 24 | 32 - 9 | 18 | 27 | 36 - 10 | 20 | 30 | 40 - 11 | 22 | 33 | 44 - 12 | 24 | 36 | 48 - 13 | 26 | 39 | 52 - 14 | 28 | 42 | 56 - 15 | 30 | 45 | 60 -(15 rows) - -FETCH 16 in foo16; - a | b | c | d -----+----+----+---- - 1 | 2 | 3 | 4 - 2 | 4 | 6 | 8 - 3 | 6 | 9 | 12 - 4 | 8 | 12 | 16 - 5 | 10 | 15 | 20 - 6 | 12 | 18 | 24 - 7 | 14 | 21 | 28 - 8 | 16 | 24 | 32 - 9 | 18 | 27 | 36 - 10 | 20 | 30 | 40 - 11 | 22 | 33 | 44 - 12 | 24 | 36 | 48 - 13 | 26 | 39 | 52 - 14 | 28 | 42 | 56 - 15 | 30 | 45 | 60 - 16 | 32 | 48 | 64 -(16 rows) - -FETCH 17 in foo17; - a | b | c | d -----+----+----+---- - 1 | 2 | 3 | 4 - 2 | 4 | 6 | 8 - 3 | 6 | 9 | 12 - 4 | 8 | 12 | 16 - 5 | 10 | 15 | 20 - 6 | 12 | 18 | 24 - 7 | 14 | 21 | 28 - 8 | 16 | 24 | 32 - 9 | 18 | 27 | 36 - 10 | 20 | 30 | 40 - 11 | 22 | 33 | 44 - 12 | 24 | 36 | 48 - 13 | 26 | 39 | 52 - 14 | 28 | 42 | 56 - 15 | 30 | 45 | 60 - 16 | 32 | 48 | 64 - 17 | 34 | 51 | 68 -(17 rows) - -FETCH 18 in foo18; - a | b | c | d -----+----+----+---- - 1 | 2 | 3 | 4 - 2 | 4 | 6 | 8 - 3 | 6 | 9 | 12 - 4 | 8 | 12 | 16 - 5 | 10 | 15 | 20 - 6 | 12 | 18 | 24 - 7 | 14 | 21 | 28 - 8 | 16 | 24 | 32 - 9 | 18 | 27 | 36 - 10 | 20 | 30 | 40 - 11 | 22 | 33 | 44 - 12 | 24 | 36 | 48 - 13 | 26 | 39 | 52 - 14 | 28 | 42 | 56 - 15 | 30 | 45 | 60 - 16 | 32 | 48 | 64 - 17 | 34 | 51 | 68 - 18 | 36 | 54 | 72 -(18 rows) - -FETCH 19 in foo19; - a | b | c | d -----+----+----+---- - 1 | 2 | 3 | 4 - 2 | 4 | 6 | 8 - 3 | 6 | 9 | 12 - 4 | 8 | 12 | 16 - 5 | 10 | 15 | 20 - 6 | 12 | 18 | 24 - 7 | 14 | 21 | 28 - 8 | 16 | 24 | 32 - 9 | 18 | 27 | 36 - 10 | 20 | 30 | 40 - 11 | 22 | 33 | 44 - 12 | 24 | 36 | 48 - 13 | 26 | 39 | 52 - 14 | 28 | 42 | 56 - 15 | 30 | 45 | 60 - 16 | 32 | 48 | 64 - 17 | 34 | 51 | 68 - 18 | 36 | 54 | 72 - 19 | 38 | 57 | 76 -(19 rows) - -FETCH 20 in foo20; - a | b | c | d -----+----+----+---- - 1 | 2 | 3 | 4 - 2 | 4 | 6 | 8 - 3 | 6 | 9 | 12 - 4 | 8 | 12 | 16 - 5 | 10 | 15 | 20 - 6 | 12 | 18 | 24 - 7 | 14 | 21 | 28 - 8 | 16 | 24 | 32 - 9 | 18 | 27 | 36 - 10 | 20 | 30 | 40 - 11 | 22 | 33 | 44 - 12 | 24 | 36 | 48 - 13 | 26 | 39 | 52 - 14 | 28 | 42 | 56 - 15 | 30 | 45 | 60 - 16 | 32 | 48 | 64 - 17 | 34 | 51 | 68 - 18 | 36 | 54 | 72 - 19 | 38 | 57 | 76 - 20 | 40 | 60 | 80 -(20 rows) - -FETCH 21 in foo21; - a | b | c | d -----+----+----+---- - 1 | 2 | 3 | 4 - 2 | 4 | 6 | 8 - 3 | 6 | 9 | 12 - 4 | 8 | 12 | 16 - 5 | 10 | 15 | 20 - 6 | 12 | 18 | 24 - 7 | 14 | 21 | 28 - 8 | 16 | 24 | 32 - 9 | 18 | 27 | 36 - 10 | 20 | 30 | 40 - 11 | 22 | 33 | 44 - 12 | 24 | 36 | 48 - 13 | 26 | 39 | 52 - 14 | 28 | 42 | 56 - 15 | 30 | 45 | 60 - 16 | 32 | 48 | 64 - 17 | 34 | 51 | 68 - 18 | 36 | 54 | 72 - 19 | 38 | 57 | 76 - 20 | 40 | 60 | 80 - 21 | 42 | 63 | 84 -(21 rows) - -FETCH 22 in foo22; - a | b | c | d -----+----+----+---- - 1 | 2 | 3 | 4 - 2 | 4 | 6 | 8 - 3 | 6 | 9 | 12 - 4 | 8 | 12 | 16 - 5 | 10 | 15 | 20 - 6 | 12 | 18 | 24 - 7 | 14 | 21 | 28 - 8 | 16 | 24 | 32 - 9 | 18 | 27 | 36 - 10 | 20 | 30 | 40 - 11 | 22 | 33 | 44 - 12 | 24 | 36 | 48 - 13 | 26 | 39 | 52 - 14 | 28 | 42 | 56 - 15 | 30 | 45 | 60 - 16 | 32 | 48 | 64 - 17 | 34 | 51 | 68 - 18 | 36 | 54 | 72 - 19 | 38 | 57 | 76 - 20 | 40 | 60 | 80 - 21 | 42 | 63 | 84 - 22 | 44 | 66 | 88 -(22 rows) - -FETCH 23 in foo23; - a | b | c | d -----+----+----+---- - 1 | 2 | 3 | 4 - 2 | 4 | 6 | 8 - 3 | 6 | 9 | 12 - 4 | 8 | 12 | 16 - 5 | 10 | 15 | 20 - 6 | 12 | 18 | 24 - 7 | 14 | 21 | 28 - 8 | 16 | 24 | 32 - 9 | 18 | 27 | 36 - 10 | 20 | 30 | 40 - 11 | 22 | 33 | 44 - 12 | 24 | 36 | 48 - 13 | 26 | 39 | 52 - 14 | 28 | 42 | 56 - 15 | 30 | 45 | 60 - 16 | 32 | 48 | 64 - 17 | 34 | 51 | 68 - 18 | 36 | 54 | 72 - 19 | 38 | 57 | 76 - 20 | 40 | 60 | 80 - 21 | 42 | 63 | 84 - 22 | 44 | 66 | 88 - 23 | 46 | 69 | 92 -(23 rows) - -CLOSE foo1; -CLOSE foo2; -CLOSE foo3; -CLOSE foo4; -CLOSE foo5; -CLOSE foo6; -CLOSE foo7; -CLOSE foo8; -CLOSE foo9; -CLOSE foo10; -CLOSE foo11; -CLOSE foo12; --- leave some cursors open, to test that auto-close works. --- record this in the system view as well (don't query the time field there --- however) -SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name; - name | statement | is_holdable | is_binary | is_scrollable --------+----------------------------------------------------------------+-------------+-----------+--------------- - foo13 | DECLARE foo13 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; | f | f | f - foo14 | DECLARE foo14 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; | f | f | f - foo15 | DECLARE foo15 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; | f | f | f - foo16 | DECLARE foo16 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; | f | f | f - foo17 | DECLARE foo17 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; | f | f | f - foo18 | DECLARE foo18 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; | f | f | f - foo19 | DECLARE foo19 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; | f | f | f - foo20 | DECLARE foo20 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; | f | f | f - foo21 | DECLARE foo21 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; | f | f | f - foo22 | DECLARE foo22 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; | f | f | f - foo23 | DECLARE foo23 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; | f | f | f -(11 rows) - -END; -SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name; - name | statement | is_holdable | is_binary | is_scrollable -------+-----------+-------------+-----------+--------------- -(0 rows) - --- --- NO SCROLL disallows backward fetching --- -BEGIN; -DECLARE foo24 NO SCROLL CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; -FETCH 1 FROM foo24; - a | b | c | d ----+---+---+--- - 1 | 2 | 3 | 4 -(1 row) - -FETCH BACKWARD 1 FROM foo24; -- should fail -ERROR: backward scan is not supported in this version of Greenplum Database -END; --- --- Cursors outside transaction blocks --- -SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name; - name | statement | is_holdable | is_binary | is_scrollable -------+-----------+-------------+-----------+--------------- -(0 rows) - -BEGIN; -DECLARE foo25 CURSOR WITH HOLD FOR SELECT * FROM test2 ORDER BY 1,2,3,4; -FETCH FROM foo25; - a | b | c | d ----+---+---+--- - 1 | 2 | 3 | 4 -(1 row) - -FETCH FROM foo25; - a | b | c | d ----+---+---+--- - 2 | 4 | 6 | 8 -(1 row) - -COMMIT; -FETCH FROM foo25; - a | b | c | d ----+---+---+---- - 3 | 6 | 9 | 12 -(1 row) - ---FETCH BACKWARD FROM foo25; ---FETCH ABSOLUTE -1 FROM foo25; -SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name; - name | statement | is_holdable | is_binary | is_scrollable --------+--------------------------------------------------------------------------+-------------+-----------+--------------- - foo25 | DECLARE foo25 CURSOR WITH HOLD FOR SELECT * FROM test2 ORDER BY 1,2,3,4; | t | f | f -(1 row) - -CLOSE foo25; --- --- ROLLBACK should close holdable cursors --- -BEGIN; -DECLARE foo26 CURSOR WITH HOLD FOR SELECT * FROM test1 ORDER BY 1,2,3,4; -ROLLBACK; --- should fail -FETCH FROM foo26; -ERROR: cursor "foo26" does not exist --- Create a cursor with the BINARY option and check the pg_cursors view -BEGIN; -SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name; - name | statement | is_holdable | is_binary | is_scrollable -------+-----------+-------------+-----------+--------------- -(0 rows) - -DECLARE bc BINARY CURSOR FOR SELECT * FROM test1; -SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name; - name | statement | is_holdable | is_binary | is_scrollable -------+---------------------------------------------------+-------------+-----------+--------------- - bc | DECLARE bc BINARY CURSOR FOR SELECT * FROM test1; | f | t | f -(1 row) - -ROLLBACK; --- teardown -DROP TABLE test1; -DROP TABLE test2; -ALTER RESOURCE QUEUE pg_default WITH (active_statements=20); http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/51338812/src/test/regress/known_good_schedule ---------------------------------------------------------------------- diff --git a/src/test/regress/known_good_schedule b/src/test/regress/known_good_schedule index da228fd..68e53a4 100755 --- a/src/test/regress/known_good_schedule +++ b/src/test/regress/known_good_schedule @@ -16,7 +16,6 @@ ignore: goh_column_compression test: goh_database test: goh_gp_dist_random ignore: gpsql_fault_tolerance -test: goh_portals ignore: goh_set_schema ignore: bit ignore: numeric http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/51338812/src/test/regress/sql/goh_portals.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/goh_portals.sql b/src/test/regress/sql/goh_portals.sql deleted file mode 100644 index f509a54..0000000 --- a/src/test/regress/sql/goh_portals.sql +++ /dev/null @@ -1,206 +0,0 @@ --- --- Cursor regression tests --- --- setup -ALTER RESOURCE QUEUE pg_default WITH (active_statements=30); - -CREATE TABLE test1 (a int, b int, c int, d int); -CREATE TABLE test2 (a int, b int, c int, d int); -INSERT INTO test1 SELECT x, 2 * x, 3 * x, 4 * x FROM generate_series(1, 1000) x; -INSERT INTO test2 SELECT x, 2 * x, 3 * x, 4 * x FROM generate_series(1, 1000) x; - -BEGIN; - -DECLARE foo1 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; - -DECLARE foo2 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; - -DECLARE foo3 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; - -DECLARE foo4 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; - -DECLARE foo5 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; - -DECLARE foo6 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; - -DECLARE foo7 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; - -DECLARE foo8 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; - -DECLARE foo9 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; - -DECLARE foo10 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; - -DECLARE foo11 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; - -DECLARE foo12 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; - -DECLARE foo13 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; - -DECLARE foo14 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; - -DECLARE foo15 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; - -DECLARE foo16 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; - -DECLARE foo17 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; - -DECLARE foo18 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; - -DECLARE foo19 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; - -DECLARE foo20 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; - -DECLARE foo21 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; - -DECLARE foo22 CURSOR FOR SELECT * FROM test2 ORDER BY 1,2,3,4; - -DECLARE foo23 CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; - -FETCH 1 in foo1; - -FETCH 2 in foo2; - -FETCH 3 in foo3; - -FETCH 4 in foo4; - -FETCH 5 in foo5; - -FETCH 6 in foo6; - -FETCH 7 in foo7; - -FETCH 8 in foo8; - -FETCH 9 in foo9; - -FETCH 10 in foo10; - -FETCH 11 in foo11; - -FETCH 12 in foo12; - -FETCH 13 in foo13; - -FETCH 14 in foo14; - -FETCH 15 in foo15; - -FETCH 16 in foo16; - -FETCH 17 in foo17; - -FETCH 18 in foo18; - -FETCH 19 in foo19; - -FETCH 20 in foo20; - -FETCH 21 in foo21; - -FETCH 22 in foo22; - -FETCH 23 in foo23; - - -CLOSE foo1; - -CLOSE foo2; - -CLOSE foo3; - -CLOSE foo4; - -CLOSE foo5; - -CLOSE foo6; - -CLOSE foo7; - -CLOSE foo8; - -CLOSE foo9; - -CLOSE foo10; - -CLOSE foo11; - -CLOSE foo12; - --- leave some cursors open, to test that auto-close works. - --- record this in the system view as well (don't query the time field there --- however) -SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name; - -END; - -SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name; - --- --- NO SCROLL disallows backward fetching --- - -BEGIN; - -DECLARE foo24 NO SCROLL CURSOR FOR SELECT * FROM test1 ORDER BY 1,2,3,4; - -FETCH 1 FROM foo24; - -FETCH BACKWARD 1 FROM foo24; -- should fail - -END; - --- --- Cursors outside transaction blocks --- - - -SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name; - -BEGIN; - -DECLARE foo25 CURSOR WITH HOLD FOR SELECT * FROM test2 ORDER BY 1,2,3,4; - -FETCH FROM foo25; - -FETCH FROM foo25; - -COMMIT; - -FETCH FROM foo25; - ---FETCH BACKWARD FROM foo25; - ---FETCH ABSOLUTE -1 FROM foo25; - -SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name; - -CLOSE foo25; - --- --- ROLLBACK should close holdable cursors --- - -BEGIN; - -DECLARE foo26 CURSOR WITH HOLD FOR SELECT * FROM test1 ORDER BY 1,2,3,4; - -ROLLBACK; - --- should fail -FETCH FROM foo26; - --- Create a cursor with the BINARY option and check the pg_cursors view -BEGIN; -SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name; -DECLARE bc BINARY CURSOR FOR SELECT * FROM test1; -SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors ORDER BY name; -ROLLBACK; - --- teardown -DROP TABLE test1; -DROP TABLE test2; - -ALTER RESOURCE QUEUE pg_default WITH (active_statements=20);
