Repository: incubator-hawq Updated Branches: refs/heads/master 597576433 -> bacfb0f1c
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/bacfb0f1/src/test/regress/output/errortbl.source ---------------------------------------------------------------------- diff --git a/src/test/regress/output/errortbl.source b/src/test/regress/output/errortbl.source deleted file mode 100644 index 8e66b11..0000000 --- a/src/test/regress/output/errortbl.source +++ /dev/null @@ -1,334 +0,0 @@ --- -------------------------------------- --- 'gpfdist' protocol --- -------------------------------------- -CREATE EXTERNAL WEB TABLE gpfdist_status (x text) -execute E'( python $GPHOME/bin/lib/gppinggpfdist.py @hostname@:7070 2>&1 || echo) ' -on SEGMENT 0 -FORMAT 'text' (delimiter '|'); -CREATE EXTERNAL WEB TABLE gpfdist_start (x text) -execute E'((@gpwhich_gpfdist@ -p 7070 -d @abs_srcdir@/data </dev/null >/dev/null 2>&1 &); sleep 2; echo "starting...") ' -on SEGMENT 0 -FORMAT 'text' (delimiter '|'); -CREATE EXTERNAL WEB TABLE gpfdist_stop (x text) -execute E'(/bin/pkill gpfdist || killall gpfdist) > /dev/null 2>&1; echo "stopping..."' -on SEGMENT 0 -FORMAT 'text' (delimiter '|'); --- start_ignore -select * from gpfdist_stop; - x -------------- - stopping... -(1 row) - -select * from gpfdist_status; - x ------------------------------------------------------- - Error: gpfdist is not running (reason: socket error) - Exit: 1 - -(3 rows) - -select * from gpfdist_start; - x -------------- - starting... -(1 row) - -select * from gpfdist_status; - x ---------------------------------------------------------------------------- - Okay, gpfdist version "main build dev" is running on @hostname@:7070. -(1 row) - --- end_ignore --- readable external table with error table -CREATE EXTERNAL TABLE EXT_NATION1 ( N_NATIONKEY INTEGER , - N_NAME CHAR(25) , - N_REGIONKEY INTEGER , - N_COMMENT VARCHAR(152)) -location ('gpfdist://@hostname@:7070/nation_error50.tbl') -FORMAT 'text' (delimiter '|') -LOG ERRORS INTO EXT_NATION_ERROR1 SEGMENT REJECT LIMIT 51; -NOTICE: Error table "ext_nation_error1" does not exist. Auto generating an error table with the same name -CREATE EXTERNAL TABLE EXT_NATION2 ( N_NATIONKEY INTEGER , - N_NAME CHAR(25) , - N_REGIONKEY INTEGER , - N_COMMENT VARCHAR(152)) -location ('gpfdist://@hostname@:7070/nation_error50.tbl') -FORMAT 'text' (delimiter '|') -LOG ERRORS INTO EXT_NATION_ERROR2 SEGMENT REJECT LIMIT 50; -NOTICE: Error table "ext_nation_error2" does not exist. Auto generating an error table with the same name -CREATE EXTERNAL TABLE EXT_NATION3 ( N_NATIONKEY INTEGER , - N_NAME CHAR(25) , - N_REGIONKEY INTEGER , - N_COMMENT VARCHAR(152)) -location ('gpfdist://@hostname@:7070/nation.tbl') -FORMAT 'text' (delimiter '|') -LOG ERRORS INTO EXT_NATION_ERROR3 SEGMENT REJECT LIMIT 50; -NOTICE: Error table "ext_nation_error3" does not exist. Auto generating an error table with the same name --- use existing error table -CREATE EXTERNAL TABLE EXT_NATION_WITH_EXIST_ERROR_TABLE ( N_NATIONKEY INTEGER , - N_NAME CHAR(25) , - N_REGIONKEY INTEGER , - N_COMMENT VARCHAR(152)) -location ('gpfdist://@hostname@:7070/nation_error50.tbl') -FORMAT 'text' (delimiter '|') -LOG ERRORS INTO EXT_NATION_ERROR1 SEGMENT REJECT LIMIT 51; -select * from EXT_NATION1; -NOTICE: Found 50 data formatting errors (50 or more input rows). Rejected related input data. - n_nationkey | n_name | n_regionkey | n_comment --------------+---------------------------+-------------+-------------------------------------------------------------------------------------------------------------------- - 0 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai - 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon - 2 | BRAZIL | 1 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special - 3 | CANADA | 1 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold - 4 | EGYPT | 4 | y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d - 5 | ETHIOPIA | 0 | ven packages wake quickly. regu - 6 | FRANCE | 3 | refully final requests. regular, ironi - 7 | GERMANY | 3 | l platelets. regular accounts x-ray: unusual, regular acco - 8 | INDIA | 2 | ss excuses cajole slyly across the packages. deposits print aroun - 9 | INDONESIA | 2 | slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull - 10 | IRAN | 4 | efully alongside of the slyly final dependencies. - 11 | IRAQ | 4 | nic deposits boost atop the quickly final requests? quickly regula - 12 | JAPAN | 2 | ously. final, express gifts cajole a - 13 | JORDAN | 4 | ic deposits are blithely about the carefully regular pa - 14 | KENYA | 0 | pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t - 15 | MOROCCO | 0 | rns. blithely bold courts among the closely regular packages use furiously bold platelets? - 16 | MOZAMBIQUE | 0 | s. ironic, unusual asymptotes wake blithely r - 17 | PERU | 1 | platelets. blithely pending dependencies use fluffily across the even pinto beans. carefully silent accoun - 18 | CHINA | 2 | c dependencies. furiously express notornis sleep slyly regular accounts. ideas sleep. depos - 19 | ROMANIA | 3 | ular asymptotes are about the furious multipliers. express dependencies nag above the ironically ironic account - 20 | SAUDI ARABIA | 4 | ts. silent requests haggle. closely express packages sleep across the blithely - 21 | VIETNAM | 2 | hely enticingly express accounts. even, final - 22 | RUSSIA | 3 | requests against the platelets use never according to the quickly regular pint - 23 | UNITED KINGDOM | 3 | eans boost carefully special requests. accounts are. carefull - 24 | UNITED STATES | 1 | y final packages. slow foxes cajole quickly. quickly silent platelets breach ironic accounts. unusual pinto be -(25 rows) - -select count(*) from EXT_NATION_ERROR1; -- should be 50 - count -------- - 50 -(1 row) - -select * from EXT_NATION_WITH_EXIST_ERROR_TABLE; -NOTICE: Found 50 data formatting errors (50 or more input rows). Rejected related input data. - n_nationkey | n_name | n_regionkey | n_comment --------------+---------------------------+-------------+-------------------------------------------------------------------------------------------------------------------- - 0 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai - 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon - 2 | BRAZIL | 1 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special - 3 | CANADA | 1 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold - 4 | EGYPT | 4 | y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d - 5 | ETHIOPIA | 0 | ven packages wake quickly. regu - 6 | FRANCE | 3 | refully final requests. regular, ironi - 7 | GERMANY | 3 | l platelets. regular accounts x-ray: unusual, regular acco - 8 | INDIA | 2 | ss excuses cajole slyly across the packages. deposits print aroun - 9 | INDONESIA | 2 | slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull - 10 | IRAN | 4 | efully alongside of the slyly final dependencies. - 11 | IRAQ | 4 | nic deposits boost atop the quickly final requests? quickly regula - 12 | JAPAN | 2 | ously. final, express gifts cajole a - 13 | JORDAN | 4 | ic deposits are blithely about the carefully regular pa - 14 | KENYA | 0 | pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t - 15 | MOROCCO | 0 | rns. blithely bold courts among the closely regular packages use furiously bold platelets? - 16 | MOZAMBIQUE | 0 | s. ironic, unusual asymptotes wake blithely r - 17 | PERU | 1 | platelets. blithely pending dependencies use fluffily across the even pinto beans. carefully silent accoun - 18 | CHINA | 2 | c dependencies. furiously express notornis sleep slyly regular accounts. ideas sleep. depos - 19 | ROMANIA | 3 | ular asymptotes are about the furious multipliers. express dependencies nag above the ironically ironic account - 20 | SAUDI ARABIA | 4 | ts. silent requests haggle. closely express packages sleep across the blithely - 21 | VIETNAM | 2 | hely enticingly express accounts. even, final - 22 | RUSSIA | 3 | requests against the platelets use never according to the quickly regular pint - 23 | UNITED KINGDOM | 3 | eans boost carefully special requests. accounts are. carefull - 24 | UNITED STATES | 1 | y final packages. slow foxes cajole quickly. quickly silent platelets breach ironic accounts. unusual pinto be -(25 rows) - -select count(*) from EXT_NATION_ERROR1; -- should be 100 - count -------- - 100 -(1 row) - -select * from EXT_NATION2; -- should fail -ERROR: Segment reject limit reached. Aborting operation. Last error was: missing data for column "n_name" -DETAIL: External table ext_nation2, line 75 of gpfdist://localhost:7070/nation_error50.tbl: "invalid format" -select count(*) from EXT_NATION_ERROR2; -- should be empty - count -------- - 0 -(1 row) - -select * from EXT_NATION3; - n_nationkey | n_name | n_regionkey | n_comment --------------+---------------------------+-------------+-------------------------------------------------------------------------------------------------------------------- - 0 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai - 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon - 2 | BRAZIL | 1 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special - 3 | CANADA | 1 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold - 4 | EGYPT | 4 | y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d - 5 | ETHIOPIA | 0 | ven packages wake quickly. regu - 6 | FRANCE | 3 | refully final requests. regular, ironi - 7 | GERMANY | 3 | l platelets. regular accounts x-ray: unusual, regular acco - 8 | INDIA | 2 | ss excuses cajole slyly across the packages. deposits print aroun - 9 | INDONESIA | 2 | slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull - 10 | IRAN | 4 | efully alongside of the slyly final dependencies. - 11 | IRAQ | 4 | nic deposits boost atop the quickly final requests? quickly regula - 12 | JAPAN | 2 | ously. final, express gifts cajole a - 13 | JORDAN | 4 | ic deposits are blithely about the carefully regular pa - 14 | KENYA | 0 | pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t - 15 | MOROCCO | 0 | rns. blithely bold courts among the closely regular packages use furiously bold platelets? - 16 | MOZAMBIQUE | 0 | s. ironic, unusual asymptotes wake blithely r - 17 | PERU | 1 | platelets. blithely pending dependencies use fluffily across the even pinto beans. carefully silent accoun - 18 | CHINA | 2 | c dependencies. furiously express notornis sleep slyly regular accounts. ideas sleep. depos - 19 | ROMANIA | 3 | ular asymptotes are about the furious multipliers. express dependencies nag above the ironically ironic account - 20 | SAUDI ARABIA | 4 | ts. silent requests haggle. closely express packages sleep across the blithely - 21 | VIETNAM | 2 | hely enticingly express accounts. even, final - 22 | RUSSIA | 3 | requests against the platelets use never according to the quickly regular pint - 23 | UNITED KINGDOM | 3 | eans boost carefully special requests. accounts are. carefull - 24 | UNITED STATES | 1 | y final packages. slow foxes cajole quickly. quickly silent platelets breach ironic accounts. unusual pinto be -(25 rows) - -select count(*) from EXT_NATION_ERROR3; -- should be empty - count -------- - 0 -(1 row) - -truncate EXT_NATION_ERROR1; -select * from EXT_NATION1 as x, EXT_NATION3 as y where x.n_nationkey = y.n_nationkey; - n_nationkey | n_name | n_regionkey | n_comment | n_nationkey | n_name | n_regionkey | n_comment --------------+---------------------------+-------------+--------------------------------------------------------------------------------------------------------------------+-------------+---------------------------+-------------+-------------------------------------------------------------------------------------------------------------------- - 0 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai | 0 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai - 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon | 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon - 2 | BRAZIL | 1 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special | 2 | BRAZIL | 1 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special - 3 | CANADA | 1 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold | 3 | CANADA | 1 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold - 4 | EGYPT | 4 | y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d | 4 | EGYPT | 4 | y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d - 5 | ETHIOPIA | 0 | ven packages wake quickly. regu | 5 | ETHIOPIA | 0 | ven packages wake quickly. regu - 6 | FRANCE | 3 | refully final requests. regular, ironi | 6 | FRANCE | 3 | refully final requests. regular, ironi - 7 | GERMANY | 3 | l platelets. regular accounts x-ray: unusual, regular acco | 7 | GERMANY | 3 | l platelets. regular accounts x-ray: unusual, regular acco - 8 | INDIA | 2 | ss excuses cajole slyly across the packages. deposits print aroun | 8 | INDIA | 2 | ss excuses cajole slyly across the packages. deposits print aroun - 9 | INDONESIA | 2 | slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull | 9 | INDONESIA | 2 | slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull - 10 | IRAN | 4 | efully alongside of the slyly final dependencies. | 10 | IRAN | 4 | efully alongside of the slyly final dependencies. - 11 | IRAQ | 4 | nic deposits boost atop the quickly final requests? quickly regula | 11 | IRAQ | 4 | nic deposits boost atop the quickly final requests? quickly regula - 12 | JAPAN | 2 | ously. final, express gifts cajole a | 12 | JAPAN | 2 | ously. final, express gifts cajole a - 13 | JORDAN | 4 | ic deposits are blithely about the carefully regular pa | 13 | JORDAN | 4 | ic deposits are blithely about the carefully regular pa - 14 | KENYA | 0 | pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t | 14 | KENYA | 0 | pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t - 15 | MOROCCO | 0 | rns. blithely bold courts among the closely regular packages use furiously bold platelets? | 15 | MOROCCO | 0 | rns. blithely bold courts among the closely regular packages use furiously bold platelets? - 16 | MOZAMBIQUE | 0 | s. ironic, unusual asymptotes wake blithely r | 16 | MOZAMBIQUE | 0 | s. ironic, unusual asymptotes wake blithely r - 17 | PERU | 1 | platelets. blithely pending dependencies use fluffily across the even pinto beans. carefully silent accoun | 17 | PERU | 1 | platelets. blithely pending dependencies use fluffily across the even pinto beans. carefully silent accoun - 18 | CHINA | 2 | c dependencies. furiously express notornis sleep slyly regular accounts. ideas sleep. depos | 18 | CHINA | 2 | c dependencies. furiously express notornis sleep slyly regular accounts. ideas sleep. depos - 19 | ROMANIA | 3 | ular asymptotes are about the furious multipliers. express dependencies nag above the ironically ironic account | 19 | ROMANIA | 3 | ular asymptotes are about the furious multipliers. express dependencies nag above the ironically ironic account - 20 | SAUDI ARABIA | 4 | ts. silent requests haggle. closely express packages sleep across the blithely | 20 | SAUDI ARABIA | 4 | ts. silent requests haggle. closely express packages sleep across the blithely - 21 | VIETNAM | 2 | hely enticingly express accounts. even, final | 21 | VIETNAM | 2 | hely enticingly express accounts. even, final - 22 | RUSSIA | 3 | requests against the platelets use never according to the quickly regular pint | 22 | RUSSIA | 3 | requests against the platelets use never according to the quickly regular pint - 23 | UNITED KINGDOM | 3 | eans boost carefully special requests. accounts are. carefull | 23 | UNITED KINGDOM | 3 | eans boost carefully special requests. accounts are. carefull - 24 | UNITED STATES | 1 | y final packages. slow foxes cajole quickly. quickly silent platelets breach ironic accounts. unusual pinto be | 24 | UNITED STATES | 1 | y final packages. slow foxes cajole quickly. quickly silent platelets breach ironic accounts. unusual pinto be -(25 rows) - -select count(*) from EXT_NATION_ERROR1; -- should be 50 - count -------- - 50 -(1 row) - -truncate EXT_NATION_ERROR1; -select * from EXT_NATION1 as x, EXT_NATION1 as y where x.n_nationkey = y.n_nationkey; --should not fail on self join - n_nationkey | n_name | n_regionkey | n_comment | n_nationkey | n_name | n_regionkey | n_comment --------------+---------------------------+-------------+--------------------------------------------------------------------------------------------------------------------+-------------+---------------------------+-------------+-------------------------------------------------------------------------------------------------------------------- - 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon | 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon - 13 | JORDAN | 4 | ic deposits are blithely about the carefully regular pa | 13 | JORDAN | 4 | ic deposits are blithely about the carefully regular pa - 15 | MOROCCO | 0 | rns. blithely bold courts among the closely regular packages use furiously bold platelets? | 15 | MOROCCO | 0 | rns. blithely bold courts among the closely regular packages use furiously bold platelets? - 17 | PERU | 1 | platelets. blithely pending dependencies use fluffily across the even pinto beans. carefully silent accoun | 17 | PERU | 1 | platelets. blithely pending dependencies use fluffily across the even pinto beans. carefully silent accoun - 9 | INDONESIA | 2 | slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull | 9 | INDONESIA | 2 | slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull - 11 | IRAQ | 4 | nic deposits boost atop the quickly final requests? quickly regula | 11 | IRAQ | 4 | nic deposits boost atop the quickly final requests? quickly regula - 23 | UNITED KINGDOM | 3 | eans boost carefully special requests. accounts are. carefull | 23 | UNITED KINGDOM | 3 | eans boost carefully special requests. accounts are. carefull - 0 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai | 0 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai - 2 | BRAZIL | 1 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special | 2 | BRAZIL | 1 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special - 14 | KENYA | 0 | pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t | 14 | KENYA | 0 | pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t - 16 | MOZAMBIQUE | 0 | s. ironic, unusual asymptotes wake blithely r | 16 | MOZAMBIQUE | 0 | s. ironic, unusual asymptotes wake blithely r - 8 | INDIA | 2 | ss excuses cajole slyly across the packages. deposits print aroun | 8 | INDIA | 2 | ss excuses cajole slyly across the packages. deposits print aroun - 10 | IRAN | 4 | efully alongside of the slyly final dependencies. | 10 | IRAN | 4 | efully alongside of the slyly final dependencies. - 12 | JAPAN | 2 | ously. final, express gifts cajole a | 12 | JAPAN | 2 | ously. final, express gifts cajole a - 24 | UNITED STATES | 1 | y final packages. slow foxes cajole quickly. quickly silent platelets breach ironic accounts. unusual pinto be | 24 | UNITED STATES | 1 | y final packages. slow foxes cajole quickly. quickly silent platelets breach ironic accounts. unusual pinto be - 3 | CANADA | 1 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold | 3 | CANADA | 1 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold - 5 | ETHIOPIA | 0 | ven packages wake quickly. regu | 5 | ETHIOPIA | 0 | ven packages wake quickly. regu - 7 | GERMANY | 3 | l platelets. regular accounts x-ray: unusual, regular acco | 7 | GERMANY | 3 | l platelets. regular accounts x-ray: unusual, regular acco - 19 | ROMANIA | 3 | ular asymptotes are about the furious multipliers. express dependencies nag above the ironically ironic account | 19 | ROMANIA | 3 | ular asymptotes are about the furious multipliers. express dependencies nag above the ironically ironic account - 21 | VIETNAM | 2 | hely enticingly express accounts. even, final | 21 | VIETNAM | 2 | hely enticingly express accounts. even, final - 4 | EGYPT | 4 | y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d | 4 | EGYPT | 4 | y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d - 6 | FRANCE | 3 | refully final requests. regular, ironi | 6 | FRANCE | 3 | refully final requests. regular, ironi - 18 | CHINA | 2 | c dependencies. furiously express notornis sleep slyly regular accounts. ideas sleep. depos | 18 | CHINA | 2 | c dependencies. furiously express notornis sleep slyly regular accounts. ideas sleep. depos - 20 | SAUDI ARABIA | 4 | ts. silent requests haggle. closely express packages sleep across the blithely | 20 | SAUDI ARABIA | 4 | ts. silent requests haggle. closely express packages sleep across the blithely - 22 | RUSSIA | 3 | requests against the platelets use never according to the quickly regular pint | 22 | RUSSIA | 3 | requests against the platelets use never according to the quickly regular pint -(25 rows) - -select count(*) from EXT_NATION_ERROR1; -- should be 100 - count -------- - 100 -(1 row) - -truncate EXT_NATION_ERROR1; -select * from EXT_NATION1 as x, EXT_NATION_WITH_EXIST_ERROR_TABLE as y where x.n_nationkey = y.n_nationkey; --should not fail with the same error table - n_nationkey | n_name | n_regionkey | n_comment | n_nationkey | n_name | n_regionkey | n_comment --------------+---------------------------+-------------+--------------------------------------------------------------------------------------------------------------------+-------------+---------------------------+-------------+-------------------------------------------------------------------------------------------------------------------- - 8 | INDIA | 2 | ss excuses cajole slyly across the packages. deposits print aroun | 8 | INDIA | 2 | ss excuses cajole slyly across the packages. deposits print aroun - 10 | IRAN | 4 | efully alongside of the slyly final dependencies. | 10 | IRAN | 4 | efully alongside of the slyly final dependencies. - 12 | JAPAN | 2 | ously. final, express gifts cajole a | 12 | JAPAN | 2 | ously. final, express gifts cajole a - 24 | UNITED STATES | 1 | y final packages. slow foxes cajole quickly. quickly silent platelets breach ironic accounts. unusual pinto be | 24 | UNITED STATES | 1 | y final packages. slow foxes cajole quickly. quickly silent platelets breach ironic accounts. unusual pinto be - 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon | 1 | ARGENTINA | 1 | al foxes promise slyly according to the regular accounts. bold requests alon - 13 | JORDAN | 4 | ic deposits are blithely about the carefully regular pa | 13 | JORDAN | 4 | ic deposits are blithely about the carefully regular pa - 15 | MOROCCO | 0 | rns. blithely bold courts among the closely regular packages use furiously bold platelets? | 15 | MOROCCO | 0 | rns. blithely bold courts among the closely regular packages use furiously bold platelets? - 17 | PERU | 1 | platelets. blithely pending dependencies use fluffily across the even pinto beans. carefully silent accoun | 17 | PERU | 1 | platelets. blithely pending dependencies use fluffily across the even pinto beans. carefully silent accoun - 4 | EGYPT | 4 | y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d | 4 | EGYPT | 4 | y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d - 6 | FRANCE | 3 | refully final requests. regular, ironi | 6 | FRANCE | 3 | refully final requests. regular, ironi - 18 | CHINA | 2 | c dependencies. furiously express notornis sleep slyly regular accounts. ideas sleep. depos | 18 | CHINA | 2 | c dependencies. furiously express notornis sleep slyly regular accounts. ideas sleep. depos - 20 | SAUDI ARABIA | 4 | ts. silent requests haggle. closely express packages sleep across the blithely | 20 | SAUDI ARABIA | 4 | ts. silent requests haggle. closely express packages sleep across the blithely - 22 | RUSSIA | 3 | requests against the platelets use never according to the quickly regular pint | 22 | RUSSIA | 3 | requests against the platelets use never according to the quickly regular pint - 9 | INDONESIA | 2 | slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull | 9 | INDONESIA | 2 | slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull - 11 | IRAQ | 4 | nic deposits boost atop the quickly final requests? quickly regula | 11 | IRAQ | 4 | nic deposits boost atop the quickly final requests? quickly regula - 23 | UNITED KINGDOM | 3 | eans boost carefully special requests. accounts are. carefull | 23 | UNITED KINGDOM | 3 | eans boost carefully special requests. accounts are. carefull - 0 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai | 0 | ALGERIA | 0 | haggle. carefully final deposits detect slyly agai - 2 | BRAZIL | 1 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special | 2 | BRAZIL | 1 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special - 14 | KENYA | 0 | pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t | 14 | KENYA | 0 | pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t - 16 | MOZAMBIQUE | 0 | s. ironic, unusual asymptotes wake blithely r | 16 | MOZAMBIQUE | 0 | s. ironic, unusual asymptotes wake blithely r - 3 | CANADA | 1 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold | 3 | CANADA | 1 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold - 5 | ETHIOPIA | 0 | ven packages wake quickly. regu | 5 | ETHIOPIA | 0 | ven packages wake quickly. regu - 7 | GERMANY | 3 | l platelets. regular accounts x-ray: unusual, regular acco | 7 | GERMANY | 3 | l platelets. regular accounts x-ray: unusual, regular acco - 19 | ROMANIA | 3 | ular asymptotes are about the furious multipliers. express dependencies nag above the ironically ironic account | 19 | ROMANIA | 3 | ular asymptotes are about the furious multipliers. express dependencies nag above the ironically ironic account - 21 | VIETNAM | 2 | hely enticingly express accounts. even, final | 21 | VIETNAM | 2 | hely enticingly express accounts. even, final -(25 rows) - -select count(*) from EXT_NATION_ERROR1; -- should be 100 - count -------- - 100 -(1 row) - --- should fail on writable external table -CREATE WRITABLE EXTERNAL TABLE EXT_NATION_WRITABLE ( N_NATIONKEY INTEGER , - N_NAME CHAR(25) , - N_REGIONKEY INTEGER , - N_COMMENT VARCHAR(152)) -LOCATION ('gpfdist://@hostname@:7070/nation_error50.tbl') -FORMAT 'text' (delimiter '|') -LOG ERRORS INTO EXT_NATION_ERROR_WRITABLE SEGMENT REJECT LIMIT 5; -ERROR: Single row error handling may not be used with a writable external table --- start_ignore -select * from gpfdist_stop; - x -------------- - stopping... -(1 row) - -select * from gpfdist_status; - x ------------------------------------------------------- - Error: gpfdist is not running (reason: socket error) - Exit: 1 - -(3 rows) - --- end_ignore -drop external table gpfdist_status; -drop external table gpfdist_start; -drop external table gpfdist_stop; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/bacfb0f1/src/test/regress/sql/goh_create_type_composite.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/goh_create_type_composite.sql b/src/test/regress/sql/goh_create_type_composite.sql deleted file mode 100644 index 841145d..0000000 --- a/src/test/regress/sql/goh_create_type_composite.sql +++ /dev/null @@ -1,96 +0,0 @@ --- --- CREATE_TYPE --- - --- --- Note: widget_in/out were created in create_function_1, without any --- prior shell-type creation. These commands therefore complete a test --- of the "old style" approach of making the functions first. --- --- start_ignore -drop database hdfs; --- end_ignore -create database hdfs; -\c hdfs - --- Test stand-alone composite type -create type temp_type_1 as (a int, b int); -create type temp_type_2 as (a int, b int); -create table temp_table (id int, a temp_type_1, b temp_type_2) distributed randomly; - -insert into temp_table values (1, (1,2), (3,4)); -insert into temp_table values (2, (5,6), (7,8)); -insert into temp_table values (3, (9,10), (11,12)); - -\d temp_table -select * from temp_table order by 1; - -drop table temp_table; - -create type temp_type_3 as (a temp_type_1, b temp_type_2); -CREATE table temp_table (id int, a temp_type_1, b temp_type_3) distributed randomly; -insert into temp_table values (1, (9,10), ((11,12),(7,8))); -insert into temp_table values (2, (1,2), ((3,4),(5,6))); - -select * from temp_table order by 1; - --- check catalog entries for types -select count(typrelid) from pg_type where typname like 'temp_type_%'; - -comment on type temp_type_1 is 'test composite type'; -\dT temp_type_1 - -select count(reltype) from pg_class where relname like 'temp_type%'; - -create table test_func (foo temp_type_1); -insert into test_func values((1,2)); -insert into test_func values((3,4)); -insert into test_func values((5,6)); -insert into test_func values((7,8)); - --- Functions with UDTs -create function test_temp_func(temp_type_1, temp_type_2) RETURNS temp_type_1 AS ' - select foo from test_func where (foo).a = 3; -' LANGUAGE SQL; - -SELECT * FROM test_temp_func((7,8), (5,6)); -drop function test_temp_func(temp_type_1, temp_type_2); - --- UDT and UDA -create or replace function test_temp_func_2(temp_type_1, temp_type_1) RETURNS temp_type_1 AS ' - select ($1.a + $2.a, $1.b + $2.b)::temp_type_1; -' LANGUAGE SQL; - -CREATE AGGREGATE agg_comp_type (temp_type_1) ( - sfunc = test_temp_func_2, stype = temp_type_1, - initcond = '(0,0)' -); -select agg_comp_type(foo) from test_func; - --- Check alter schema -create schema type_test; -alter type temp_type_1 set schema type_test; - -\dT temp_type_1 -\dT type_test.temp_type_1 -\d test_func - -select foo from test_func where (foo).a = 3; - --- type name with truncation -create type abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopqrstuvwxyz1234567890 as (a int, b int); - -create table huge_type_table (a abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopqrstuvwxyz1234567890); -insert into huge_type_table values ((1,2)); -insert into huge_type_table values ((3,4)); - -select * from huge_type_table; -\d huge_type_table; - -drop table huge_type_table; -drop type abcdefghijklmnopqrstuvwxyz1234567890abcdefghijklmnopqrstuvwxyz1234567890; - --- composite type array tests ..negative test -create table type_array_table (col_one type_test.temp_type_1[]); - -\c regression http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/bacfb0f1/src/test/regress/sql/goh_database.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/goh_database.sql b/src/test/regress/sql/goh_database.sql deleted file mode 100644 index 239ba99..0000000 --- a/src/test/regress/sql/goh_database.sql +++ /dev/null @@ -1,41 +0,0 @@ -CREATE DATABASE goh_database; -DROP DATABASE goh_database; - --- should be a clean databse -CREATE DATABASE goh_database1; -\c goh_database1 -CREATE TABLE x(c int); -INSERT INTO x VALUES(generate_series(1, 10)); -SELECT * FROM x ORDER BY c; -DROP TABLE x; -\c regression - --- table should be removed -CREATE DATABASE goh_database2; -\c goh_database2 -CREATE TABLE x(c int); -INSERT INTO x VALUES(generate_series(1, 10)); -SELECT * FROM x ORDER BY c; -\c regression - -CREATE TABLESPACE goh_regression_tablespace1 FILESPACE dfs_system; -CREATE DATABASE goh_database3 TABLESPACE goh_regression_tablespace1; -\c goh_database3 -CREATE TABLE x(c int); -INSERT INTO x VALUES(generate_series(1, 10)); -SELECT * FROM x ORDER BY c; -\d x -\c regression - -BEGIN; -CREATE TABLESPACE goh_regression_tablespace2 FILESPACE dfs_system; -CREATE TABLE x(c int) TABLESPACE goh_regression_tablespace2; -INSERT INTO x VALUES(generate_series(1, 10)); -SELECT * FROM x ORDER BY c; -\d x -ROLLBACK; - -DROP DATABASE goh_database1; -DROP DATABASE goh_database2; -DROP DATABASE goh_database3; -DROP TABLESPACE goh_regression_tablespace1; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/bacfb0f1/src/test/regress/sql/goh_gp_dist_random.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/goh_gp_dist_random.sql b/src/test/regress/sql/goh_gp_dist_random.sql deleted file mode 100644 index d476520..0000000 --- a/src/test/regress/sql/goh_gp_dist_random.sql +++ /dev/null @@ -1,6 +0,0 @@ -select count(*) > 0 as c from gp_dist_random('pg_class'); -select relname from gp_dist_random('pg_class') c - inner join gp_dist_random('pg_namespace') n - on c.gp_segment_id = n.gp_segment_id - and c.relnamespace = n.oid - where n.nspname = 'nonexistent'; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/bacfb0f1/src/test/regress/sql/goh_toast.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/goh_toast.sql b/src/test/regress/sql/goh_toast.sql deleted file mode 100644 index 45d9305..0000000 --- a/src/test/regress/sql/goh_toast.sql +++ /dev/null @@ -1,32 +0,0 @@ -CREATE TABLE toastable_ao(a text, b varchar, c int) with(appendonly=true, compresslevel=1) distributed randomly; - --- INSERT --- uses the toast call to store the large tuples -INSERT INTO toastable_ao VALUES(repeat('a',100000), repeat('b',100001), 1); -INSERT INTO toastable_ao VALUES(repeat('A',100000), repeat('B',100001), 2); - --- Check that tuples were toasted and are detoasted correctly. we use --- char_length() because it guarantees a detoast without showing tho whole result -SELECT char_length(a), char_length(b), c FROM toastable_ao ORDER BY c; - --- ALTER --- this will cause a full table rewrite. we make sure the tosted values and references --- stay intact after all the oid switching business going on. --- ALTER TABLE toastable_ao ADD COLUMN d int DEFAULT 10; - --- SELECT char_length(a), char_length(b), c, d FROM toastable_ao ORDER BY c; -SELECT char_length(a), char_length(b), c FROM toastable_ao ORDER BY c; - --- TRUNCATE --- remove reference to toast table and create a new one with different values -TRUNCATE toastable_ao; - -INSERT INTO toastable_ao VALUES(repeat('a',100002), repeat('b',100003), 2); - -SELECT char_length(a), char_length(b), c FROM toastable_ao; - --- TODO: figure out a way to verify that toasted data is removed after the truncate. - -DROP TABLE toastable_ao; - --- TODO: figure out a way to verify that the toast tables are dropped http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/bacfb0f1/src/test/regress/sql/information_schema.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/information_schema.sql b/src/test/regress/sql/information_schema.sql deleted file mode 100644 index ef2db03..0000000 --- a/src/test/regress/sql/information_schema.sql +++ /dev/null @@ -1,57 +0,0 @@ -drop table if exists r; -create table r(a int, b int); - -SELECT attnum::information_schema.cardinal_number -from pg_attribute -where attnum > 0 and attrelid = 'r'::regclass; - --- this one should fail -SELECT attnum::information_schema.cardinal_number -from pg_attribute -where attrelid = 'r'::regclass; - - - -SELECT * -from (SELECT attnum::information_schema.cardinal_number - from pg_attribute - where attnum > 0 and attrelid = 'r'::regclass) q -where attnum=2; - -select table_schema, table_name,column_name,ordinal_position -from information_schema.columns -where table_name ='r'; - - -select table_schema, table_name,column_name,ordinal_position -from information_schema.columns -where table_name ='r' -and ordinal_position =1; - -select table_schema, table_name,column_name,ordinal_position -from information_schema.columns -where ordinal_position = 20; - --- MPP-25724 -select a.column_name -from information_schema.columns a -where a.table_name -in -(select b.table_name from information_schema.tables b where - a.column_name like 'b') and a.table_name = 'r'; - -select c.relname -from pg_class c -where c.relname -in -(select b.table_name from information_schema.tables b where - c.relname like 'r'); - -select a.table_name -from information_schema.tables a -where a.table_name -in -(select b.relname from pg_class b where - a.table_name like 'r'); - -drop table r; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/bacfb0f1/src/test/regress/sql/rowtypes.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/rowtypes.sql b/src/test/regress/sql/rowtypes.sql deleted file mode 100644 index 66ad780..0000000 --- a/src/test/regress/sql/rowtypes.sql +++ /dev/null @@ -1,111 +0,0 @@ --- --- ROWTYPES --- - --- Make both a standalone composite type and a table rowtype - -create type complex as (r float8, i float8); - -create temp table fullname (first text, last text); - --- Nested composite - -create type quad as (c1 complex, c2 complex); - --- Some simple tests of I/O conversions and row construction - -select (1.1,2.2)::complex, row((3.3,4.4),(5.5,null))::quad; - -select row('Joe', 'Blow')::fullname, '(Joe,Blow)'::fullname; - -select '(Joe,von Blow)'::fullname, '(Joe,d''Blow)'::fullname; - -select '(Joe,"von""Blow")'::fullname, E'(Joe,d\\\\Blow)'::fullname; - -select '(Joe,"Blow,Jr")'::fullname; - -select '(Joe,)'::fullname; -- ok, null 2nd column -select '(Joe)'::fullname; -- bad -select '(Joe,,)'::fullname; -- bad - -create temp table quadtable(f1 int, q quad); - -insert into quadtable values (1, ((3.3,4.4),(5.5,6.6))); -insert into quadtable values (2, ((null,4.4),(5.5,6.6))); - -select * from quadtable; - -begin; -set local add_missing_from = false; -select f1, q.c1 from quadtable; -- fails, q is a table reference -rollback; - -select f1, (q).c1, (qq.q).c1.i from quadtable qq; - -create temp table people (fn fullname, bd date); - -insert into people values ('(Joe,Blow)', '1984-01-10'); - -select * from people; - --- at the moment this will not work due to ALTER TABLE inadequacy: -alter table fullname add column suffix text default ''; - --- Not supported in HAWQ -alter table fullname add column suffix text default null; - -select * from people; - --- test insertion/updating of subfields, not supported in HAWQ -update people set fn.suffix = 'Jr'; - -select * from people; - -insert into quadtable (f1, q.c1.r, q.c2.i) values(44,55,66); - -select * from quadtable; - --- The object here is to ensure that toasted references inside --- composite values don't cause problems. The large f1 value will --- be toasted inside pp, it must still work after being copied to people. - -create temp table pp (f1 text); -insert into pp values (repeat('abcdefghijkl', 100000)); --- HAWQ does not support alter, so remove third column null -insert into people select ('Jim', f1)::fullname, current_date from pp; - -select (fn).first, substr((fn).last, 1, 20), length((fn).last) from people; - --- Test row comparison semantics. Prior to PG 8.2 we did this in a totally --- non-spec-compliant way. - -select ROW(1,2) < ROW(1,3) as true; -select ROW(1,2) < ROW(1,1) as false; -select ROW(1,2) < ROW(1,NULL) as null; -select ROW(1,2,3) < ROW(1,3,NULL) as true; -- the NULL is not examined -select ROW(11,'ABC') < ROW(11,'DEF') as true; -select ROW(11,'ABC') > ROW(11,'DEF') as false; -select ROW(12,'ABC') > ROW(11,'DEF') as true; - --- = and <> have different NULL-behavior than < etc -select ROW(1,2,3) < ROW(1,NULL,4) as null; -select ROW(1,2,3) = ROW(1,NULL,4) as false; -select ROW(1,2,3) <> ROW(1,NULL,4) as true; - --- We allow operators beyond the six standard ones, if they have btree --- operator classes. -select ROW('ABC','DEF') ~<=~ ROW('DEF','ABC') as true; -select ROW('ABC','DEF') ~>=~ ROW('DEF','ABC') as false; -select ROW('ABC','DEF') ~~ ROW('DEF','ABC') as fail; - --- Check row comparison with a subselect -select unique1, unique2 from tenk1 -where (unique1, unique2) < any (select ten, ten from tenk1 where hundred < 3); - --- Also check row comparison with an indexable condition -select thousand, tenthous from tenk1 -where (thousand, tenthous) >= (997, 5000) -order by thousand, tenthous; - --- empty row constructor is valid -select ROW(); http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/bacfb0f1/src/test/regress/sql/temp.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/temp.sql b/src/test/regress/sql/temp.sql deleted file mode 100644 index 685b062..0000000 --- a/src/test/regress/sql/temp.sql +++ /dev/null @@ -1,163 +0,0 @@ --- --- TEMP --- Test temp relations and indexes --- - --- test temp table/index masking - -CREATE TABLE temptest(col int); - --- CREATE INDEX i_temptest ON temptest(col); - -CREATE TEMP TABLE temptest(tcol int); - --- CREATE INDEX i_temptest ON temptest(tcol); - -SELECT * FROM temptest; - --- DROP INDEX i_temptest; - -DROP TABLE temptest; - -SELECT * FROM temptest; - --- DROP INDEX i_temptest; - -DROP TABLE temptest; - --- test temp table selects - -CREATE TABLE temptest(col int); - -INSERT INTO temptest VALUES (1); - -CREATE TEMP TABLE temptest(tcol float); - -INSERT INTO temptest VALUES (2.1); - -SELECT * FROM temptest; - -DROP TABLE temptest; - -SELECT * FROM temptest; - -DROP TABLE temptest; - --- test temp table deletion - -CREATE TEMP TABLE temptest(col int); - -\c regression - -SELECT * FROM temptest; - --- Test ON COMMIT DELETE ROWS - -CREATE TEMP TABLE temptest(col int) ON COMMIT DELETE ROWS; - -BEGIN; -INSERT INTO temptest VALUES (1); -INSERT INTO temptest VALUES (2); - -SELECT * FROM temptest; -COMMIT; - -SELECT * FROM temptest; - -DROP TABLE temptest; - -BEGIN; -CREATE TEMP TABLE temptest(col) ON COMMIT DELETE ROWS AS SELECT 1; - -SELECT * FROM temptest; -COMMIT; - -SELECT * FROM temptest; - -DROP TABLE temptest; - --- Test ON COMMIT DROP - -BEGIN; - -CREATE TEMP TABLE temptest(col int) ON COMMIT DROP; - -INSERT INTO temptest VALUES (1); -INSERT INTO temptest VALUES (2); - -SELECT * FROM temptest; -COMMIT; - -SELECT * FROM temptest; - -BEGIN; -CREATE TEMP TABLE temptest(col) ON COMMIT DROP AS SELECT 1; - -SELECT * FROM temptest; -COMMIT; - -SELECT * FROM temptest; - --- ON COMMIT is only allowed for TEMP - -CREATE TABLE temptest(col int) ON COMMIT DELETE ROWS; -CREATE TABLE temptest(col) ON COMMIT DELETE ROWS AS SELECT 1; - --- Test foreign keys --- BEGIN; --- CREATE TEMP TABLE temptest1(col int PRIMARY KEY) DISTRIBUTED BY (col); --- CREATE TEMP TABLE temptest2(col int REFERENCES temptest1) --- ON COMMIT DELETE ROWS; --- INSERT INTO temptest1 VALUES (1); --- INSERT INTO temptest2 VALUES (1); --- COMMIT; --- SELECT * FROM temptest1; --- SELECT * FROM temptest2; - --- BEGIN; --- CREATE TEMP TABLE temptest3(col int PRIMARY KEY) ON COMMIT DELETE ROWS DISTRIBUTED BY (col); --- CREATE TEMP TABLE temptest4(col int REFERENCES temptest3); --- COMMIT; - --- Test manipulation of temp schema's placement in search path - -create table public.whereami (f1 text); -insert into public.whereami values ('public'); - -create temp table whereami (f1 text); -insert into whereami values ('temp'); - --- create function public.whoami() returns text --- as $$select 'public'::text$$ language sql; - --- create function pg_temp.whoami() returns text --- as $$select 'temp'::text$$ language sql; - --- default should have pg_temp implicitly first, but only for tables -select * from whereami; --- select whoami(); - --- can list temp first explicitly, but it still doesn't affect functions -set search_path = pg_temp, public; -select * from whereami; --- select whoami(); - --- or put it last for security -set search_path = public, pg_temp; -select * from whereami; --- select whoami(); - --- you can invoke a temp function explicitly, though --- select pg_temp.whoami(); - -drop table public.whereami; - --- Test querying column using pg_temp schema -create table pg_temp.test (row integer, count integer); -insert into pg_temp.test values (1, 10), (2, 20), (3, 30); -select avg(pg_temp.test.count) from pg_temp.test; -select avg(test.count) from pg_temp.test; -select avg(count) from pg_temp.test; - -select case when pg_temp.test.count = 30 then 30 when pg_temp.test.count = 20 then 20 else 10 end from pg_temp.test; - http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/bacfb0f1/src/test/regress/sql/transactions.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/transactions.sql b/src/test/regress/sql/transactions.sql deleted file mode 100644 index 9fa0d4b..0000000 --- a/src/test/regress/sql/transactions.sql +++ /dev/null @@ -1,335 +0,0 @@ --- --- TRANSACTIONS --- - -BEGIN; - -SELECT * - INTO TABLE xacttest - FROM aggtest; - -INSERT INTO xacttest (a, b) VALUES (777, 777.777); - -END; - --- should retrieve one value-- -SELECT a FROM xacttest WHERE a > 100; - - -BEGIN; - -CREATE TABLE disappear (a int4); - -TRUNCATE aggtest; - --- should be empty -SELECT * FROM aggtest; - -ABORT; - --- should not exist -SELECT oid FROM pg_class WHERE relname = 'disappear'; - --- should have members again -SELECT * FROM aggtest; - -drop table aggtest; - - --- Read-only tests - -CREATE TABLE writetest (a int); -CREATE TEMPORARY TABLE temptest (a int); - -SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY; - -DROP TABLE writetest; -- fail -INSERT INTO writetest VALUES (1); -- fail -SELECT * FROM writetest; -- ok --- DELETE FROM temptest; -- ok --- UPDATE temptest SET a = 0 FROM writetest WHERE temptest.a = 1 AND writetest.a = temptest.a; -- ok --- PREPARE test AS INSERT INTO writetest VALUES (1); -- ok --- EXECUTE test; -- fail -SELECT * FROM writetest, temptest; -- ok -CREATE TABLE test AS SELECT * FROM writetest; -- fail - -START TRANSACTION READ WRITE; -DROP TABLE writetest; -- ok -COMMIT; - --- Subtransactions, basic tests --- create & drop tables -SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE; -CREATE TABLE foobar (a int); -BEGIN; - CREATE TABLE foo (a int); - SAVEPOINT one; - DROP TABLE foo; - CREATE TABLE bar (a int); - ROLLBACK TO SAVEPOINT one; - RELEASE SAVEPOINT one; - SAVEPOINT two; - CREATE TABLE baz (a int); - RELEASE SAVEPOINT two; - drop TABLE foobar; - CREATE TABLE barbaz (a int); -COMMIT; --- should exist: barbaz, baz, foo -SELECT * FROM foo; -- should be empty -SELECT * FROM bar; -- shouldn't exist -SELECT * FROM barbaz; -- should be empty -SELECT * FROM baz; -- should be empty - --- inserts -BEGIN; - INSERT INTO foo VALUES (1); - SAVEPOINT one; - INSERT into bar VALUES (1); - ROLLBACK TO one; - RELEASE SAVEPOINT one; - SAVEPOINT two; - INSERT into barbaz VALUES (1); - RELEASE two; - SAVEPOINT three; - SAVEPOINT four; - INSERT INTO foo VALUES (2); - RELEASE SAVEPOINT four; - ROLLBACK TO SAVEPOINT three; - RELEASE SAVEPOINT three; - INSERT INTO foo VALUES (3); -COMMIT; -SELECT * FROM foo; -- should have 1 and 3 -SELECT * FROM barbaz; -- should have 1 - --- test whole-tree commit -BEGIN; - SAVEPOINT one; - SELECT foo; - ROLLBACK TO SAVEPOINT one; - RELEASE SAVEPOINT one; - SAVEPOINT two; - CREATE TABLE savepoints (a int); - SAVEPOINT three; - INSERT INTO savepoints VALUES (1); - SAVEPOINT four; - INSERT INTO savepoints VALUES (2); - SAVEPOINT five; - INSERT INTO savepoints VALUES (3); - ROLLBACK TO SAVEPOINT five; -COMMIT; -COMMIT; -- should not be in a transaction block -SELECT * FROM savepoints; - --- test whole-tree rollback -BEGIN; - SAVEPOINT one; - INSERT INTO savepoints VALUES (23); - RELEASE SAVEPOINT one; - SAVEPOINT two; - INSERT INTO savepoints VALUES (24); - SAVEPOINT three; - INSERT INTO savepoints VALUES (25); -ROLLBACK; -COMMIT; -- should not be in a transaction block - -SELECT * FROM savepoints ORDER BY 1; - --- test whole-tree commit on an aborted subtransaction -BEGIN; - INSERT INTO savepoints VALUES (4); - SAVEPOINT one; - INSERT INTO savepoints VALUES (5); - SELECT foo; -COMMIT; -SELECT * FROM savepoints ORDER BY 1; - -BEGIN; - INSERT INTO savepoints VALUES (6); - SAVEPOINT one; - INSERT INTO savepoints VALUES (7); - RELEASE SAVEPOINT one; - INSERT INTO savepoints VALUES (8); -COMMIT; --- rows 6 and 8 should have been created by the same xact --- SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=6 AND b.a=8; --- rows 6 and 7 should have been created by different xacts --- SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=6 AND b.a=7; - -BEGIN; - INSERT INTO savepoints VALUES (9); - SAVEPOINT one; - INSERT INTO savepoints VALUES (10); - ROLLBACK TO SAVEPOINT one; - INSERT INTO savepoints VALUES (11); -COMMIT; -SELECT a FROM savepoints WHERE a in (9, 10, 11) ORDER BY 1; --- rows 9 and 11 should have been created by different xacts --- SELECT a.xmin = b.xmin FROM savepoints a, savepoints b WHERE a.a=9 AND b.a=11; - -BEGIN; - INSERT INTO savepoints VALUES (12); - SAVEPOINT one; - INSERT INTO savepoints VALUES (13); - SAVEPOINT two; - INSERT INTO savepoints VALUES (14); - ROLLBACK TO SAVEPOINT one; - INSERT INTO savepoints VALUES (15); - SAVEPOINT two; - INSERT INTO savepoints VALUES (16); - SAVEPOINT three; - INSERT INTO savepoints VALUES (17); -COMMIT; -SELECT a FROM savepoints WHERE a BETWEEN 12 AND 17 ORDER BY 1; - -BEGIN; - INSERT INTO savepoints VALUES (18); - SAVEPOINT one; - INSERT INTO savepoints VALUES (19); - SAVEPOINT two; - INSERT INTO savepoints VALUES (20); - ROLLBACK TO SAVEPOINT one; - INSERT INTO savepoints VALUES (21); - ROLLBACK TO SAVEPOINT one; - INSERT INTO savepoints VALUES (22); -COMMIT; -SELECT a FROM savepoints WHERE a BETWEEN 18 AND 22 ORDER BY 1; - -DROP TABLE savepoints; - --- only in a transaction block: -SAVEPOINT one; -ROLLBACK TO SAVEPOINT one; -RELEASE SAVEPOINT one; - --- Only "rollback to" allowed in aborted state -BEGIN; - SAVEPOINT one; - SELECT 0/0; - SAVEPOINT two; -- ignored till the end of ... - RELEASE SAVEPOINT one; -- ignored till the end of ... - ROLLBACK TO SAVEPOINT one; - SELECT 1; -COMMIT; -SELECT 1; -- this should work - --- check non-transactional behavior of cursors --- BEGIN; --- DECLARE c CURSOR FOR SELECT unique2 FROM tenk1 ORDER BY 1; --- SAVEPOINT one; --- FETCH 10 FROM c; --- ROLLBACK TO SAVEPOINT one; --- FETCH 10 FROM c; --- RELEASE SAVEPOINT one; --- FETCH 10 FROM c; --- CLOSE c; --- DECLARE c CURSOR FOR SELECT unique2/0 FROM tenk1 ORDER BY 1; --- SAVEPOINT two; --- FETCH 10 FROM c; --- ROLLBACK TO SAVEPOINT two; --- -- c is now dead to the world ... --- FETCH 10 FROM c; --- ROLLBACK TO SAVEPOINT two; --- RELEASE SAVEPOINT two; --- FETCH 10 FROM c; --- COMMIT; - --- --- Check that "stable" functions are really stable. They should not be --- able to see the partial results of the calling query. (Ideally we would --- also check that they don't see commits of concurrent transactions, but --- that's a mite hard to do within the limitations of pg_regress.) --- -select * from xacttest; - --- create or replace function max_xacttest() returns smallint language sql as --- 'select max(a) from xacttest' stable; - --- begin; --- update xacttest set a = max_xacttest() + 10 where a > 0; --- select * from xacttest; --- rollback; - --- But a volatile function can see the partial results of the calling query --- create or replace function max_xacttest() returns smallint language sql as --- 'select max(a) from xacttest' volatile; - --- begin; --- update xacttest set a = max_xacttest() + 10 where a > 0; --- select * from xacttest; --- rollback; - --- Now the same test with plpgsql (since it depends on SPI which is different) --- create or replace function max_xacttest() returns smallint language plpgsql as --- 'begin return max(a) from xacttest; end' stable; - --- begin; --- update xacttest set a = max_xacttest() + 10 where a > 0; --- select * from xacttest; --- rollback; - --- create or replace function max_xacttest() returns smallint language plpgsql as --- 'begin return max(a) from xacttest; end' volatile; - --- begin; --- update xacttest set a = max_xacttest() + 10 where a > 0; --- select * from xacttest; --- rollback; - - --- test case for problems with dropping an open relation during abort --- BEGIN; --- savepoint x; --- CREATE TABLE koju (a INT UNIQUE); --- INSERT INTO koju VALUES (1); --- INSERT INTO koju VALUES (1); --- rollback to x; - --- CREATE TABLE koju (a INT UNIQUE); --- INSERT INTO koju VALUES (1); --- INSERT INTO koju VALUES (1); --- ROLLBACK; - -DROP TABLE foo; -DROP TABLE baz; -DROP TABLE barbaz; - --- verify that cursors created during an aborted subtransaction are --- closed, but that we do not rollback the effect of any FETCHs --- performed in the aborted subtransaction --- begin; - --- savepoint x; --- create table abc (a int); --- insert into abc values (5); --- insert into abc values (10); --- declare foo cursor for select * from abc; --- fetch from foo; --- rollback to x; - --- should fail --- fetch from foo; --- commit; - --- begin; - --- create table abc (a int); --- insert into abc values (5); --- insert into abc values (10); --- insert into abc values (15); --- declare foo cursor for select * from abc; - --- fetch from foo; - --- savepoint x; --- fetch from foo; --- rollback to x; - --- fetch from foo; - --- abort; - --- tests for the "tid" type -SELECT '(3, 3)'::tid = '(3, 4)'::tid; -SELECT '(3, 3)'::tid = '(3, 3)'::tid; -SELECT '(3, 3)'::tid <> '(3, 3)'::tid; -SELECT '(3, 3)'::tid <> '(3, 4)'::tid;
