You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@hawq.apache.org by pa...@apache.org on 2016/12/19 02:23:45 UTC
[1/4] incubator-hawq git commit: HAWQ-870. Allocate target's tuple
table slot in PortalHeapMemory during split partition
Repository: incubator-hawq
Updated Branches:
refs/heads/master 111655e36 -> b32e56c50
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/b32e56c5/src/test/regress/sql/partition.sql
----------------------------------------------------------------------
diff --git a/src/test/regress/sql/partition.sql b/src/test/regress/sql/partition.sql
index 9fa893c..6f8ba2c 100644
--- a/src/test/regress/sql/partition.sql
+++ b/src/test/regress/sql/partition.sql
@@ -4924,3 +4924,21 @@ insert into t values(1,2,'a');
select * from t;
-- END MPP-19105
reset optimizer_nestloop_factor;
+
+-- Test split default partition while per tuple memory context is reset
+-- Origin GPDB PR: https://github.com/greenplum-db/gpdb/pull/866
+drop table if exists test_split_part cascade;
+
+CREATE TABLE test_split_part ( log_id int NOT NULL, f_array int[] NOT NULL)
+DISTRIBUTED BY (log_id)
+PARTITION BY RANGE(log_id)
+(
+ START (1::int) END (100::int) EVERY (5),
+ PARTITION "old" START (101::int) END (201::int),
+ DEFAULT PARTITION other_log_ids
+);
+
+insert into test_split_part (log_id , f_array) select id, '{10}' from generate_series(1,1000) id;
+
+ALTER TABLE test_split_part SPLIT DEFAULT PARTITION START (201) INCLUSIVE END (301) EXCLUSIVE INTO (PARTITION "new", DEFAULT PARTITION);
+
[4/4] incubator-hawq git commit: HAWQ-870. Allocate target's tuple
table slot in PortalHeapMemory during split partition
Posted by pa...@apache.org.
HAWQ-870. Allocate target's tuple table slot in PortalHeapMemory during split partition
merge GPDB split-partiton optimization
Origin GPDB PR:
greenplum-db/gpdb#866
Project: http://git-wip-us.apache.org/repos/asf/incubator-hawq/repo
Commit: http://git-wip-us.apache.org/repos/asf/incubator-hawq/commit/b32e56c5
Tree: http://git-wip-us.apache.org/repos/asf/incubator-hawq/tree/b32e56c5
Diff: http://git-wip-us.apache.org/repos/asf/incubator-hawq/diff/b32e56c5
Branch: refs/heads/master
Commit: b32e56c501a1db2b91481c88192e65bdb8fbdfd2
Parents: 111655e
Author: interma <in...@outlook.com>
Authored: Fri Dec 16 15:16:07 2016 +0800
Committer: Paul Guo <pa...@gmail.com>
Committed: Mon Dec 19 10:21:29 2016 +0800
----------------------------------------------------------------------
src/backend/commands/tablecmds.c | 29 +-
src/test/regress/expected/partition.out | 2669 ++++++------------
.../regress/expected/partition_optimizer.out | 2666 ++++++-----------
src/test/regress/sql/partition.sql | 18 +
4 files changed, 1697 insertions(+), 3685 deletions(-)
----------------------------------------------------------------------
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/b32e56c5/src/backend/commands/tablecmds.c
----------------------------------------------------------------------
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 975e121..fc35124 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -15304,6 +15304,10 @@ split_rows(Relation intoa, Relation intob, Relation temprel, List *splits, int s
rrib->ri_partSlot = MakeSingleTupleTableSlot(RelationGetDescr(intob));
map_part_attrs(temprel, intoa, &rria->ri_partInsertMap, true);
map_part_attrs(temprel, intob, &rrib->ri_partInsertMap, true);
+ Assert(NULL != rria->ri_RelationDesc);
+ rria->ri_resultSlot = MakeSingleTupleTableSlot(rria->ri_RelationDesc->rd_att);
+ Assert(NULL != rrib->ri_RelationDesc);
+ rrib->ri_resultSlot = MakeSingleTupleTableSlot(rrib->ri_RelationDesc->rd_att);
/* constr might not be defined if this is a default partition */
if (intoa->rd_att->constr && intoa->rd_att->constr->num_check)
@@ -15575,21 +15579,18 @@ split_rows(Relation intoa, Relation intob, Relation temprel, List *splits, int s
ExecDropSingleTupleTableSlot(rrib->ri_partSlot);
/*
- * We may have created "cached" version of our target result tuple table slot
- * inside reconstructMatchingTupleSlot. Drop any such slots.
+ * We created our target result tuple table slots upfront.
+ * We can drop them now.
*/
- if (NULL != rria->ri_resultSlot)
- {
- Assert(NULL != rria->ri_resultSlot->tts_tupleDescriptor);
- ExecDropSingleTupleTableSlot(rria->ri_resultSlot);
- rria->ri_resultSlot = NULL;
- }
- if (NULL != rrib->ri_resultSlot)
- {
- Assert(NULL != rrib->ri_resultSlot->tts_tupleDescriptor);
- ExecDropSingleTupleTableSlot(rrib->ri_resultSlot);
- rrib->ri_resultSlot = NULL;
- }
+ Assert(NULL != rria->ri_resultSlot);
+ Assert(NULL != rria->ri_resultSlot->tts_tupleDescriptor);
+ ExecDropSingleTupleTableSlot(rria->ri_resultSlot);
+ rria->ri_resultSlot = NULL;
+
+ Assert(NULL != rrib->ri_resultSlot);
+ Assert(NULL != rrib->ri_resultSlot->tts_tupleDescriptor);
+ ExecDropSingleTupleTableSlot(rrib->ri_resultSlot);
+ rrib->ri_resultSlot = NULL;
if (rria->ri_partInsertMap)
pfree(rria->ri_partInsertMap);
[3/4] incubator-hawq git commit: HAWQ-870. Allocate target's tuple
table slot in PortalHeapMemory during split partition
Posted by pa...@apache.org.
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>
[2/4] incubator-hawq git commit: HAWQ-870. Allocate target's tuple
table slot in PortalHeapMemory during split partition
Posted by pa...@apache.org.
http://git-wip-us.apache.org/repos/asf/incubator-hawq/blob/b32e56c5/src/test/regress/expected/partition_optimizer.out
----------------------------------------------------------------------
diff --git a/src/test/regress/expected/partition_optimizer.out b/src/test/regress/expected/partition_optimizer.out
index e6a8fb9..b74930e 100644
--- a/src/test/regress/expected/partition_optimizer.out
+++ b/src/test/regress/expected/partition_optimizer.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 qpvm4:40001 pid=6507)
+ERROR: no partition for partitioning key (seg3 localhost:40000 pid=18460)
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')
@@ -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_r1667423841" 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,16 +1619,16 @@ insert into rank values (10, 1, date '2005-05-15', 'F');
select * from rank ;
id | rank | year | gender
----+------+------------+--------
- 1 | 1 | 01-15-2001 | M
- 3 | 1 | 03-15-2003 | M
- 4 | 1 | 04-15-2004 | M
- 6 | 1 | 01-15-2001 | F
- 8 | 1 | 03-15-2003 | F
- 9 | 1 | 04-15-2004 | F
- 2 | 1 | 02-15-2002 | M
- 5 | 1 | 05-15-2005 | M
7 | 1 | 02-15-2002 | F
+ 9 | 1 | 04-15-2004 | 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
+ 4 | 1 | 04-15-2004 | M
+ 1 | 1 | 01-15-2001 | M
+ 3 | 1 | 03-15-2003 | M
(10 rows)
alter table rank DROP partition boys restrict;
@@ -1654,11 +1636,11 @@ NOTICE: dropped partition "boys" for relation "rank" and its children
select * from rank ;
id | rank | year | gender
----+------+------------+--------
- 6 | 1 | 01-15-2001 | F
- 8 | 1 | 03-15-2003 | F
+ 10 | 1 | 05-15-2005 | F
9 | 1 | 04-15-2004 | F
7 | 1 | 02-15-2002 | F
- 10 | 1 | 05-15-2005 | F
+ 6 | 1 | 01-15-2001 | F
+ 8 | 1 | 03-15-2003 | 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);
^
@@ -2013,8 +1984,8 @@ insert into foz values(2, '2010-04-01');
select * from foz;
i | d
---+------------
- 2 | 04-01-2010
1 | 04-01-2003
+ 2 | 04-01-2010
(2 rows)
select * from foz_1_prt_dsf;
@@ -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 qpvm4:40000 pid=6505)
+ERROR: no partition for partitioning key (seg2 localhost:40000 pid=18459)
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 qpvm4:40000 pid=6505)
+ERROR: no partition for partitioning key (seg3 localhost:40000 pid=18499)
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 qpvm4:40000 pid=6505)
+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 qpvm4:40000 pid=6505)
+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 qpvm4:40000 pid=6505)
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 qpvm4:40001 pid=10156)
+ERROR: check constraint "foo_p_1_prt_6_check" is violated by some row (seg3 localhost:40000 pid=18526)
alter table foo_p exchange partition for(rank(6)) with table bar_p without
validation;
select * from foo_p;
@@ -2839,8 +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: One or more assertions failed (seg1 antova-mbp.local:40011 pid=36151)
-DETAIL: Check constraint foo_p_1_prt_6_check for table bar_p was violated
+ERROR: new row for relation "bar_p" violates check constraint "foo_p_1_prt_6_check" (seg2 localhost:40000 pid=18525)
drop table foo_p;
select * from bar_p;
i | j
@@ -2876,10 +2820,10 @@ alter table foo_p exchange partition for(rank(6)) with table bar_p;
select * from foo_p;
i | j
---+---
- 6 | 6
- 2 | 1
1 | 1
3 | 1
+ 2 | 1
+ 6 | 6
(4 rows)
drop table bar_p;
@@ -2897,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)
@@ -2932,8 +2875,8 @@ alter table foo_p exchange partition for(rank(6)) with table bar_p;
select * from foo_p;
i | j
---+---
- 1 | 2
3 | 4
+ 1 | 2
2 | 3
6 | 6
(4 rows)
@@ -3143,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"
@@ -3227,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"
@@ -3252,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 qpvm4:40001 pid=10156)
+ERROR: no partition for partitioning key (seg0 localhost:40000 pid=18523)
insert into foo_p values(5994376);
drop table foo_p;
create table foo_p (i int)
@@ -3260,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"
@@ -3284,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"
@@ -3313,8 +3248,6 @@ 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;
@@ -3322,9 +3255,9 @@ select * from foo_p;
i
---
2
- 4
1
3
+ 4
(4 rows)
select * from foo_p_1_prt_p1;
@@ -3348,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,
@@ -3394,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"
@@ -3451,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"
@@ -3479,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);
@@ -3498,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)
@@ -3535,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"
@@ -3566,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)
@@ -3619,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"
@@ -3632,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)
@@ -4094,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;
@@ -4129,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"
@@ -4405,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 (
@@ -4515,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"
@@ -4575,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 (
@@ -4602,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;
@@ -4618,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"
@@ -4646,22 +4532,20 @@ 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_8472976"
+NOTICE: exchanged partition "a" of relation "j" with relation "pg_temp_229583"
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"
select * from j_1_prt_fa;
i
---
- 4
1
+ 4
(2 rows)
select * from j_1_prt_fb;
@@ -4672,21 +4556,21 @@ select * from j_1_prt_fb;
(2 rows)
alter table j split partition for(5) at (6);
-NOTICE: exchanged partition "b" of relation "j" with relation "pg_temp_8472976"
+NOTICE: exchanged partition "b" of relation "j" with relation "pg_temp_229583"
NOTICE: dropped partition "b" for relation "j"
-NOTICE: CREATE TABLE will create partition "j_1_prt_r1898830594" for table "j"
-NOTICE: CREATE TABLE will create partition "j_1_prt_r370203341" for table "j"
+NOTICE: CREATE TABLE will create partition "j_1_prt_r1376022457" for table "j"
+NOTICE: CREATE TABLE will create partition "j_1_prt_r1474158092" for table "j"
select * from j;
i
---
+ 1
4
2
- 8
- 6
- 1
3
5
7
+ 8
+ 6
(8 rows)
-- should fail
@@ -4695,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"
@@ -4712,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"
@@ -4723,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_8473694"
+NOTICE: exchanged partition "mydef" of relation "k" with relation "pg_temp_229690"
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"
@@ -4731,15 +4611,15 @@ 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_8473694"
+NOTICE: exchanged partition "mydef" of relation "k" with relation "pg_temp_229690"
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"
@@ -4747,12 +4627,12 @@ select * from k_1_prt_bar;
i
----
23
- 25
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_8473694"
+NOTICE: exchanged partition "bar" of relation "k" with relation "pg_temp_229690"
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"
@@ -4776,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
@@ -4786,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"
@@ -4799,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);
@@ -4809,15 +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: Update append-only table statement not supported yet
drop table v;
-- test AO seg totals
--
@@ -4835,12 +4709,10 @@ begin
execute 'select tupcount from pg_aoseg.' || aosegname into tupcount;
end if;
return tupcount;
-end; $$ language plpgsql volatile READS SQL DATA;
+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:
<TRUNCATED>