From 74ce5c93c7b5cbe416e46590a6930e42fb57b351 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sun, 11 Aug 2002 02:06:32 +0000 Subject: Make cluster regress test functional. --- src/test/regress/expected/cluster.out | 273 ++++++++++++++++++++++++++++++++++ src/test/regress/output/cluster.out | 232 ----------------------------- src/test/regress/parallel_schedule | 7 +- src/test/regress/serial_schedule | 3 +- src/test/regress/sql/cluster.sql | 12 +- 5 files changed, 287 insertions(+), 240 deletions(-) create mode 100644 src/test/regress/expected/cluster.out delete mode 100644 src/test/regress/output/cluster.out (limited to 'src') diff --git a/src/test/regress/expected/cluster.out b/src/test/regress/expected/cluster.out new file mode 100644 index 00000000000..12ae4a2c2fa --- /dev/null +++ b/src/test/regress/expected/cluster.out @@ -0,0 +1,273 @@ +-- +-- CLUSTER +-- +CREATE TABLE clstr_tst_s (rf_a SERIAL PRIMARY KEY, + b INT); +NOTICE: CREATE TABLE will create implicit sequence 'clstr_tst_s_rf_a_seq' for SERIAL column 'clstr_tst_s.rf_a' +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'clstr_tst_s_pkey' for table 'clstr_tst_s' +CREATE TABLE clstr_tst (a SERIAL PRIMARY KEY, + b INT, + c TEXT, + CONSTRAINT clstr_tst_con FOREIGN KEY (b) REFERENCES clstr_tst_s); +NOTICE: CREATE TABLE will create implicit sequence 'clstr_tst_a_seq' for SERIAL column 'clstr_tst.a' +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'clstr_tst_pkey' for table 'clstr_tst' +NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) +CREATE INDEX clstr_tst_b ON clstr_tst (b); +CREATE INDEX clstr_tst_c ON clstr_tst (c); +CREATE INDEX clstr_tst_c_b ON clstr_tst (c,b); +CREATE INDEX clstr_tst_b_c ON clstr_tst (b,c); +INSERT INTO clstr_tst_s (b) VALUES (0); +INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s; +INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s; +INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s; +INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s; +INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s; +CREATE TABLE clstr_tst_inh () INHERITS (clstr_tst); +INSERT INTO clstr_tst (b, c) VALUES (11, 'once'); +INSERT INTO clstr_tst (b, c) VALUES (10, 'diez'); +INSERT INTO clstr_tst (b, c) VALUES (31, 'treinta y uno'); +INSERT INTO clstr_tst (b, c) VALUES (22, 'veintidos'); +INSERT INTO clstr_tst (b, c) VALUES (3, 'tres'); +INSERT INTO clstr_tst (b, c) VALUES (20, 'veinte'); +INSERT INTO clstr_tst (b, c) VALUES (23, 'veintitres'); +INSERT INTO clstr_tst (b, c) VALUES (21, 'veintiuno'); +INSERT INTO clstr_tst (b, c) VALUES (4, 'cuatro'); +INSERT INTO clstr_tst (b, c) VALUES (14, 'catorce'); +INSERT INTO clstr_tst (b, c) VALUES (2, 'dos'); +INSERT INTO clstr_tst (b, c) VALUES (18, 'dieciocho'); +INSERT INTO clstr_tst (b, c) VALUES (27, 'veintisiete'); +INSERT INTO clstr_tst (b, c) VALUES (25, 'veinticinco'); +INSERT INTO clstr_tst (b, c) VALUES (13, 'trece'); +INSERT INTO clstr_tst (b, c) VALUES (28, 'veintiocho'); +INSERT INTO clstr_tst (b, c) VALUES (32, 'treinta y dos'); +INSERT INTO clstr_tst (b, c) VALUES (5, 'cinco'); +INSERT INTO clstr_tst (b, c) VALUES (29, 'veintinueve'); +INSERT INTO clstr_tst (b, c) VALUES (1, 'uno'); +INSERT INTO clstr_tst (b, c) VALUES (24, 'veinticuatro'); +INSERT INTO clstr_tst (b, c) VALUES (30, 'treinta'); +INSERT INTO clstr_tst (b, c) VALUES (12, 'doce'); +INSERT INTO clstr_tst (b, c) VALUES (17, 'diecisiete'); +INSERT INTO clstr_tst (b, c) VALUES (9, 'nueve'); +INSERT INTO clstr_tst (b, c) VALUES (19, 'diecinueve'); +INSERT INTO clstr_tst (b, c) VALUES (26, 'veintiseis'); +INSERT INTO clstr_tst (b, c) VALUES (15, 'quince'); +INSERT INTO clstr_tst (b, c) VALUES (7, 'siete'); +INSERT INTO clstr_tst (b, c) VALUES (16, 'dieciseis'); +INSERT INTO clstr_tst (b, c) VALUES (8, 'ocho'); +INSERT INTO clstr_tst (b, c) VALUES (6, 'seis'); +CLUSTER clstr_tst_c ON clstr_tst; +SELECT * from clstr_tst; + a | b | c +----+----+--------------- + 10 | 14 | catorce + 18 | 5 | cinco + 9 | 4 | cuatro + 26 | 19 | diecinueve + 12 | 18 | dieciocho + 30 | 16 | dieciseis + 24 | 17 | diecisiete + 2 | 10 | diez + 23 | 12 | doce + 11 | 2 | dos + 25 | 9 | nueve + 31 | 8 | ocho + 1 | 11 | once + 28 | 15 | quince + 32 | 6 | seis + 29 | 7 | siete + 15 | 13 | trece + 22 | 30 | treinta + 17 | 32 | treinta y dos + 3 | 31 | treinta y uno + 5 | 3 | tres + 20 | 1 | uno + 6 | 20 | veinte + 14 | 25 | veinticinco + 21 | 24 | veinticuatro + 4 | 22 | veintidos + 19 | 29 | veintinueve + 16 | 28 | veintiocho + 27 | 26 | veintiseis + 13 | 27 | veintisiete + 7 | 23 | veintitres + 8 | 21 | veintiuno +(32 rows) + +SELECT * from clstr_tst ORDER BY a; + a | b | c +----+----+--------------- + 1 | 11 | once + 2 | 10 | diez + 3 | 31 | treinta y uno + 4 | 22 | veintidos + 5 | 3 | tres + 6 | 20 | veinte + 7 | 23 | veintitres + 8 | 21 | veintiuno + 9 | 4 | cuatro + 10 | 14 | catorce + 11 | 2 | dos + 12 | 18 | dieciocho + 13 | 27 | veintisiete + 14 | 25 | veinticinco + 15 | 13 | trece + 16 | 28 | veintiocho + 17 | 32 | treinta y dos + 18 | 5 | cinco + 19 | 29 | veintinueve + 20 | 1 | uno + 21 | 24 | veinticuatro + 22 | 30 | treinta + 23 | 12 | doce + 24 | 17 | diecisiete + 25 | 9 | nueve + 26 | 19 | diecinueve + 27 | 26 | veintiseis + 28 | 15 | quince + 29 | 7 | siete + 30 | 16 | dieciseis + 31 | 8 | ocho + 32 | 6 | seis +(32 rows) + +SELECT * from clstr_tst ORDER BY b; + a | b | c +----+----+--------------- + 20 | 1 | uno + 11 | 2 | dos + 5 | 3 | tres + 9 | 4 | cuatro + 18 | 5 | cinco + 32 | 6 | seis + 29 | 7 | siete + 31 | 8 | ocho + 25 | 9 | nueve + 2 | 10 | diez + 1 | 11 | once + 23 | 12 | doce + 15 | 13 | trece + 10 | 14 | catorce + 28 | 15 | quince + 30 | 16 | dieciseis + 24 | 17 | diecisiete + 12 | 18 | dieciocho + 26 | 19 | diecinueve + 6 | 20 | veinte + 8 | 21 | veintiuno + 4 | 22 | veintidos + 7 | 23 | veintitres + 21 | 24 | veinticuatro + 14 | 25 | veinticinco + 27 | 26 | veintiseis + 13 | 27 | veintisiete + 16 | 28 | veintiocho + 19 | 29 | veintinueve + 22 | 30 | treinta + 3 | 31 | treinta y uno + 17 | 32 | treinta y dos +(32 rows) + +SELECT * from clstr_tst ORDER BY c; + a | b | c +----+----+--------------- + 10 | 14 | catorce + 18 | 5 | cinco + 9 | 4 | cuatro + 26 | 19 | diecinueve + 12 | 18 | dieciocho + 30 | 16 | dieciseis + 24 | 17 | diecisiete + 2 | 10 | diez + 23 | 12 | doce + 11 | 2 | dos + 25 | 9 | nueve + 31 | 8 | ocho + 1 | 11 | once + 28 | 15 | quince + 32 | 6 | seis + 29 | 7 | siete + 15 | 13 | trece + 22 | 30 | treinta + 17 | 32 | treinta y dos + 3 | 31 | treinta y uno + 5 | 3 | tres + 20 | 1 | uno + 6 | 20 | veinte + 14 | 25 | veinticinco + 21 | 24 | veinticuatro + 4 | 22 | veintidos + 19 | 29 | veintinueve + 16 | 28 | veintiocho + 27 | 26 | veintiseis + 13 | 27 | veintisiete + 7 | 23 | veintitres + 8 | 21 | veintiuno +(32 rows) + +-- Verify that inheritance link still works +INSERT INTO clstr_tst_inh VALUES (0, 100, 'in child table'); +SELECT * from clstr_tst; + a | b | c +----+-----+---------------- + 10 | 14 | catorce + 18 | 5 | cinco + 9 | 4 | cuatro + 26 | 19 | diecinueve + 12 | 18 | dieciocho + 30 | 16 | dieciseis + 24 | 17 | diecisiete + 2 | 10 | diez + 23 | 12 | doce + 11 | 2 | dos + 25 | 9 | nueve + 31 | 8 | ocho + 1 | 11 | once + 28 | 15 | quince + 32 | 6 | seis + 29 | 7 | siete + 15 | 13 | trece + 22 | 30 | treinta + 17 | 32 | treinta y dos + 3 | 31 | treinta y uno + 5 | 3 | tres + 20 | 1 | uno + 6 | 20 | veinte + 14 | 25 | veinticinco + 21 | 24 | veinticuatro + 4 | 22 | veintidos + 19 | 29 | veintinueve + 16 | 28 | veintiocho + 27 | 26 | veintiseis + 13 | 27 | veintisiete + 7 | 23 | veintitres + 8 | 21 | veintiuno + 0 | 100 | in child table +(33 rows) + +-- Verify that foreign key link still works +INSERT INTO clstr_tst (b, c) VALUES (1111, 'this should fail'); +ERROR: clstr_tst_con referential integrity violation - key referenced from clstr_tst not found in clstr_tst_s +SELECT conname FROM pg_constraint WHERE conrelid=(SELECT oid FROM pg_class + WHERE relname='clstr_tst'); + conname +---------------- + clstr_tst_pkey + clstr_tst_con +(2 rows) + +SELECT relname FROM pg_class WHERE relname LIKE 'clstr_tst%' ORDER BY relname; + relname +---------------------- + clstr_tst + clstr_tst_a_seq + clstr_tst_b + clstr_tst_b_c + clstr_tst_c + clstr_tst_c_b + clstr_tst_inh + clstr_tst_pkey + clstr_tst_s + clstr_tst_s_pkey + clstr_tst_s_rf_a_seq +(11 rows) + diff --git a/src/test/regress/output/cluster.out b/src/test/regress/output/cluster.out deleted file mode 100644 index 67f1f8a8b7a..00000000000 --- a/src/test/regress/output/cluster.out +++ /dev/null @@ -1,232 +0,0 @@ --- --- CLUSTER --- -CREATE TABLE clstr_tst_s (rf_a SERIAL PRIMARY KEY, - b INT); -NOTICE: CREATE TABLE will create implicit sequence 'clstr_tst_s_rf_a_seq' for SERIAL column 'clstr_tst_s.rf_a' -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'clstr_tst_s_pkey' for table 'clstr_tst_s' -CREATE TABLE clstr_tst (a SERIAL PRIMARY KEY, - b INT, - c TEXT, - CONSTRAINT clstr_tst_con FOREIGN KEY (b) REFERENCES clstr_tst_s); -NOTICE: CREATE TABLE will create implicit sequence 'clstr_tst_a_seq' for SERIAL column 'clstr_tst.a' -NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index 'clstr_tst_pkey' for table 'clstr_tst' -NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) -CREATE INDEX clstr_tst_b ON clstr_tst (b); -CREATE INDEX clstr_tst_c ON clstr_tst (c); -CREATE INDEX clstr_tst_c_b ON clstr_tst (c,b); -CREATE INDEX clstr_tst_b_c ON clstr_tst (b,c); -INSERT INTO clstr_tst_s (b) VALUES (0); -INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s; -INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s; -INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s; -INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s; -INSERT INTO clstr_tst_s (b) SELECT b FROM clstr_tst_s; -CREATE TABLE clstr_tst_inh () INHERITS (clstr_tst); -INSERT INTO clstr_tst (b, c) VALUES (11, 'once'); -INSERT INTO clstr_tst (b, c) VALUES (10, 'diez'); -INSERT INTO clstr_tst (b, c) VALUES (31, 'treinta y uno'); -INSERT INTO clstr_tst (b, c) VALUES (22, 'veintidos'); -INSERT INTO clstr_tst (b, c) VALUES (3, 'tres'); -INSERT INTO clstr_tst (b, c) VALUES (20, 'veinte'); -INSERT INTO clstr_tst (b, c) VALUES (23, 'veintitres'); -INSERT INTO clstr_tst (b, c) VALUES (21, 'veintiuno'); -INSERT INTO clstr_tst (b, c) VALUES (4, 'cuatro'); -INSERT INTO clstr_tst (b, c) VALUES (14, 'catorce'); -INSERT INTO clstr_tst (b, c) VALUES (2, 'dos'); -INSERT INTO clstr_tst (b, c) VALUES (18, 'dieciocho'); -INSERT INTO clstr_tst (b, c) VALUES (27, 'veintisiete'); -INSERT INTO clstr_tst (b, c) VALUES (25, 'veinticinco'); -INSERT INTO clstr_tst (b, c) VALUES (13, 'trece'); -INSERT INTO clstr_tst (b, c) VALUES (28, 'veintiocho'); -INSERT INTO clstr_tst (b, c) VALUES (32, 'treinta y dos'); -INSERT INTO clstr_tst (b, c) VALUES (5, 'cinco'); -INSERT INTO clstr_tst (b, c) VALUES (29, 'veintinueve'); -INSERT INTO clstr_tst (b, c) VALUES (1, 'uno'); -INSERT INTO clstr_tst (b, c) VALUES (24, 'veinticuatro'); -INSERT INTO clstr_tst (b, c) VALUES (30, 'treinta'); -INSERT INTO clstr_tst (b, c) VALUES (12, 'doce'); -INSERT INTO clstr_tst (b, c) VALUES (17, 'diecisiete'); -INSERT INTO clstr_tst (b, c) VALUES (9, 'nueve'); -INSERT INTO clstr_tst (b, c) VALUES (19, 'diecinueve'); -INSERT INTO clstr_tst (b, c) VALUES (26, 'veintiseis'); -INSERT INTO clstr_tst (b, c) VALUES (15, 'quince'); -INSERT INTO clstr_tst (b, c) VALUES (7, 'siete'); -INSERT INTO clstr_tst (b, c) VALUES (16, 'dieciseis'); -INSERT INTO clstr_tst (b, c) VALUES (8, 'ocho'); -INSERT INTO clstr_tst (b, c) VALUES (6, 'seis'); -CLUSTER clstr_tst_c ON clstr_tst; -SELECT * from clstr_tst; - a | b | c -----+----+--------------- - 10 | 14 | catorce - 18 | 5 | cinco - 9 | 4 | cuatro - 26 | 19 | diecinueve - 12 | 18 | dieciocho - 30 | 16 | dieciseis - 24 | 17 | diecisiete - 2 | 10 | diez - 23 | 12 | doce - 11 | 2 | dos - 25 | 9 | nueve - 31 | 8 | ocho - 1 | 11 | once - 28 | 15 | quince - 32 | 6 | seis - 29 | 7 | siete - 15 | 13 | trece - 22 | 30 | treinta - 17 | 32 | treinta y dos - 3 | 31 | treinta y uno - 5 | 3 | tres - 20 | 1 | uno - 6 | 20 | veinte - 14 | 25 | veinticinco - 21 | 24 | veinticuatro - 4 | 22 | veintidos - 19 | 29 | veintinueve - 16 | 28 | veintiocho - 27 | 26 | veintiseis - 13 | 27 | veintisiete - 7 | 23 | veintitres - 8 | 21 | veintiuno -(32 rows) - -SELECT * from clstr_tst ORDER BY a; - a | b | c -----+----+--------------- - 1 | 11 | once - 2 | 10 | diez - 3 | 31 | treinta y uno - 4 | 22 | veintidos - 5 | 3 | tres - 6 | 20 | veinte - 7 | 23 | veintitres - 8 | 21 | veintiuno - 9 | 4 | cuatro - 10 | 14 | catorce - 11 | 2 | dos - 12 | 18 | dieciocho - 13 | 27 | veintisiete - 14 | 25 | veinticinco - 15 | 13 | trece - 16 | 28 | veintiocho - 17 | 32 | treinta y dos - 18 | 5 | cinco - 19 | 29 | veintinueve - 20 | 1 | uno - 21 | 24 | veinticuatro - 22 | 30 | treinta - 23 | 12 | doce - 24 | 17 | diecisiete - 25 | 9 | nueve - 26 | 19 | diecinueve - 27 | 26 | veintiseis - 28 | 15 | quince - 29 | 7 | siete - 30 | 16 | dieciseis - 31 | 8 | ocho - 32 | 6 | seis -(32 rows) - -SELECT * from clstr_tst ORDER BY b; - a | b | c -----+----+--------------- - 20 | 1 | uno - 11 | 2 | dos - 5 | 3 | tres - 9 | 4 | cuatro - 18 | 5 | cinco - 32 | 6 | seis - 29 | 7 | siete - 31 | 8 | ocho - 25 | 9 | nueve - 2 | 10 | diez - 1 | 11 | once - 23 | 12 | doce - 15 | 13 | trece - 10 | 14 | catorce - 28 | 15 | quince - 30 | 16 | dieciseis - 24 | 17 | diecisiete - 12 | 18 | dieciocho - 26 | 19 | diecinueve - 6 | 20 | veinte - 8 | 21 | veintiuno - 4 | 22 | veintidos - 7 | 23 | veintitres - 21 | 24 | veinticuatro - 14 | 25 | veinticinco - 27 | 26 | veintiseis - 13 | 27 | veintisiete - 16 | 28 | veintiocho - 19 | 29 | veintinueve - 22 | 30 | treinta - 3 | 31 | treinta y uno - 17 | 32 | treinta y dos -(32 rows) - -SELECT * from clstr_tst ORDER BY c; - a | b | c -----+----+--------------- - 10 | 14 | catorce - 18 | 5 | cinco - 9 | 4 | cuatro - 26 | 19 | diecinueve - 12 | 18 | dieciocho - 30 | 16 | dieciseis - 24 | 17 | diecisiete - 2 | 10 | diez - 23 | 12 | doce - 11 | 2 | dos - 25 | 9 | nueve - 31 | 8 | ocho - 1 | 11 | once - 28 | 15 | quince - 32 | 6 | seis - 29 | 7 | siete - 15 | 13 | trece - 22 | 30 | treinta - 17 | 32 | treinta y dos - 3 | 31 | treinta y uno - 5 | 3 | tres - 20 | 1 | uno - 6 | 20 | veinte - 14 | 25 | veinticinco - 21 | 24 | veinticuatro - 4 | 22 | veintidos - 19 | 29 | veintinueve - 16 | 28 | veintiocho - 27 | 26 | veintiseis - 13 | 27 | veintisiete - 7 | 23 | veintitres - 8 | 21 | veintiuno -(32 rows) - -SELECT conname FROM pg_constraint WHERE conrelid=(SELECT oid FROM pg_class - WHERE relname='clstr_tst'); - conname ----------------- - clstr_tst_pkey - clstr_tst_con -(2 rows) - -SELECT relname FROM pg_class WHERE relname LIKE 'clstr_tst%' ORDER BY relname; - relname ----------------------- - clstr_tst - clstr_tst_a_seq - clstr_tst_b - clstr_tst_b_c - clstr_tst_c - clstr_tst_c_b - clstr_tst_inh - clstr_tst_pkey - clstr_tst_s - clstr_tst_s_pkey - clstr_tst_s_rf_a_seq -(11 rows) - -DROP TABLE clstr_tst_inh; -DROP TABLE clstr_tst; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 513bdf8c430..0ac26307063 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -55,11 +55,11 @@ test: sanity_check # ---------- test: errors test: select +ignore: random # ---------- # The fourth group of parallel test # ---------- -ignore: random test: select_into select_distinct select_distinct_on select_implicit select_having subselect union case join aggregates transactions random portals arrays btree_index hash_index test: privileges @@ -68,11 +68,10 @@ test: misc # ---------- # The fifth group of parallel test # ---------- -test: select_views alter_table portals_p2 rules foreign_key +test: select_views alter_table portals_p2 rules foreign_key cluster # ---------- # The sixth group of parallel test # ---------- # "plpgsql" cannot run concurrently with "rules" -test: limit plpgsql temp domain rangefuncs copy2 conversion -test: without_oid +test: limit plpgsql temp domain rangefuncs copy2 conversion without_oid diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index 271c5123d70..d7a6deed2c9 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -1,4 +1,4 @@ -# $Header: /cvsroot/pgsql/src/test/regress/serial_schedule,v 1.14 2002/07/25 10:07:13 ishii Exp $ +# $Header: /cvsroot/pgsql/src/test/regress/serial_schedule,v 1.15 2002/08/11 02:06:32 tgl Exp $ # This should probably be in an order similar to parallel_schedule. test: boolean test: char @@ -79,6 +79,7 @@ test: alter_table test: portals_p2 test: rules test: foreign_key +test: cluster test: limit test: plpgsql test: copy2 diff --git a/src/test/regress/sql/cluster.sql b/src/test/regress/sql/cluster.sql index 6879c618f24..32041c75cc3 100644 --- a/src/test/regress/sql/cluster.sql +++ b/src/test/regress/sql/cluster.sql @@ -64,9 +64,15 @@ SELECT * from clstr_tst ORDER BY a; SELECT * from clstr_tst ORDER BY b; SELECT * from clstr_tst ORDER BY c; +-- Verify that inheritance link still works +INSERT INTO clstr_tst_inh VALUES (0, 100, 'in child table'); +SELECT * from clstr_tst; + +-- Verify that foreign key link still works +INSERT INTO clstr_tst (b, c) VALUES (1111, 'this should fail'); + SELECT conname FROM pg_constraint WHERE conrelid=(SELECT oid FROM pg_class WHERE relname='clstr_tst'); -SELECT relname FROM pg_class WHERE relname LIKE 'clstr_tst%' ORDER BY relname; -DROP TABLE clstr_tst_inh; -DROP TABLE clstr_tst; + +SELECT relname FROM pg_class WHERE relname LIKE 'clstr_tst%' ORDER BY relname; -- cgit v1.2.3