set optimizer_print_missing_stats = off; -- -- Cursor regression tests -- BEGIN; DECLARE foo1 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; DECLARE foo2 CURSOR FOR SELECT * FROM tenk2 ORDER BY 1,2,3,4; DECLARE foo3 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; DECLARE foo4 CURSOR FOR SELECT * FROM tenk2 ORDER BY 1,2,3,4; DECLARE foo5 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; DECLARE foo6 CURSOR FOR SELECT * FROM tenk2 ORDER BY 1,2,3,4; DECLARE foo7 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; DECLARE foo8 CURSOR FOR SELECT * FROM tenk2 ORDER BY 1,2,3,4; DECLARE foo9 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; DECLARE foo10 CURSOR FOR SELECT * FROM tenk2 ORDER BY 1,2,3,4; DECLARE foo11 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; DECLARE foo12 CURSOR FOR SELECT * FROM tenk2 ORDER BY 1,2,3,4; DECLARE foo13 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; DECLARE foo14 CURSOR FOR SELECT * FROM tenk2 ORDER BY 1,2,3,4; DECLARE foo15 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; DECLARE foo16 CURSOR FOR SELECT * FROM tenk2 ORDER BY 1,2,3,4; DECLARE foo17 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; DECLARE foo18 CURSOR FOR SELECT * FROM tenk2 ORDER BY 1,2,3,4; DECLARE foo19 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; DECLARE foo20 CURSOR FOR SELECT * FROM tenk2 ORDER BY 1,2,3,4; DECLARE foo21 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; DECLARE foo22 CURSOR FOR SELECT * FROM tenk2 ORDER BY 1,2,3,4; DECLARE foo23 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; FETCH 1 in foo1; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx (1 row) FETCH 2 in foo2; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 0 | 9998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | OUOAAA | OOOOxx 1 | 2838 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | EFEAAA | OOOOxx (2 rows) FETCH 3 in foo3; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx (3 rows) FETCH 4 in foo4; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 0 | 9998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | OUOAAA | OOOOxx 1 | 2838 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | EFEAAA | OOOOxx 2 | 2716 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | MAEAAA | AAAAxx 3 | 5679 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | LKIAAA | VVVVxx (4 rows) FETCH 5 in foo5; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx (5 rows) FETCH 6 in foo6; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 0 | 9998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | OUOAAA | OOOOxx 1 | 2838 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | EFEAAA | OOOOxx 2 | 2716 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | MAEAAA | AAAAxx 3 | 5679 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | LKIAAA | VVVVxx 4 | 1621 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | JKCAAA | HHHHxx 5 | 5557 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | TFIAAA | HHHHxx (6 rows) FETCH 7 in foo7; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx (7 rows) FETCH 8 in foo8; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 0 | 9998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | OUOAAA | OOOOxx 1 | 2838 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | EFEAAA | OOOOxx 2 | 2716 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | MAEAAA | AAAAxx 3 | 5679 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | LKIAAA | VVVVxx 4 | 1621 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | JKCAAA | HHHHxx 5 | 5557 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | TFIAAA | HHHHxx 6 | 2855 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | VFEAAA | VVVVxx 7 | 8518 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | QPMAAA | OOOOxx (8 rows) FETCH 9 in foo9; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx (9 rows) FETCH 10 in foo10; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 0 | 9998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | OUOAAA | OOOOxx 1 | 2838 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | EFEAAA | OOOOxx 2 | 2716 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | MAEAAA | AAAAxx 3 | 5679 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | LKIAAA | VVVVxx 4 | 1621 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | JKCAAA | HHHHxx 5 | 5557 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | TFIAAA | HHHHxx 6 | 2855 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | VFEAAA | VVVVxx 7 | 8518 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | QPMAAA | OOOOxx 8 | 5435 | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 16 | 17 | IAAAAA | BBIAAA | VVVVxx 9 | 4463 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 18 | 19 | JAAAAA | RPGAAA | VVVVxx (10 rows) FETCH 11 in foo11; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx (11 rows) FETCH 12 in foo12; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 0 | 9998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | OUOAAA | OOOOxx 1 | 2838 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | EFEAAA | OOOOxx 2 | 2716 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | MAEAAA | AAAAxx 3 | 5679 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | LKIAAA | VVVVxx 4 | 1621 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | JKCAAA | HHHHxx 5 | 5557 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | TFIAAA | HHHHxx 6 | 2855 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | VFEAAA | VVVVxx 7 | 8518 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | QPMAAA | OOOOxx 8 | 5435 | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 16 | 17 | IAAAAA | BBIAAA | VVVVxx 9 | 4463 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 18 | 19 | JAAAAA | RPGAAA | VVVVxx 10 | 8788 | 0 | 2 | 0 | 10 | 10 | 10 | 10 | 10 | 10 | 20 | 21 | KAAAAA | AANAAA | AAAAxx 11 | 8396 | 1 | 3 | 1 | 11 | 11 | 11 | 11 | 11 | 11 | 22 | 23 | LAAAAA | YKMAAA | AAAAxx (12 rows) FETCH 13 in foo13; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx 1504 | 11 | 0 | 0 | 4 | 4 | 4 | 504 | 1504 | 1504 | 1504 | 8 | 9 | WFAAAA | LAAAAA | VVVVxx 5222 | 12 | 0 | 2 | 2 | 2 | 22 | 222 | 1222 | 222 | 5222 | 44 | 45 | WSAAAA | MAAAAA | AAAAxx (13 rows) FETCH 14 in foo14; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 0 | 9998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | OUOAAA | OOOOxx 1 | 2838 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | EFEAAA | OOOOxx 2 | 2716 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | MAEAAA | AAAAxx 3 | 5679 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | LKIAAA | VVVVxx 4 | 1621 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | JKCAAA | HHHHxx 5 | 5557 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | TFIAAA | HHHHxx 6 | 2855 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | VFEAAA | VVVVxx 7 | 8518 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | QPMAAA | OOOOxx 8 | 5435 | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 16 | 17 | IAAAAA | BBIAAA | VVVVxx 9 | 4463 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 18 | 19 | JAAAAA | RPGAAA | VVVVxx 10 | 8788 | 0 | 2 | 0 | 10 | 10 | 10 | 10 | 10 | 10 | 20 | 21 | KAAAAA | AANAAA | AAAAxx 11 | 8396 | 1 | 3 | 1 | 11 | 11 | 11 | 11 | 11 | 11 | 22 | 23 | LAAAAA | YKMAAA | AAAAxx 12 | 6605 | 0 | 0 | 2 | 12 | 12 | 12 | 12 | 12 | 12 | 24 | 25 | MAAAAA | BUJAAA | HHHHxx 13 | 5696 | 1 | 1 | 3 | 13 | 13 | 13 | 13 | 13 | 13 | 26 | 27 | NAAAAA | CLIAAA | AAAAxx (14 rows) FETCH 15 in foo15; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx 1504 | 11 | 0 | 0 | 4 | 4 | 4 | 504 | 1504 | 1504 | 1504 | 8 | 9 | WFAAAA | LAAAAA | VVVVxx 5222 | 12 | 0 | 2 | 2 | 2 | 22 | 222 | 1222 | 222 | 5222 | 44 | 45 | WSAAAA | MAAAAA | AAAAxx 6243 | 13 | 1 | 3 | 3 | 3 | 43 | 243 | 243 | 1243 | 6243 | 86 | 87 | DGAAAA | NAAAAA | HHHHxx 5471 | 14 | 1 | 3 | 1 | 11 | 71 | 471 | 1471 | 471 | 5471 | 142 | 143 | LCAAAA | OAAAAA | OOOOxx (15 rows) FETCH 16 in foo16; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 0 | 9998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | OUOAAA | OOOOxx 1 | 2838 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | EFEAAA | OOOOxx 2 | 2716 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | MAEAAA | AAAAxx 3 | 5679 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | LKIAAA | VVVVxx 4 | 1621 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | JKCAAA | HHHHxx 5 | 5557 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | TFIAAA | HHHHxx 6 | 2855 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | VFEAAA | VVVVxx 7 | 8518 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | QPMAAA | OOOOxx 8 | 5435 | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 16 | 17 | IAAAAA | BBIAAA | VVVVxx 9 | 4463 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 18 | 19 | JAAAAA | RPGAAA | VVVVxx 10 | 8788 | 0 | 2 | 0 | 10 | 10 | 10 | 10 | 10 | 10 | 20 | 21 | KAAAAA | AANAAA | AAAAxx 11 | 8396 | 1 | 3 | 1 | 11 | 11 | 11 | 11 | 11 | 11 | 22 | 23 | LAAAAA | YKMAAA | AAAAxx 12 | 6605 | 0 | 0 | 2 | 12 | 12 | 12 | 12 | 12 | 12 | 24 | 25 | MAAAAA | BUJAAA | HHHHxx 13 | 5696 | 1 | 1 | 3 | 13 | 13 | 13 | 13 | 13 | 13 | 26 | 27 | NAAAAA | CLIAAA | AAAAxx 14 | 4341 | 0 | 2 | 4 | 14 | 14 | 14 | 14 | 14 | 14 | 28 | 29 | OAAAAA | ZKGAAA | HHHHxx 15 | 1358 | 1 | 3 | 5 | 15 | 15 | 15 | 15 | 15 | 15 | 30 | 31 | PAAAAA | GACAAA | OOOOxx (16 rows) FETCH 17 in foo17; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx 1504 | 11 | 0 | 0 | 4 | 4 | 4 | 504 | 1504 | 1504 | 1504 | 8 | 9 | WFAAAA | LAAAAA | VVVVxx 5222 | 12 | 0 | 2 | 2 | 2 | 22 | 222 | 1222 | 222 | 5222 | 44 | 45 | WSAAAA | MAAAAA | AAAAxx 6243 | 13 | 1 | 3 | 3 | 3 | 43 | 243 | 243 | 1243 | 6243 | 86 | 87 | DGAAAA | NAAAAA | HHHHxx 5471 | 14 | 1 | 3 | 1 | 11 | 71 | 471 | 1471 | 471 | 5471 | 142 | 143 | LCAAAA | OAAAAA | OOOOxx 5006 | 15 | 0 | 2 | 6 | 6 | 6 | 6 | 1006 | 6 | 5006 | 12 | 13 | OKAAAA | PAAAAA | VVVVxx 5387 | 16 | 1 | 3 | 7 | 7 | 87 | 387 | 1387 | 387 | 5387 | 174 | 175 | FZAAAA | QAAAAA | AAAAxx (17 rows) FETCH 18 in foo18; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 0 | 9998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | OUOAAA | OOOOxx 1 | 2838 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | EFEAAA | OOOOxx 2 | 2716 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | MAEAAA | AAAAxx 3 | 5679 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | LKIAAA | VVVVxx 4 | 1621 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | JKCAAA | HHHHxx 5 | 5557 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | TFIAAA | HHHHxx 6 | 2855 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | VFEAAA | VVVVxx 7 | 8518 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | QPMAAA | OOOOxx 8 | 5435 | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 16 | 17 | IAAAAA | BBIAAA | VVVVxx 9 | 4463 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 18 | 19 | JAAAAA | RPGAAA | VVVVxx 10 | 8788 | 0 | 2 | 0 | 10 | 10 | 10 | 10 | 10 | 10 | 20 | 21 | KAAAAA | AANAAA | AAAAxx 11 | 8396 | 1 | 3 | 1 | 11 | 11 | 11 | 11 | 11 | 11 | 22 | 23 | LAAAAA | YKMAAA | AAAAxx 12 | 6605 | 0 | 0 | 2 | 12 | 12 | 12 | 12 | 12 | 12 | 24 | 25 | MAAAAA | BUJAAA | HHHHxx 13 | 5696 | 1 | 1 | 3 | 13 | 13 | 13 | 13 | 13 | 13 | 26 | 27 | NAAAAA | CLIAAA | AAAAxx 14 | 4341 | 0 | 2 | 4 | 14 | 14 | 14 | 14 | 14 | 14 | 28 | 29 | OAAAAA | ZKGAAA | HHHHxx 15 | 1358 | 1 | 3 | 5 | 15 | 15 | 15 | 15 | 15 | 15 | 30 | 31 | PAAAAA | GACAAA | OOOOxx 16 | 9675 | 0 | 0 | 6 | 16 | 16 | 16 | 16 | 16 | 16 | 32 | 33 | QAAAAA | DIOAAA | VVVVxx 17 | 8274 | 1 | 1 | 7 | 17 | 17 | 17 | 17 | 17 | 17 | 34 | 35 | RAAAAA | GGMAAA | OOOOxx (18 rows) FETCH 19 in foo19; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx 1504 | 11 | 0 | 0 | 4 | 4 | 4 | 504 | 1504 | 1504 | 1504 | 8 | 9 | WFAAAA | LAAAAA | VVVVxx 5222 | 12 | 0 | 2 | 2 | 2 | 22 | 222 | 1222 | 222 | 5222 | 44 | 45 | WSAAAA | MAAAAA | AAAAxx 6243 | 13 | 1 | 3 | 3 | 3 | 43 | 243 | 243 | 1243 | 6243 | 86 | 87 | DGAAAA | NAAAAA | HHHHxx 5471 | 14 | 1 | 3 | 1 | 11 | 71 | 471 | 1471 | 471 | 5471 | 142 | 143 | LCAAAA | OAAAAA | OOOOxx 5006 | 15 | 0 | 2 | 6 | 6 | 6 | 6 | 1006 | 6 | 5006 | 12 | 13 | OKAAAA | PAAAAA | VVVVxx 5387 | 16 | 1 | 3 | 7 | 7 | 87 | 387 | 1387 | 387 | 5387 | 174 | 175 | FZAAAA | QAAAAA | AAAAxx 5785 | 17 | 1 | 1 | 5 | 5 | 85 | 785 | 1785 | 785 | 5785 | 170 | 171 | NOAAAA | RAAAAA | HHHHxx 6621 | 18 | 1 | 1 | 1 | 1 | 21 | 621 | 621 | 1621 | 6621 | 42 | 43 | RUAAAA | SAAAAA | OOOOxx (19 rows) FETCH 20 in foo20; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 0 | 9998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | OUOAAA | OOOOxx 1 | 2838 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | EFEAAA | OOOOxx 2 | 2716 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | MAEAAA | AAAAxx 3 | 5679 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | LKIAAA | VVVVxx 4 | 1621 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | JKCAAA | HHHHxx 5 | 5557 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | TFIAAA | HHHHxx 6 | 2855 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | VFEAAA | VVVVxx 7 | 8518 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | QPMAAA | OOOOxx 8 | 5435 | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 16 | 17 | IAAAAA | BBIAAA | VVVVxx 9 | 4463 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 18 | 19 | JAAAAA | RPGAAA | VVVVxx 10 | 8788 | 0 | 2 | 0 | 10 | 10 | 10 | 10 | 10 | 10 | 20 | 21 | KAAAAA | AANAAA | AAAAxx 11 | 8396 | 1 | 3 | 1 | 11 | 11 | 11 | 11 | 11 | 11 | 22 | 23 | LAAAAA | YKMAAA | AAAAxx 12 | 6605 | 0 | 0 | 2 | 12 | 12 | 12 | 12 | 12 | 12 | 24 | 25 | MAAAAA | BUJAAA | HHHHxx 13 | 5696 | 1 | 1 | 3 | 13 | 13 | 13 | 13 | 13 | 13 | 26 | 27 | NAAAAA | CLIAAA | AAAAxx 14 | 4341 | 0 | 2 | 4 | 14 | 14 | 14 | 14 | 14 | 14 | 28 | 29 | OAAAAA | ZKGAAA | HHHHxx 15 | 1358 | 1 | 3 | 5 | 15 | 15 | 15 | 15 | 15 | 15 | 30 | 31 | PAAAAA | GACAAA | OOOOxx 16 | 9675 | 0 | 0 | 6 | 16 | 16 | 16 | 16 | 16 | 16 | 32 | 33 | QAAAAA | DIOAAA | VVVVxx 17 | 8274 | 1 | 1 | 7 | 17 | 17 | 17 | 17 | 17 | 17 | 34 | 35 | RAAAAA | GGMAAA | OOOOxx 18 | 376 | 0 | 2 | 8 | 18 | 18 | 18 | 18 | 18 | 18 | 36 | 37 | SAAAAA | MOAAAA | AAAAxx 19 | 7303 | 1 | 3 | 9 | 19 | 19 | 19 | 19 | 19 | 19 | 38 | 39 | TAAAAA | XUKAAA | VVVVxx (20 rows) FETCH 21 in foo21; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx 1504 | 11 | 0 | 0 | 4 | 4 | 4 | 504 | 1504 | 1504 | 1504 | 8 | 9 | WFAAAA | LAAAAA | VVVVxx 5222 | 12 | 0 | 2 | 2 | 2 | 22 | 222 | 1222 | 222 | 5222 | 44 | 45 | WSAAAA | MAAAAA | AAAAxx 6243 | 13 | 1 | 3 | 3 | 3 | 43 | 243 | 243 | 1243 | 6243 | 86 | 87 | DGAAAA | NAAAAA | HHHHxx 5471 | 14 | 1 | 3 | 1 | 11 | 71 | 471 | 1471 | 471 | 5471 | 142 | 143 | LCAAAA | OAAAAA | OOOOxx 5006 | 15 | 0 | 2 | 6 | 6 | 6 | 6 | 1006 | 6 | 5006 | 12 | 13 | OKAAAA | PAAAAA | VVVVxx 5387 | 16 | 1 | 3 | 7 | 7 | 87 | 387 | 1387 | 387 | 5387 | 174 | 175 | FZAAAA | QAAAAA | AAAAxx 5785 | 17 | 1 | 1 | 5 | 5 | 85 | 785 | 1785 | 785 | 5785 | 170 | 171 | NOAAAA | RAAAAA | HHHHxx 6621 | 18 | 1 | 1 | 1 | 1 | 21 | 621 | 621 | 1621 | 6621 | 42 | 43 | RUAAAA | SAAAAA | OOOOxx 6969 | 19 | 1 | 1 | 9 | 9 | 69 | 969 | 969 | 1969 | 6969 | 138 | 139 | BIAAAA | TAAAAA | VVVVxx 9460 | 20 | 0 | 0 | 0 | 0 | 60 | 460 | 1460 | 4460 | 9460 | 120 | 121 | WZAAAA | UAAAAA | AAAAxx (21 rows) FETCH 22 in foo22; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 0 | 9998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | OUOAAA | OOOOxx 1 | 2838 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | EFEAAA | OOOOxx 2 | 2716 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | MAEAAA | AAAAxx 3 | 5679 | 1 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 3 | 6 | 7 | DAAAAA | LKIAAA | VVVVxx 4 | 1621 | 0 | 0 | 4 | 4 | 4 | 4 | 4 | 4 | 4 | 8 | 9 | EAAAAA | JKCAAA | HHHHxx 5 | 5557 | 1 | 1 | 5 | 5 | 5 | 5 | 5 | 5 | 5 | 10 | 11 | FAAAAA | TFIAAA | HHHHxx 6 | 2855 | 0 | 2 | 6 | 6 | 6 | 6 | 6 | 6 | 6 | 12 | 13 | GAAAAA | VFEAAA | VVVVxx 7 | 8518 | 1 | 3 | 7 | 7 | 7 | 7 | 7 | 7 | 7 | 14 | 15 | HAAAAA | QPMAAA | OOOOxx 8 | 5435 | 0 | 0 | 8 | 8 | 8 | 8 | 8 | 8 | 8 | 16 | 17 | IAAAAA | BBIAAA | VVVVxx 9 | 4463 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 9 | 9 | 18 | 19 | JAAAAA | RPGAAA | VVVVxx 10 | 8788 | 0 | 2 | 0 | 10 | 10 | 10 | 10 | 10 | 10 | 20 | 21 | KAAAAA | AANAAA | AAAAxx 11 | 8396 | 1 | 3 | 1 | 11 | 11 | 11 | 11 | 11 | 11 | 22 | 23 | LAAAAA | YKMAAA | AAAAxx 12 | 6605 | 0 | 0 | 2 | 12 | 12 | 12 | 12 | 12 | 12 | 24 | 25 | MAAAAA | BUJAAA | HHHHxx 13 | 5696 | 1 | 1 | 3 | 13 | 13 | 13 | 13 | 13 | 13 | 26 | 27 | NAAAAA | CLIAAA | AAAAxx 14 | 4341 | 0 | 2 | 4 | 14 | 14 | 14 | 14 | 14 | 14 | 28 | 29 | OAAAAA | ZKGAAA | HHHHxx 15 | 1358 | 1 | 3 | 5 | 15 | 15 | 15 | 15 | 15 | 15 | 30 | 31 | PAAAAA | GACAAA | OOOOxx 16 | 9675 | 0 | 0 | 6 | 16 | 16 | 16 | 16 | 16 | 16 | 32 | 33 | QAAAAA | DIOAAA | VVVVxx 17 | 8274 | 1 | 1 | 7 | 17 | 17 | 17 | 17 | 17 | 17 | 34 | 35 | RAAAAA | GGMAAA | OOOOxx 18 | 376 | 0 | 2 | 8 | 18 | 18 | 18 | 18 | 18 | 18 | 36 | 37 | SAAAAA | MOAAAA | AAAAxx 19 | 7303 | 1 | 3 | 9 | 19 | 19 | 19 | 19 | 19 | 19 | 38 | 39 | TAAAAA | XUKAAA | VVVVxx 20 | 5574 | 0 | 0 | 0 | 0 | 20 | 20 | 20 | 20 | 20 | 40 | 41 | UAAAAA | KGIAAA | OOOOxx 21 | 1628 | 1 | 1 | 1 | 1 | 21 | 21 | 21 | 21 | 21 | 42 | 43 | VAAAAA | QKCAAA | AAAAxx (22 rows) FETCH 23 in foo23; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx 1891 | 1 | 1 | 3 | 1 | 11 | 91 | 891 | 1891 | 1891 | 1891 | 182 | 183 | TUAAAA | BAAAAA | HHHHxx 3420 | 2 | 0 | 0 | 0 | 0 | 20 | 420 | 1420 | 3420 | 3420 | 40 | 41 | OBAAAA | CAAAAA | OOOOxx 9850 | 3 | 0 | 2 | 0 | 10 | 50 | 850 | 1850 | 4850 | 9850 | 100 | 101 | WOAAAA | DAAAAA | VVVVxx 7164 | 4 | 0 | 0 | 4 | 4 | 64 | 164 | 1164 | 2164 | 7164 | 128 | 129 | OPAAAA | EAAAAA | AAAAxx 8009 | 5 | 1 | 1 | 9 | 9 | 9 | 9 | 9 | 3009 | 8009 | 18 | 19 | BWAAAA | FAAAAA | HHHHxx 5057 | 6 | 1 | 1 | 7 | 17 | 57 | 57 | 1057 | 57 | 5057 | 114 | 115 | NMAAAA | GAAAAA | OOOOxx 6701 | 7 | 1 | 1 | 1 | 1 | 1 | 701 | 701 | 1701 | 6701 | 2 | 3 | TXAAAA | HAAAAA | VVVVxx 4321 | 8 | 1 | 1 | 1 | 1 | 21 | 321 | 321 | 4321 | 4321 | 42 | 43 | FKAAAA | IAAAAA | AAAAxx 3043 | 9 | 1 | 3 | 3 | 3 | 43 | 43 | 1043 | 3043 | 3043 | 86 | 87 | BNAAAA | JAAAAA | HHHHxx 1314 | 10 | 0 | 2 | 4 | 14 | 14 | 314 | 1314 | 1314 | 1314 | 28 | 29 | OYAAAA | KAAAAA | OOOOxx 1504 | 11 | 0 | 0 | 4 | 4 | 4 | 504 | 1504 | 1504 | 1504 | 8 | 9 | WFAAAA | LAAAAA | VVVVxx 5222 | 12 | 0 | 2 | 2 | 2 | 22 | 222 | 1222 | 222 | 5222 | 44 | 45 | WSAAAA | MAAAAA | AAAAxx 6243 | 13 | 1 | 3 | 3 | 3 | 43 | 243 | 243 | 1243 | 6243 | 86 | 87 | DGAAAA | NAAAAA | HHHHxx 5471 | 14 | 1 | 3 | 1 | 11 | 71 | 471 | 1471 | 471 | 5471 | 142 | 143 | LCAAAA | OAAAAA | OOOOxx 5006 | 15 | 0 | 2 | 6 | 6 | 6 | 6 | 1006 | 6 | 5006 | 12 | 13 | OKAAAA | PAAAAA | VVVVxx 5387 | 16 | 1 | 3 | 7 | 7 | 87 | 387 | 1387 | 387 | 5387 | 174 | 175 | FZAAAA | QAAAAA | AAAAxx 5785 | 17 | 1 | 1 | 5 | 5 | 85 | 785 | 1785 | 785 | 5785 | 170 | 171 | NOAAAA | RAAAAA | HHHHxx 6621 | 18 | 1 | 1 | 1 | 1 | 21 | 621 | 621 | 1621 | 6621 | 42 | 43 | RUAAAA | SAAAAA | OOOOxx 6969 | 19 | 1 | 1 | 9 | 9 | 69 | 969 | 969 | 1969 | 6969 | 138 | 139 | BIAAAA | TAAAAA | VVVVxx 9460 | 20 | 0 | 0 | 0 | 0 | 60 | 460 | 1460 | 4460 | 9460 | 120 | 121 | WZAAAA | UAAAAA | AAAAxx 59 | 21 | 1 | 3 | 9 | 19 | 59 | 59 | 59 | 59 | 59 | 118 | 119 | HCAAAA | VAAAAA | HHHHxx 8020 | 22 | 0 | 0 | 0 | 0 | 20 | 20 | 20 | 3020 | 8020 | 40 | 41 | MWAAAA | WAAAAA | OOOOxx (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 1; name | statement | is_holdable | is_binary | is_scrollable -------+-----------------------------------------------------------------------+-------------+-----------+--------------- foo13 | DECLARE foo13 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; | f | f | f foo14 | DECLARE foo14 CURSOR FOR SELECT * FROM tenk2 ORDER BY 1,2,3,4; | f | f | f foo15 | DECLARE foo15 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; | f | f | f foo16 | DECLARE foo16 CURSOR FOR SELECT * FROM tenk2 ORDER BY 1,2,3,4; | f | f | f foo17 | DECLARE foo17 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; | f | f | f foo18 | DECLARE foo18 CURSOR FOR SELECT * FROM tenk2 ORDER BY 1,2,3,4; | f | f | f foo19 | DECLARE foo19 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; | f | f | f foo20 | DECLARE foo20 CURSOR FOR SELECT * FROM tenk2 ORDER BY 1,2,3,4; | f | f | f foo21 | DECLARE foo21 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; | f | f | f foo22 | DECLARE foo22 CURSOR FOR SELECT * FROM tenk2 ORDER BY 1,2,3,4; | f | f | f foo23 | DECLARE foo23 SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; | f | f | f (11 rows) END; SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors; name | statement | is_holdable | is_binary | is_scrollable ------+-----------+-------------+-----------+--------------- (0 rows) -- -- NO SCROLL disallows backward fetching -- BEGIN; DECLARE foo24 NO SCROLL CURSOR FOR SELECT * FROM tenk1 ORDER BY unique2; FETCH 1 FROM foo24; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 8800 | 0 | 0 | 0 | 0 | 0 | 0 | 800 | 800 | 3800 | 8800 | 0 | 1 | MAAAAA | AAAAAA | AAAAxx (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; name | statement | is_holdable | is_binary | is_scrollable ------+-----------+-------------+-----------+--------------- (0 rows) BEGIN; DECLARE foo25 CURSOR WITH HOLD FOR SELECT * FROM tenk2 ORDER BY 1,2,3,4; FETCH FROM foo25; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 0 | 9998 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1 | AAAAAA | OUOAAA | OOOOxx (1 row) FETCH FROM foo25; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 1 | 2838 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 2 | 3 | BAAAAA | EFEAAA | OOOOxx (1 row) COMMIT; FETCH FROM foo25; unique1 | unique2 | two | four | ten | twenty | hundred | thousand | twothousand | fivethous | tenthous | odd | even | stringu1 | stringu2 | string4 ---------+---------+-----+------+-----+--------+---------+----------+-------------+-----------+----------+-----+------+----------+----------+--------- 2 | 2716 | 0 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 2 | 4 | 5 | CAAAAA | MAEAAA | AAAAxx (1 row) --FETCH BACKWARD FROM foo25; --FETCH ABSOLUTE -1 FROM foo25; SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors; name | statement | is_holdable | is_binary | is_scrollable -------+--------------------------------------------------------------------------+-------------+-----------+--------------- foo25 | DECLARE foo25 CURSOR WITH HOLD FOR SELECT * FROM tenk2 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 tenk1 ORDER BY unique2; ROLLBACK; -- should fail FETCH FROM foo26; ERROR: cursor "foo26" does not exist -- -- Parameterized DECLARE needs to insert param values into the cursor portal -- BEGIN; CREATE FUNCTION declares_cursor(text) RETURNS void AS 'DECLARE c CURSOR FOR SELECT stringu1 FROM tenk1 WHERE stringu1 LIKE $1;' LANGUAGE SQL READS SQL DATA; SELECT declares_cursor('AB%'); declares_cursor ----------------- (1 row) FETCH ALL FROM c; stringu1 ---------- ABAAAA ABAAAA ABAAAA ABAAAA ABAAAA ABAAAA ABAAAA ABAAAA ABAAAA ABAAAA ABAAAA ABAAAA ABAAAA ABAAAA ABAAAA (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); NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'f1' as the Greenplum Database data distribution key for this table. HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. create function count_tt1_v() returns int8 as 'select count(*) from tt1' language sql volatile READS SQL DATA; create function count_tt1_s() returns int8 as 'select count(*) from tt1' language sql stable READS SQL DATA; 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; -- DISABLED: see open JIRA MPP-835 -- start_ignore count_tt1_v | count_tt1_s -------------+------------- 2 | 1 (1 row) -- end_ignore 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; -- DISABLED: see open JIRA MPP-835 -- start_ignore count_tt1_v | count_tt1_s -------------+------------- 2 | 1 (1 row) -- end_ignore drop function count_tt1_v(); drop function count_tt1_s(); -- 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; name | statement | is_holdable | is_binary | is_scrollable ------+----------------------------------------------------------------------+-------------+-----------+--------------- c2 | declare c2 cursor with hold for select count_tt1_v(), count_tt1_s(); | t | f | f (1 row) DECLARE bc BINARY CURSOR FOR SELECT * FROM tenk1; SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors; name | statement | is_holdable | is_binary | is_scrollable ------+----------------------------------------------------------------------+-------------+-----------+--------------- bc | DECLARE bc BINARY CURSOR FOR SELECT * FROM tenk1; | f | t | f c2 | declare c2 cursor with hold for select count_tt1_v(), count_tt1_s(); | t | f | f (2 rows) ROLLBACK; -- We should not see the portal that is created internally to -- implement EXECUTE in pg_cursors PREPARE cprep AS SELECT name, statement, is_holdable, is_binary, is_scrollable FROM pg_cursors; EXECUTE cprep; name | statement | is_holdable | is_binary | is_scrollable ------+----------------------------------------------------------------------+-------------+-----------+--------------- c2 | declare c2 cursor with hold for select count_tt1_v(), count_tt1_s(); | t | f | f (1 row) -- test CLOSE ALL; SELECT name FROM pg_cursors ORDER BY 1; name ------ c2 (1 row) CLOSE ALL; SELECT name FROM pg_cursors ORDER BY 1; name ------ (0 rows) BEGIN; DECLARE foo1 CURSOR WITH HOLD FOR SELECT 1; DECLARE foo2 CURSOR WITHOUT HOLD FOR SELECT 1; SELECT name FROM pg_cursors ORDER BY 1; name ------ foo1 foo2 (2 rows) CLOSE ALL; SELECT name FROM pg_cursors ORDER BY 1; name ------ (0 rows) COMMIT; -- -- Tests for updatable cursors -- -- In GPDB, we use a dummy column as distribution key, so that all the -- rows land on the same segment. Otherwise the order the cursor returns -- the rows is unstable. CREATE TEMP TABLE uctest(f1 int, f2 text, distkey text) distributed by (distkey); INSERT INTO uctest VALUES (1, 'one'), (2, 'two'), (3, 'three'); SELECT f1, f2 FROM uctest; f1 | f2 ----+------- 1 | one 2 | two 3 | three (3 rows) -- Check DELETE WHERE CURRENT BEGIN; DECLARE c1 CURSOR FOR SELECT f1, f2 FROM uctest; FETCH 2 FROM c1; f1 | f2 ----+----- 1 | one 2 | two (2 rows) DELETE FROM uctest WHERE CURRENT OF c1; -- should show deletion SELECT f1, f2 FROM uctest; f1 | f2 ----+------- 1 | one 3 | three (2 rows) -- cursor did not move FETCH ALL FROM c1; f1 | f2 ----+------- 3 | three (1 row) -- cursor is insensitive --MOVE BACKWARD ALL IN c1; -- backwards scans not supported in GPDB --FETCH ALL FROM c1; COMMIT; -- should still see deletion SELECT f1, f2 FROM uctest; f1 | f2 ----+------- 1 | one 3 | three (2 rows) -- Check UPDATE WHERE CURRENT; this time use FOR UPDATE BEGIN; DECLARE c1 CURSOR FOR SELECT f1, f2 FROM uctest FOR UPDATE; FETCH c1; f1 | f2 ----+----- 1 | one (1 row) UPDATE uctest SET f1 = 8 WHERE CURRENT OF c1; SELECT f1, f2 FROM uctest; f1 | f2 ----+------- 3 | three 8 | one (2 rows) COMMIT; SELECT f1, f2 FROM uctest; f1 | f2 ----+------- 3 | three 8 | one (2 rows) -- Check repeated-update and update-then-delete cases BEGIN; DECLARE c1 CURSOR FOR SELECT f1, f2 FROM uctest; FETCH c1; f1 | f2 ----+------- 3 | three (1 row) UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; SELECT f1, f2 FROM uctest; f1 | f2 ----+------- 8 | one 13 | three (2 rows) UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; -- currently broken on GPDB! (does nothing) SELECT f1, f2 FROM uctest; f1 | f2 ----+------- 8 | one 13 | three (2 rows) -- insensitive cursor should not show effects of updates or deletes --FETCH RELATIVE 0 FROM c1; DELETE FROM uctest WHERE CURRENT OF c1; -- currently broken on GPDB! (does nothing) SELECT f1, f2 FROM uctest; f1 | f2 ----+------- 8 | one 13 | three (2 rows) DELETE FROM uctest WHERE CURRENT OF c1; -- no-op SELECT f1, f2 FROM uctest; f1 | f2 ----+------- 8 | one 13 | three (2 rows) UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; -- no-op SELECT f1, f2 FROM uctest; f1 | f2 ----+------- 8 | one 13 | three (2 rows) --FETCH RELATIVE 0 FROM c1; ROLLBACK; SELECT f1, f2 FROM uctest; f1 | f2 ----+------- 3 | three 8 | one (2 rows) BEGIN; DECLARE c1 CURSOR FOR SELECT f1, f2 FROM uctest FOR UPDATE; FETCH c1; f1 | f2 ----+------- 3 | three (1 row) UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; SELECT f1, f2 FROM uctest; f1 | f2 ----+------- 8 | one 13 | three (2 rows) UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; -- currently broken on GPDB! (does nothing) SELECT f1, f2 FROM uctest; f1 | f2 ----+------- 8 | one 13 | three (2 rows) DELETE FROM uctest WHERE CURRENT OF c1; -- currently broken on GPDB! (does nothing) SELECT f1, f2 FROM uctest; f1 | f2 ----+------- 8 | one 13 | three (2 rows) DELETE FROM uctest WHERE CURRENT OF c1; -- no-op SELECT f1, f2 FROM uctest; f1 | f2 ----+------- 8 | one 13 | three (2 rows) UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; -- no-op SELECT f1, f2 FROM uctest; f1 | f2 ----+------- 8 | one 13 | three (2 rows) --- sensitive cursors can't currently scroll back, so this is an error: FETCH RELATIVE 0 FROM c1; ERROR: cursor can only scan forward HINT: Declare it with SCROLL option to enable backward scan. ROLLBACK; SELECT f1, f2 FROM uctest; f1 | f2 ----+------- 3 | three 8 | one (2 rows) -- Check inheritance cases CREATE TEMP TABLE ucchild () inherits (uctest); NOTICE: table has parent, setting distribution columns to match parent table INSERT INTO ucchild values(100, 'hundred'); SELECT f1, f2 FROM uctest; f1 | f2 -----+--------- 3 | three 8 | one 100 | hundred (3 rows) BEGIN; DECLARE c1 CURSOR FOR SELECT f1, f2 FROM uctest FOR UPDATE; FETCH 1 FROM c1; f1 | f2 ----+------- 3 | three (1 row) UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; FETCH 1 FROM c1; f1 | f2 ----+----- 8 | one (1 row) UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; FETCH 1 FROM c1; f1 | f2 -----+--------- 100 | hundred (1 row) UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; FETCH 1 FROM c1; f1 | f2 ----+---- (0 rows) COMMIT; SELECT f1, f2 FROM uctest; f1 | f2 -----+--------- 13 | three 18 | one 110 | hundred (3 rows) -- Can update from a self-join, but only if FOR UPDATE says which to use BEGIN; DECLARE c1 CURSOR FOR SELECT * FROM uctest a, uctest b WHERE a.f1 = b.f1 + 5; FETCH 1 FROM c1; f1 | f2 | distkey | f1 | f2 | distkey ----+-----+---------+----+-------+--------- 18 | one | | 13 | three | (1 row) UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; -- fail ERROR: cursor "c1" is not a simply updatable scan of table "uctest" ROLLBACK; BEGIN; DECLARE c1 CURSOR FOR SELECT * FROM uctest a, uctest b WHERE a.f1 = b.f1 + 5 FOR UPDATE; FETCH 1 FROM c1; f1 | f2 | distkey | f1 | f2 | distkey ----+-----+---------+----+-------+--------- 18 | one | | 13 | three | (1 row) UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; -- fail ERROR: cursor "c1" is not a simply updatable scan of table "uctest" ROLLBACK; BEGIN; DECLARE c1 CURSOR FOR SELECT * FROM uctest a, uctest b WHERE a.f1 = b.f1 + 5 FOR SHARE OF a; FETCH 1 FROM c1; f1 | f2 | distkey | f1 | f2 | distkey ----+-----+---------+----+-------+--------- 18 | one | | 13 | three | (1 row) UPDATE uctest SET f1 = f1 + 10 WHERE CURRENT OF c1; ERROR: cursor "c1" is not a simply updatable scan of table "uctest" SELECT * FROM uctest; ERROR: current transaction is aborted, commands ignored until end of transaction block ROLLBACK; -- Check various error cases DELETE FROM uctest WHERE CURRENT OF c1; -- fail, no such cursor ERROR: cursor "c1" does not exist DECLARE cx CURSOR WITH HOLD FOR SELECT * FROM uctest; DELETE FROM uctest WHERE CURRENT OF cx; -- fail, can't use held cursor ERROR: cursor "cx" is held from a previous transaction BEGIN; DECLARE c CURSOR FOR SELECT * FROM tenk2; DELETE FROM uctest WHERE CURRENT OF c; -- fail, cursor on wrong table ERROR: cursor "c" is not a simply updatable scan of table "uctest" ROLLBACK; BEGIN; DECLARE c CURSOR FOR SELECT * FROM tenk2 FOR SHARE; DELETE FROM uctest WHERE CURRENT OF c; -- fail, cursor on wrong table ERROR: cursor "c" is not a simply updatable scan of table "uctest" ROLLBACK; BEGIN; DECLARE c CURSOR FOR SELECT * FROM tenk1 JOIN tenk2 USING (unique1); DELETE FROM tenk1 WHERE CURRENT OF c; -- fail, cursor is on a join ERROR: cursor "c" is not a simply updatable scan of table "tenk1" ROLLBACK; BEGIN; DECLARE c CURSOR FOR SELECT f1,count(*) FROM uctest GROUP BY f1; DELETE FROM uctest WHERE CURRENT OF c; -- fail, cursor is on aggregation ERROR: cursor "c" is not a simply updatable scan of table "uctest" ROLLBACK; BEGIN; DECLARE c1 CURSOR FOR SELECT * FROM uctest; DELETE FROM uctest WHERE CURRENT OF c1; -- fail, no current row ERROR: cursor "c1" is not positioned on a row ROLLBACK; BEGIN; DECLARE c1 CURSOR FOR SELECT MIN(f1) FROM uctest FOR UPDATE; ERROR: FOR UPDATE is not allowed with aggregate functions ROLLBACK; -- WHERE CURRENT OF may someday work with views, but today is not that day. -- For now, just make sure it errors out cleanly. CREATE TEMP VIEW ucview AS SELECT f1, f2 FROM uctest; CREATE RULE ucrule AS ON DELETE TO ucview DO INSTEAD DELETE FROM uctest WHERE f1 = OLD.f1; BEGIN; DECLARE c1 CURSOR FOR SELECT * FROM ucview; FETCH FROM c1; f1 | f2 ----+------- 13 | three (1 row) DELETE FROM ucview WHERE CURRENT OF c1; -- fail, views not supported ERROR: WHERE CURRENT OF on a view is not implemented ROLLBACK; -- Check cursors for functions. BEGIN; DECLARE c1 CURSOR FOR SELECT * FROM LOWER('TEST'); FETCH ALL FROM c1; lower ------- test (1 row) COMMIT; -- Check WHERE CURRENT OF with an index-only scan BEGIN; EXPLAIN (costs off) DECLARE c1 CURSOR FOR SELECT stringu1 FROM onek WHERE stringu1 = 'DZAAAA'; QUERY PLAN --------------------------------------------- Gather Motion 3:1 (slice1; segments: 3) -> Seq Scan on onek Filter: (stringu1 = 'DZAAAA'::name) Optimizer: Postgres query optimizer (4 rows) DECLARE c1 CURSOR FOR SELECT stringu1 FROM onek WHERE stringu1 = 'DZAAAA'; FETCH FROM c1; stringu1 ---------- DZAAAA (1 row) DELETE FROM onek WHERE CURRENT OF c1; SELECT stringu1 FROM onek WHERE stringu1 = 'DZAAAA'; stringu1 ---------- (0 rows) ROLLBACK; -- start_ignore -- ignore the block, because cursor can only scan forward -- Check behavior with rewinding to a previous child scan node, -- as per bug #15395 BEGIN; CREATE TABLE current_check (currentid int, payload text); CREATE TABLE current_check_1 () INHERITS (current_check); CREATE TABLE current_check_2 () INHERITS (current_check); INSERT INTO current_check_1 SELECT i, 'p' || i FROM generate_series(1,9) i; INSERT INTO current_check_2 SELECT i, 'P' || i FROM generate_series(10,19) i; DECLARE c1 SCROLL CURSOR FOR SELECT * FROM current_check; -- This tests the fetch-backwards code path FETCH ABSOLUTE 12 FROM c1; currentid | payload -----------+--------- 12 | P12 (1 row) FETCH ABSOLUTE 8 FROM c1; currentid | payload -----------+--------- 8 | p8 (1 row) DELETE FROM current_check WHERE CURRENT OF c1 RETURNING *; currentid | payload -----------+--------- 8 | p8 (1 row) -- This tests the ExecutorRewind code path FETCH ABSOLUTE 13 FROM c1; currentid | payload -----------+--------- 13 | P13 (1 row) FETCH ABSOLUTE 1 FROM c1; currentid | payload -----------+--------- 1 | p1 (1 row) DELETE FROM current_check WHERE CURRENT OF c1 RETURNING *; currentid | payload -----------+--------- 1 | p1 (1 row) SELECT * FROM current_check; currentid | payload -----------+--------- 2 | p2 3 | p3 4 | p4 5 | p5 6 | p6 7 | p7 9 | p9 10 | P10 11 | P11 12 | P12 13 | P13 14 | P14 15 | P15 16 | P16 17 | P17 18 | P18 19 | P19 (17 rows) ROLLBACK; -- end_ignore -- Make sure snapshot management works okay, per bug report in -- 235395b90909301035v7228ce63q392931f15aa74b31@mail.gmail.com -- GPDB_90_MERGE_FIXME: This doesn't work correctly. Two issues: -- 1. In GPDB, an UPDATE, or FOR UPDATE, locks the whole table. Because of -- that, there cannot be concurrent updates, and we don't bother with -- LockRows nodes in FOR UPDATE plans. However, in the upstream, the -- LockRows node also handles fetching the latest tuple version, if it -- was updated in the same transaction, by a *later* command. -- -- 2. Even if we had LockRows in the plan, it still wouldn't work, at least -- not always. In PostgreSQL, the LockRows node checks the visibility -- when a row is FETCHed. Not before that. So if a row is UPDATEd in -- the same transaction, before it's FETCHed, the FETCH is supposed to -- see the effects of the UPDATE. In GPDB, however, a cursor starts -- executing in the segments, as soon as the DECLARE CURSOR is issued, -- so there's a race condition. BEGIN; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; CREATE TABLE cursor (a int, b int); INSERT INTO cursor VALUES (1, 1); DECLARE c1 NO SCROLL CURSOR FOR SELECT * FROM cursor FOR UPDATE; UPDATE cursor SET b = 2; FETCH ALL FROM c1; a | b ---+--- 1 | 1 (1 row) COMMIT; DROP TABLE cursor; -- Check rewinding a cursor containing a stable function in LIMIT, -- per bug report in 8336843.9833.1399385291498.JavaMail.root@quick -- GPDB: ignore the result of the FETCH, because the order the rows -- arrive from the segments is arbitrary in GPDB. This test isn't -- very useful in GPDB anyway, as the bug that this was testing -- happened when rewinding the cursor, and GPDB doesn't support -- MOVE BACKWARD at all. But doesn't hurt to keep it to the extent -- we can, I guess.. begin; create function nochange(int) returns int as 'select $1 limit 1' language sql stable; declare c cursor for select * from int8_tbl limit nochange(3); -- start_ignore fetch all from c; q1 | q2 ------------------+------------------ 123 | 456 123 | 4567890123456789 4567890123456789 | 123 (3 rows) -- end_ignore move backward all in c; ERROR: backward scan is not supported in this version of Greenplum Database fetch all from c; ERROR: current transaction is aborted, commands ignored until end of transaction block rollback;