Repository: incubator-hawq Updated Branches: refs/heads/master 9b004b7cb -> cc7844cce
HAWQ-897. Add feature test for create table distribution with new test framework Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq/repo Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq/commit/cc7844cc Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/cc7844cc Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/cc7844cc Branch: refs/heads/master Commit: cc7844cced833ed43f2b655fc8386a934fac046e Parents: 9b004b7 Author: YI JIN <[email protected]> Authored: Thu Jul 21 14:43:13 2016 +1000 Committer: YI JIN <[email protected]> Committed: Thu Jul 21 14:43:13 2016 +1000 ---------------------------------------------------------------------- src/test/feature/catalog/test_create_table.cpp | 307 ++++++++++++++- .../expected/create_table_distribution.out | 382 ------------------- src/test/regress/known_good_schedule | 1 - .../regress/sql/create_table_distribution.sql | 227 ----------- 4 files changed, 287 insertions(+), 630 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/cc7844cc/src/test/feature/catalog/test_create_table.cpp ---------------------------------------------------------------------- diff --git a/src/test/feature/catalog/test_create_table.cpp b/src/test/feature/catalog/test_create_table.cpp index f72b2ec..fe12703 100644 --- a/src/test/feature/catalog/test_create_table.cpp +++ b/src/test/feature/catalog/test_create_table.cpp @@ -22,26 +22,26 @@ class TestCreateTable : public ::testing::Test { TEST_F(TestCreateTable, TestCreateTable1) { hawq::test::SQLUtility util; // prepare - util.execute("DROP TABLE IF EXISTS aggtest"); - util.execute("DROP TABLE IF EXISTS tenk1"); - util.execute("DROP TABLE IF EXISTS slow_emp4000"); - util.execute("DROP TABLE IF EXISTS person"); - util.execute("DROP TABLE IF EXISTS onek"); - util.execute("DROP TABLE IF EXISTS emp"); - util.execute("DROP TABLE IF EXISTS student"); - util.execute("DROP TABLE IF EXISTS stud_emp"); - util.execute("DROP TABLE IF EXISTS real_city"); - util.execute("DROP TABLE IF EXISTS road"); - util.execute("DROP TABLE IF EXISTS hash_i4_heap"); - util.execute("DROP TABLE IF EXISTS hash_name_heap"); - util.execute("DROP TABLE IF EXISTS hash_txt_heap"); - util.execute("DROP TABLE IF EXISTS hash_f8_heap"); - util.execute("DROP TABLE IF EXISTS bt_i4_heap"); - util.execute("DROP TABLE IF EXISTS bt_name_heap"); - util.execute("DROP TABLE IF EXISTS bt_txt_heap"); - util.execute("DROP TABLE IF EXISTS bt_f8_heap"); - util.execute("DROP TABLE IF EXISTS array_op_test"); - util.execute("DROP TABLE IF EXISTS array_index_op_test"); + util.execute("DROP TABLE IF EXISTS aggtest CASCADE"); + util.execute("DROP TABLE IF EXISTS tenk1 CASCADE"); + util.execute("DROP TABLE IF EXISTS slow_emp4000 CASCADE"); + util.execute("DROP TABLE IF EXISTS person CASCADE"); + util.execute("DROP TABLE IF EXISTS onek CASCADE"); + util.execute("DROP TABLE IF EXISTS emp CASCADE"); + util.execute("DROP TABLE IF EXISTS student CASCADE"); + util.execute("DROP TABLE IF EXISTS stud_emp CASCADE"); + util.execute("DROP TABLE IF EXISTS real_city CASCADE"); + util.execute("DROP TABLE IF EXISTS road CASCADE"); + util.execute("DROP TABLE IF EXISTS hash_i4_heap CASCADE"); + util.execute("DROP TABLE IF EXISTS hash_name_heap CASCADE"); + util.execute("DROP TABLE IF EXISTS hash_txt_heap CASCADE"); + util.execute("DROP TABLE IF EXISTS hash_f8_heap CASCADE"); + util.execute("DROP TABLE IF EXISTS bt_i4_heap CASCADE"); + util.execute("DROP TABLE IF EXISTS bt_name_heap CASCADE"); + util.execute("DROP TABLE IF EXISTS bt_txt_heap CASCADE"); + util.execute("DROP TABLE IF EXISTS bt_f8_heap CASCADE"); + util.execute("DROP TABLE IF EXISTS array_op_test CASCADE"); + util.execute("DROP TABLE IF EXISTS array_index_op_test CASCADE"); // test util.execute("CREATE TABLE aggtest (a int2, b float4)"); @@ -157,3 +157,270 @@ TEST_F(TestCreateTable, TestCreateTable1) { util.execute("DROP TABLE aggtest"); } +TEST_F(TestCreateTable, TestCreateTableInherits) { + hawq::test::SQLUtility util; + // prepare + util.execute("DROP TABLE IF EXISTS t1_1_6, t1_1_5, t1_1_4, t1_1_3, " + "t1_1_2, t1_1_1, t1_1_w, t1_1, t1 CASCADE"); + + // test + util.execute("CREATE TABLE t1(c1 int)"); + util.execute("CREATE TABLE t1_1(c2 int) INHERITS(t1)"); + util.executeExpectErrorMsgStartWith( + "CREATE TABLE t1_1_w(c2 int) INHERITS(t1) WITH (bucketnum=3)", + "NOTICE: Table has parent, setting distribution columns to match parent table\n" + "ERROR: distribution policy for \"t1_1_w\" must be the same as that for \"t1\""); + util.executeExpectErrorMsgStartWith( + "CREATE TABLE t1_1_1(c2 int) INHERITS (t1) DISTRIBUTED BY (c1)", + "ERROR: distribution policy for \"t1_1_1\" must be the same as that for \"t1\""); + util.executeExpectErrorMsgStartWith( + "CREATE TABLE t1_1_2(c2 int) INHERITS (t1) DISTRIBUTED BY (c1)", + "ERROR: distribution policy for \"t1_1_2\" must be the same as that for \"t1\""); + util.execute("CREATE TABLE t1_1_3(c2 int) INHERITS (t1) DISTRIBUTED RANDOMLY"); + + util.executeExpectErrorMsgStartWith( + "CREATE TABLE t1_1_4(c2 int) INHERITS (t1) WITH (bucketnum = 3) DISTRIBUTED BY(c1)", + "ERROR: distribution policy for \"t1_1_4\" must be the same as that for \"t1\""); + util.executeExpectErrorMsgStartWith( + "CREATE TABLE t1_1_5(c2 int) INHERITS (t1) WITH (bucketnum = 5) DISTRIBUTED BY(c2)", + "ERROR: distribution policy for \"t1_1_5\" must be the same as that for \"t1\""); + util.executeExpectErrorMsgStartWith( + "CREATE TABLE t1_1_6(c2 int) INHERITS (t1) WITH (bucketnum = 7) DISTRIBUTED RANDOMLY", + "ERROR: distribution policy for \"t1_1_6\" must be the same as that for \"t1\""); + + util.execute("CREATE TABLE t1_1_w(c2 int) INHERITS(t1) WITH (bucketnum=6)"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname='t1')", + "6||\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname='t1_1')", + "6||\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname='t1_1_w')", + "6||\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname='t1_1_3')", + "6||\n"); + + util.execute("DROP TABLE t1_1_3, t1_1_w, t1_1, t1"); +} + +TEST_F(TestCreateTable, TestCreateTableDistribution1) { + hawq::test::SQLUtility util; + // prepare + util.execute("DROP TABLE IF EXISTS t1_3_4, t1_3_3, t1_3_2, t1_3_1, t1_3_w, t1_3 CASCADE"); + util.execute("DROP TABLE IF EXISTS t1_2_4, t1_2_3, t1_2_2, t1_2_1, t1_2_w, t1_2 CASCADE"); + util.execute("DROP TABLE IF EXISTS t1 CASCADE"); + util.execute("CREATE TABLE t1(c1 int)"); + + // test + util.execute("CREATE TABLE t1_2 (LIKE t1)"); + util.execute("CREATE TABLE t1_2_w(LIKE t1) WITH (bucketnum = 4)"); + util.execute("CREATE TABLE t1_2_1(LIKE t1) DISTRIBUTED BY (c1)"); + util.execute("CREATE TABLE t1_2_2(LIKE t1) DISTRIBUTED RANDOMLY"); + util.execute("CREATE TABLE t1_2_3(LIKE t1) WITH (bucketnum = 4) DISTRIBUTED BY (c1)"); + util.execute("CREATE TABLE t1_2_4(LIKE t1) WITH (bucketnum = 4) DISTRIBUTED RANDOMLY"); + + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname='t1_2')", + "6||\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname='t1_2_w')", + "4||\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname='t1_2_1')", + "6|{1}|\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname='t1_2_2')", + "6||\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname='t1_2_3')", + "4|{1}|\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname='t1_2_4')", + "4||\n"); + + util.execute("CREATE TABLE t1_3 AS (SELECT * FROM t1)"); + util.execute("CREATE TABLE t1_3_w WITH (bucketnum = 4) AS (SELECT * FROM t1)"); + util.execute("CREATE TABLE t1_3_1 AS (SELECT * FROM t1) DISTRIBUTED BY (c1)"); + util.execute("CREATE TABLE t1_3_2 AS (SELECT * FROM t1) DISTRIBUTED RANDOMLY"); + util.execute("CREATE TABLE t1_3_3 WITH (bucketnum = 6) AS (SELECT * FROM t1) DISTRIBUTED BY (c1)"); + util.execute("CREATE TABLE t1_3_4 WITH (bucketnum = 7) AS (SELECT * FROM t1) DISTRIBUTED RANDOMLY"); + + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname='t1_2')", + "6||\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname='t1_3_w')", + "4||\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname='t1_3_1')", + "6|{1}|\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname='t1_3_2')", + "6||\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname='t1_3_3')", + "6|{1}|\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname='t1_3_4')", + "7||\n"); + + // cleanup + util.execute("DROP TABLE t1_3_4, t1_3_3, t1_3_2, t1_3_1, t1_3_w, t1_3"); + util.execute("DROP TABLE t1_2_4, t1_2_3, t1_2_2, t1_2_1, t1_2_w, t1_2"); + util.execute("DROP TABLE t1 CASCADE"); +} + +TEST_F(TestCreateTable, TestCreateTableDistribution2) { + hawq::test::SQLUtility util; + // prepare + util.execute("DROP TABLE IF EXISTS t2_2, t2_2_w, t2_2_1, t2_2_2, t2_2_3, t2_2_4 CASCADE"); + util.execute("DROP TABLE IF EXISTS t2_3, t2_3_w, t2_3_1, t2_3_2, t2_3_3, t2_3_4 CASCADE"); + util.execute("DROP TABLE IF EXISTS t2_1_1, t2_1, t2 CASCADE"); + util.execute("CREATE TABLE t2(c1 int) DISTRIBUTED BY (c1)"); + + // test + util.execute("CREATE TABLE t2_1(c2 int) INHERITS (t2)"); + util.executeExpectErrorMsgStartWith( + "CREATE TABLE t2_1_w(c2 int) INHERITS (t2) WITH (bucketnum = 3)", + "NOTICE: Table has parent, setting distribution columns to match parent table\n" + "ERROR: distribution policy for \"t2_1_w\" must be the same as that for \"t2\""); + util.execute("CREATE TABLE t2_1_1(c2 int) INHERITS (t2) DISTRIBUTED BY (c1)"); + util.executeExpectErrorMsgStartWith( + "CREATE TABLE t2_1_2(c2 int) INHERITS (t2) DISTRIBUTED BY (c2)", + "ERROR: distribution policy for \"t2_1_2\" must be the same as that for \"t2\""); + util.executeExpectErrorMsgStartWith( + "CREATE TABLE t2_1_3(c2 int) INHERITS (t2) DISTRIBUTED RANDOMLY", + "ERROR: distribution policy for \"t2_1_3\" must be the same as that for \"t2\""); + util.executeExpectErrorMsgStartWith( + "CREATE TABLE t2_1_4(c2 int) INHERITS (t2) WITH (bucketnum = 3) DISTRIBUTED BY (c1)", + "ERROR: distribution policy for \"t2_1_4\" must be the same as that for \"t2\""); + util.executeExpectErrorMsgStartWith( + "CREATE TABLE t2_1_5(c2 int) INHERITS (t2) WITH (bucketnum = 5) DISTRIBUTED BY (c2)", + "ERROR: distribution policy for \"t2_1_5\" must be the same as that for \"t2\""); + util.executeExpectErrorMsgStartWith( + "CREATE TABLE t2_1_6(c2 int) INHERITS (t2) WITH (bucketnum = 7) DISTRIBUTED RANDOMLY", + "ERROR: distribution policy for \"t2_1_6\" must be the same as that for \"t2\""); + + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_1_1')", + "6|{1}|\n"); + + util.execute("CREATE TABLE t2_2(LIKE t2)"); + util.execute("CREATE TABLE t2_2_w(LIKE t2) WITH (bucketnum = 4)"); + util.execute("CREATE TABLE t2_2_1(LIKE t2) DISTRIBUTED BY (c1)"); + util.execute("CREATE TABLE t2_2_2(LIKE t2) DISTRIBUTED RANDOMLY"); + util.execute("CREATE TABLE t2_2_3(LIKE t2) WITH (bucketnum = 5) DISTRIBUTED BY (c1)"); + util.execute("CREATE TABLE t2_2_4(LIKE t2) WITH (bucketnum = 6) DISTRIBUTED RANDOMLY"); + + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_2')", + "6|{1}|\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_2_w')", + "4|{1}|\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_2_1')", + "6|{1}|\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_2_2')", + "6||\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_2_3')", + "5|{1}|\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_2_4')", + "6||\n"); + + util.execute("CREATE TABLE t2_3 AS (SELECT * FROM t2)"); + util.execute("CREATE TABLE t2_3_w WITH (bucketnum = 4) AS (SELECT * FROM t2)"); + util.execute("CREATE TABLE t2_3_1 AS (SELECT * FROM t2) DISTRIBUTED BY (c1)"); + util.execute("CREATE TABLE t2_3_2 AS (SELECT * FROM t2) DISTRIBUTED RANDOMLY"); + util.execute("CREATE TABLE t2_3_3 WITH (bucketnum = 5) AS (SELECT * FROM t2) DISTRIBUTED BY (c1)"); + util.execute("CREATE TABLE t2_3_4 WITH (bucketnum = 6) AS (SELECT * FROM t2) DISTRIBUTED RANDOMLY"); + + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_3')", + "6||\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_3_w')", + "4||\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_3_1')", + "6|{1}|\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_3_2')", + "6||\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_3_3')", + "5|{1}|\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_3_4')", + "6||\n"); + + // cleanup + util.execute("DROP TABLE t2_2, t2_2_w, t2_2_1, t2_2_2, t2_2_3, t2_2_4"); + util.execute("DROP TABLE t2_3, t2_3_w, t2_3_1, t2_3_2, t2_3_3, t2_3_4"); + util.execute("DROP TABLE t2_1_1,t2_1, t2"); +} + +TEST_F(TestCreateTable, TestCreateTableDistribution3) { + hawq::test::SQLUtility util; + // prepare + util.execute("DROP TABLE IF EXISTS t3_2, t3_1, t3 CASCADE"); + + // test + util.execute("CREATE TABLE t3 (c1 int) WITH (bucketnum = 4)"); + util.execute("CREATE TABLE t3_1 (c1 int) WITH (bucketnum = 5) DISTRIBUTED BY(c1)"); + util.execute("CREATE TABLE t3_2 (c1 int) WITH (bucketnum = 6) DISTRIBUTED RANDOMLY"); + + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't3')", + "4||\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't3_1')", + "5|{1}|\n"); + util.query("SELECT bucketnum, attrnums FROM gp_distribution_policy " + "WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't3_2')", + "6||\n"); + + // cleanup + util.execute("DROP TABLE t3_2, t3_1, t3"); +} + +TEST_F(TestCreateTable, TestCreateTableDistribution4) { + hawq::test::SQLUtility util; + // prepare + util.execute("DROP TABLE IF EXISTS t4"); + + // test + util.executeExpectErrorMsgStartWith( + "CREATE TABLE t4 (id int, date date, amt decimal(10,2)) " + "DISTRIBUTED RANDOMLY PARTITION BY RANGE (date) " + "( PARTITION Jan08 START (date '2008-01-01') INCLUSIVE WITH (bucketnum = 9)," + " PARTITION Feb08 START (date '2008-02-01') INCLUSIVE END (date '2008-03-01') EXCLUSIVE WITH (bucketnum = 6))", + "ERROR: distribution policy for \"t4_1_prt_jan08\" must be the same as that for \"t4\""); + util.execute( + "CREATE TABLE t4 (id int, date date, amt decimal(10,2)) " + "DISTRIBUTED RANDOMLY PARTITION BY RANGE (date) " + "( PARTITION Jan08 START (date '2008-01-01') INCLUSIVE WITH (bucketnum = 6)," + " PARTITION Feb08 START (date '2008-02-01') INCLUSIVE END (date '2008-03-01') EXCLUSIVE WITH (bucketnum = 6))"); + util.query("select bucketnum, attrnums from gp_distribution_policy where localoid='t4'::regclass", + "6||\n"); + + util.executeExpectErrorMsgStartWith( + "ALTER TABLE t4 ADD PARTITION " + "START (date '2008-03-01') INCLUSIVE END (date '2008-04-01') EXCLUSIVE WITH (bucketnum = 8, tablename='t4_new_part')", + "ERROR: distribution policy for partition must be the same as that for relation \"t4\""); + util.execute("ALTER TABLE t4 ADD PARTITION " + "START (date '2008-03-01') INCLUSIVE END (date '2008-04-01') EXCLUSIVE WITH (bucketnum = 6, tablename='t4_new_part')"); + util.query("select bucketnum, attrnums from gp_distribution_policy where localoid='t4_new_part'::regclass", + "6||\n"); + + // cleanup + util.execute("DROP TABLE t4"); +} + + + http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/cc7844cc/src/test/regress/expected/create_table_distribution.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/create_table_distribution.out b/src/test/regress/expected/create_table_distribution.out deleted file mode 100644 index 717cc46..0000000 --- a/src/test/regress/expected/create_table_distribution.out +++ /dev/null @@ -1,382 +0,0 @@ ---- ---- Test for CREATE TABLE distribution policy ---- -CREATE TABLE t1(c1 int); -CREATE TABLE t1_1(c2 int) INHERITS(t1); -NOTICE: Table has parent, setting distribution columns to match parent table --- should error out messages with different bucketnum -CREATE TABLE t1_1_w(c2 int) INHERITS(t1) WITH (bucketnum = 3); -NOTICE: Table has parent, setting distribution columns to match parent table -ERROR: distribution policy for "t1_1_w" must be the same as that for "t1" -CREATE TABLE t1_1_w(c2 int) INHERITS(t1) WITH (bucketnum = 6); -NOTICE: Table has parent, setting distribution columns to match parent table -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1'); - bucketnum | attrnums ------------+---------- - 6 | -(1 row) - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_1'); - bucketnum | attrnums ------------+---------- - 6 | -(1 row) - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_1_w'); - bucketnum | attrnums ------------+---------- - 6 | -(1 row) - -CREATE TABLE t1_1_1(c2 int) INHERITS (t1) DISTRIBUTED BY(c1); -ERROR: distribution policy for "t1_1_1" must be the same as that for "t1" -CREATE TABLE t1_1_2(c2 int) INHERITS (t1) DISTRIBUTED BY(c2); -ERROR: distribution policy for "t1_1_2" must be the same as that for "t1" -CREATE TABLE t1_1_3(c2 int) INHERITS (t1) DISTRIBUTED RANDOMLY; --- should error out messages with different bucketnum -CREATE TABLE t1_1_4(c2 int) INHERITS (t1) WITH (bucketnum = 3) DISTRIBUTED BY(c1) ; -ERROR: distribution policy for "t1_1_4" must be the same as that for "t1" -CREATE TABLE t1_1_5(c2 int) INHERITS (t1) WITH (bucketnum = 5) DISTRIBUTED BY(c2); -ERROR: distribution policy for "t1_1_5" must be the same as that for "t1" -CREATE TABLE t1_1_6(c2 int) INHERITS (t1) WITH (bucketnum = 7) DISTRIBUTED RANDOMLY; -ERROR: distribution policy for "t1_1_6" must be the same as that for "t1" -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_1_1'); - bucketnum | attrnums ------------+---------- -(0 rows) - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_1_2'); - bucketnum | attrnums ------------+---------- -(0 rows) - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_1_3'); - bucketnum | attrnums ------------+---------- - 6 | -(1 row) - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_1_4'); - bucketnum | attrnums ------------+---------- -(0 rows) - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_1_5'); - bucketnum | attrnums ------------+---------- -(0 rows) - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_1_6'); - bucketnum | attrnums ------------+---------- -(0 rows) - -CREATE TABLE t1_2(LIKE t1); -NOTICE: Table doesn't have 'distributed by' clause, defaulting to distribution columns from LIKE table --- should error out messages with different bucketnum -CREATE TABLE t1_2_w(LIKE t1) WITH (bucketnum = 4); -NOTICE: Table doesn't have 'distributed by' clause, defaulting to distribution columns from LIKE table -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_2'); - bucketnum | attrnums ------------+---------- - 6 | -(1 row) - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_2_w'); - bucketnum | attrnums ------------+---------- - 4 | -(1 row) - -CREATE TABLE t1_2_1(LIKE t1) DISTRIBUTED BY (c1); -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_2_1'); - bucketnum | attrnums ------------+---------- - 6 | {1} -(1 row) - -CREATE TABLE t1_2_2(LIKE t1) DISTRIBUTED RANDOMLY; -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_2_2'); - bucketnum | attrnums ------------+---------- - 6 | -(1 row) - --- should error out messages with different bucketnum -CREATE TABLE t1_2_3(LIKE t1) WITH (bucketnum = 4) DISTRIBUTED BY (c1); -CREATE TABLE t1_2_4(LIKE t1) WITH (bucketnum = 4) DISTRIBUTED RANDOMLY; -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_2_3'); - bucketnum | attrnums ------------+---------- - 4 | {1} -(1 row) - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_2_4'); - bucketnum | attrnums ------------+---------- - 4 | -(1 row) - -CREATE TABLE t1_3 AS (SELECT * FROM t1); -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_3'); - bucketnum | attrnums ------------+---------- - 6 | -(1 row) - -CREATE TABLE t1_3_w WITH (bucketnum = 4) AS (SELECT * FROM t1); -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_3_w'); - bucketnum | attrnums ------------+---------- - 4 | -(1 row) - -CREATE TABLE t1_3_1 AS (SELECT * FROM t1) DISTRIBUTED BY (c1); -CREATE TABLE t1_3_2 AS (SELECT * FROM t1) DISTRIBUTED RANDOMLY; -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_3_1'); - bucketnum | attrnums ------------+---------- - 6 | {1} -(1 row) - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_3_2'); - bucketnum | attrnums ------------+---------- - 6 | -(1 row) - -CREATE TABLE t1_3_3 WITH (bucketnum = 6) AS (SELECT * FROM t1) DISTRIBUTED BY (c1); -CREATE TABLE t1_3_4 WITH (bucketnum = 7) AS (SELECT * FROM t1) DISTRIBUTED RANDOMLY; -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_3_3'); - bucketnum | attrnums ------------+---------- - 6 | {1} -(1 row) - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_3_4'); - bucketnum | attrnums ------------+---------- - 7 | -(1 row) - -DROP TABLE t1_3_4, t1_3_3, t1_3_2, t1_3_1, t1_3_w, t1_3, t1_2_4, t1_2_3, t1_2_2, t1_2_1, t1_2_w, t1_2, t1_1_3, t1_1_w, t1_1, t1; -CREATE TABLE t2(c1 int) DISTRIBUTED BY (c1); -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2'); - bucketnum | attrnums ------------+---------- - 6 | {1} -(1 row) - -CREATE TABLE t2_1(c2 int) INHERITS (t2); -NOTICE: Table has parent, setting distribution columns to match parent table -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_1'); - bucketnum | attrnums ------------+---------- - 6 | {1} -(1 row) - -CREATE TABLE t2_1_w(c2 int) INHERITS (t2) WITH (bucketnum = 3); -NOTICE: Table has parent, setting distribution columns to match parent table -ERROR: distribution policy for "t2_1_w" must be the same as that for "t2" -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_1_w'); - bucketnum | attrnums ------------+---------- -(0 rows) - -CREATE TABLE t2_1_1(c2 int) INHERITS (t2) DISTRIBUTED BY (c1); -CREATE TABLE t2_1_2(c2 int) INHERITS (t2) DISTRIBUTED BY (c2); -ERROR: distribution policy for "t2_1_2" must be the same as that for "t2" -CREATE TABLE t2_1_3(c2 int) INHERITS (t2) DISTRIBUTED RANDOMLY; -ERROR: distribution policy for "t2_1_3" must be the same as that for "t2" -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_1_1'); - bucketnum | attrnums ------------+---------- - 6 | {1} -(1 row) - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_1_2'); - bucketnum | attrnums ------------+---------- -(0 rows) - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_1_3'); - bucketnum | attrnums ------------+---------- -(0 rows) - -CREATE TABLE t2_1_4(c2 int) INHERITS (t2) WITH (bucketnum = 3) DISTRIBUTED BY (c1); -ERROR: distribution policy for "t2_1_4" must be the same as that for "t2" -CREATE TABLE t2_1_5(c2 int) INHERITS (t2) WITH (bucketnum = 5) DISTRIBUTED BY (c2); -ERROR: distribution policy for "t2_1_5" must be the same as that for "t2" -CREATE TABLE t2_1_6(c2 int) INHERITS (t2) WITH (bucketnum = 7) DISTRIBUTED RANDOMLY; -ERROR: distribution policy for "t2_1_6" must be the same as that for "t2" -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_1_4'); - bucketnum | attrnums ------------+---------- -(0 rows) - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_1_5'); - bucketnum | attrnums ------------+---------- -(0 rows) - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_1_6'); - bucketnum | attrnums ------------+---------- -(0 rows) - -CREATE TABLE t2_2(LIKE t2); -NOTICE: Table doesn't have 'distributed by' clause, defaulting to distribution columns from LIKE table -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_2'); - bucketnum | attrnums ------------+---------- - 6 | {1} -(1 row) - -CREATE TABLE t2_2_w(LIKE t2) WITH (bucketnum = 4); -NOTICE: Table doesn't have 'distributed by' clause, defaulting to distribution columns from LIKE table -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_2_w'); - bucketnum | attrnums ------------+---------- - 4 | {1} -(1 row) - -CREATE TABLE t2_2_1(LIKE t2) DISTRIBUTED BY (c1); -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_2_1'); - bucketnum | attrnums ------------+---------- - 6 | {1} -(1 row) - -CREATE TABLE t2_2_2(LIKE t2) DISTRIBUTED RANDOMLY; -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_2_2'); - bucketnum | attrnums ------------+---------- - 6 | -(1 row) - -CREATE TABLE t2_2_3(LIKE t2) WITH (bucketnum = 5) DISTRIBUTED BY (c1); -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_2_3'); - bucketnum | attrnums ------------+---------- - 5 | {1} -(1 row) - -CREATE TABLE t2_2_4(LIKE t2) WITH (bucketnum = 6) DISTRIBUTED RANDOMLY; -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_2_4'); - bucketnum | attrnums ------------+---------- - 6 | -(1 row) - -CREATE TABLE t2_3 AS (SELECT * FROM t2); -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_3'); - bucketnum | attrnums ------------+---------- - 6 | -(1 row) - -CREATE TABLE t2_3_w WITH (bucketnum = 4) AS (SELECT * FROM t2); -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_3_w'); - bucketnum | attrnums ------------+---------- - 4 | -(1 row) - - ; -CREATE TABLE t2_3_1 AS (SELECT * FROM t2) DISTRIBUTED BY (c1); -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_3_1'); - bucketnum | attrnums ------------+---------- - 6 | {1} -(1 row) - -CREATE TABLE t2_3_2 AS (SELECT * FROM t2) DISTRIBUTED RANDOMLY; -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_3_2'); - bucketnum | attrnums ------------+---------- - 6 | -(1 row) - -CREATE TABLE t2_3_3 WITH (bucketnum = 5) AS (SELECT * FROM t2) DISTRIBUTED BY (c1); -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_3_3'); - bucketnum | attrnums ------------+---------- - 5 | {1} -(1 row) - -CREATE TABLE t2_3_4 WITH (bucketnum = 6) AS (SELECT * FROM t2) DISTRIBUTED RANDOMLY; -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_3_4'); - bucketnum | attrnums ------------+---------- - 6 | -(1 row) - -DROP TABLE t2_3_4, t2_3_3, t2_3_2, t2_3_1, t2_3_w, t2_3, t2_2_4, t2_2_3, t2_2_2, t2_2_1, t2_2_w, t2_2, t2_1_1, t2_1_w, t2_1, t2; -ERROR: table "t2_1_w" does not exist -CREATE TABLE t3 (c1 int) WITH (bucketnum = 4); -CREATE TABLE t3_1 (c1 int) WITH (bucketnum = 5) DISTRIBUTED BY(c1); -CREATE TABLE t3_2 (c1 int) WITH (bucketnum = 6) DISTRIBUTED RANDOMLY; -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't3'); - bucketnum | attrnums ------------+---------- - 4 | -(1 row) - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't3_1'); - bucketnum | attrnums ------------+---------- - 5 | {1} -(1 row) - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't3_2'); - bucketnum | attrnums ------------+---------- - 6 | -(1 row) - -DROP TABLE t3_2, t3_1, t3; -CREATE TABLE t4 (id int, date date, amt decimal(10,2)) -DISTRIBUTED RANDOMLY -PARTITION BY RANGE (date) -( PARTITION Jan08 START (date '2008-01-01') INCLUSIVE WITH (bucketnum = 9), - PARTITION Feb08 START (date '2008-02-01') INCLUSIVE END (date '2008-03-01') EXCLUSIVE WITH (bucketnum = 6)); -ERROR: distribution policy for "t4_1_prt_jan08" must be the same as that for "t4" --- expected error out -select bucketnum, attrnums from gp_distribution_policy where localoid='t4'::regclass; -ERROR: relation "t4" does not exist -LINE 1: ...trnums from gp_distribution_policy where localoid='t4'::regc... - ^ -CREATE TABLE t4 (id int, date date, amt decimal(10,2)) -DISTRIBUTED RANDOMLY -PARTITION BY RANGE (date) -( PARTITION Jan08 START (date '2008-01-01') INCLUSIVE WITH (bucketnum = 6), - PARTITION Feb08 START (date '2008-02-01') INCLUSIVE END (date '2008-03-01') EXCLUSIVE WITH (bucketnum = 6)); -NOTICE: CREATE TABLE will create partition "t4_1_prt_jan08" for table "t4" -NOTICE: CREATE TABLE will create partition "t4_1_prt_feb08" for table "t4" -select bucketnum, attrnums from gp_distribution_policy where localoid='t4'::regclass; - bucketnum | attrnums ------------+---------- - 6 | -(1 row) - -ALTER TABLE t4 ADD PARTITION -START (date '2008-03-01') INCLUSIVE -END (date '2008-04-01') EXCLUSIVE WITH (bucketnum = 8, tablename='t4_new_part'); -ERROR: distribution policy for partition must be the same as that for relation "t4" --- expected error out -select bucketnum, attrnums from gp_distribution_policy where localoid='t4_new_part'::regclass; -ERROR: relation "t4_new_part" does not exist -LINE 1: ...trnums from gp_distribution_policy where localoid='t4_new_pa... - ^ -ALTER TABLE t4 ADD PARTITION -START (date '2008-03-01') INCLUSIVE -END (date '2008-04-01') EXCLUSIVE WITH (bucketnum = 6, tablename='t4_new_part'); -NOTICE: CREATE TABLE will create partition "t4_new_part" for table "t4" -select bucketnum, attrnums from gp_distribution_policy where localoid='t4_new_part'::regclass; - bucketnum | attrnums ------------+---------- - 6 | -(1 row) - -DROP TABLE t4 CASCADE; http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/cc7844cc/src/test/regress/known_good_schedule ---------------------------------------------------------------------- diff --git a/src/test/regress/known_good_schedule b/src/test/regress/known_good_schedule index eb95086..8c9091f 100755 --- a/src/test/regress/known_good_schedule +++ b/src/test/regress/known_good_schedule @@ -43,7 +43,6 @@ ignore: geometry ignore: horology ignore: create_type test: create_table_test -test: create_table_distribution ignore: create_function_2 test: copy ignore: copyselect http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/cc7844cc/src/test/regress/sql/create_table_distribution.sql ---------------------------------------------------------------------- diff --git a/src/test/regress/sql/create_table_distribution.sql b/src/test/regress/sql/create_table_distribution.sql deleted file mode 100644 index 7e98c8f..0000000 --- a/src/test/regress/sql/create_table_distribution.sql +++ /dev/null @@ -1,227 +0,0 @@ ---- ---- Test for CREATE TABLE distribution policy ---- - -CREATE TABLE t1(c1 int); - -CREATE TABLE t1_1(c2 int) INHERITS(t1); - --- should error out messages with different bucketnum -CREATE TABLE t1_1_w(c2 int) INHERITS(t1) WITH (bucketnum = 3); - -CREATE TABLE t1_1_w(c2 int) INHERITS(t1) WITH (bucketnum = 6); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1'); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_1'); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_1_w'); - -CREATE TABLE t1_1_1(c2 int) INHERITS (t1) DISTRIBUTED BY(c1); - -CREATE TABLE t1_1_2(c2 int) INHERITS (t1) DISTRIBUTED BY(c2); - -CREATE TABLE t1_1_3(c2 int) INHERITS (t1) DISTRIBUTED RANDOMLY; - --- should error out messages with different bucketnum -CREATE TABLE t1_1_4(c2 int) INHERITS (t1) WITH (bucketnum = 3) DISTRIBUTED BY(c1) ; - -CREATE TABLE t1_1_5(c2 int) INHERITS (t1) WITH (bucketnum = 5) DISTRIBUTED BY(c2); - -CREATE TABLE t1_1_6(c2 int) INHERITS (t1) WITH (bucketnum = 7) DISTRIBUTED RANDOMLY; - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_1_1'); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_1_2'); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_1_3'); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_1_4'); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_1_5'); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_1_6'); - -CREATE TABLE t1_2(LIKE t1); - --- should error out messages with different bucketnum -CREATE TABLE t1_2_w(LIKE t1) WITH (bucketnum = 4); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_2'); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_2_w'); - -CREATE TABLE t1_2_1(LIKE t1) DISTRIBUTED BY (c1); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_2_1'); - -CREATE TABLE t1_2_2(LIKE t1) DISTRIBUTED RANDOMLY; - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_2_2'); - --- should error out messages with different bucketnum -CREATE TABLE t1_2_3(LIKE t1) WITH (bucketnum = 4) DISTRIBUTED BY (c1); - -CREATE TABLE t1_2_4(LIKE t1) WITH (bucketnum = 4) DISTRIBUTED RANDOMLY; - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_2_3'); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_2_4'); - -CREATE TABLE t1_3 AS (SELECT * FROM t1); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_3'); - -CREATE TABLE t1_3_w WITH (bucketnum = 4) AS (SELECT * FROM t1); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_3_w'); - -CREATE TABLE t1_3_1 AS (SELECT * FROM t1) DISTRIBUTED BY (c1); - -CREATE TABLE t1_3_2 AS (SELECT * FROM t1) DISTRIBUTED RANDOMLY; - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_3_1'); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_3_2'); - -CREATE TABLE t1_3_3 WITH (bucketnum = 6) AS (SELECT * FROM t1) DISTRIBUTED BY (c1); - -CREATE TABLE t1_3_4 WITH (bucketnum = 7) AS (SELECT * FROM t1) DISTRIBUTED RANDOMLY; - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_3_3'); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't1_3_4'); - -DROP TABLE t1_3_4, t1_3_3, t1_3_2, t1_3_1, t1_3_w, t1_3, t1_2_4, t1_2_3, t1_2_2, t1_2_1, t1_2_w, t1_2, t1_1_3, t1_1_w, t1_1, t1; - -CREATE TABLE t2(c1 int) DISTRIBUTED BY (c1); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2'); - -CREATE TABLE t2_1(c2 int) INHERITS (t2); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_1'); - -CREATE TABLE t2_1_w(c2 int) INHERITS (t2) WITH (bucketnum = 3); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_1_w'); - -CREATE TABLE t2_1_1(c2 int) INHERITS (t2) DISTRIBUTED BY (c1); - -CREATE TABLE t2_1_2(c2 int) INHERITS (t2) DISTRIBUTED BY (c2); - -CREATE TABLE t2_1_3(c2 int) INHERITS (t2) DISTRIBUTED RANDOMLY; - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_1_1'); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_1_2'); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_1_3'); - -CREATE TABLE t2_1_4(c2 int) INHERITS (t2) WITH (bucketnum = 3) DISTRIBUTED BY (c1); - -CREATE TABLE t2_1_5(c2 int) INHERITS (t2) WITH (bucketnum = 5) DISTRIBUTED BY (c2); - -CREATE TABLE t2_1_6(c2 int) INHERITS (t2) WITH (bucketnum = 7) DISTRIBUTED RANDOMLY; - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_1_4'); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_1_5'); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_1_6'); - -CREATE TABLE t2_2(LIKE t2); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_2'); - -CREATE TABLE t2_2_w(LIKE t2) WITH (bucketnum = 4); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_2_w'); - -CREATE TABLE t2_2_1(LIKE t2) DISTRIBUTED BY (c1); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_2_1'); - -CREATE TABLE t2_2_2(LIKE t2) DISTRIBUTED RANDOMLY; - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_2_2'); - -CREATE TABLE t2_2_3(LIKE t2) WITH (bucketnum = 5) DISTRIBUTED BY (c1); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_2_3'); - -CREATE TABLE t2_2_4(LIKE t2) WITH (bucketnum = 6) DISTRIBUTED RANDOMLY; - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_2_4'); - -CREATE TABLE t2_3 AS (SELECT * FROM t2); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_3'); - -CREATE TABLE t2_3_w WITH (bucketnum = 4) AS (SELECT * FROM t2); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_3_w'); - ; -CREATE TABLE t2_3_1 AS (SELECT * FROM t2) DISTRIBUTED BY (c1); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_3_1'); - -CREATE TABLE t2_3_2 AS (SELECT * FROM t2) DISTRIBUTED RANDOMLY; - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_3_2'); - -CREATE TABLE t2_3_3 WITH (bucketnum = 5) AS (SELECT * FROM t2) DISTRIBUTED BY (c1); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_3_3'); - -CREATE TABLE t2_3_4 WITH (bucketnum = 6) AS (SELECT * FROM t2) DISTRIBUTED RANDOMLY; - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't2_3_4'); - -DROP TABLE t2_3_4, t2_3_3, t2_3_2, t2_3_1, t2_3_w, t2_3, t2_2_4, t2_2_3, t2_2_2, t2_2_1, t2_2_w, t2_2, t2_1_1, t2_1_w, t2_1, t2; - -CREATE TABLE t3 (c1 int) WITH (bucketnum = 4); - -CREATE TABLE t3_1 (c1 int) WITH (bucketnum = 5) DISTRIBUTED BY(c1); - -CREATE TABLE t3_2 (c1 int) WITH (bucketnum = 6) DISTRIBUTED RANDOMLY; - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't3'); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't3_1'); - -SELECT bucketnum, attrnums FROM gp_distribution_policy WHERE localoid = (SELECT oid FROM pg_class WHERE relname = 't3_2'); - -DROP TABLE t3_2, t3_1, t3; - -CREATE TABLE t4 (id int, date date, amt decimal(10,2)) -DISTRIBUTED RANDOMLY -PARTITION BY RANGE (date) -( PARTITION Jan08 START (date '2008-01-01') INCLUSIVE WITH (bucketnum = 9), - PARTITION Feb08 START (date '2008-02-01') INCLUSIVE END (date '2008-03-01') EXCLUSIVE WITH (bucketnum = 6)); - --- expected error out -select bucketnum, attrnums from gp_distribution_policy where localoid='t4'::regclass; - -CREATE TABLE t4 (id int, date date, amt decimal(10,2)) -DISTRIBUTED RANDOMLY -PARTITION BY RANGE (date) -( PARTITION Jan08 START (date '2008-01-01') INCLUSIVE WITH (bucketnum = 6), - PARTITION Feb08 START (date '2008-02-01') INCLUSIVE END (date '2008-03-01') EXCLUSIVE WITH (bucketnum = 6)); - -select bucketnum, attrnums from gp_distribution_policy where localoid='t4'::regclass; - -ALTER TABLE t4 ADD PARTITION -START (date '2008-03-01') INCLUSIVE -END (date '2008-04-01') EXCLUSIVE WITH (bucketnum = 8, tablename='t4_new_part'); - --- expected error out -select bucketnum, attrnums from gp_distribution_policy where localoid='t4_new_part'::regclass; - -ALTER TABLE t4 ADD PARTITION -START (date '2008-03-01') INCLUSIVE -END (date '2008-04-01') EXCLUSIVE WITH (bucketnum = 6, tablename='t4_new_part'); - -select bucketnum, attrnums from gp_distribution_policy where localoid='t4_new_part'::regclass; - -DROP TABLE t4 CASCADE;
