http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/b32e56c5/src/test/regress/expected/partition.out ---------------------------------------------------------------------- diff --git a/src/test/regress/expected/partition.out b/src/test/regress/expected/partition.out index f3bcded..67ac33d 100755 --- a/src/test/regress/expected/partition.out +++ b/src/test/regress/expected/partition.out @@ -275,9 +275,7 @@ partition by range (b,d) partition aa start (2007,1) end (2008,2), partition bb start (2008,2) end (2009,3) ); -ERROR: too many columns for RANGE partition -- only one column is allowed. -LINE 3: partition by range (b,d) - ^ +ERROR: Composite partition keys are not allowed drop table ggg cascade; ERROR: table "ggg" does not exist -- demo starts here @@ -723,9 +721,7 @@ partition by list (rank,gender) partition ff values ((4, 'M')), partition bb values ((1, 'M')) ); -ERROR: duplicate VALUES in partition "bb" -LINE 10: partition bb values ((1, 'M')) - ^ +ERROR: Composite partition keys are not allowed -- RANGE validation -- legal if end of aa not inclusive create table ggg (a char(1), b date, d char(3)) @@ -1016,7 +1012,7 @@ insert into ggg values (7, 7); insert into ggg values (8, 8); insert into ggg values (9, 9); insert into ggg values (10, 10); -ERROR: no partition for partitioning key (seg1 localhost:12002 pid=29884) +ERROR: no partition for partitioning key (seg3 localhost:40000 pid=16873) select * from ggg order by 1, 2; id | a ----+--- @@ -1100,75 +1096,71 @@ distributed by (a) partition by hash(b,d) partitions 3 (partition a, partition b, partition c); -NOTICE: CREATE TABLE will create partition "ggg_1_prt_a" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_b" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_c" for table "ggg" +ERROR: Composite partition keys are not allowed insert into ggg values (1,1,1); +ERROR: relation "ggg" does not exist insert into ggg values (2,2,1); +ERROR: relation "ggg" does not exist insert into ggg values (1,3,1); +ERROR: relation "ggg" does not exist insert into ggg values (2,2,3); +ERROR: relation "ggg" does not exist insert into ggg values (1,4,5); +ERROR: relation "ggg" does not exist insert into ggg values (2,2,4); +ERROR: relation "ggg" does not exist insert into ggg values (1,5,6); +ERROR: relation "ggg" does not exist insert into ggg values (2,7,3); +ERROR: relation "ggg" does not exist insert into ggg values (1,'a','b'); +ERROR: relation "ggg" does not exist insert into ggg values (2,'c','c'); +ERROR: relation "ggg" does not exist select * from ggg order by 1, 2, 3; - a | b | d ----+---+--- - 1 | 1 | 1 - 1 | 3 | 1 - 1 | 4 | 5 - 1 | 5 | 6 - 1 | a | b - 2 | 2 | 1 - 2 | 2 | 3 - 2 | 2 | 4 - 2 | 7 | 3 - 2 | c | c -(10 rows) - +ERROR: relation "ggg" does not exist +LINE 1: select * from ggg order by 1, 2, 3; + ^ --select * from ggg_1_prt_a order by 1, 2, 3; --select * from ggg_1_prt_b order by 1, 2, 3; --select * from ggg_1_prt_c order by 1, 2, 3; drop table ggg cascade; +ERROR: table "ggg" does not exist -- use multiple cols of different types and without a partition spec create table ggg (a char(1), b varchar(2), d integer, e date) distributed by (a) partition by hash(b,d,e) partitions 3; -NOTICE: CREATE TABLE will create partition "ggg_1_prt_1" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_2" for table "ggg" -NOTICE: CREATE TABLE will create partition "ggg_1_prt_3" for table "ggg" +ERROR: Composite partition keys are not allowed insert into ggg values (1,1,1,date '2001-01-15'); +ERROR: relation "ggg" does not exist insert into ggg values (2,2,1,date '2001-01-15'); +ERROR: relation "ggg" does not exist insert into ggg values (1,3,1,date '2001-01-15'); +ERROR: relation "ggg" does not exist insert into ggg values (2,2,3,date '2001-01-15'); +ERROR: relation "ggg" does not exist insert into ggg values (1,4,5,date '2001-01-15'); +ERROR: relation "ggg" does not exist insert into ggg values (2,2,4,date '2001-01-15'); +ERROR: relation "ggg" does not exist insert into ggg values (1,5,6,date '2001-01-15'); +ERROR: relation "ggg" does not exist insert into ggg values (2,7,3,date '2001-01-15'); +ERROR: relation "ggg" does not exist insert into ggg values (1,'a',33,date '2001-01-15'); +ERROR: relation "ggg" does not exist insert into ggg values (2,'c',44,date '2001-01-15'); +ERROR: relation "ggg" does not exist select * from ggg order by 1, 2, 3, 4; - a | b | d | e ----+---+----+------------ - 1 | 1 | 1 | 01-15-2001 - 1 | 3 | 1 | 01-15-2001 - 1 | 4 | 5 | 01-15-2001 - 1 | 5 | 6 | 01-15-2001 - 1 | a | 33 | 01-15-2001 - 2 | 2 | 1 | 01-15-2001 - 2 | 2 | 3 | 01-15-2001 - 2 | 2 | 4 | 01-15-2001 - 2 | 7 | 3 | 01-15-2001 - 2 | c | 44 | 01-15-2001 -(10 rows) - +ERROR: relation "ggg" does not exist +LINE 1: select * from ggg order by 1, 2, 3, 4; + ^ --select * from ggg_1_prt_1 order by 1, 2, 3, 4; --select * from ggg_1_prt_2 order by 1, 2, 3, 4; --select * from ggg_1_prt_3 order by 1, 2, 3, 4; drop table ggg cascade; +ERROR: table "ggg" does not exist -- append only tests create table foz (i int, d date) with (appendonly = true) distributed by (i) partition by range (d) (start (date '2001-01-01') end (date '2005-01-01') @@ -1234,8 +1226,8 @@ COPY foz FROM stdin DELIMITER '|'; select * from foz_1_prt_1; i | d ---+------------ - 2 | 10-10-2001 1 | 01-02-2001 + 2 | 10-10-2001 (2 rows) select * from foz_1_prt_2; @@ -1273,8 +1265,8 @@ COPY foz FROM stdin DELIMITER '|'; select * from foz_1_prt_1; i | d ---+------------ - 1 | 01-02-2001 2 | 10-10-2001 + 1 | 01-02-2001 (2 rows) select * from foz_1_prt_2; @@ -1303,8 +1295,6 @@ drop table foz cascade; -- complain if create table as select (CTAS) CREATE TABLE rank1 (id int, rank int, year date, gender char(1)); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. create table rank2 as select * from rank1 DISTRIBUTED BY (id, gender, year) partition by list (gender) @@ -1354,32 +1344,31 @@ partition bb start (date '2008-01-01') end (date '2009-01-01') ); NOTICE: CREATE TABLE will create partition "hhh_1_prt_aa" for table "hhh" NOTICE: CREATE TABLE will create partition "hhh_1_prt_bb" for table "hhh" +ERROR: tablespace "dfs_default" does not support heap relation -- already exists alter table hhh add partition aa; -ERROR: partition "aa" of relation "hhh" already exists +ERROR: relation "hhh" does not exist -- no partition spec alter table hhh add partition cc; -ERROR: missing boundary specification +ERROR: relation "hhh" does not exist -- overlaps alter table hhh add partition cc start ('2008-01-01') end ('2010-01-01'); -ERROR: new partition overlaps existing partition "bb" +ERROR: relation "hhh" does not exist alter table hhh add partition cc end ('2008-01-01'); -ERROR: new partition overlaps existing partition +ERROR: relation "hhh" does not exist -- reversed (start > end) alter table hhh add partition cc start ('2010-01-01') end ('2009-01-01'); -ERROR: START greater than END for partition "cc" +ERROR: relation "hhh" does not exist -- works --alter table hhh add partition cc start ('2009-01-01') end ('2010-01-01'); alter table hhh add partition cc end ('2010-01-01'); -NOTICE: CREATE TABLE will create partition "hhh_1_prt_cc" for table "hhh" +ERROR: relation "hhh" does not exist -- works - anonymous partition MPP-3350 alter table hhh add partition end ('2010-02-01'); -NOTICE: CREATE TABLE will create partition "hhh_1_prt_r1081726955" for table "hhh" +ERROR: relation "hhh" does not exist -- MPP-3607 - ADD PARTITION with open intervals create table no_end1 (aa int, bb int) partition by range (bb) (partition foo start(3)); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'aa' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "no_end1_1_prt_foo" for table "no_end1" -- fail overlap alter table no_end1 add partition baz end (4); @@ -1395,8 +1384,6 @@ alter table no_end1 add partition baz2 end (1); NOTICE: CREATE TABLE will create partition "no_end1_1_prt_baz2" for table "no_end1" create table no_start1 (aa int, bb int) partition by range (bb) (partition foo end(3)); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'aa' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "no_start1_1_prt_foo" for table "no_start1" -- fail overlap (because next partition has no start) alter table no_start1 add partition baz start (2); @@ -1414,14 +1401,14 @@ select tablename, partitionlevel, parentpartitiontablename, partitionname, partitionrank, partitionboundary from pg_partitions where tablename = 'no_start1' or tablename = 'no_end1' order by tablename, partitionrank; - tablename | partitionlevel | parentpartitiontablename | partitionname | partitionrank | partitionboundary ------------+----------------+--------------------------+---------------+---------------+--------------------------------- - no_end1 | 0 | | baz2 | 1 | PARTITION baz2 END (1) - no_end1 | 0 | | baz | 2 | PARTITION baz START (2) END (3) - no_end1 | 0 | | foo | 3 | PARTITION foo START (3) - no_start1 | 0 | | foo | 1 | PARTITION foo END (3) - no_start1 | 0 | | baz | 2 | PARTITION baz START (3) END (4) - no_start1 | 0 | | baz2 | 3 | PARTITION baz2 START (5) + tablename | partitionlevel | parentpartitiontablename | partitionname | partitionrank | partitionboundary +-----------+----------------+--------------------------+---------------+---------------+-------------------------------------------------------- + no_end1 | 0 | | baz2 | 1 | PARTITION baz2 END (1) WITH (appendonly=true) + no_end1 | 0 | | baz | 2 | PARTITION baz START (2) END (3) WITH (appendonly=true) + no_end1 | 0 | | foo | 3 | PARTITION foo START (3) WITH (appendonly=true) + no_start1 | 0 | | foo | 1 | PARTITION foo END (3) WITH (appendonly=true) + no_start1 | 0 | | baz | 2 | PARTITION baz START (3) END (4) WITH (appendonly=true) + no_start1 | 0 | | baz2 | 3 | PARTITION baz2 START (5) WITH (appendonly=true) (6 rows) drop table no_end1; @@ -1430,8 +1417,6 @@ drop table no_start1; create table jjj (aa int, bb int) partition by hash(bb) (partition j1, partition j2); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'aa' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "jjj_1_prt_j1" for table "jjj" NOTICE: CREATE TABLE will create partition "jjj_1_prt_j2" for table "jjj" alter table jjj add default partition; @@ -1444,8 +1429,6 @@ create table jjj (aa int, bb date) partition by range(bb) (partition j1 end (date '2008-01-01'), partition j2 end (date '2009-01-01')); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'aa' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "jjj_1_prt_j1" for table "jjj" NOTICE: CREATE TABLE will create partition "jjj_1_prt_j2" for table "jjj" -- must have a name @@ -1465,8 +1448,6 @@ partition by range(bb) (partition j1 end (date '2008-01-01'), partition j2 end (date '2009-01-01'), default partition j3); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'aa' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "jjj_1_prt_j3" for table "jjj" NOTICE: CREATE TABLE will create partition "jjj_1_prt_j1" for table "jjj" NOTICE: CREATE TABLE will create partition "jjj_1_prt_j2" for table "jjj" @@ -1480,27 +1461,28 @@ ERROR: cannot add RANGE partition "j5" to relation "jjj" with DEFAULT partition HINT: need to SPLIT partition "j3" drop table jjj cascade; alter table hhh alter partition cc set tablespace foo_p; -ERROR: tablespace "foo_p" does not exist +ERROR: relation "hhh" does not exist alter table hhh alter partition aa set tablespace foo_p; -ERROR: tablespace "foo_p" does not exist +ERROR: relation "hhh" does not exist alter table hhh coalesce partition cc; -ERROR: cannot COALESCE PARTITION for relation "hhh" +ERROR: relation "hhh" does not exist alter table hhh coalesce partition aa; -ERROR: cannot COALESCE PARTITION for relation "hhh" +ERROR: relation "hhh" does not exist alter table hhh drop partition cc; +ERROR: relation "hhh" does not exist alter table hhh drop partition cc cascade; -ERROR: partition "cc" of relation "hhh" does not exist +ERROR: relation "hhh" does not exist alter table hhh drop partition cc restrict; -ERROR: partition "cc" of relation "hhh" does not exist +ERROR: relation "hhh" does not exist alter table hhh drop partition if exists cc; -NOTICE: partition "cc" of relation "hhh" does not exist, skipping +ERROR: relation "hhh" does not exist -- fail (mpp-3265) alter table hhh drop partition for (rank(0)); -ERROR: partition for rank 0 of relation "hhh" does not exist +ERROR: relation "hhh" does not exist alter table hhh drop partition for (rank(-55)); -ERROR: partition for rank -55 of relation "hhh" does not exist +ERROR: relation "hhh" does not exist alter table hhh drop partition for ('2001-01-01'); -ERROR: partition for value ('2001-01-01') of relation "hhh" does not exist +ERROR: relation "hhh" does not exist create table hhh_r1 (a char(1), b date, d char(3)) distributed by (a) partition by range (b) @@ -1637,13 +1619,13 @@ insert into rank values (10, 1, date '2005-05-15', 'F'); select * from rank ; id | rank | year | gender ----+------+------------+-------- + 9 | 1 | 04-15-2004 | F 7 | 1 | 02-15-2002 | F 10 | 1 | 05-15-2005 | F 5 | 1 | 05-15-2005 | M 2 | 1 | 02-15-2002 | M 6 | 1 | 01-15-2001 | F 8 | 1 | 03-15-2003 | F - 9 | 1 | 04-15-2004 | F 4 | 1 | 04-15-2004 | M 1 | 1 | 01-15-2001 | M 3 | 1 | 03-15-2003 | M @@ -1654,11 +1636,11 @@ NOTICE: dropped partition "boys" for relation "rank" and its children select * from rank ; id | rank | year | gender ----+------+------------+-------- - 7 | 1 | 02-15-2002 | F + 9 | 1 | 04-15-2004 | F 10 | 1 | 05-15-2005 | F + 7 | 1 | 02-15-2002 | F 6 | 1 | 01-15-2001 | F 8 | 1 | 03-15-2003 | F - 9 | 1 | 04-15-2004 | F (5 rows) -- MPP-3722: complain if for(value) matches the default partition @@ -1688,27 +1670,27 @@ alter table rank truncate partition for ('F'); NOTICE: truncated partition "girls" for relation "rank" and its children drop table rank cascade; alter table hhh exchange partition cc with table nosuchtable with validation; -ERROR: partition "cc" of relation "hhh" does not exist +ERROR: relation "hhh" does not exist alter table hhh exchange partition cc with table nosuchtable without validation; -ERROR: partition "cc" of relation "hhh" does not exist +ERROR: relation "hhh" does not exist alter table hhh exchange partition aa with table nosuchtable with validation; -ERROR: relation "nosuchtable" does not exist +ERROR: relation "hhh" does not exist alter table hhh exchange partition aa with table nosuchtable without validation; -ERROR: relation "nosuchtable" does not exist +ERROR: relation "hhh" does not exist alter table hhh merge partition cc, partition dd; -ERROR: partition "cc" of relation "hhh" does not exist +ERROR: relation "hhh" does not exist alter table hhh merge partition cc, partition dd into partition ee; -ERROR: partition "cc" of relation "hhh" does not exist +ERROR: relation "hhh" does not exist alter table hhh merge partition aa, partition dd into partition ee; -ERROR: partition "dd" of relation "hhh" does not exist +ERROR: relation "hhh" does not exist alter table hhh modify partition cc add values ('a'); -ERROR: partition "cc" of relation "hhh" does not exist +ERROR: relation "hhh" does not exist alter table hhh modify partition cc drop values ('a'); -ERROR: partition "cc" of relation "hhh" does not exist +ERROR: relation "hhh" does not exist alter table hhh modify partition aa add values ('a'); -ERROR: invalid use of LIST boundary specification in partition "aa" of type RANGE +ERROR: relation "hhh" does not exist alter table hhh modify partition aa drop values ('a'); -ERROR: invalid use of LIST boundary specification in partition "aa" of type RANGE +ERROR: relation "hhh" does not exist create table mmm_r1 (a char(1), b date, d char(3)) distributed by (a) partition by range (b) @@ -1746,229 +1728,222 @@ alter table mmm_r1 add partition bb START ('2007-03-03') END ('2007-03-20'); NOTICE: CREATE TABLE will create partition "mmm_r1_1_prt_bb" for table "mmm_r1" -- fail alter table mmm_r1 modify partition for (rank(-55)) start ('2007-03-02'); -ERROR: partition for rank -55 of relation "mmm_r1" does not exist +ERROR: ALTER TABLE ... MODIFY PARTITION is not supported alter table mmm_r1 modify partition for ('2001-01-01') start ('2007-03-02'); -ERROR: partition for value ('2001-01-01') of relation "mmm_r1" does not exist +ERROR: ALTER TABLE ... MODIFY PARTITION is not supported alter table mmm_r1 modify partition bb start ('2006-03-02'); -ERROR: cannot MODIFY RANGE partition "bb" for relation "mmm_r1" -- would overlap existing partition "aa_2" +ERROR: ALTER TABLE ... MODIFY PARTITION is not supported alter table mmm_r1 modify partition bb start ('2011-03-02'); -ERROR: START greater than END for partition "bb" +ERROR: ALTER TABLE ... MODIFY PARTITION is not supported alter table mmm_r1 modify partition bb end ('2006-03-02'); -ERROR: START greater than END for partition "bb" +ERROR: ALTER TABLE ... MODIFY PARTITION is not supported alter table mmm_r1 modify partition bb end ('2011-03-02'); -ERROR: cannot MODIFY RANGE partition "bb" for relation "mmm_r1" -- would overlap existing partition "aa_4" +ERROR: ALTER TABLE ... MODIFY PARTITION is not supported alter table mmm_r1 modify partition bb add values ('2011-03-02'); -ERROR: invalid use of LIST boundary specification in partition "bb" of type RANGE +ERROR: ALTER TABLE ... MODIFY PARTITION is not supported alter table mmm_r1 modify partition bb drop values ('2011-03-02'); -ERROR: invalid use of LIST boundary specification in partition "bb" of type RANGE +ERROR: ALTER TABLE ... MODIFY PARTITION is not supported --ok alter table mmm_r1 modify partition bb START ('2007-03-02') END ('2007-03-22'); +ERROR: ALTER TABLE ... MODIFY PARTITION is not supported alter table mmm_r1 modify partition bb START ('2007-03-01') END ('2007-03-31'); +ERROR: ALTER TABLE ... MODIFY PARTITION is not supported alter table mmm_r1 modify partition bb START ('2007-03-02') END ('2007-03-22'); +ERROR: ALTER TABLE ... MODIFY PARTITION is not supported -- with default alter table mmm_r1 add default partition def1; NOTICE: CREATE TABLE will create partition "mmm_r1_1_prt_def1" for table "mmm_r1" -- now fail alter table mmm_r1 modify partition bb START ('2007-03-01') END ('2007-03-31'); -ERROR: cannot MODIFY RANGE partition "bb" for relation "mmm_r1" to extend range -- would overlap DEFAULT partition "def1" -HINT: need to SPLIT partition "def1" +ERROR: ALTER TABLE ... MODIFY PARTITION is not supported -- still ok to reduce range alter table mmm_r1 modify partition bb START ('2007-03-09') END ('2007-03-10'); +ERROR: ALTER TABLE ... MODIFY PARTITION is not supported -- fail alter table mmm_l1 modify partition for (rank(1)) drop values ('k'); -ERROR: cannot find partition by RANK -- relation "mmm_l1" is LIST partitioned +ERROR: ALTER TABLE ... MODIFY PARTITION is not supported alter table mmm_l1 modify partition for ('j') drop values ('k'); -ERROR: partition for value ('j') of relation "mmm_l1" does not exist +ERROR: ALTER TABLE ... MODIFY PARTITION is not supported alter table mmm_l1 modify partition for ('a') drop values ('k'); -ERROR: cannot MODIFY LIST partition "aa" for relation "mmm_l1" -- DROP value not found +ERROR: ALTER TABLE ... MODIFY PARTITION is not supported alter table mmm_l1 modify partition for ('a') drop values ('e'); -ERROR: cannot MODIFY LIST partition "aa" for relation "mmm_l1" -- found DROP value in partition "bb" +ERROR: ALTER TABLE ... MODIFY PARTITION is not supported alter table mmm_l1 modify partition for ('a') add values ('e'); -ERROR: cannot MODIFY LIST partition "aa" for relation "mmm_l1" -- would overlap existing partition "bb" +ERROR: ALTER TABLE ... MODIFY PARTITION is not supported alter table mmm_l1 modify partition for ('a') START ('2007-03-09') ; -ERROR: invalid use of RANGE boundary specification in partition "aa" of type LIST +ERROR: ALTER TABLE ... MODIFY PARTITION is not supported --ok alter table mmm_l1 modify partition for ('a') drop values ('b'); +ERROR: ALTER TABLE ... MODIFY PARTITION is not supported alter table mmm_l1 modify partition for ('a') add values ('z'); +ERROR: ALTER TABLE ... MODIFY PARTITION is not supported -- with default alter table mmm_l1 add default partition def1; NOTICE: CREATE TABLE will create partition "mmm_l1_1_prt_def1" for table "mmm_l1" -- ok alter table mmm_l1 modify partition for ('a') drop values ('c'); +ERROR: ALTER TABLE ... MODIFY PARTITION is not supported -- now fail alter table mmm_l1 modify partition for ('a') add values ('y'); -ERROR: cannot MODIFY LIST partition "aa" for relation "mmm_l1" to ADD values -- would overlap DEFAULT partition "def1" -HINT: need to SPLIT partition "def1" +ERROR: ALTER TABLE ... MODIFY PARTITION is not supported -- XXX XXX: add some data drop table mmm_r1 cascade; drop table mmm_l1 cascade; alter table hhh rename partition cc to aa; -ERROR: partition "cc" of relation "hhh" does not exist +ERROR: relation "hhh" does not exist alter table hhh rename partition aa to aa; -ERROR: partition "aa" of relation "hhh" already exists +ERROR: relation "hhh" does not exist alter table hhh rename partition aa to "funky fresh"; +ERROR: relation "hhh" does not exist alter table hhh rename partition "funky fresh" to aa; +ERROR: relation "hhh" does not exist -- use FOR PARTITION VALUE (with implicate date conversion) alter table hhh rename partition for ('2007-01-01') to "funky fresh"; -NOTICE: renamed partition "aa" to "funky fresh" for relation "hhh" +ERROR: relation "hhh" does not exist alter table hhh rename partition for ('2007-01-01') to aa; -NOTICE: renamed partition "funky fresh" to "aa" for relation "hhh" +ERROR: relation "hhh" does not exist alter table hhh set subpartition template (); -ERROR: relation "hhh" does not have a level 1 subpartition template specification +ERROR: relation "hhh" does not exist alter table hhh split partition cc at ('a'); -ERROR: partition "cc" of relation "hhh" does not exist +ERROR: relation "hhh" does not exist alter table hhh split partition cc at ('a') into (partition gg, partition hh); -ERROR: partition "cc" of relation "hhh" does not exist +ERROR: relation "hhh" does not exist alter table hhh split partition aa at ('a'); -ERROR: invalid input syntax for type date: "a" +ERROR: relation "hhh" does not exist alter table hhh truncate partition cc; -ERROR: partition "cc" of relation "hhh" does not exist +ERROR: relation "hhh" does not exist alter table hhh truncate partition aa; +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2007-01-02', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2007-02-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2007-03-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2007-04-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2007-05-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2007-06-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2007-07-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2007-08-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2007-09-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2007-10-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2007-11-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2007-12-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2008-01-02', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2008-02-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2008-03-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2008-04-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2008-05-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2008-06-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2008-07-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2008-08-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2008-09-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2008-10-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2008-11-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2008-12-01', 'b'); +ERROR: relation "hhh" does not exist select * from hhh; - a | b | d ----+------------+----- - a | 01-02-2007 | b - a | 02-01-2007 | b - a | 03-01-2007 | b - a | 04-01-2007 | b - a | 05-01-2007 | b - a | 06-01-2007 | b - a | 07-01-2007 | b - a | 08-01-2007 | b - a | 09-01-2007 | b - a | 10-01-2007 | b - a | 11-01-2007 | b - a | 12-01-2007 | b - a | 01-02-2008 | b - a | 02-01-2008 | b - a | 03-01-2008 | b - a | 04-01-2008 | b - a | 05-01-2008 | b - a | 06-01-2008 | b - a | 07-01-2008 | b - a | 08-01-2008 | b - a | 09-01-2008 | b - a | 10-01-2008 | b - a | 11-01-2008 | b - a | 12-01-2008 | b -(24 rows) - +ERROR: relation "hhh" does not exist +LINE 1: select * from hhh; + ^ alter table hhh truncate partition aa; +ERROR: relation "hhh" does not exist select * from hhh; - a | b | d ----+------------+----- - a | 01-02-2008 | b - a | 02-01-2008 | b - a | 03-01-2008 | b - a | 04-01-2008 | b - a | 05-01-2008 | b - a | 06-01-2008 | b - a | 07-01-2008 | b - a | 08-01-2008 | b - a | 09-01-2008 | b - a | 10-01-2008 | b - a | 11-01-2008 | b - a | 12-01-2008 | b -(12 rows) - +ERROR: relation "hhh" does not exist +LINE 1: select * from hhh; + ^ alter table hhh truncate partition bb; +ERROR: relation "hhh" does not exist select * from hhh; - a | b | d ----+---+--- -(0 rows) - +ERROR: relation "hhh" does not exist +LINE 1: select * from hhh; + ^ insert into hhh values('a', date '2007-01-02', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2007-02-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2007-03-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2007-04-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2007-05-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2007-06-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2007-07-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2007-08-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2007-09-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2007-10-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2007-11-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2007-12-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2008-01-02', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2008-02-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2008-03-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2008-04-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2008-05-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2008-06-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2008-07-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2008-08-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2008-09-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2008-10-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2008-11-01', 'b'); +ERROR: relation "hhh" does not exist insert into hhh values('a', date '2008-12-01', 'b'); +ERROR: relation "hhh" does not exist select * from hhh; - a | b | d ----+------------+----- - a | 01-02-2007 | b - a | 02-01-2007 | b - a | 03-01-2007 | b - a | 04-01-2007 | b - a | 05-01-2007 | b - a | 06-01-2007 | b - a | 07-01-2007 | b - a | 08-01-2007 | b - a | 09-01-2007 | b - a | 10-01-2007 | b - a | 11-01-2007 | b - a | 12-01-2007 | b - a | 01-02-2008 | b - a | 02-01-2008 | b - a | 03-01-2008 | b - a | 04-01-2008 | b - a | 05-01-2008 | b - a | 06-01-2008 | b - a | 07-01-2008 | b - a | 08-01-2008 | b - a | 09-01-2008 | b - a | 10-01-2008 | b - a | 11-01-2008 | b - a | 12-01-2008 | b -(24 rows) - +ERROR: relation "hhh" does not exist +LINE 1: select * from hhh; + ^ -- truncate child partitions recursively truncate table hhh; +ERROR: relation "hhh" does not exist select * from hhh; - a | b | d ----+---+--- -(0 rows) - +ERROR: relation "hhh" does not exist +LINE 1: select * from hhh; + ^ drop table hhh cascade; +ERROR: table "hhh" does not exist -- default partitions -- hash partitions cannot have default partitions create table jjj (aa int, bb int) partition by hash(bb) (partition j1, partition j2, default partition j3); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'aa' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. ERROR: invalid use of DEFAULT partition for partition "j3" of type HASH LINE 3: (partition j1, partition j2, default partition j3); ^ @@ -1978,8 +1953,6 @@ partition by range(bb) (partition j1 end (date '2008-01-01'), partition j2 end (date '2009-01-01'), default partition j3 end (date '2010-01-01')); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'aa' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. ERROR: invalid use of boundary specification for DEFAULT partition "j3" LINE 5: default partition j3 end (date '2010-01-01')); ^ @@ -1990,8 +1963,6 @@ partition by range(bb) partition j2 end (date '2009-01-01'), default partition j3, default partition j4); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'aa' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. ERROR: multiple default partitions are not allowed LINE 6: default partition j4); ^ @@ -2036,7 +2007,7 @@ insert into d values(1, 10); insert into d values(1, 11); insert into d values(1, 55); insert into d values(1, 70); -ERROR: no partition for partitioning key (seg0 localhost:12001 pid=29883) +ERROR: no partition for partitioning key (seg2 localhost:40000 pid=16872) select * from d; i | j ---+---- @@ -2069,8 +2040,6 @@ drop table d cascade; -- check for NULL support -- hash create table d (i int, j int) partition by hash(j) partitions 4; -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "d_1_prt_1" for table "d" NOTICE: CREATE TABLE will create partition "d_1_prt_2" for table "d" NOTICE: CREATE TABLE will create partition "d_1_prt_3" for table "d" @@ -2083,8 +2052,6 @@ create table d (i int, j int) partition by list(j) (partition a values(1, 2, NULL), partition b values(3, 4) ); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "d_1_prt_a" for table "d" NOTICE: CREATE TABLE will create partition "d_1_prt_b" for table "d" insert into d values(1, 1); @@ -2112,21 +2079,17 @@ drop table d cascade; -- Reject NULL values create table d (i int, j int) partition by range(j) (partition a start (1) end(10), partition b start(11) end(20)); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "d_1_prt_a" for table "d" NOTICE: CREATE TABLE will create partition "d_1_prt_b" for table "d" insert into d values (1, 1); insert into d values (1, 2); insert into d values (1, NULL); -ERROR: no partition for partitioning key (seg0 localhost:12001 pid=29883) +ERROR: no partition for partitioning key (seg3 localhost:40000 pid=16912) drop table d cascade; -- allow NULLs into the default partition create table d (i int, j int) partition by range(j) (partition a start (1) end(10), partition b start(11) end(20), default partition abc); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "d_1_prt_abc" for table "d" NOTICE: CREATE TABLE will create partition "d_1_prt_a" for table "d" NOTICE: CREATE TABLE will create partition "d_1_prt_b" for table "d" @@ -2146,39 +2109,33 @@ partition by list(b, c) (partition a values(('1', '2'), ('3', '4')), partition b values(('100', '20')), partition c values(('1000', '1001'), ('1001', '1002'), ('1003', '1004'))); -NOTICE: CREATE TABLE will create partition "d_1_prt_a" for table "d" -NOTICE: CREATE TABLE will create partition "d_1_prt_b" for table "d" -NOTICE: CREATE TABLE will create partition "d_1_prt_c" for table "d" +ERROR: Composite partition keys are not allowed insert into d values(1, 1, 2); +ERROR: relation "d" does not exist insert into d values(1, 3, 4); +ERROR: relation "d" does not exist insert into d values(1, 100, 20); +ERROR: relation "d" does not exist insert into d values(1, 100, 2000); -ERROR: no partition for partitioning key (seg0 localhost:12001 pid=29883) +ERROR: relation "d" does not exist insert into d values(1, '1000', '1001'), (1, '1001', '1002'), (1, '1003', '1004'); +ERROR: relation "d" does not exist insert into d values(1, 100, NULL); -ERROR: no partition for partitioning key (seg0 localhost:12001 pid=29883) +ERROR: relation "d" does not exist select * from d_1_prt_a; - a | b | c ----+---+--- - 1 | 1 | 2 - 1 | 3 | 4 -(2 rows) - +ERROR: relation "d_1_prt_a" does not exist +LINE 1: select * from d_1_prt_a; + ^ select * from d_1_prt_b; - a | b | c ----+-----+---- - 1 | 100 | 20 -(1 row) - +ERROR: relation "d_1_prt_b" does not exist +LINE 1: select * from d_1_prt_b; + ^ select * from d_1_prt_c; - a | b | c ----+------+------ - 1 | 1000 | 1001 - 1 | 1001 | 1002 - 1 | 1003 | 1004 -(3 rows) - +ERROR: relation "d_1_prt_c" does not exist +LINE 1: select * from d_1_prt_c; + ^ drop table d cascade; +ERROR: table "d" does not exist -- test multi value range partitioning create table b (i int, j date) distributed by (i) partition by range (i, j) @@ -2187,9 +2144,7 @@ partition by range (i, j) start(15, '2010-01-01') end (30, '2011-01-01'), start(1, '2011-01-01') end (100, '2012-01-01') ); -ERROR: too many columns for RANGE partition -- only one column is allowed. -LINE 2: partition by range (i, j) - ^ +ERROR: Composite partition keys are not allowed -- should work insert into b values(1, '2008-06-11'); ERROR: relation "b" does not exist @@ -2235,9 +2190,7 @@ start(2000.99, '2007-01-01 00:00:00', 'BBB') start(4000.95, '2007-01-01 00:00:00', 'AAA') end (7000.95, '2007-02-02 15:00:00', 'BBB') ); -ERROR: too many columns for RANGE partition -- only one column is allowed. -LINE 3: partition by range(n, t, s) - ^ +ERROR: Composite partition keys are not allowed -- should work insert into b values(1, 2000.99, '2007-01-01 00:00:00', 'AAA'); ERROR: relation "b" does not exist @@ -2341,6 +2294,7 @@ NOTICE: building index for child partition "region_1_prt_p1_2_prt_sp2_3_prt_2" NOTICE: building index for child partition "region_1_prt_p1_2_prt_sp2_3_prt_1" NOTICE: building index for child partition "region_1_prt_p1_2_prt_sp3_3_prt_1" NOTICE: building index for child partition "region_1_prt_p1_2_prt_sp3_3_prt_2" +ERROR: Cannot support create index statement yet copy region from stdin with delimiter '|'; -- Test indexes set enable_seqscan to off; @@ -2400,7 +2354,6 @@ select * from region where r_regionkey = '7'; -- tables since we cannot enfoce them. But since this insert maps to a -- single definitive partition, we can detect it. insert into region values(7, 'abc', 'def'); -ERROR: duplicate key violates unique constraint "region_pkey_1_prt_p1_2_prt_sp1_3_prt_1" (seg0 localhost:12001 pid=29883) drop table region; -- exchange -- 1) test all sanity checking @@ -2629,8 +2582,8 @@ create table bar_p(i int, j int) distributed by (i); create table baz_p(i int, j int) distributed by (i); create rule bar_baz as on insert to bar_p do instead insert into baz_p values(NEW.i, NEW.j); +ERROR: Cannot support create rule statement yet alter table foo_p exchange partition for(rank(2)) with table bar_p; -ERROR: cannot exchange table which has rules defined on it drop table foo_p, bar_p, baz_p; -- Should fail: A constraint on bar_p isn't shared by all the parts. -- Allowing this would make an inconsistent partitioned table. Note @@ -2708,8 +2661,6 @@ create role part_role; NOTICE: resource queue required -- using default resource queue "pg_default" create table foo_p (i int) partition by range(i) (start(1) end(10) every(1)); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "foo_p_1_prt_1" for table "foo_p" NOTICE: CREATE TABLE will create partition "foo_p_1_prt_2" for table "foo_p" NOTICE: CREATE TABLE will create partition "foo_p_1_prt_3" for table "foo_p" @@ -2720,8 +2671,6 @@ NOTICE: CREATE TABLE will create partition "foo_p_1_prt_7" for table "foo_p" NOTICE: CREATE TABLE will create partition "foo_p_1_prt_8" for table "foo_p" NOTICE: CREATE TABLE will create partition "foo_p_1_prt_9" for table "foo_p" create table bar_p (i int); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. grant select on foo_p to part_role; revoke all on bar_p from part_role; select has_table_privilege('part_role', 'foo_p_1_prt_6'::regclass, 'select'); @@ -2755,8 +2704,6 @@ drop role part_role; -- validation create table foo_p (i int) partition by range(i) (start(1) end(10) every(1)); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "foo_p_1_prt_1" for table "foo_p" NOTICE: CREATE TABLE will create partition "foo_p_1_prt_2" for table "foo_p" NOTICE: CREATE TABLE will create partition "foo_p_1_prt_3" for table "foo_p" @@ -2767,13 +2714,11 @@ NOTICE: CREATE TABLE will create partition "foo_p_1_prt_7" for table "foo_p" NOTICE: CREATE TABLE will create partition "foo_p_1_prt_8" for table "foo_p" NOTICE: CREATE TABLE will create partition "foo_p_1_prt_9" for table "foo_p" create table bar_p (i int); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. insert into bar_p values(6); insert into bar_p values(100); -- should fail alter table foo_p exchange partition for(rank(6)) with table bar_p; -ERROR: exchange table contains a row which violates the partitioning specification of "foo_p" (seg1 localhost:12002 pid=29892) +ERROR: check constraint "foo_p_1_prt_6_check" is violated by some row (seg3 localhost:40000 pid=16937) alter table foo_p exchange partition for(rank(6)) with table bar_p without validation; select * from foo_p; @@ -2839,7 +2784,7 @@ alter table foo_p exchange partition for(rank(6)) with table bar_p; -- but we do now, so the following tries to insert a value that can't go -- in part 6. insert into bar_p values(10, 10); -ERROR: new row for relation "bar_p" violates check constraint "foo_p_1_prt_6_check" (seg1 localhost:12002 pid=29892) +ERROR: new row for relation "bar_p" violates check constraint "foo_p_1_prt_6_check" (seg2 localhost:40000 pid=16936) drop table foo_p; select * from bar_p; i | j @@ -2875,10 +2820,10 @@ alter table foo_p exchange partition for(rank(6)) with table bar_p; select * from foo_p; i | j ---+--- - 2 | 1 - 6 | 6 1 | 1 3 | 1 + 2 | 1 + 6 | 6 (4 rows) drop table bar_p; @@ -2896,21 +2841,20 @@ NOTICE: CREATE TABLE will create partition "foo_p_1_prt_6" for table "foo_p" NOTICE: CREATE TABLE will create partition "foo_p_1_prt_7" for table "foo_p" NOTICE: CREATE TABLE will create partition "foo_p_1_prt_8" for table "foo_p" NOTICE: CREATE TABLE will create partition "foo_p_1_prt_9" for table "foo_p" +ERROR: tablespace "dfs_default" does not support heap relation create table bar_p(i int, j int) with(appendonly = true) distributed by (i); insert into foo_p values(1, 1), (2, 1), (3, 2); +ERROR: relation "foo_p" does not exist insert into bar_p values(6, 6); alter table foo_p exchange partition for(rank(6)) with table bar_p; +ERROR: relation "foo_p" does not exist select * from foo_p; - i | j ----+--- - 1 | 1 - 3 | 2 - 2 | 1 - 6 | 6 -(4 rows) - +ERROR: relation "foo_p" does not exist +LINE 1: select * from foo_p; + ^ drop table bar_p; drop table foo_p; +ERROR: table "foo_p" does not exist -- exchange AO with AO create table foo_p (i int, j int) with(appendonly = true) distributed by (i) partition by range(j) @@ -3142,8 +3086,6 @@ partition p3 end(20) inclusive, partition p4 start(20) exclusive , partition p5 start(22) end(25) ); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 's_suppkey' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "supplier2_1_prt_p1" for table "supplier2" NOTICE: CREATE TABLE will create partition "supplier2_1_prt_p2" for table "supplier2" NOTICE: CREATE TABLE will create partition "supplier2_1_prt_p3" for table "supplier2" @@ -3226,8 +3168,6 @@ create table foo_p (i int) partition by range (i) partition p4 start('5981976') end('5994376') inclusive, partition p5 end('6000001') ); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "foo_p_1_prt_p1" for table "foo_p" NOTICE: CREATE TABLE will create partition "foo_p_1_prt_p2" for table "foo_p" NOTICE: CREATE TABLE will create partition "foo_p_1_prt_p3" for table "foo_p" @@ -3251,7 +3191,7 @@ select parname, parruleord, pg_get_expr(parrangestart, parchildrelid, false) as insert into foo_p values(5994400); insert into foo_p values(1); insert into foo_p values(6000002); -ERROR: no partition for partitioning key (seg1 localhost:12002 pid=29892) +ERROR: no partition for partitioning key (seg0 localhost:40000 pid=16934) insert into foo_p values(5994376); drop table foo_p; create table foo_p (i int) @@ -3259,8 +3199,6 @@ partition by range(i) (partition p1 start(1) end(5), partition p2 start(10), partition p3 end(10) exclusive); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "foo_p_1_prt_p1" for table "foo_p" NOTICE: CREATE TABLE will create partition "foo_p_1_prt_p3" for table "foo_p" NOTICE: CREATE TABLE will create partition "foo_p_1_prt_p2" for table "foo_p" @@ -3283,8 +3221,6 @@ partition by range(i) (partition p1 start(1) end(5), partition p2 start(10) exclusive, partition p3 end(10) inclusive); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "foo_p_1_prt_p1" for table "foo_p" NOTICE: CREATE TABLE will create partition "foo_p_1_prt_p3" for table "foo_p" NOTICE: CREATE TABLE will create partition "foo_p_1_prt_p2" for table "foo_p" @@ -3312,17 +3248,15 @@ partition by list(i) (partition p1 values(1, 2, 3) with (appendonly = true), partition p2 values(4) ); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "foo_p_1_prt_p1" for table "foo_p" NOTICE: CREATE TABLE will create partition "foo_p_1_prt_p2" for table "foo_p" copy foo_p from stdin; select * from foo_p; i --- + 2 1 3 - 2 4 (4 rows) @@ -3347,35 +3281,31 @@ partition by list(i) (partition p1 values(1, 2, 3) with (appendonly = false), partition p2 values(4) ); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "foo_p_1_prt_p1" for table "foo_p" NOTICE: CREATE TABLE will create partition "foo_p_1_prt_p2" for table "foo_p" +ERROR: tablespace "dfs_default" does not support heap relation copy foo_p from stdin; +ERROR: relation "foo_p" does not exist +1 +2 +3 +4 +\. +invalid command \. select * from foo_p; - i ---- - 1 - 3 - 2 - 4 -(4 rows) - +ERROR: syntax error at or near "1" +LINE 1: 1 + ^ select * from foo_p_1_prt_p1; - i ---- - 1 - 3 - 2 -(3 rows) - +ERROR: relation "foo_p_1_prt_p1" does not exist +LINE 1: select * from foo_p_1_prt_p1; + ^ select * from foo_p_1_prt_p2; - i ---- - 4 -(1 row) - +ERROR: relation "foo_p_1_prt_p2" does not exist +LINE 1: select * from foo_p_1_prt_p2; + ^ drop table foo_p; +ERROR: table "foo_p" does not exist -- MPP-3283 CREATE TABLE PARTSUPP ( PS_PARTKEY INTEGER, @@ -3393,8 +3323,6 @@ partition p1 start('1') end('10001') every(5000) (subpartition sp1 start('1') end('200001') every(66666) ) ); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'ps_partkey' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "partsupp_1_prt_p1_1" for table "partsupp" NOTICE: CREATE TABLE will create partition "partsupp_1_prt_p1_2" for table "partsupp" NOTICE: CREATE TABLE will create partition "partsupp_1_prt_p1_1_2_prt_sp1_1" for table "partsupp_1_prt_p1_1" @@ -3450,8 +3378,6 @@ partition by range (l_commitdate) ( partition p1 start('1992-01-31') end('1998-11-01') every(interval '20 months') ); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'l_orderkey' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "lineitem_1_prt_p1_1" for table "lineitem" NOTICE: CREATE TABLE will create partition "lineitem_1_prt_p1_2" for table "lineitem" NOTICE: CREATE TABLE will create partition "lineitem_1_prt_p1_3" for table "lineitem" @@ -3478,16 +3404,12 @@ select parname, parruleord, pg_get_expr(parrangestart, parchildrelid, false) as drop table lineitem; -- Make sure ADD creates dependencies create table i (i int) partition by range(i) (start (1) end(3) every(1)); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "i_1_prt_1" for table "i" NOTICE: CREATE TABLE will create partition "i_1_prt_2" for table "i" alter table i add partition foo2 start(40) end (50); NOTICE: CREATE TABLE will create partition "i_1_prt_foo2" for table "i" drop table i; create table i (i int) partition by range(i) (start (1) end(3) every(1)); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "i_1_prt_1" for table "i" NOTICE: CREATE TABLE will create partition "i_1_prt_2" for table "i" alter table i add partition foo2 start(40) end (50); @@ -3497,23 +3419,21 @@ drop table i; -- dumpability of partition info create table i5 (i int) partition by RANGE(i) (start(1) exclusive end(10) inclusive); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "i5_1_prt_1" for table "i5" select tablename, partitiontablename, partitionboundary from pg_partitions where tablename = 'i5'; - tablename | partitiontablename | partitionboundary ------------+--------------------+---------------------------------------- - i5 | i5_1_prt_1 | START (1) EXCLUSIVE END (10) INCLUSIVE + tablename | partitiontablename | partitionboundary +-----------+--------------------+--------------------------------------------------------------- + i5 | i5_1_prt_1 | START (1) EXCLUSIVE END (10) INCLUSIVE WITH (appendonly=true) (1 row) select pg_get_partition_def('i5'::regclass, true); - pg_get_partition_def --------------------------------------------------- - PARTITION BY RANGE(i) - ( - START (1) EXCLUSIVE END (10) INCLUSIVE + pg_get_partition_def +------------------------------------------------------------------------- + PARTITION BY RANGE(i) + ( + START (1) EXCLUSIVE END (10) INCLUSIVE WITH (appendonly=true) ) (1 row) @@ -3534,8 +3454,6 @@ partition p1 start('1') end('10001') every(5000) (subpartition sp1 start('1') end('200001') every(66666) ) ); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'ps_partkey' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "partsupp_1_prt_p1_1" for table "partsupp" NOTICE: CREATE TABLE will create partition "partsupp_1_prt_p1_2" for table "partsupp" NOTICE: CREATE TABLE will create partition "partsupp_1_prt_p1_1_2_prt_sp1_1" for table "partsupp_1_prt_p1_1" @@ -3565,50 +3483,50 @@ NOTICE: CREATE TABLE will create partition "partsupp_1_prt_p1_2_2_prt_sp1_4_3_p select tablename, partitiontablename, partitionboundary from pg_partitions where tablename = 'partsupp'; - tablename | partitiontablename | partitionboundary ------------+-----------------------------------------+--------------------------------------------------------------- - partsupp | partsupp_1_prt_p1_1 | PARTITION p1_1 START (1) END (5001) EVERY (5000) - partsupp | partsupp_1_prt_p1_2 | PARTITION p1_2 START (5001) END (10001) EVERY (5000) - partsupp | partsupp_1_prt_p1_1_2_prt_sp1_1 | SUBPARTITION sp1_1 START (1) END (66667) EVERY (66666) - partsupp | partsupp_1_prt_p1_1_2_prt_sp1_2 | SUBPARTITION sp1_2 START (66667) END (133333) EVERY (66666) - partsupp | partsupp_1_prt_p1_1_2_prt_sp1_3 | SUBPARTITION sp1_3 START (133333) END (199999) EVERY (66666) - partsupp | partsupp_1_prt_p1_1_2_prt_sp1_4 | SUBPARTITION sp1_4 START (199999) END (200001) EVERY (66666) - partsupp | partsupp_1_prt_p1_2_2_prt_sp1_1 | SUBPARTITION sp1_1 START (1) END (66667) EVERY (66666) - partsupp | partsupp_1_prt_p1_2_2_prt_sp1_2 | SUBPARTITION sp1_2 START (66667) END (133333) EVERY (66666) - partsupp | partsupp_1_prt_p1_2_2_prt_sp1_3 | SUBPARTITION sp1_3 START (133333) END (199999) EVERY (66666) - partsupp | partsupp_1_prt_p1_2_2_prt_sp1_4 | SUBPARTITION sp1_4 START (199999) END (200001) EVERY (66666) - partsupp | partsupp_1_prt_p1_1_2_prt_sp1_1_3_prt_1 | START (1::numeric) END (501::numeric) EVERY (500::numeric) - partsupp | partsupp_1_prt_p1_1_2_prt_sp1_1_3_prt_2 | START (501::numeric) END (1001::numeric) EVERY (500::numeric) - partsupp | partsupp_1_prt_p1_1_2_prt_sp1_2_3_prt_1 | START (1::numeric) END (501::numeric) EVERY (500::numeric) - partsupp | partsupp_1_prt_p1_1_2_prt_sp1_2_3_prt_2 | START (501::numeric) END (1001::numeric) EVERY (500::numeric) - partsupp | partsupp_1_prt_p1_1_2_prt_sp1_3_3_prt_1 | START (1::numeric) END (501::numeric) EVERY (500::numeric) - partsupp | partsupp_1_prt_p1_1_2_prt_sp1_3_3_prt_2 | START (501::numeric) END (1001::numeric) EVERY (500::numeric) - partsupp | partsupp_1_prt_p1_1_2_prt_sp1_4_3_prt_1 | START (1::numeric) END (501::numeric) EVERY (500::numeric) - partsupp | partsupp_1_prt_p1_1_2_prt_sp1_4_3_prt_2 | START (501::numeric) END (1001::numeric) EVERY (500::numeric) - partsupp | partsupp_1_prt_p1_2_2_prt_sp1_1_3_prt_1 | START (1::numeric) END (501::numeric) EVERY (500::numeric) - partsupp | partsupp_1_prt_p1_2_2_prt_sp1_1_3_prt_2 | START (501::numeric) END (1001::numeric) EVERY (500::numeric) - partsupp | partsupp_1_prt_p1_2_2_prt_sp1_2_3_prt_1 | START (1::numeric) END (501::numeric) EVERY (500::numeric) - partsupp | partsupp_1_prt_p1_2_2_prt_sp1_2_3_prt_2 | START (501::numeric) END (1001::numeric) EVERY (500::numeric) - partsupp | partsupp_1_prt_p1_2_2_prt_sp1_3_3_prt_1 | START (1::numeric) END (501::numeric) EVERY (500::numeric) - partsupp | partsupp_1_prt_p1_2_2_prt_sp1_3_3_prt_2 | START (501::numeric) END (1001::numeric) EVERY (500::numeric) - partsupp | partsupp_1_prt_p1_2_2_prt_sp1_4_3_prt_1 | START (1::numeric) END (501::numeric) EVERY (500::numeric) - partsupp | partsupp_1_prt_p1_2_2_prt_sp1_4_3_prt_2 | START (501::numeric) END (1001::numeric) EVERY (500::numeric) + tablename | partitiontablename | partitionboundary +-----------+-----------------------------------------+-------------------------------------------------------------------------------------- + partsupp | partsupp_1_prt_p1_1 | PARTITION p1_1 START (1) END (5001) EVERY (5000) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_2 | PARTITION p1_2 START (5001) END (10001) EVERY (5000) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_1_2_prt_sp1_1 | SUBPARTITION sp1_1 START (1) END (66667) EVERY (66666) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_1_2_prt_sp1_2 | SUBPARTITION sp1_2 START (66667) END (133333) EVERY (66666) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_1_2_prt_sp1_3 | SUBPARTITION sp1_3 START (133333) END (199999) EVERY (66666) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_1_2_prt_sp1_4 | SUBPARTITION sp1_4 START (199999) END (200001) EVERY (66666) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_2_2_prt_sp1_1 | SUBPARTITION sp1_1 START (1) END (66667) EVERY (66666) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_2_2_prt_sp1_2 | SUBPARTITION sp1_2 START (66667) END (133333) EVERY (66666) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_2_2_prt_sp1_3 | SUBPARTITION sp1_3 START (133333) END (199999) EVERY (66666) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_2_2_prt_sp1_4 | SUBPARTITION sp1_4 START (199999) END (200001) EVERY (66666) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_1_2_prt_sp1_1_3_prt_1 | START (1::numeric) END (501::numeric) EVERY (500::numeric) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_1_2_prt_sp1_1_3_prt_2 | START (501::numeric) END (1001::numeric) EVERY (500::numeric) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_1_2_prt_sp1_2_3_prt_1 | START (1::numeric) END (501::numeric) EVERY (500::numeric) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_1_2_prt_sp1_2_3_prt_2 | START (501::numeric) END (1001::numeric) EVERY (500::numeric) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_1_2_prt_sp1_3_3_prt_1 | START (1::numeric) END (501::numeric) EVERY (500::numeric) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_1_2_prt_sp1_3_3_prt_2 | START (501::numeric) END (1001::numeric) EVERY (500::numeric) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_1_2_prt_sp1_4_3_prt_1 | START (1::numeric) END (501::numeric) EVERY (500::numeric) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_1_2_prt_sp1_4_3_prt_2 | START (501::numeric) END (1001::numeric) EVERY (500::numeric) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_2_2_prt_sp1_1_3_prt_1 | START (1::numeric) END (501::numeric) EVERY (500::numeric) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_2_2_prt_sp1_1_3_prt_2 | START (501::numeric) END (1001::numeric) EVERY (500::numeric) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_2_2_prt_sp1_2_3_prt_1 | START (1::numeric) END (501::numeric) EVERY (500::numeric) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_2_2_prt_sp1_2_3_prt_2 | START (501::numeric) END (1001::numeric) EVERY (500::numeric) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_2_2_prt_sp1_3_3_prt_1 | START (1::numeric) END (501::numeric) EVERY (500::numeric) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_2_2_prt_sp1_3_3_prt_2 | START (501::numeric) END (1001::numeric) EVERY (500::numeric) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_2_2_prt_sp1_4_3_prt_1 | START (1::numeric) END (501::numeric) EVERY (500::numeric) WITH (appendonly=true) + partsupp | partsupp_1_prt_p1_2_2_prt_sp1_4_3_prt_2 | START (501::numeric) END (1001::numeric) EVERY (500::numeric) WITH (appendonly=true) (26 rows) select pg_get_partition_def('partsupp'::regclass, true); - pg_get_partition_def ---------------------------------------------------------------------------------------- - PARTITION BY RANGE(ps_suppkey) - SUBPARTITION BY RANGE(ps_partkey) - SUBPARTITION BY RANGE(ps_supplycost) - ( - PARTITION p1 START (1) END (10001) EVERY (5000) - ( - SUBPARTITION sp1 START (1) END (200001) EVERY (66666) - ( - START (1::numeric) END (1001::numeric) EVERY (500::numeric) - ) - ) + pg_get_partition_def +-------------------------------------------------------------------------------------------------------------- + PARTITION BY RANGE(ps_suppkey) + SUBPARTITION BY RANGE(ps_partkey) + SUBPARTITION BY RANGE(ps_supplycost) + ( + PARTITION p1 START (1) END (10001) EVERY (5000) WITH (appendonly=true) + ( + SUBPARTITION sp1 START (1) END (200001) EVERY (66666) WITH (appendonly=true) + ( + START (1::numeric) END (1001::numeric) EVERY (500::numeric) WITH (appendonly=true) + ) + ) ) (1 row) @@ -3618,8 +3536,6 @@ create table i5 (i int, g text) partition by list(g) subpartition by hash(i) subpartitions 3 (partition p1 values('foo', 'bar'), partition p2 values('foz') ); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "i5_1_prt_p1" for table "i5" NOTICE: CREATE TABLE will create partition "i5_1_prt_p2" for table "i5" NOTICE: CREATE TABLE will create partition "i5_1_prt_p1_2_prt_1" for table "i5_1_prt_p1" @@ -3631,30 +3547,30 @@ NOTICE: CREATE TABLE will create partition "i5_1_prt_p2_2_prt_3" for table "i5_ select tablename, partitiontablename, partitionboundary from pg_partitions where tablename = 'i5'; - tablename | partitiontablename | partitionboundary ------------+---------------------+----------------------------------- - i5 | i5_1_prt_p1 | PARTITION p1 VALUES('foo', 'bar') - i5 | i5_1_prt_p2 | PARTITION p2 VALUES('foz') - i5 | i5_1_prt_p1_2_prt_1 | - i5 | i5_1_prt_p1_2_prt_2 | - i5 | i5_1_prt_p1_2_prt_3 | - i5 | i5_1_prt_p2_2_prt_1 | - i5 | i5_1_prt_p2_2_prt_2 | - i5 | i5_1_prt_p2_2_prt_3 | + tablename | partitiontablename | partitionboundary +-----------+---------------------+---------------------------------------------------------- + i5 | i5_1_prt_p1 | PARTITION p1 VALUES('foo', 'bar') WITH (appendonly=true) + i5 | i5_1_prt_p2 | PARTITION p2 VALUES('foz') WITH (appendonly=true) + i5 | i5_1_prt_p1_2_prt_1 | WITH (appendonly=true) + i5 | i5_1_prt_p1_2_prt_2 | WITH (appendonly=true) + i5 | i5_1_prt_p1_2_prt_3 | WITH (appendonly=true) + i5 | i5_1_prt_p2_2_prt_1 | WITH (appendonly=true) + i5 | i5_1_prt_p2_2_prt_2 | WITH (appendonly=true) + i5 | i5_1_prt_p2_2_prt_3 | WITH (appendonly=true) (8 rows) select pg_get_partition_def('i5'::regclass, true); - pg_get_partition_def ------------------------------------------------------ - PARTITION BY LIST(g) - SUBPARTITION BY HASH(i) SUBPARTITIONS 3 - ( - PARTITION p1 VALUES('foo', 'bar') - ( - ), - PARTITION p2 VALUES('foz') - ( - ) + pg_get_partition_def +--------------------------------------------------------------------- + PARTITION BY LIST(g) + SUBPARTITION BY HASH(i) SUBPARTITIONS 3 + ( + PARTITION p1 VALUES('foo', 'bar') WITH (appendonly=true) + ( + ), + PARTITION p2 VALUES('foz') WITH (appendonly=true) + ( + ) ) (1 row) @@ -4093,34 +4009,24 @@ drop table rank3 ; -- **END** ALTER TABLE ALTER PARTITION tests -- Test casting create table f (i int) partition by range (i) (start(1::int) end(10::int)); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "f_1_prt_1" for table "f" drop table f; create table f (i bigint) partition by range (i) (start(1::int8) end(1152921504606846976::int8) every(576460752303423488)); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "f_1_prt_1" for table "f" NOTICE: CREATE TABLE will create partition "f_1_prt_2" for table "f" drop table f; create table f (n numeric(20, 2)) partition by range(n) (start(1::bigint) end(10000::bigint)); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'n' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "f_1_prt_1" for table "f" drop table f; create table f (n numeric(20, 2)) partition by range(n) (start(1::bigint) end(10000::text)); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'n' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "f_1_prt_1" for table "f" drop table f; --should fail. bool -> numeric makes no sense create table f (n numeric(20, 2)) partition by range(n) (start(1::bigint) end('f'::bool)); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'n' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. ERROR: cannot coerce RANGE partition parameter (false) to column type (numeric) -- see that grant and revoke cascade to children create role part_role; @@ -4128,8 +4034,6 @@ NOTICE: resource queue required -- using default resource queue "pg_default" create table granttest (i int, j int) partition by range(i) subpartition by list(j) subpartition template (values(1, 2, 3)) (start(1) end(4) every(1)); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "granttest_1_prt_1" for table "granttest" NOTICE: CREATE TABLE will create partition "granttest_1_prt_2" for table "granttest" NOTICE: CREATE TABLE will create partition "granttest_1_prt_3" for table "granttest" @@ -4404,13 +4308,9 @@ NOTICE: CREATE TABLE will create partition "partsupp_1_prt_p1_4_2_prt_2_3_prt_2 drop table partsupp; -- Accept negative values trivially: create table partition_g (i int) partition by range(i) (start((-1)) end(10)); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "partition_g_1_prt_1" for table "partition_g" drop table partition_g; create table partition_g (i int) partition by range(i) (start(-1) end(10)); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "partition_g_1_prt_1" for table "partition_g" drop table partition_g; CREATE TABLE orders ( @@ -4514,8 +4414,6 @@ WITH (appendonly=true, checksum=true, blocksize=368640, compresslevel=9) PARTITI ) ) ); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'o_orderkey' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "orders_1_prt_p1_1" for table "orders" NOTICE: CREATE TABLE will create partition "orders_1_prt_p1_2" for table "orders" NOTICE: CREATE TABLE will create partition "orders_1_prt_p1_3" for table "orders" @@ -4574,19 +4472,13 @@ NOTICE: CREATE TABLE will create partition "orders_1_prt_p1_5_2_prt_sp2_3_prt_4 drop table orders; -- grammar bug: MPP-3361 create table i2 (i int) partition by range(i) (start(-2::int) end(20)); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "i2_1_prt_1" for table "i2" drop table i2; create table i2 (i int) partition by range(i) (start((-2)::int) end(20)); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "i2_1_prt_1" for table "i2" drop table i2; create table i2 (i int) partition by range(i) (start(cast ((-2)::bigint as int)) end(20)); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "i2_1_prt_1" for table "i2" drop table i2; CREATE TABLE partsupp ( @@ -4601,8 +4493,6 @@ CREATE TABLE partsupp ( , PARTITION p1 START (1::numeric) END (1001::numeric) ); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'ps_partkey' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "partsupp_1_prt_newpart" for table "partsupp" NOTICE: CREATE TABLE will create partition "partsupp_1_prt_p1" for table "partsupp" drop table partsupp; @@ -4617,21 +4507,18 @@ partition p2 start('11') end('15') inclusive WITH (checksum=false,appendonly=tru partition p3 start('15') exclusive end('19'), partition p4 start('19') WITH (compresslevel=8,appendonly=true,checksum=false,blocksize=884736), partition p5 start('20') ); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'n_nationkey' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "tmp_nation_1_prt_p1" for table "tmp_nation" NOTICE: CREATE TABLE will create partition "tmp_nation_1_prt_p2" for table "tmp_nation" NOTICE: CREATE TABLE will create partition "tmp_nation_1_prt_p3" for table "tmp_nation" NOTICE: CREATE TABLE will create partition "tmp_nation_1_prt_p4" for table "tmp_nation" NOTICE: CREATE TABLE will create partition "tmp_nation_1_prt_p5" for table "tmp_nation" delete from tmp_nation; +ERROR: Delete append-only table statement not supported yet drop table tmp_nation; -- SPLIT tests -- basic sanity tests. All should pass. create table k (i int) partition by range(i) (start(1) end(10) every(2), default partition mydef); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "k_1_prt_mydef" for table "k" NOTICE: CREATE TABLE will create partition "k_1_prt_2" for table "k" NOTICE: CREATE TABLE will create partition "k_1_prt_3" for table "k" @@ -4645,14 +4532,12 @@ ERROR: AT clause cannot be used when splitting a default RANGE partition drop table k; create table j (i int) partition by list(i) (partition a values(1, 2, 3, 4), partition b values(5, 6, 7, 8)); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "j_1_prt_a" for table "j" NOTICE: CREATE TABLE will create partition "j_1_prt_b" for table "j" insert into j select i from generate_series(1, 8) i; alter table j split partition for(1) at (2, 3) into (partition fa, partition fb); -NOTICE: exchanged partition "a" of relation "j" with relation "pg_temp_6076623" +NOTICE: exchanged partition "a" of relation "j" with relation "pg_temp_215113" NOTICE: dropped partition "a" for relation "j" NOTICE: CREATE TABLE will create partition "j_1_prt_fa" for table "j" NOTICE: CREATE TABLE will create partition "j_1_prt_fb" for table "j" @@ -4666,24 +4551,24 @@ select * from j_1_prt_fa; select * from j_1_prt_fb; i --- - 3 2 + 3 (2 rows) alter table j split partition for(5) at (6); -NOTICE: exchanged partition "b" of relation "j" with relation "pg_temp_6076623" +NOTICE: exchanged partition "b" of relation "j" with relation "pg_temp_215113" NOTICE: dropped partition "b" for relation "j" -NOTICE: CREATE TABLE will create partition "j_1_prt_r968951306" for table "j" -NOTICE: CREATE TABLE will create partition "j_1_prt_r1280956441" for table "j" +NOTICE: CREATE TABLE will create partition "j_1_prt_r1661772419" for table "j" +NOTICE: CREATE TABLE will create partition "j_1_prt_r54112706" for table "j" select * from j; i --- 1 + 4 + 2 3 5 7 - 4 - 2 8 6 (8 rows) @@ -4694,8 +4579,6 @@ ERROR: AT clause parameter is not a member of the target partition specificatio drop table j; create table k (i int) partition by range(i) (start(1) end(10) every(2), default partition mydef); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "k_1_prt_mydef" for table "k" NOTICE: CREATE TABLE will create partition "k_1_prt_2" for table "k" NOTICE: CREATE TABLE will create partition "k_1_prt_3" for table "k" @@ -4711,8 +4594,6 @@ drop table k; -- should work create table k (i int) partition by range(i) (start(1) end(10) every(2), default partition mydef); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "k_1_prt_mydef" for table "k" NOTICE: CREATE TABLE will create partition "k_1_prt_2" for table "k" NOTICE: CREATE TABLE will create partition "k_1_prt_3" for table "k" @@ -4722,7 +4603,7 @@ NOTICE: CREATE TABLE will create partition "k_1_prt_6" for table "k" insert into k select i from generate_series(1, 30) i; alter table k split default partition start(15) end(20) into (partition mydef, partition foo); -NOTICE: exchanged partition "mydef" of relation "k" with relation "pg_temp_6077293" +NOTICE: exchanged partition "mydef" of relation "k" with relation "pg_temp_215220" NOTICE: dropped partition "mydef" for relation "k" NOTICE: CREATE TABLE will create partition "k_1_prt_foo" for table "k" NOTICE: CREATE TABLE will create partition "k_1_prt_mydef" for table "k" @@ -4730,28 +4611,28 @@ select * from k_1_prt_foo; i ---- 15 - 17 - 19 16 + 17 18 + 19 (5 rows) alter table k split default partition start(22) exclusive end(25) inclusive into (partition bar, partition mydef); -NOTICE: exchanged partition "mydef" of relation "k" with relation "pg_temp_6077293" +NOTICE: exchanged partition "mydef" of relation "k" with relation "pg_temp_215220" NOTICE: dropped partition "mydef" for relation "k" NOTICE: CREATE TABLE will create partition "k_1_prt_bar" for table "k" NOTICE: CREATE TABLE will create partition "k_1_prt_mydef" for table "k" select * from k_1_prt_bar; i ---- - 24 23 + 24 25 (3 rows) alter table k split partition bar at (23) into (partition baz, partition foz); -NOTICE: exchanged partition "bar" of relation "k" with relation "pg_temp_6077293" +NOTICE: exchanged partition "bar" of relation "k" with relation "pg_temp_215220" NOTICE: dropped partition "bar" for relation "k" NOTICE: CREATE TABLE will create partition "k_1_prt_baz" for table "k" NOTICE: CREATE TABLE will create partition "k_1_prt_foz" for table "k" @@ -4775,8 +4656,6 @@ drop table k; -- Test errors for default handling create table k (i int) partition by range(i) (start(1) end(2), default partition mydef); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "k_1_prt_mydef" for table "k" NOTICE: CREATE TABLE will create partition "k_1_prt_2" for table "k" alter table k split partition mydef at (25) into (partition foo, partition @@ -4785,8 +4664,6 @@ ERROR: AT clause cannot be used when splitting a default RANGE partition drop table k; create table k (i int) partition by list(i) (values(1), values(2), default partition mydef); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "k_1_prt_1" for table "k" NOTICE: CREATE TABLE will create partition "k_1_prt_2" for table "k" NOTICE: CREATE TABLE will create partition "k_1_prt_mydef" for table "k" @@ -4798,8 +4675,6 @@ drop table k; CREATE TABLE myINT2_TBL(q1 int2) partition by range (q1) (start (1) end (3) every (1)); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'q1' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "myint2_tbl_1_prt_1" for table "myint2_tbl" NOTICE: CREATE TABLE will create partition "myint2_tbl_1_prt_2" for table "myint2_tbl" insert into myint2_tbl values(1), (2); @@ -4808,16 +4683,15 @@ drop table myint2_tbl; -- between partitions create table v (i int, j int) partition by range(j) (start(1) end(5) every(2)); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "v_1_prt_1" for table "v" NOTICE: CREATE TABLE will create partition "v_1_prt_2" for table "v" insert into v values(1, 1) ; -- should work update v set j = 2; +ERROR: Update append-only table statement not supported yet -- should fail update v set j = 3; -ERROR: moving tuple from partition "v_1_prt_1" to partition "v_1_prt_2" not supported (seg0 localhost:12001 pid=29891) +ERROR: Update append-only table statement not supported yet drop table v; -- test AO seg totals -- @@ -4839,8 +4713,6 @@ end; $$ language plpgsql volatile; create table ao_p (i int) with (appendonly = true) partition by range(i) (start(1) end(10) every(1)); -NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Greenplum Database data distribution key for this table. -HINT: The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen are the optimal data distribution key to minimize skew. NOTICE: CREATE TABLE will create partition "ao_p_1_prt_1" for table "ao_p" NOTICE: CREATE TABLE will create partition "ao_p_1_prt_2" for table "ao_p" NOTICE: CREATE TABLE will create partition "ao_p_1_prt_3" for table "ao_p" @@ -4859,12 +4731,12 @@ from pg_partitions where tablename = 'ao_p'; ao_p_1_prt_1 | 1 ao_p_1_prt_2 | 1 ao_p_1_prt_3 | 1 - ao_p_1_prt_4 | - ao_p_1_prt_5 | - ao_p_1_prt_6 |
<TRUNCATED>
