Google Git
Sign in
apache / cloudberry / refs/heads/cbdb-postgres-merge / . / src / test / regress / expected / bfv_partition.out
blob: ec180b9855fa35e0961bc526009db6e772e3cb29 [file] [log] [blame]
create schema bfv_partition;
set search_path=bfv_partition;
show autovacuum;
autovacuum
------------
off
(1 row)
--
-- Tests if it is using casting comparator for partition selector with compatible types
--
-- SETUP
CREATE TABLE TIMESTAMP_MONTH_rangep_STARTINCL (i1 int, f2 timestamp)
partition by range (f2)
(
start ('2000-01-01'::timestamp) INCLUSIVE
end (date '2001-01-01'::timestamp) EXCLUSIVE
every ('1 month'::interval)
);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i1' as the Apache Cloudberry 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 TIMESTAMP_MONTH_rangep_STARTEXCL (i1 int, f2 timestamp)
partition by range (f2)
(
start ('2000-01-02'::timestamp)
end (date '2001-01-01'::timestamp) INCLUSIVE
every ('1 month'::interval)
);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i1' as the Apache Cloudberry 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 TIMESTAMP_MONTH_listp (i1 int, f2 timestamp)
partition by list (f2)
(
partition jan1 values ('2000-01-01'::timestamp),
partition jan2 values ('2000-01-02'::timestamp),
partition jan3 values ('2000-01-03'::timestamp),
partition jan4 values ('2000-01-04'::timestamp),
partition jan5 values ('2000-01-05'::timestamp)
);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i1' as the Apache Cloudberry 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.
-- TEST
-- Middle of a middle range
INSERT INTO TIMESTAMP_MONTH_rangep_STARTINCL values (1, '2000-07-16');
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = '2000-07-16';
i1 | f2
----+--------------------------
1 | Sun Jul 16 00:00:00 2000
(1 row)
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_TIMESTAMP('2000-07-16', 'YYYY-MM-DD');
i1 | f2
----+--------------------------
1 | Sun Jul 16 00:00:00 2000
(1 row)
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_DATE('2000-07-16', 'YYYY-MM-DD');
i1 | f2
----+--------------------------
1 | Sun Jul 16 00:00:00 2000
(1 row)
-- Beginning of the first range
INSERT INTO TIMESTAMP_MONTH_rangep_STARTINCL values (2, '2000-01-01');
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = '2000-01-01';
i1 | f2
----+--------------------------
2 | Sat Jan 01 00:00:00 2000
(1 row)
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_TIMESTAMP('2000-01-01', 'YYYY-MM-DD');
i1 | f2
----+--------------------------
2 | Sat Jan 01 00:00:00 2000
(1 row)
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_DATE('2000-01-01', 'YYYY-MM-DD');
i1 | f2
----+--------------------------
2 | Sat Jan 01 00:00:00 2000
(1 row)
INSERT INTO TIMESTAMP_MONTH_rangep_STARTINCL values (3, '2000-01-02');
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = '2000-01-02';
i1 | f2
----+--------------------------
3 | Sun Jan 02 00:00:00 2000
(1 row)
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_TIMESTAMP('2000-01-02', 'YYYY-MM-DD');
i1 | f2
----+--------------------------
3 | Sun Jan 02 00:00:00 2000
(1 row)
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_DATE('2000-01-02', 'YYYY-MM-DD');
i1 | f2
----+--------------------------
3 | Sun Jan 02 00:00:00 2000
(1 row)
-- End of the last range
INSERT INTO TIMESTAMP_MONTH_rangep_STARTINCL values (4, '2000-12-31');
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = '2000-12-31';
i1 | f2
----+--------------------------
4 | Sun Dec 31 00:00:00 2000
(1 row)
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_TIMESTAMP('2000-12-31', 'YYYY-MM-DD');
i1 | f2
----+--------------------------
4 | Sun Dec 31 00:00:00 2000
(1 row)
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_DATE('2000-12-31', 'YYYY-MM-DD');
i1 | f2
----+--------------------------
4 | Sun Dec 31 00:00:00 2000
(1 row)
INSERT INTO TIMESTAMP_MONTH_rangep_STARTINCL values (5, '2001-01-01'); -- should fail, no such partition
ERROR: no partition of relation "timestamp_month_rangep_startincl" found for row (seg2 127.0.0.1:40002 pid=28965)
DETAIL: Partition key of the failing row contains (f2) = (Mon Jan 01 00:00:00 2001).
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = '2001-01-01';
i1 | f2
----+----
(0 rows)
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_TIMESTAMP('2001-01-01', 'YYYY-MM-DD');
i1 | f2
----+----
(0 rows)
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTINCL WHERE f2 = TO_DATE('2001-01-01', 'YYYY-MM-DD');
i1 | f2
----+----
(0 rows)
-- Range partitioning: START EXCLUSIVE, END INCLUSIVE
-- Middle of a middle range
INSERT INTO TIMESTAMP_MONTH_rangep_STARTEXCL values (1, '2000-07-16');
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = '2000-07-16';
i1 | f2
----+--------------------------
1 | Sun Jul 16 00:00:00 2000
(1 row)
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_TIMESTAMP('2000-07-16', 'YYYY-MM-DD');
i1 | f2
----+--------------------------
1 | Sun Jul 16 00:00:00 2000
(1 row)
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_DATE('2000-07-16', 'YYYY-MM-DD');
i1 | f2
----+--------------------------
1 | Sun Jul 16 00:00:00 2000
(1 row)
-- Beginning of the first range
INSERT INTO TIMESTAMP_MONTH_rangep_STARTEXCL values (2, '2000-01-01'); -- should fail, no such partition
ERROR: no partition of relation "timestamp_month_rangep_startexcl" found for row (seg0 127.0.1.1:7002 pid=19596)
DETAIL: Partition key of the failing row contains (f2) = (Sat Jan 01 00:00:00 2000).
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = '2000-01-01';
i1 | f2
----+----
(0 rows)
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_TIMESTAMP('2000-01-01', 'YYYY-MM-DD');
i1 | f2
----+----
(0 rows)
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_DATE('2000-01-01', 'YYYY-MM-DD');
i1 | f2
----+----
(0 rows)
INSERT INTO TIMESTAMP_MONTH_rangep_STARTEXCL values (3, '2000-01-02');
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = '2000-01-02';
i1 | f2
----+--------------------------
3 | Sun Jan 02 00:00:00 2000
(1 row)
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_TIMESTAMP('2000-01-02', 'YYYY-MM-DD');
i1 | f2
----+--------------------------
3 | Sun Jan 02 00:00:00 2000
(1 row)
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_DATE('2000-01-02', 'YYYY-MM-DD');
i1 | f2
----+--------------------------
3 | Sun Jan 02 00:00:00 2000
(1 row)
-- End of the last range
INSERT INTO TIMESTAMP_MONTH_rangep_STARTEXCL values (4, '2000-12-31');
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = '2000-12-31';
i1 | f2
----+--------------------------
4 | Sun Dec 31 00:00:00 2000
(1 row)
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_TIMESTAMP('2000-12-31', 'YYYY-MM-DD');
i1 | f2
----+--------------------------
4 | Sun Dec 31 00:00:00 2000
(1 row)
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_DATE('2000-12-31', 'YYYY-MM-DD');
i1 | f2
----+--------------------------
4 | Sun Dec 31 00:00:00 2000
(1 row)
INSERT INTO TIMESTAMP_MONTH_rangep_STARTEXCL values (5, '2001-01-01');
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = '2001-01-01';
i1 | f2
----+--------------------------
5 | Mon Jan 01 00:00:00 2001
(1 row)
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_TIMESTAMP('2001-01-01', 'YYYY-MM-DD');
i1 | f2
----+--------------------------
5 | Mon Jan 01 00:00:00 2001
(1 row)
SELECT * FROM TIMESTAMP_MONTH_rangep_STARTEXCL WHERE f2 = TO_DATE('2001-01-01', 'YYYY-MM-DD');
i1 | f2
----+--------------------------
5 | Mon Jan 01 00:00:00 2001
(1 row)
-- List partitioning
INSERT INTO TIMESTAMP_MONTH_listp values (1, '2000-01-03');
SELECT * FROM TIMESTAMP_MONTH_listp WHERE f2 = '2000-01-03';
i1 | f2
----+--------------------------
1 | Mon Jan 03 00:00:00 2000
(1 row)
SELECT * FROM TIMESTAMP_MONTH_listp WHERE f2 = TO_TIMESTAMP('2000-01-03', 'YYYY-MM-DD');
i1 | f2
----+--------------------------
1 | Mon Jan 03 00:00:00 2000
(1 row)
SELECT * FROM TIMESTAMP_MONTH_listp WHERE f2 = TO_DATE('2000-01-03', 'YYYY-MM-DD');
i1 | f2
----+--------------------------
1 | Mon Jan 03 00:00:00 2000
(1 row)
--
-- Data Engineer can see partition key in psql
--
-- SETUP
CREATE TABLE T26002_T1 (empid int, departmentid int, year int, region varchar(20))
DISTRIBUTED BY (empid)
PARTITION BY RANGE (year)
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE (
SUBPARTITION usa VALUES (('usa')),
SUBPARTITION europe VALUES (('europe')),
SUBPARTITION asia VALUES (('asia')),
DEFAULT SUBPARTITION other_regions)
( START (2012) END (2015) EVERY (3),
DEFAULT PARTITION outlying_years);
-- TEST
-- expected to see the partition key
\d T26002_T1;
Partitioned table "bfv_partition.t26002_t1"
Column | Type | Collation | Nullable | Default
--------------+-----------------------+-----------+----------+---------
empid | integer | | |
departmentid | integer | | |
year | integer | | |
region | character varying(20) | | |
Partition key: RANGE (year)
Number of partitions: 2 (Use \d+ to list them.)
Distributed by: (empid)
\d t26002_t1_1_prt_2;
Partitioned table "bfv_partition.t26002_t1_1_prt_2"
Column | Type | Collation | Nullable | Default
--------------+-----------------------+-----------+----------+---------
empid | integer | | |
departmentid | integer | | |
year | integer | | |
region | character varying(20) | | |
Partition of: t26002_t1 FOR VALUES FROM (2012) TO (2015)
Partition key: LIST (region)
Number of partitions: 4 (Use \d+ to list them.)
Distributed by: (empid)
\d t26002_t1_1_prt_2_2_prt_asia;
Table "bfv_partition.t26002_t1_1_prt_2_2_prt_asia"
Column | Type | Collation | Nullable | Default
--------------+-----------------------+-----------+----------+---------
empid | integer | | |
departmentid | integer | | |
year | integer | | |
region | character varying(20) | | |
Partition of: t26002_t1_1_prt_2 FOR VALUES IN ('asia')
Distributed by: (empid)
\d+ T26002_T1;
Partitioned table "bfv_partition.t26002_t1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------------+-----------------------+-----------+----------+---------+----------+--------------+-------------
empid | integer | | | | plain | |
departmentid | integer | | | | plain | |
year | integer | | | | plain | |
region | character varying(20) | | | | extended | |
Partition key: RANGE (year)
Partitions: t26002_t1_1_prt_2 FOR VALUES FROM (2012) TO (2015), PARTITIONED,
t26002_t1_1_prt_outlying_years DEFAULT, PARTITIONED
Distributed by: (empid)
\d+ t26002_t1_1_prt_2;
Partitioned table "bfv_partition.t26002_t1_1_prt_2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------------+-----------------------+-----------+----------+---------+----------+--------------+-------------
empid | integer | | | | plain | |
departmentid | integer | | | | plain | |
year | integer | | | | plain | |
region | character varying(20) | | | | extended | |
Partition of: t26002_t1 FOR VALUES FROM (2012) TO (2015)
Partition constraint: ((year IS NOT NULL) AND (year >= 2012) AND (year < 2015))
Partition key: LIST (region)
Partitions: t26002_t1_1_prt_2_2_prt_asia FOR VALUES IN ('asia'),
t26002_t1_1_prt_2_2_prt_europe FOR VALUES IN ('europe'),
t26002_t1_1_prt_2_2_prt_usa FOR VALUES IN ('usa'),
t26002_t1_1_prt_2_2_prt_other_regions DEFAULT
Distributed by: (empid)
\d+ t26002_t1_1_prt_2_2_prt_asia;
Table "bfv_partition.t26002_t1_1_prt_2_2_prt_asia"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------------+-----------------------+-----------+----------+---------+----------+--------------+-------------
empid | integer | | | | plain | |
departmentid | integer | | | | plain | |
year | integer | | | | plain | |
region | character varying(20) | | | | extended | |
Partition of: t26002_t1_1_prt_2 FOR VALUES IN ('asia')
Partition constraint: ((year IS NOT NULL) AND (year >= 2012) AND (year < 2015) AND (region IS NOT NULL) AND ((region)::text = 'asia'::character varying(20)))
Distributed by: (empid)
/*
-- test 2: Data Engineer won't see partition key for non-partitioned table
GIVEN I am a Data Engineer
WHEN I run \d on a non-partitioned table
THEN I should NOT see the partition key in the output
*/
CREATE TABLE T26002_T2 (empid int, departmentid int, year int, region varchar(20))
DISTRIBUTED BY (empid);
\d T26002_T2;
Table "bfv_partition.t26002_t2"
Column | Type | Collation | Nullable | Default
--------------+-----------------------+-----------+----------+---------
empid | integer | | |
departmentid | integer | | |
year | integer | | |
region | character varying(20) | | |
Distributed by: (empid)
\d+ T26002_T2;
Table "bfv_partition.t26002_t2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------------+-----------------------+-----------+----------+---------+----------+--------------+-------------
empid | integer | | | | plain | |
departmentid | integer | | | | plain | |
year | integer | | | | plain | |
region | character varying(20) | | | | extended | |
Distributed by: (empid)
--
-- Test whether test gives wrong results with partition tables when
-- sub-partitions are distributed differently than the parent partition.
--
-- SETUP
create table pt(a int, b int, c int) distributed by (a) partition by range(b) (start(0) end(10) every (2));
alter table pt_1_prt_1 set distributed randomly;
ERROR: can't set the distribution policy of "pt_1_prt_1"
HINT: Distribution policy of a partition can only be the same as its parent's.
create table t(a int, b int);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry 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 pt select g%10, g%2 + 1, g*2 from generate_series(1,20) g;
insert into pt values(1,1,3);
insert into t select g%10, g%2 + 1 from generate_series(1,20) g;
create index pt_c on pt(c);
analyze t;
analyze pt;
-- TEST
SELECT COUNT(*) FROM pt, t WHERE pt.a = t.a;
count
-------
42
(1 row)
SELECT COUNT(*) FROM pt, t WHERE pt.a = t.a and pt.c=4;
count
-------
2
(1 row)
select a, count(*) from pt group by a;
a | count
---+-------
9 | 2
8 | 2
2 | 2
1 | 3
0 | 2
3 | 2
6 | 2
7 | 2
5 | 2
4 | 2
(10 rows)
select b, count(*) from pt group by b;
b | count
---+-------
2 | 10
1 | 11
(2 rows)
select a, count(*) from pt where a<2 group by a;
a | count
---+-------
1 | 3
0 | 2
(2 rows)
--
-- Partition table with appendonly leaf, full join
--
-- SETUP
CREATE TABLE foo (a int);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry 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 bar (b int, c int)
PARTITION BY RANGE (b)
SUBPARTITION BY RANGE (c) SUBPARTITION TEMPLATE
(
START (1) END (10) WITH (appendonly=true),
START (10) END (20)
)
(
START (1) END (10) ,
START (10) END (20)
);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'b' as the Apache Cloudberry 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 foo VALUES (1);
INSERT INTO bar VALUES (2,3);
SELECT * FROM foo FULL JOIN bar ON foo.a = bar.b;
a | b | c
---+---+---
1 | |
| 2 | 3
(2 rows)
-- CLEANUP
DROP TABLE IF EXISTS foo, bar;
--
-- Partition table with appendonly set at middlevel partition, full join
--
-- SETUP
CREATE TABLE foo (a int);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry 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 bar (b int, c int)
PARTITION BY RANGE (b)
SUBPARTITION BY RANGE (c) SUBPARTITION TEMPLATE
(
START (1) END (10),
START (10) END (20)
)
(
START (1) END (10) WITH (appendonly=true),
START (10) END (20)
);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'b' as the Apache Cloudberry 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 foo VALUES (1);
INSERT INTO bar VALUES (2,3);
SELECT * FROM foo FULL JOIN bar ON foo.a = bar.b;
a | b | c
---+---+---
1 | |
| 2 | 3
(2 rows)
-- CLEANUP
DROP TABLE IF EXISTS foo, bar;
--
-- Partition table with appendonly set at root partition, full join
--
-- SETUP
CREATE TABLE foo (a int);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry 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 bar (b int, c int) WITH (appendonly=true)
PARTITION BY RANGE (b)
SUBPARTITION BY RANGE (c) SUBPARTITION TEMPLATE
(
START (1) END (10),
START (10) END (20)
)
(
START (1) END (10),
START (10) END (20)
);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'b' as the Apache Cloudberry 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 foo VALUES (1);
INSERT INTO bar VALUES (2,3);
SELECT * FROM foo FULL JOIN bar ON foo.a = bar.b;
a | b | c
---+---+---
1 | |
| 2 | 3
(2 rows)
CREATE TABLE mpp3263 (
unique1 int4,
unique2 int4,
two int4,
four int4,
ten int4,
twenty int4,
hundred int4,
thousand int4,
twothousand int4,
fivethous int4,
tenthous int4,
odd int4,
even int4,
stringu1 name,
stringu2 name,
string4 name
) partition by range (unique1)
( partition aa start (0) end (1000) every (500), default partition default_part );
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'unique1' as the Apache Cloudberry 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.
-- These are OK
alter table mpp3263 add column AAA int;
alter table mpp3263 add column BBB int;
alter table mpp3263 drop column BBB;
alter table mpp3263 drop partition for (0);
alter table mpp3263 add column CCC int;
insert into mpp3263 (unique1) values (1111);
drop table mpp3263;
CREATE TABLE mpp3541 (f1 time(2) with time zone, f2 char(4), f3 varchar(10))
partition by list (f2)
subpartition by range (f3)
subpartition template (
subpartition male values ('Male','M'),
subpartition female values ('Female','F')
)
( partition pst values ('PST'),
partition est values ('EST')
);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'f1' as the Apache Cloudberry 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 boundary specification for RANGE partition
LINE 5: subpartition male values ('Male','M'),
^
CREATE TABLE mpp3541 (f1 time(2) with time zone, f2 char(4), f3 varchar(10))
partition by range (f2)
subpartition by list (f3)
subpartition template (
subpartition male values ('Male','M'),
subpartition female values ('Female','F')
)
( partition pst values ('PST'),
partition est values ('EST')
);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'f1' as the Apache Cloudberry 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 boundary specification for RANGE partition
LINE 8: ( partition pst values ('PST'),
^
CREATE TABLE mpp3541 (f1 time(2) with time zone, f2 char(4), f3 varchar(10))
partition by range (f2)
subpartition by range (f3)
subpartition template (
subpartition male values ('Male','M'),
subpartition female values ('Female','F')
)
( partition pst values ('PST'),
partition est values ('EST')
);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'f1' as the Apache Cloudberry 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 boundary specification for RANGE partition
LINE 8: ( partition pst values ('PST'),
^
CREATE TABLE mpp3542_000000000011111111112222222222333333333344444444445555555555556666666666777777777788888888889999999999 (f1 time(2) with time zone)
partition by range (f1)
(
partition "Los Angeles" start (time with time zone '00:00 PST') end (time with time zone '23:00 PST') EVERY (INTERVAL '1 hour'),
partition "New York" start (time with time zone '00:00 EST') end (time with time zone '23:00 EST') EVERY (INTERVAL '1 hour')
);
NOTICE: identifier "mpp3542_000000000011111111112222222222333333333344444444445555555555556666666666777777777788888888889999999999" will be truncated to "mpp3542_0000000000111111111122222222223333333333444444444455555"
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'f1' as the Apache Cloudberry 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: partition "mpp3542_00000000001111111111222222222233333333_1_prt_New York_4" would overlap partition "mpp3542_00000000001111111111222222222233333_1_prt_Los Angeles_1"
-- Truncates the table name to mpp3542_0000000000111111111122222222223333333333444444444455555, but partition name is too long, so ERROR
alter table mpp3542_000000000011111111112222222222333333333344444444445555555555556666666666777777777788888888889999999999 rename partition "Los Angeles_1" to "123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890";
NOTICE: identifier "mpp3542_000000000011111111112222222222333333333344444444445555555555556666666666777777777788888888889999999999" will be truncated to "mpp3542_0000000000111111111122222222223333333333444444444455555"
NOTICE: identifier "123456789012345678901234567890123456789012345678901234567890123456789012345678901234567890" will be truncated to "123456789012345678901234567890123456789012345678901234567890123"
ERROR: relation "mpp3542_0000000000111111111122222222223333333333444444444455555" does not exist
-- Truncates the table name to mpp3542_0000000000111111111122222222223333333333444444444455555, and partition name is safe, so renamed
alter table mpp3542_000000000011111111112222222222333333333344444444445555555555556666666666777777777788888888889999999999 rename partition "Los Angeles_1" to "LA1";
NOTICE: identifier "mpp3542_000000000011111111112222222222333333333344444444445555555555556666666666777777777788888888889999999999" will be truncated to "mpp3542_0000000000111111111122222222223333333333444444444455555"
ERROR: relation "mpp3542_0000000000111111111122222222223333333333444444444455555" does not exist
-- Use the actual table name
alter table mpp3542_0000000000111111111122222222223333333333444444444455555 rename partition "Los Angeles_2" to "LA2";
ERROR: relation "mpp3542_0000000000111111111122222222223333333333444444444455555" does not exist
-- MPP-3542
alter table mpp3542_0000000000111111111122222222223333333333444444444455555 rename to m;
ERROR: relation "mpp3542_0000000000111111111122222222223333333333444444444455555" does not exist
create table mpp3466 (i int) partition by range(i) (start(1) end(10) every(2), default partition f);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry 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.
alter table mpp3466 split partition f at (3) into (partition f, partition new1);
ERROR: AT clause cannot be used when splitting a default RANGE partition
drop table mpp3466;
create table mpp3058 (a char(1), b date, d char(3))
distributed by (a)
partition by range (b)
(
partition aa start (date '2008-01-01') end (date '2009-01-01')
every (interval '50 days'));
drop table mpp3058;
create table mpp3058 (a char(1), b date, d char(3))
distributed by (a)
partition by range (b)
(
partition aa start ('2008-01-01') end ('2010-01-01')
);
drop table mpp3058;
-- Expected Error
create table mpp3058 (a char(1), b date, d char(3))
distributed by (a)
partition by range (b)
(
partition aa start ('2008-01-01') end ('2006-01-01')
);
ERROR: empty range bound specified for partition "mpp3058_1_prt_aa"
DETAIL: Specified lower bound ('01-01-2008') is greater than or equal to upper bound ('01-01-2006').
drop table mpp3058;
ERROR: table "mpp3058" does not exist
-- Expected Error
create table mpp3058 (a char(1), b date, d char(3))
distributed by (a)
partition by range (b)
(
partition aa start ('2008-01-01') end ('-2009-01-01')
);
ERROR: time zone displacement out of range: "-2009-01-01"
LINE 5: partition aa start ('2008-01-01') end ('-2009-01-01') ...
^
drop table mpp3058;
ERROR: table "mpp3058" does not exist
-- Expected Error
create table mpp3058 (a char(1), b date, d char(3))
distributed by (a)
partition by range (b)
(
partition aa start ('-2008-01-01') end ('2009-01-01')
);
ERROR: time zone displacement out of range: "-2008-01-01"
LINE 5: partition aa start ('-2008-01-01') end ('2009-01-01') ...
^
drop table mpp3058;
ERROR: table "mpp3058" does not exist
create table mpp3058 (a char(1), b date, d char(3))
distributed by (a)
partition by range (b)
(
partition aa start ('2008-01-01') end ('2010-01-01')
);
drop table mpp3058;
create table mpp3058 (a char(1), b date, d char(3))
distributed by (a)
partition by range (b)
(
partition aa start ('2008-01-01') end ('2008-02-01') every(interval '1 day')
);
drop table mpp3058;
-- Expected Error
create table mpp3058 (a char(1), b date, d char(3))
distributed by (a)
partition by range (b)
(
partition aa start ('2008-01-01') end ('2009-01-01') every( '1 day')
);
ERROR: operator is not unique: date pg_catalog.+ unknown
HINT: Could not choose a best candidate operator. You might need to add explicit type casts.
drop table mpp3058;
ERROR: table "mpp3058" does not exist
-- Expected Error
create table mpp3058 (a char(1), b date, d char(3))
distributed by (a)
partition by range (b)
(
partition aa end ('2009-01-01') every( interval '1 day')
);
ERROR: EVERY clause requires START and END
LINE 5: partition aa end ('2009-01-01') every( interval '1 day')...
^
drop table mpp3058;
ERROR: table "mpp3058" does not exist
-- Expected Error
create table mpp3058 (a char(1), b date, d char(3))
distributed by (a)
partition by range (b)
(
partition aa start ('2006-01-01') every( interval '1 day')
);
ERROR: EVERY clause requires START and END
LINE 5: partition aa start ('2006-01-01') every( interval '1 day...
^
drop table mpp3058;
ERROR: table "mpp3058" does not exist
create table mpp3607 (aa int, bb int) partition by range (bb)
(partition foo start(2));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'aa' as the Apache Cloudberry 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.
alter table mpp3607 add partition baz end (3); -- Expected Overlaps
ERROR: partition "mpp3607_1_prt_baz" would overlap partition "mpp3607_1_prt_foo"
alter table mpp3607 add partition baz end (4); -- Expected Overlaps
ERROR: partition "mpp3607_1_prt_baz" would overlap partition "mpp3607_1_prt_foo"
alter table mpp3607 add partition aa end (2); -- OK as of 4.2.0.0 (RIO)
alter table mpp3607 add partition bb end (1); -- Expected Overlaps
ERROR: partition "mpp3607_1_prt_bb" would overlap partition "mpp3607_1_prt_aa"
alter table mpp3607 add partition baz end (3); -- Expected Overlaps
ERROR: partition "mpp3607_1_prt_baz" would overlap partition "mpp3607_1_prt_foo"
alter table mpp3607 add partition baz end (4); -- Expected Overlaps
ERROR: partition "mpp3607_1_prt_baz" would overlap partition "mpp3607_1_prt_foo"
alter table mpp3607 add partition baz end (2); -- Expected Overlaps
ERROR: partition "mpp3607_1_prt_baz" would overlap partition "mpp3607_1_prt_aa"
alter table mpp3607 add partition bb end (0); -- Expected Overlaps
ERROR: partition "mpp3607_1_prt_bb" would overlap partition "mpp3607_1_prt_aa"
alter table mpp3607 drop partition aa;
alter table mpp3607 add partition aa end (-4); -- partition rule aa < -4, foo >=2
alter table mpp3607 add partition bb end (-3); -- will not overlap
alter table mpp3607 drop partition aa;
alter table mpp3607 drop partition bb;
alter table mpp3607 add partition aa end (0); -- partition rule aa < 0, foo >=2
alter table mpp3607 drop partition aa;
alter table mpp3607 add partition aa start (4); -- Overlaps
ERROR: partition "mpp3607_1_prt_aa" would overlap partition "mpp3607_1_prt_foo"
alter table mpp3607 add partition aa start (3) end (4); -- Overlaps
ERROR: partition "mpp3607_1_prt_aa" would overlap partition "mpp3607_1_prt_foo"
alter table mpp3607 add partition aa start (0) end (1); -- partition rule aa bb>=0 and bb<1, foo bb>=2
alter table mpp3607 add partition bb start (-1) end (0); -- partition rule bb bb>=-1 and bb<0
alter table mpp3607 add partition cc start (-4); -- partition rule cc bb>=-4 and bb <-1
alter table mpp3607 add partition dd start (-5) end (-4);
alter table mpp3607 add partition ee start (-10);
alter table mpp3607 add partition ff start (-9) end (-8); -- Overlaps
ERROR: partition "mpp3607_1_prt_ff" would overlap partition "mpp3607_1_prt_ee"
drop table mpp3607;
CREATE TABLE mpp3632(a int, b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int)
partition by range (a)
( partition aa start (1) end (10) every (1) );
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry 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.
alter table mpp3632 add partition a1 start (30);
alter table mpp3632 add partition a2 start (25) end (30);
alter table mpp3632 add partition a3 start (10) end (20);
alter table mpp3632 add partition a4 start (20) end (21);
alter table mpp3632 add partition a5 start (22) end (24);
alter table mpp3632 add partition a6 start (21) end (22);
-- alter table mpp3632 add partition a7 start (23) end (24); -- MPP-3667
drop table mpp3632;
create table mpp3671 (i int) partition by list(i) (partition a values(1, 2, 3, 4),
partition b values(5, 6, 7, 8), default partition default_part);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry 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 mpp3671 select i from generate_series(1, 10) i;
alter table mpp3671 split partition for(1) at (1,2) into (partition f1a, partition f1b);
drop table mpp3671;
create table mpp3639 (i int) partition by range(i) (start(1) end(100) inclusive every(10));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry 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 mpp3639 select i from generate_series(1, 100) i;
insert into mpp3639 select i from generate_series(1, 100) i;
insert into mpp3639 select i from generate_series(1, 100) i;
select * from mpp3639 order by i;
i
-----
1
1
1
2
2
2
3
3
3
4
4
4
5
5
5
6
6
6
7
7
7
8
8
8
9
9
9
10
10
10
11
11
11
12
12
12
13
13
13
14
14
14
15
15
15
16
16
16
17
17
17
18
18
18
19
19
19
20
20
20
21
21
21
22
22
22
23
23
23
24
24
24
25
25
25
26
26
26
27
27
27
28
28
28
29
29
29
30
30
30
31
31
31
32
32
32
33
33
33
34
34
34
35
35
35
36
36
36
37
37
37
38
38
38
39
39
39
40
40
40
41
41
41
42
42
42
43
43
43
44
44
44
45
45
45
46
46
46
47
47
47
48
48
48
49
49
49
50
50
50
51
51
51
52
52
52
53
53
53
54
54
54
55
55
55
56
56
56
57
57
57
58
58
58
59
59
59
60
60
60
61
61
61
62
62
62
63
63
63
64
64
64
65
65
65
66
66
66
67
67
67
68
68
68
69
69
69
70
70
70
71
71
71
72
72
72
73
73
73
74
74
74
75
75
75
76
76
76
77
77
77
78
78
78
79
79
79
80
80
80
81
81
81
82
82
82
83
83
83
84
84
84
85
85
85
86
86
86
87
87
87
88
88
88
89
89
89
90
90
90
91
91
91
92
92
92
93
93
93
94
94
94
95
95
95
96
96
96
97
97
97
98
98
98
99
99
99
100
100
100
(300 rows)
drop table mpp3639;
create table mpp3588 (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 Apache Cloudberry 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 mpp3588 select i from generate_series(1, 8) i;
alter table mpp3588 split partition for(1) at (1,2) into (partition fa, partition fb);
alter table mpp3588 split partition for(1) at (1,2) into (partition f1a, partition f1b); -- This has partition rules that overlaps
ERROR: AT clause cannot contain all values in the partition to be split
alter table mpp3588 split partition for(1) at (1,2) into (partition f2a, partition f2b); -- 5,6 are not within the boundary of first partition
ERROR: AT clause cannot contain all values in the partition to be split
alter table mpp3588 split partition for(1) at (1,2) into (partition f3a, partition f3b);
ERROR: AT clause cannot contain all values in the partition to be split
alter table mpp3588 split partition for(1) at (1,2) into (partition f4a, partition f4b);
ERROR: AT clause cannot contain all values in the partition to be split
alter table mpp3588 split partition for(1) at (1,2) into (partition f5a, partition f5b); -- Out of the original boundary specification
ERROR: AT clause cannot contain all values in the partition to be split
alter table mpp3588 split partition for(1) at (1,2) into (partition f6a, partition f6b); -- I can keep going and going and going....
ERROR: AT clause cannot contain all values in the partition to be split
alter table mpp3588 split partition for(1) at (1,2) into (partition f7a, partition f7b);
ERROR: AT clause cannot contain all values in the partition to be split
drop table mpp3588;
-- MPP-3691, MPP-3681
create table mpp3681 (id int, date date, amt decimal(10,2)) distributed by (id) partition by range(date) (start (date '2008-01-01') inclusive end ('2008-04-01') exclusive every (interval '1 month'));
alter table mpp3681 add default partition def;
alter table mpp3681 split default partition start('2008-04-01') inclusive end('2008-05-01') exclusive into (partition apr08, default partition);
drop table mpp3681;
-- MPP-3593
create table mpp3593 (i int) partition by range(i) (start(1) end(100) every(10));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry 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 mpp3593 select i from generate_series(1, 99) i;
alter table mpp3593 split partition for(1) at (5) into (partition aa, partition bb);
alter table mpp3593 split partition for(15) at (20) into (partition a1, partition b1);
alter table mpp3593 split partition for(25) at (30) into (partition a2, partition b2);
alter table mpp3593 split partition for(35) at (40) into (partition a3, partition b3);
alter table mpp3593 split partition for(55) at (60) into (partition a4, partition b4);
alter table mpp3593 split partition for(45) at (50) into (partition a5, partition b5);
drop table mpp3593;
CREATE TABLE mpp3742 (
c_id varchar(36),
ss_id varchar(36),
c_ts timestamp,
name varchar(36),
PRIMARY KEY (c_id,ss_id,c_ts)) partition by range (c_ts)
(
start (date '2007-07-01')
end (date '2008-01-01') every (interval '1 month'),
default partition default_part
);
alter table mpp3742 split default partition start ('2009-01-01') end ('2009-02-01') into (partition a3, default partition);
drop table mpp3742;
create table mpp3597 (i int) partition by list(i) (partition a values(1, 2, 3, 4),
partition b values(5, 6, 7, 8), default partition default_part);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry 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 mpp3597 select i from generate_series(1, 100) i;
insert into mpp3597 values (NULL);
alter table mpp3597 split default partition at (NULL);
insert into mpp3597 values (NULL);
select * from mpp3597_1_prt_default_part where i=NULL; -- No NULL values
i
---
(0 rows)
drop table mpp3597;
create table mpp3594 (i date) partition by range(i) (start('2008-07-01') end('2009-01-01') every(interval '1 month'), default partition default_part);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry 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.
alter table mpp3594 split default partition start ('2009-01-01') end ('2009-02-01') into (partition aa, partition nodate);
ERROR: default partition name missing from INTO clause
drop table mpp3594;
CREATE TABLE mpp3512 (id int, rank int, year int, gender char(1), count int)
DISTRIBUTED BY (id);
create table mpp3512_part (like mpp3512) partition by range (year) ( start (2001) end (2006) every ('1'));
NOTICE: table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table
create table mpp3512a (like mpp3512_part);
NOTICE: table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table
\d mpp3512
Table "bfv_partition.mpp3512"
Column | Type | Collation | Nullable | Default
--------+--------------+-----------+----------+---------
id | integer | | |
rank | integer | | |
year | integer | | |
gender | character(1) | | |
count | integer | | |
Distributed by: (id)
\d mpp3512a
Table "bfv_partition.mpp3512a"
Column | Type | Collation | Nullable | Default
--------+--------------+-----------+----------+---------
id | integer | | |
rank | integer | | |
year | integer | | |
gender | character(1) | | |
count | integer | | |
Distributed by: (id)
select relid,parentrelid,isleaf,level, pg_catalog.pg_get_expr(relpartbound, oid) from pg_partition_tree('mpp3512_part'), pg_class where relid = oid;
relid | parentrelid | isleaf | level | pg_get_expr
----------------------+--------------+--------+-------+----------------------------------
mpp3512_part | | f | 0 |
mpp3512_part_1_prt_1 | mpp3512_part | t | 1 | FOR VALUES FROM (2001) TO (2002)
mpp3512_part_1_prt_2 | mpp3512_part | t | 1 | FOR VALUES FROM (2002) TO (2003)
mpp3512_part_1_prt_3 | mpp3512_part | t | 1 | FOR VALUES FROM (2003) TO (2004)
mpp3512_part_1_prt_4 | mpp3512_part | t | 1 | FOR VALUES FROM (2004) TO (2005)
mpp3512_part_1_prt_5 | mpp3512_part | t | 1 | FOR VALUES FROM (2005) TO (2006)
(6 rows)
drop table mpp3512;
drop table mpp3512_part;
drop table mpp3512a;
CREATE TABLE mpp3988 ( ps_partkey integer,
ps_suppkey integer, ps_availqty integer,
ps_supplycost numeric, ps_comment character varying(199) )
PARTITION BY RANGE(ps_supplycost)
subpartition by range (ps_supplycost)
(default partition foo (default subpartition bar));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'ps_partkey' as the Apache Cloudberry 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.
drop table mpp3988;
CREATE TABLE mpp3816 (
unique1 int4,
unique2 int4,
two int4,
four int4,
ten int4,
twenty int4,
hundred int4,
thousand int4,
twothousand int4,
fivethous int4,
tenthous int4,
odd int4,
even int4,
stringu1 name,
stringu2 name,
string4 name,
startDate date
) partition by range (startDate)
( start ('2007-06-01') end ('2008-01-01') every (interval '1 month'), default partition default_part );
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'unique1' as the Apache Cloudberry 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.
alter table mpp3816 add column AAA int;
alter table mpp3816 add column BBB int;
alter table mpp3816 drop column BBB;
alter table mpp3816 drop column startDate;
ERROR: cannot drop column "startdate" because it is part of the partition key of relation "mpp3816"
drop table mpp3816;
CREATE TABLE mpp3762_cities (
city varchar(80) primary key,
location point
);
CREATE TABLE mpp3762_weather (
city varchar(80) references mpp3762_cities(city),
temp_lo int,
temp_hi int,
prcp real,
date date
);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'city' as the Apache Cloudberry 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.
WARNING: referential integrity (FOREIGN KEY) constraints are not supported in Apache Cloudberry, will not be enforced
CREATE TABLE mpp3762_cities_partition (
city varchar(80) primary key,
location point
) partition by list (city) ( partition a values ('Los Angeles'), partition b values ('San Mateo') );
CREATE TABLE mpp3762_weather_partition (
city varchar(80) references mpp3762_cities_partition(city),
temp_lo int,
temp_hi int,
prcp real,
date date
) partition by range(date) ( start('2008-01-01') end ('2009-01-01') every (interval '1 month'));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'city' as the Apache Cloudberry 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.
WARNING: referential integrity (FOREIGN KEY) constraints are not supported in Apache Cloudberry, will not be enforced
drop table mpp3762_cities, mpp3762_weather cascade;
drop table mpp3762_cities_partition, mpp3762_weather_partition cascade;
create table mpp3754a ( i int, d date, primary key (d)) partition by range(d) ( start ('2008-01-01') inclusive end ('2008-12-01') exclusive every (interval '1 month'));
create table mpp3754b ( i int, d date, constraint prim_tr primary key (d)) partition by range(d) ( start ('2008-01-01') inclusive end ('2008-12-01') exclusive every (interval '1 month'));
drop table mpp3754a;
drop table mpp3754b;
CREATE TABLE mpp4582 (id int,
mpp4582 int, year date, gender char(1))
DISTRIBUTED BY (id, gender, year)
partition by list (gender)
subpartition by range (year)
subpartition template (
start (date '2001-01-01')
end (date '2006-01-01') every (interval '1 year')) (
partition boys values ('M'),
partition girls values ('F'),
default partition neuter
);
ALTER table mpp4582 drop partition for ('-1');
ERROR: value too long for type character(1)
ALTER table mpp4582 drop partition for ('--');
ERROR: value too long for type character(1)
alter table mpp4582 drop partition for (';');
ERROR: FOR expression matches DEFAULT partition for specified value of relation "mpp4582"
HINT: FOR expression may only specify a non-default partition in this context.
alter table mpp4582 drop partition for ();
ERROR: syntax error at or near ")"
LINE 1: alter table mpp4582 drop partition for ();
^
alter table mpp4582 drop partition for (NULL);
ERROR: FOR expression matches DEFAULT partition for specified value of relation "mpp4582"
HINT: FOR expression may only specify a non-default partition in this context.
alter table mpp4582 drop partition for ('NULL');
ERROR: value too long for type character(1)
drop table mpp4582;
-- Use a particular username in the tests below, so that the output of \di
-- commands don't vary depending on current user.
DROP USER IF EXISTS mpp3641_user;
CREATE USER mpp3641_user;
NOTICE: resource queue required -- using default resource queue "pg_default"
GRANT ALL ON SCHEMA bfv_partition TO mpp3641_user;
SET ROLE mpp3641_user;
CREATE TABLE mpp3641a (
unique1 int4,
unique2 int4,
two int4,
four int4,
ten int4,
twenty int4,
hundred int4,
thousand int4,
twothousand int4,
fivethous int4,
tenthous int4,
odd int4,
even int4,
stringu1 name,
stringu2 name,
string4 name
) partition by range (unique1)
( partition aa start (0) end (500) every (100), default partition default_part );
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'unique1' as the Apache Cloudberry 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 mpp3641b (
unique1 int4,
unique2 int4,
two int4,
four int4,
ten int4,
twenty int4,
hundred int4,
thousand int4,
twothousand int4,
fivethous int4,
tenthous int4,
odd int4,
even int4,
stringu1 name,
stringu2 name,
string4 name
) partition by range (unique1)
subpartition by range (unique2) subpartition template ( start (0) end (500) every (100) )
( start (0) end (500) every (100));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'unique1' as the Apache Cloudberry 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.
alter table mpp3641b add default partition default_part;
CREATE INDEX mpp3641a_unique1 ON mpp3641a USING btree(unique1 int4_ops);
CREATE INDEX mpp3641a_unique2 ON mpp3641a USING btree(unique2 int4_ops);
CREATE INDEX mpp3641a_hundred ON mpp3641a USING btree(hundred int4_ops);
CREATE INDEX mpp3641a_stringu1 ON mpp3641a USING btree(stringu1 name_ops);
CREATE INDEX mpp3641b_unique1 ON mpp3641b USING btree(unique1 int4_ops);
CREATE INDEX mpp3641b_unique2 ON mpp3641b USING btree(unique2 int4_ops);
CREATE INDEX mpp3641b_hundred ON mpp3641b USING btree(hundred int4_ops);
CREATE INDEX mpp3641b_stringu1 ON mpp3641b USING btree(stringu1 name_ops);
\t
\di mpp3641*
bfv_partition | mpp3641a_1_prt_aa_1_hundred_idx | index | mpp3641_user | mpp3641a_1_prt_aa_1
bfv_partition | mpp3641a_1_prt_aa_1_stringu1_idx | index | mpp3641_user | mpp3641a_1_prt_aa_1
bfv_partition | mpp3641a_1_prt_aa_1_unique1_idx | index | mpp3641_user | mpp3641a_1_prt_aa_1
bfv_partition | mpp3641a_1_prt_aa_1_unique2_idx | index | mpp3641_user | mpp3641a_1_prt_aa_1
bfv_partition | mpp3641a_1_prt_aa_2_hundred_idx | index | mpp3641_user | mpp3641a_1_prt_aa_2
bfv_partition | mpp3641a_1_prt_aa_2_stringu1_idx | index | mpp3641_user | mpp3641a_1_prt_aa_2
bfv_partition | mpp3641a_1_prt_aa_2_unique1_idx | index | mpp3641_user | mpp3641a_1_prt_aa_2
bfv_partition | mpp3641a_1_prt_aa_2_unique2_idx | index | mpp3641_user | mpp3641a_1_prt_aa_2
bfv_partition | mpp3641a_1_prt_aa_3_hundred_idx | index | mpp3641_user | mpp3641a_1_prt_aa_3
bfv_partition | mpp3641a_1_prt_aa_3_stringu1_idx | index | mpp3641_user | mpp3641a_1_prt_aa_3
bfv_partition | mpp3641a_1_prt_aa_3_unique1_idx | index | mpp3641_user | mpp3641a_1_prt_aa_3
bfv_partition | mpp3641a_1_prt_aa_3_unique2_idx | index | mpp3641_user | mpp3641a_1_prt_aa_3
bfv_partition | mpp3641a_1_prt_aa_4_hundred_idx | index | mpp3641_user | mpp3641a_1_prt_aa_4
bfv_partition | mpp3641a_1_prt_aa_4_stringu1_idx | index | mpp3641_user | mpp3641a_1_prt_aa_4
bfv_partition | mpp3641a_1_prt_aa_4_unique1_idx | index | mpp3641_user | mpp3641a_1_prt_aa_4
bfv_partition | mpp3641a_1_prt_aa_4_unique2_idx | index | mpp3641_user | mpp3641a_1_prt_aa_4
bfv_partition | mpp3641a_1_prt_aa_5_hundred_idx | index | mpp3641_user | mpp3641a_1_prt_aa_5
bfv_partition | mpp3641a_1_prt_aa_5_stringu1_idx | index | mpp3641_user | mpp3641a_1_prt_aa_5
bfv_partition | mpp3641a_1_prt_aa_5_unique1_idx | index | mpp3641_user | mpp3641a_1_prt_aa_5
bfv_partition | mpp3641a_1_prt_aa_5_unique2_idx | index | mpp3641_user | mpp3641a_1_prt_aa_5
bfv_partition | mpp3641a_1_prt_default_part_hundred_idx | index | mpp3641_user | mpp3641a_1_prt_default_part
bfv_partition | mpp3641a_1_prt_default_part_stringu1_idx | index | mpp3641_user | mpp3641a_1_prt_default_part
bfv_partition | mpp3641a_1_prt_default_part_unique1_idx | index | mpp3641_user | mpp3641a_1_prt_default_part
bfv_partition | mpp3641a_1_prt_default_part_unique2_idx | index | mpp3641_user | mpp3641a_1_prt_default_part
bfv_partition | mpp3641a_hundred | partitioned index | mpp3641_user | mpp3641a
bfv_partition | mpp3641a_stringu1 | partitioned index | mpp3641_user | mpp3641a
bfv_partition | mpp3641a_unique1 | partitioned index | mpp3641_user | mpp3641a
bfv_partition | mpp3641a_unique2 | partitioned index | mpp3641_user | mpp3641a
bfv_partition | mpp3641b_1_prt_1_2_prt_1_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_1_2_prt_1
bfv_partition | mpp3641b_1_prt_1_2_prt_1_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_1_2_prt_1
bfv_partition | mpp3641b_1_prt_1_2_prt_1_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_1_2_prt_1
bfv_partition | mpp3641b_1_prt_1_2_prt_1_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_1_2_prt_1
bfv_partition | mpp3641b_1_prt_1_2_prt_2_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_1_2_prt_2
bfv_partition | mpp3641b_1_prt_1_2_prt_2_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_1_2_prt_2
bfv_partition | mpp3641b_1_prt_1_2_prt_2_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_1_2_prt_2
bfv_partition | mpp3641b_1_prt_1_2_prt_2_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_1_2_prt_2
bfv_partition | mpp3641b_1_prt_1_2_prt_3_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_1_2_prt_3
bfv_partition | mpp3641b_1_prt_1_2_prt_3_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_1_2_prt_3
bfv_partition | mpp3641b_1_prt_1_2_prt_3_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_1_2_prt_3
bfv_partition | mpp3641b_1_prt_1_2_prt_3_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_1_2_prt_3
bfv_partition | mpp3641b_1_prt_1_2_prt_4_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_1_2_prt_4
bfv_partition | mpp3641b_1_prt_1_2_prt_4_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_1_2_prt_4
bfv_partition | mpp3641b_1_prt_1_2_prt_4_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_1_2_prt_4
bfv_partition | mpp3641b_1_prt_1_2_prt_4_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_1_2_prt_4
bfv_partition | mpp3641b_1_prt_1_2_prt_5_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_1_2_prt_5
bfv_partition | mpp3641b_1_prt_1_2_prt_5_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_1_2_prt_5
bfv_partition | mpp3641b_1_prt_1_2_prt_5_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_1_2_prt_5
bfv_partition | mpp3641b_1_prt_1_2_prt_5_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_1_2_prt_5
bfv_partition | mpp3641b_1_prt_1_hundred_idx | partitioned index | mpp3641_user | mpp3641b_1_prt_1
bfv_partition | mpp3641b_1_prt_1_stringu1_idx | partitioned index | mpp3641_user | mpp3641b_1_prt_1
bfv_partition | mpp3641b_1_prt_1_unique1_idx | partitioned index | mpp3641_user | mpp3641b_1_prt_1
bfv_partition | mpp3641b_1_prt_1_unique2_idx | partitioned index | mpp3641_user | mpp3641b_1_prt_1
bfv_partition | mpp3641b_1_prt_2_2_prt_1_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_2_2_prt_1
bfv_partition | mpp3641b_1_prt_2_2_prt_1_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_2_2_prt_1
bfv_partition | mpp3641b_1_prt_2_2_prt_1_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_2_2_prt_1
bfv_partition | mpp3641b_1_prt_2_2_prt_1_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_2_2_prt_1
bfv_partition | mpp3641b_1_prt_2_2_prt_2_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_2_2_prt_2
bfv_partition | mpp3641b_1_prt_2_2_prt_2_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_2_2_prt_2
bfv_partition | mpp3641b_1_prt_2_2_prt_2_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_2_2_prt_2
bfv_partition | mpp3641b_1_prt_2_2_prt_2_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_2_2_prt_2
bfv_partition | mpp3641b_1_prt_2_2_prt_3_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_2_2_prt_3
bfv_partition | mpp3641b_1_prt_2_2_prt_3_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_2_2_prt_3
bfv_partition | mpp3641b_1_prt_2_2_prt_3_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_2_2_prt_3
bfv_partition | mpp3641b_1_prt_2_2_prt_3_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_2_2_prt_3
bfv_partition | mpp3641b_1_prt_2_2_prt_4_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_2_2_prt_4
bfv_partition | mpp3641b_1_prt_2_2_prt_4_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_2_2_prt_4
bfv_partition | mpp3641b_1_prt_2_2_prt_4_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_2_2_prt_4
bfv_partition | mpp3641b_1_prt_2_2_prt_4_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_2_2_prt_4
bfv_partition | mpp3641b_1_prt_2_2_prt_5_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_2_2_prt_5
bfv_partition | mpp3641b_1_prt_2_2_prt_5_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_2_2_prt_5
bfv_partition | mpp3641b_1_prt_2_2_prt_5_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_2_2_prt_5
bfv_partition | mpp3641b_1_prt_2_2_prt_5_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_2_2_prt_5
bfv_partition | mpp3641b_1_prt_2_hundred_idx | partitioned index | mpp3641_user | mpp3641b_1_prt_2
bfv_partition | mpp3641b_1_prt_2_stringu1_idx | partitioned index | mpp3641_user | mpp3641b_1_prt_2
bfv_partition | mpp3641b_1_prt_2_unique1_idx | partitioned index | mpp3641_user | mpp3641b_1_prt_2
bfv_partition | mpp3641b_1_prt_2_unique2_idx | partitioned index | mpp3641_user | mpp3641b_1_prt_2
bfv_partition | mpp3641b_1_prt_3_2_prt_1_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_3_2_prt_1
bfv_partition | mpp3641b_1_prt_3_2_prt_1_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_3_2_prt_1
bfv_partition | mpp3641b_1_prt_3_2_prt_1_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_3_2_prt_1
bfv_partition | mpp3641b_1_prt_3_2_prt_1_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_3_2_prt_1
bfv_partition | mpp3641b_1_prt_3_2_prt_2_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_3_2_prt_2
bfv_partition | mpp3641b_1_prt_3_2_prt_2_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_3_2_prt_2
bfv_partition | mpp3641b_1_prt_3_2_prt_2_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_3_2_prt_2
bfv_partition | mpp3641b_1_prt_3_2_prt_2_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_3_2_prt_2
bfv_partition | mpp3641b_1_prt_3_2_prt_3_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_3_2_prt_3
bfv_partition | mpp3641b_1_prt_3_2_prt_3_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_3_2_prt_3
bfv_partition | mpp3641b_1_prt_3_2_prt_3_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_3_2_prt_3
bfv_partition | mpp3641b_1_prt_3_2_prt_3_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_3_2_prt_3
bfv_partition | mpp3641b_1_prt_3_2_prt_4_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_3_2_prt_4
bfv_partition | mpp3641b_1_prt_3_2_prt_4_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_3_2_prt_4
bfv_partition | mpp3641b_1_prt_3_2_prt_4_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_3_2_prt_4
bfv_partition | mpp3641b_1_prt_3_2_prt_4_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_3_2_prt_4
bfv_partition | mpp3641b_1_prt_3_2_prt_5_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_3_2_prt_5
bfv_partition | mpp3641b_1_prt_3_2_prt_5_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_3_2_prt_5
bfv_partition | mpp3641b_1_prt_3_2_prt_5_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_3_2_prt_5
bfv_partition | mpp3641b_1_prt_3_2_prt_5_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_3_2_prt_5
bfv_partition | mpp3641b_1_prt_3_hundred_idx | partitioned index | mpp3641_user | mpp3641b_1_prt_3
bfv_partition | mpp3641b_1_prt_3_stringu1_idx | partitioned index | mpp3641_user | mpp3641b_1_prt_3
bfv_partition | mpp3641b_1_prt_3_unique1_idx | partitioned index | mpp3641_user | mpp3641b_1_prt_3
bfv_partition | mpp3641b_1_prt_3_unique2_idx | partitioned index | mpp3641_user | mpp3641b_1_prt_3
bfv_partition | mpp3641b_1_prt_4_2_prt_1_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_4_2_prt_1
bfv_partition | mpp3641b_1_prt_4_2_prt_1_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_4_2_prt_1
bfv_partition | mpp3641b_1_prt_4_2_prt_1_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_4_2_prt_1
bfv_partition | mpp3641b_1_prt_4_2_prt_1_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_4_2_prt_1
bfv_partition | mpp3641b_1_prt_4_2_prt_2_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_4_2_prt_2
bfv_partition | mpp3641b_1_prt_4_2_prt_2_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_4_2_prt_2
bfv_partition | mpp3641b_1_prt_4_2_prt_2_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_4_2_prt_2
bfv_partition | mpp3641b_1_prt_4_2_prt_2_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_4_2_prt_2
bfv_partition | mpp3641b_1_prt_4_2_prt_3_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_4_2_prt_3
bfv_partition | mpp3641b_1_prt_4_2_prt_3_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_4_2_prt_3
bfv_partition | mpp3641b_1_prt_4_2_prt_3_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_4_2_prt_3
bfv_partition | mpp3641b_1_prt_4_2_prt_3_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_4_2_prt_3
bfv_partition | mpp3641b_1_prt_4_2_prt_4_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_4_2_prt_4
bfv_partition | mpp3641b_1_prt_4_2_prt_4_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_4_2_prt_4
bfv_partition | mpp3641b_1_prt_4_2_prt_4_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_4_2_prt_4
bfv_partition | mpp3641b_1_prt_4_2_prt_4_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_4_2_prt_4
bfv_partition | mpp3641b_1_prt_4_2_prt_5_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_4_2_prt_5
bfv_partition | mpp3641b_1_prt_4_2_prt_5_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_4_2_prt_5
bfv_partition | mpp3641b_1_prt_4_2_prt_5_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_4_2_prt_5
bfv_partition | mpp3641b_1_prt_4_2_prt_5_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_4_2_prt_5
bfv_partition | mpp3641b_1_prt_4_hundred_idx | partitioned index | mpp3641_user | mpp3641b_1_prt_4
bfv_partition | mpp3641b_1_prt_4_stringu1_idx | partitioned index | mpp3641_user | mpp3641b_1_prt_4
bfv_partition | mpp3641b_1_prt_4_unique1_idx | partitioned index | mpp3641_user | mpp3641b_1_prt_4
bfv_partition | mpp3641b_1_prt_4_unique2_idx | partitioned index | mpp3641_user | mpp3641b_1_prt_4
bfv_partition | mpp3641b_1_prt_5_2_prt_1_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_5_2_prt_1
bfv_partition | mpp3641b_1_prt_5_2_prt_1_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_5_2_prt_1
bfv_partition | mpp3641b_1_prt_5_2_prt_1_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_5_2_prt_1
bfv_partition | mpp3641b_1_prt_5_2_prt_1_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_5_2_prt_1
bfv_partition | mpp3641b_1_prt_5_2_prt_2_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_5_2_prt_2
bfv_partition | mpp3641b_1_prt_5_2_prt_2_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_5_2_prt_2
bfv_partition | mpp3641b_1_prt_5_2_prt_2_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_5_2_prt_2
bfv_partition | mpp3641b_1_prt_5_2_prt_2_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_5_2_prt_2
bfv_partition | mpp3641b_1_prt_5_2_prt_3_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_5_2_prt_3
bfv_partition | mpp3641b_1_prt_5_2_prt_3_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_5_2_prt_3
bfv_partition | mpp3641b_1_prt_5_2_prt_3_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_5_2_prt_3
bfv_partition | mpp3641b_1_prt_5_2_prt_3_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_5_2_prt_3
bfv_partition | mpp3641b_1_prt_5_2_prt_4_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_5_2_prt_4
bfv_partition | mpp3641b_1_prt_5_2_prt_4_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_5_2_prt_4
bfv_partition | mpp3641b_1_prt_5_2_prt_4_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_5_2_prt_4
bfv_partition | mpp3641b_1_prt_5_2_prt_4_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_5_2_prt_4
bfv_partition | mpp3641b_1_prt_5_2_prt_5_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_5_2_prt_5
bfv_partition | mpp3641b_1_prt_5_2_prt_5_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_5_2_prt_5
bfv_partition | mpp3641b_1_prt_5_2_prt_5_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_5_2_prt_5
bfv_partition | mpp3641b_1_prt_5_2_prt_5_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_5_2_prt_5
bfv_partition | mpp3641b_1_prt_5_hundred_idx | partitioned index | mpp3641_user | mpp3641b_1_prt_5
bfv_partition | mpp3641b_1_prt_5_stringu1_idx | partitioned index | mpp3641_user | mpp3641b_1_prt_5
bfv_partition | mpp3641b_1_prt_5_unique1_idx | partitioned index | mpp3641_user | mpp3641b_1_prt_5
bfv_partition | mpp3641b_1_prt_5_unique2_idx | partitioned index | mpp3641_user | mpp3641b_1_prt_5
bfv_partition | mpp3641b_1_prt_default_part_2_prt_1_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_default_part_2_prt_1
bfv_partition | mpp3641b_1_prt_default_part_2_prt_1_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_default_part_2_prt_1
bfv_partition | mpp3641b_1_prt_default_part_2_prt_1_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_default_part_2_prt_1
bfv_partition | mpp3641b_1_prt_default_part_2_prt_1_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_default_part_2_prt_1
bfv_partition | mpp3641b_1_prt_default_part_2_prt_2_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_default_part_2_prt_2
bfv_partition | mpp3641b_1_prt_default_part_2_prt_2_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_default_part_2_prt_2
bfv_partition | mpp3641b_1_prt_default_part_2_prt_2_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_default_part_2_prt_2
bfv_partition | mpp3641b_1_prt_default_part_2_prt_2_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_default_part_2_prt_2
bfv_partition | mpp3641b_1_prt_default_part_2_prt_3_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_default_part_2_prt_3
bfv_partition | mpp3641b_1_prt_default_part_2_prt_3_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_default_part_2_prt_3
bfv_partition | mpp3641b_1_prt_default_part_2_prt_3_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_default_part_2_prt_3
bfv_partition | mpp3641b_1_prt_default_part_2_prt_3_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_default_part_2_prt_3
bfv_partition | mpp3641b_1_prt_default_part_2_prt_4_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_default_part_2_prt_4
bfv_partition | mpp3641b_1_prt_default_part_2_prt_4_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_default_part_2_prt_4
bfv_partition | mpp3641b_1_prt_default_part_2_prt_4_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_default_part_2_prt_4
bfv_partition | mpp3641b_1_prt_default_part_2_prt_4_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_default_part_2_prt_4
bfv_partition | mpp3641b_1_prt_default_part_2_prt_5_hundred_idx | index | mpp3641_user | mpp3641b_1_prt_default_part_2_prt_5
bfv_partition | mpp3641b_1_prt_default_part_2_prt_5_stringu1_idx | index | mpp3641_user | mpp3641b_1_prt_default_part_2_prt_5
bfv_partition | mpp3641b_1_prt_default_part_2_prt_5_unique1_idx | index | mpp3641_user | mpp3641b_1_prt_default_part_2_prt_5
bfv_partition | mpp3641b_1_prt_default_part_2_prt_5_unique2_idx | index | mpp3641_user | mpp3641b_1_prt_default_part_2_prt_5
bfv_partition | mpp3641b_1_prt_default_part_hundred_idx | partitioned index | mpp3641_user | mpp3641b_1_prt_default_part
bfv_partition | mpp3641b_1_prt_default_part_stringu1_idx | partitioned index | mpp3641_user | mpp3641b_1_prt_default_part
bfv_partition | mpp3641b_1_prt_default_part_unique1_idx | partitioned index | mpp3641_user | mpp3641b_1_prt_default_part
bfv_partition | mpp3641b_1_prt_default_part_unique2_idx | partitioned index | mpp3641_user | mpp3641b_1_prt_default_part
bfv_partition | mpp3641b_hundred | partitioned index | mpp3641_user | mpp3641b
bfv_partition | mpp3641b_stringu1 | partitioned index | mpp3641_user | mpp3641b
bfv_partition | mpp3641b_unique1 | partitioned index | mpp3641_user | mpp3641b
bfv_partition | mpp3641b_unique2 | partitioned index | mpp3641_user | mpp3641b
drop table mpp3641a;
drop table mpp3641b;
RESET ROLE;
\di mpp3641*
\t
create schema rgs;
show search_path;
search_path
---------------
bfv_partition
(1 row)
create table rgs.mpp4604(
id int,
time timestamp
) partition by range( time ) (
partition p1 start (date '2008-10-14') end (date '2008-10-15'),
partition p2 end (date '2008-11-01')
);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Apache Cloudberry 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.
ALTER TABLE rgs.mpp4604 SPLIT PARTITION p2 AT( '2008-10-20' ) INTO( PARTITION p2_tmp, PARTITION p3 );
alter table rgs.mpp4604 drop partition p3;
drop schema rgs cascade;
NOTICE: drop cascades to table rgs.mpp4604
CREATE TABLE mpp3817 (
unique1 int4,
unique2 int4,
two int4,
four int4,
ten int4,
twenty int4,
hundred int4,
thousand int4,
twothousand int4,
fivethous int4,
tenthous int4,
odd int4,
even int4,
stringu1 name,
stringu2 name,
string4 name,
startDate date
) partition by range (startDate)
( start ('2007-01-01') end ('2008-01-01') every (interval '1 month'), default partition default_part );
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'unique1' as the Apache Cloudberry 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.
alter table mpp3817 drop column unique1; -- Set distribution key to randomly
NOTICE: dropping a column that is part of the distribution policy forces a random distribution policy
NOTICE: dropping a column that is part of the distribution policy forces a random distribution policy
NOTICE: dropping a column that is part of the distribution policy forces a random distribution policy
NOTICE: dropping a column that is part of the distribution policy forces a random distribution policy
NOTICE: dropping a column that is part of the distribution policy forces a random distribution policy
NOTICE: dropping a column that is part of the distribution policy forces a random distribution policy
NOTICE: dropping a column that is part of the distribution policy forces a random distribution policy
NOTICE: dropping a column that is part of the distribution policy forces a random distribution policy
NOTICE: dropping a column that is part of the distribution policy forces a random distribution policy
NOTICE: dropping a column that is part of the distribution policy forces a random distribution policy
NOTICE: dropping a column that is part of the distribution policy forces a random distribution policy
NOTICE: dropping a column that is part of the distribution policy forces a random distribution policy
NOTICE: dropping a column that is part of the distribution policy forces a random distribution policy
NOTICE: dropping a column that is part of the distribution policy forces a random distribution policy
alter table mpp3817 drop column unique2;
\d mpp3817
Partitioned table "bfv_partition.mpp3817"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
two | integer | | |
four | integer | | |
ten | integer | | |
twenty | integer | | |
hundred | integer | | |
thousand | integer | | |
twothousand | integer | | |
fivethous | integer | | |
tenthous | integer | | |
odd | integer | | |
even | integer | | |
stringu1 | name | | |
stringu2 | name | | |
string4 | name | | |
startdate | date | | |
Partition key: RANGE (startdate)
Number of partitions: 13 (Use \d+ to list them.)
Distributed randomly
\d mpp3817_1_prt_10
Table "bfv_partition.mpp3817_1_prt_10"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
two | integer | | |
four | integer | | |
ten | integer | | |
twenty | integer | | |
hundred | integer | | |
thousand | integer | | |
twothousand | integer | | |
fivethous | integer | | |
tenthous | integer | | |
odd | integer | | |
even | integer | | |
stringu1 | name | | |
stringu2 | name | | |
string4 | name | | |
startdate | date | | |
Partition of: mpp3817 FOR VALUES FROM ('09-01-2007') TO ('10-01-2007')
Distributed randomly
\d mpp3817_1_prt_default_part
Table "bfv_partition.mpp3817_1_prt_default_part"
Column | Type | Collation | Nullable | Default
-------------+---------+-----------+----------+---------
two | integer | | |
four | integer | | |
ten | integer | | |
twenty | integer | | |
hundred | integer | | |
thousand | integer | | |
twothousand | integer | | |
fivethous | integer | | |
tenthous | integer | | |
odd | integer | | |
even | integer | | |
stringu1 | name | | |
stringu2 | name | | |
string4 | name | | |
startdate | date | | |
Partition of: mpp3817 DEFAULT
Distributed randomly
drop table mpp3817;
-- All these should error out because they have overlapping range partitions
CREATE TABLE NATION (
N_NATIONKEY INTEGER,
N_NAME CHAR(25),
N_REGIONKEY INTEGER,
N_COMMENT VARCHAR(152)
)
partition by range (n_regionkey)
(
partition p1 start('0') end('1') inclusive, partition p2 start('1') end('5') inclusive
);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'n_nationkey' as the Apache Cloudberry 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: partition "nation_1_prt_p2" would overlap partition "nation_1_prt_p1"
CREATE TABLE ORDERS (
O_ORDERKEY INT8,
O_CUSTKEY INTEGER,
O_ORDERSTATUS CHAR(1),
O_TOTALPRICE decimal,
O_ORDERDATE date,
O_ORDERPRIORITY CHAR(15),
O_CLERK CHAR(15),
O_SHIPPRIORITY integer,
O_COMMENT VARCHAR(79)
)
partition by range (o_custkey)
subpartition by range (o_orderkey)
(
partition p1 start('1') end('150001') every 9 (150000)
(subpartition sp1 start('1') end('1500000'),subpartition sp2 start('1351816') end('6000001'))
);
ERROR: syntax error at or near "9"
LINE 15: partition p1 start('1') end('150001') every 9 (150000)
^
CREATE TABLE LINEITEM (
L_ORDERKEY INT8,
L_PARTKEY INTEGER,
L_SUPPKEY INTEGER,
L_LINENUMBER integer,
L_QUANTITY decimal,
L_EXTENDEDPRICE decimal,
L_DISCOUNT decimal,
L_TAX decimal,
L_RETURNFLAG CHAR(1),
L_LINESTATUS CHAR(1),
L_SHIPDATE date,
L_COMMITDATE date,
L_RECEIPTDATE date,
L_SHIPINSTRUCT CHAR(25),
L_SHIPMODE CHAR(10),
L_COMMENT VARCHAR(44)
)
partition by range (l_discount)
subpartition by range (l_quantity)
subpartition by range (l_tax) subpartition template (start('0') end('1.08') every 6 (1))
,subpartition by range (l_receiptdate) subpartition template (subpartition sp1 start('1992-01-03') end('1999-01-01'), subpartition sp2 start('1993-01-03') end ('1997-01-01'))
(
partition p1 start('0') end('1.1')
(subpartition sp1 start('1') end('51') every(10)
) , partition p2 start('1.1') end ('2.2') (subpartition sp1 start('1') end('51') every (5))
);
ERROR: syntax error at or near "6"
LINE 21: ...) subpartition template (start('0') end('1.08') every 6 (1))
^
CREATE TABLE ORDERS (
O_ORDERKEY INT8,
O_CUSTKEY INTEGER,
O_ORDERSTATUS CHAR(1),
O_TOTALPRICE decimal,
O_ORDERDATE date,
O_ORDERPRIORITY CHAR(15),
O_CLERK CHAR(15),
O_SHIPPRIORITY integer,
O_COMMENT VARCHAR(79)
)
partition by range (o_custkey)
subpartition by range (o_orderdate) subpartition template (start('1992-01-01') end('1998-08-03') every 3 (interval '12 months')
)
,subpartition by range (o_orderkey) subpartition template (start('1') end('6000001') every 3 (3000000)
)
(
partition p1 start('1') , partition p2 start('55170') end('114873'), partition p3 start('44717') end('56000'), partition p4 start('114873') end('150001')
);
ERROR: syntax error at or near "3"
LINE 13: ...late (start('1992-01-01') end('1998-08-03') every 3 (interva...
^
CREATE TABLE mpp3114 (f1 time(2) with time zone)
partition by range (f1)
(
partition pst start (time with time zone '00:00 PST') end (time with time zone '23:00 PST') EVERY (INTERVAL '1 hour'),
partition est start (time with time zone '00:00 EST') end (time with time zone '23:00 EST') EVERY (INTERVAL '1 hour')
);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'f1' as the Apache Cloudberry 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: partition "mpp3114_1_prt_est_4" would overlap partition "mpp3114_1_prt_pst_1"
DROP TABLE mpp3114;
ERROR: table "mpp3114" does not exist
CREATE TABLE sg_cal_detail_r1 (
datacenter character varying(32),
poolname character varying(128),
machinename character varying(128),
transactionid character varying(32),
threadid integer,
transactionorder integer,
eventclass character(1),
eventtime timestamp(2) without time zone,
eventtype character varying(128),
eventname character varying(128),
status character varying(128),
duration numeric(18,2),
data character varying(4096)
)
WITH (appendonly=true, compresslevel=5, blocksize=2097152)
DISTRIBUTED BY (transactionid)
PARTITION BY RANGE(eventtime)
SUBPARTITION BY LIST(datacenter)
SUBPARTITION TEMPLATE
(
SUBPARTITION SMF VALUES ('smf01','smf02'),
SUBPARTITION SJC VALUES ('sjc01','sjc02'),
SUBPARTITION DEN VALUES ('den01','den02'),
SUBPARTITION PHX VALUES ('phx01','phx02'),
DEFAULT SUBPARTITION xdc
)
SUBPARTITION BY LIST(eventtype)
SUBPARTITION TEMPLATE
(
SUBPARTITION ET1 VALUES ('EXEC'),
SUBPARTITION ET2 VALUES ('URL','EXECP','ufb'),
SUBPARTITION ET3 VALUES
('EXECT','V3Rules','SOJ','MEQ','RTM','TL','ActiveRules','RTMe','API',
'Info','BizProcess','APIRequest','_ui','Warning','Consume','XML','DSAPar
serTransform'),
SUBPARTITION ET4 VALUES('InflowHandler',
'TaskType',
'LOG',
'FETCH',
'TD',
'AxisInflowPipeline',
'AxisOutflowPipeline',
'API Security',
'SD_DSBE',
'SD_ExpressSale',
'V4Header',
'V4Footer',
'SOAP_Handler',
'MLR',
'EvictedStmtRemove',
'CT',
'DSATransform',
'APIClient',
'DSAQueryExec',
'processDSA',
'FilterEngine',
'Prefetch',
'AsyncCb',
'MC',
'SQL',
'SD_UInfo',
'TnSPayload',
'Serialization',
'CxtSetup',
'LazyInit',
'Deserialization',
'CleanUp',
'RESTDeserialize',
'RESTSerialize',
'SD_StoreNames',
'Serialize',
'Deserialize',
'SVC_INVOKE',
'SD_TitleAggr',
'eLVIS',
'SD_Promo',
'ServerCalLogId',
'SD_DSA',
'ClientCalLogId',
'NCF Async processor',
'V3Rules_OLAP',
'RTAM',
'SOAP_Handlers',
'SOAP_Ser',
'SOAP_Exec',
'RtmAB',
'RTPromotionOptimizer',
'crypt',
'Error',
'DBGetDataHlp',
'NoEncoding',
'Default',
'PromoAppCenter',
'BES_CONSUMER',
'TitleKeywordsModel',
'SOA_CLIENT',
'SD_UserContent',
'NCF',
'BEGenericPortlet',
'PortletExecution',
'SoaPortlet',
'ICEP',
'LOGIC',
'SYI_Eval_Detail',
'SD_Catalog',
'SignIn_Eval_Detail',
'Elvis Client',
'BES',
'TIMESTAMP',
'TLH',
'TLH-PRE-SYI',
'RFC',
'Offer_Eval_Detail',
'SFE_RunQuery',
'DBGetData',
'TKOItem2',
'Notification',
'XSHModel',
'APIDefinition',
'captcha',
'SD_HalfItem',
'Mail_Transport',
'MODPUT',
'60DAY_OLD_ITEM_FETCHED',
'List',
'RemotePortlet',
'MakeOffer_Eval_Detail',
'60_TO_90_DAY_OLD_ITEM_FETCHED',
'Logic',
'RtmGetContentName',
'BEPortletService',
'SYI_EUP_Rbo',
'SYI_Rbo',
'EOA',
'SEC',
'CCHP',
'TKOItem3',
'TnsFindingModelBucket',
'Mail_Send',
'SignIn_Rbo',
'SignIn=23_elvisEvl',
'TnsFindingModelXSH',
'RtmSvc',
'SWEET_TOOTH_LOCATOR_EXPIRED',
'COOKIE_INFO',
'Database',
'RYI_Eval_Detail',
'TnsFindingModelSNP',
'TitleRiskScoringModel_2_0',
'ClientIPin10',
'TnsFindingModelFraud',
'SignIn_BaseRbo2',
'Offer_EUP_Rbo',
'Offer_Rbo',
'FSA',
'Processing_elvis_events',
'NSS_API',
'MyebayBetaRedirect',
'MOTORS_PARTNER_RECIPIENT_HANDLER',
'ElvisEngine',
'PreSyi_Eval_Detail',
'RADAR',
'Latency',
'SD_TAggrCache',
'MEA',
'SD_TitleAggregatorShopping',
'KEM',
'SD_Batch',
'KG',
'ITEM_VISIBILITY',
'APPLOGIC',
'OOPexecute',
'ERRPAGE',
'FQ_RECIPIENT_HANDLER',
'RADAR_POST_Eval_Detail',
'Captcha',
'V3Rules_Detail',
'FilterEngDetail_AAQBuyerSentPre',
'Task',
'SYI_EUP_Report',
'WRITE_MOVE_FILE',
'KG_SYI',
'BatchRecord',
'SD_TitleDesc',
'B001_RTAM',
'SignIn_Report',
'SD_StoreUrl',
'CACHE_REFRESH',
'TKOItem',
'KG_EXTERNAL_CALL',
'WatchDelSkipped',
'SD_Completed',
'RequestCounts',
'FilterEngDetail_RTQEmail',
'FilterEngDetail_AAQBuyerSentPost',
'RYI_EUP_Rbo',
'RYI_Rbo',
'MF_RECIPIENT_HANDLER',
'SYI_Report',
'LCBT',
'HalfRyiSingle_Eval_Detail',
'FilterEngDetail_AAQBuyerSentEmail',
'ViewAdAbTests',
'MakeOffer_EUP_Rbo',
'MakeOffer_Rbo',
'ShipCalc Url',
'Offer_Report',
'TKOUser',
'RADAR_POST_EUP_Rbo',
'SiteStat_LeftNav',
'SiteStat_UserIsSeller',
'FilterEngDetail_RTQPost',
'INFO',
'Offer_EUP_Report',
'RADAR_POST_Rbo',
'SignIn_EUP_Rbo',
'Mail_XML',
'Processing_item_events',
'GEM',
'Mail',
'ELVIS',
'FilterEngDetail_SYIRYI',
'SD_TitleCach',
'Processing_itemend_events',
'HalfRyiSingle_EUP_Rbo',
'AlertNotify',
'AVSRedirectLog',
'BillerService',
'MENMSG',
'UserSegSvc',
'PRICE_CHANGE_ALERT_RECIPIENT_HANDLER',
'NSSOptP',
'PreSyi_Rbo',
'PreSyi_EUP_Rbo',
'NOTIFICATION.BES.BID_NEW',
'Mail_Connect',
'Mail_Close',
'GEMRECORD',
'McapCommunicatorTx',
'IMGPROC',
'KnownGood',
'FilterEngDetail_RTQPre',
'AUTH',
'BULKAPI',
'AAQBuyerSentPre_Eval_Detail',
'RYI_EUP_Report',
'HalfRyiSingle_Rbo',
'MakeOffer_Report',
'ItemClosureLOGIC',
'MakeOffer_EUP_Report',
'RADAR_POST_Report',
'BidBinStats',
'Iterator',
'RADAR_POST_EUP_Report',
'SessionStats',
'RYI_Report',
'SIBE',
'EOT',
'UsageProcessingTx',
'Processing_itemrevised_events',
'HalfSyiSingle_Eval_Detail',
'SignIn_EUP_Report',
'Referer',
'RTQEmail_Eval_Detail',
'AAQBuyerSentPost_Eval_Detail',
'AAQBuyerSentEmail_Eval_Detail',
'NCFEvent',
'CHKOUT',
'SocketWriter',
'RTQPost_Eval_Detail',
'HalfRyiSingle_Report',
'HalfRyiSingle_EUP_Report',
'DcpConnectRequest',
'SD_CatalogCache',
'PreSyi_Report',
'BotSignIn',
'Total Listing : BE_MAIN',
'Z',
'ItemPopularityScore',
'SD_TitleCacheOverflow',
'UserSegmentationCommand',
'FilterEngDetail_AAQSellerSentPre',
'PreSyi_EUP_Report',
'FilterEngDetail_AAQSellerSentPost',
'FilterEngDetail_BestOffer',
'RS',
'FilterEngDetail_AAQSellerSentEmail',
'HalfSyiSingle_EUP_Rbo',
'Service',
'Total Listing : BE_DE',
'BULK.API.HALF.PUT'),
DEFAULT SUBPARTITION etx
)
(
START ('2008-09-30')
END ('2008-10-01')
EVERY (INTERVAL '1 day')
);
ALTER TABLE sg_cal_detail_r1 ADD PARTITION START ('2008-10-01') INCLUSIVE END ('2008-10-02') EXCLUSIVE
WITH (appendonly=true, compresslevel=5, blocksize=2097152);
select count(*) from pg_class where relname like 'sg_cal_detail_r1%';
count
-------
63
(1 row)
drop table sg_cal_detail_r1;
create table j (i int, a date) partition by range(i)
subpartition by list(a) subpartition template
(subpartition a values(1, 2, 3, 4),
subpartition b values(5, 6, 7, 8))
(
start (date '2001-01-01'),
start (date '2002-01-01'),
start (date '2003-01-01'),
start (date '2004-01-01'),
start (date '2005-01-01')
);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry 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: specified value cannot be cast to type integer for column "i"
LINE 6: start (date '2001-01-01'),
^
set optimizer_analyze_root_partition=on;
create table mpp3487 (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 Apache Cloudberry 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 mpp3487 select i from generate_series(1, 9) i;
vacuum analyze mpp3487;
select schemaname, tablename, attname, null_frac, avg_width, n_distinct, most_common_freqs, histogram_bounds from pg_stats where tablename like 'mpp3487%' order by 2;
schemaname | tablename | attname | null_frac | avg_width | n_distinct | most_common_freqs | histogram_bounds
---------------+-----------------+---------+-----------+-----------+------------+-------------------+------------------
bfv_partition | mpp3487 | i | 0 | 4 | -1 | |
bfv_partition | mpp3487_1_prt_1 | i | 0 | 4 | -1 | |
bfv_partition | mpp3487_1_prt_2 | i | 0 | 4 | -1 | |
bfv_partition | mpp3487_1_prt_3 | i | 0 | 4 | -1 | |
bfv_partition | mpp3487_1_prt_4 | i | 0 | 4 | -1 | |
bfv_partition | mpp3487_1_prt_5 | i | 0 | 4 | -1 | |
bfv_partition | mpp3487_1_prt_6 | i | 0 | 4 | -1 | |
bfv_partition | mpp3487_1_prt_7 | i | 0 | 4 | -1 | |
bfv_partition | mpp3487_1_prt_8 | i | 0 | 4 | -1 | |
bfv_partition | mpp3487_1_prt_9 | i | 0 | 4 | -1 | |
(10 rows)
drop table mpp3487;
-- Negative Tests for alter subpartition template syntax with Schema
create schema qa147;
CREATE TABLE qa147.sales (trans_id int, date date, amount
decimal(9,2), region text)
DISTRIBUTED BY (trans_id)
PARTITION BY RANGE (date)
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE
( SUBPARTITION usa VALUES ('usa'),
SUBPARTITION asia VALUES ('asia'),
SUBPARTITION europe VALUES ('europe') )
( START (date '2008-01-01') INCLUSIVE
END (date '2009-01-01') EXCLUSIVE
EVERY (INTERVAL '1 month') );
-- Invalid TEMPLATE
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (NULL);
ERROR: syntax error at or near "NULL"
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (NULL);
^
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (-1);
ERROR: syntax error at or near "-"
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (-1);
^
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (10000);
ERROR: syntax error at or near "10000"
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (10000);
^
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE ('');
ERROR: syntax error at or near "''"
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE ('');
^
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE ("");
ERROR: zero-length delimited identifier at or near """"
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE ("");
^
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (*);
ERROR: syntax error at or near "*"
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (*);
^
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (1*);
ERROR: syntax error at or near "1"
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (1*);
^
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE ("1*");
ERROR: syntax error at or near ""1*""
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE ("1*");
^
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (ABC);
ERROR: syntax error at or near "ABC"
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (ABC);
^
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE ($);
ERROR: syntax error at or near "$"
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE ($);
^
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (%%);
ERROR: syntax error at or near "%%"
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (%%);
^
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (#);
ERROR: syntax error at or near "#"
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (#);
^
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (!);
ERROR: syntax error at or near "!"
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (!);
^
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (&);
ERROR: syntax error at or near "&"
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (&);
^
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (^);
ERROR: syntax error at or near "^"
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (^);
^
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (@);
ERROR: syntax error at or near "@"
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (@);
^
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (<);
ERROR: syntax error at or near "<"
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (<);
^
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (>);
ERROR: syntax error at or near ">"
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (>);
^
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (.);
ERROR: syntax error at or near "."
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (.);
^
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (?);
ERROR: syntax error at or near "?"
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (?);
^
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (/);
ERROR: syntax error at or near "/"
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (/);
^
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (|);
ERROR: syntax error at or near "|"
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (|);
^
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (~);
ERROR: syntax error at or near "~"
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (~);
^
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (`);
ERROR: syntax error at or near "`"
LINE 1: ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE (`);
^
select relid::regclass, level, template from gp_partition_template where relid = 'qa147.sales'::regclass;
relid | level | template
-------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
qa147.sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM :partName usa :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "usa" :location 216}))} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>} {GPPARTDEFELEM :partName asia :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "asia" :location 252}))} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>} {GPPARTDEFELEM :partName europe :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "europe" :location 291}))} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
(1 row)
set client_min_messages='warning';
drop schema qa147 cascade;
reset client_min_messages;
select relid, level, template from gp_partition_template where not exists (select oid from pg_class where oid = relid);
relid | level | template
-------+-------+----------
(0 rows)
-- Mix-Match for Alter subpartition template
CREATE TABLE qa147sales (trans_id int, date date, amount
decimal(9,2), region text)
DISTRIBUTED BY (trans_id)
PARTITION BY RANGE (date)
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE
( SUBPARTITION usa VALUES ('usa'),
SUBPARTITION asia VALUES ('asia'),
SUBPARTITION europe VALUES ('europe') )
( START (date '2008-01-01') INCLUSIVE
END (date '2009-01-01') EXCLUSIVE
EVERY (INTERVAL '1 month') );
-- Clear TEMPLATE
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE ();
select relid::regclass, level, template from gp_partition_template where relid = 'qa147sales'::regclass;
relid | level | template
-------+-------+----------
(0 rows)
-- This will overwrite previous subpartition template
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ('asia') );
select relid::regclass, level, template from gp_partition_template where relid = 'qa147sales'::regclass;
relid | level | template
------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
qa147sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM :partName usa :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "usa" :location 76}))} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>} {GPPARTDEFELEM :partName asia :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "asia" :location 110}))} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
(1 row)
-- Invalid subpartition
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END (date '2008-02-01') EXCLUSIVE );
ERROR: invalid boundary specification for LIST partition
LINE 2: ( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END...
^
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE EVERY (INTERVAL '1 month') );
ERROR: invalid boundary specification for LIST partition
LINE 2: ( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END...
^
select relid::regclass, level, template from gp_partition_template where relid = 'qa147sales'::regclass;
relid | level | template
------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
qa147sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM :partName usa :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "usa" :location 76}))} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>} {GPPARTDEFELEM :partName asia :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "asia" :location 110}))} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
(1 row)
-- Mix and Match RANGE/LIST . Expect to Error
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
(
SUBPARTITION usa1 VALUES('usa'),
SUBPARTITION usadate start (date '2008-01-01') INCLUSIVE END(date '2009-01-01') EXCLUSIVE);
ERROR: invalid boundary specification for LIST partition
LINE 4: SUBPARTITION usadate start (date '2008-01-01') INCLUSIVE END...
^
select relid::regclass, level, template from gp_partition_template where relid = 'qa147sales'::regclass;
relid | level | template
------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
qa147sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM :partName usa :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "usa" :location 76}))} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>} {GPPARTDEFELEM :partName asia :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "asia" :location 110}))} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
(1 row)
-- Mix and Match RANGE/LIST . Expect to Error
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
(
SUBPARTITION usadate start (date '2008-01-01') INCLUSIVE END(date '2009-01-01') EXCLUSIVE,
SUBPARTITION usa1 VALUES('usa'));
ERROR: invalid boundary specification for LIST partition
LINE 3: SUBPARTITION usadate start (date '2008-01-01') INCLUSIVE END...
^
select relid::regclass, level, template from gp_partition_template where relid = 'qa147sales'::regclass;
relid | level | template
------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
qa147sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM :partName usa :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "usa" :location 76}))} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>} {GPPARTDEFELEM :partName asia :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "asia" :location 110}))} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
(1 row)
drop table qa147sales;
CREATE TABLE qa147sales (trans_id int, date date, amount
decimal(9,2), region text)
DISTRIBUTED BY (trans_id)
PARTITION BY LIST (region)
SUBPARTITION BY RANGE (date)
SUBPARTITION TEMPLATE
( START (date '2008-01-01') INCLUSIVE
END (date '2009-01-01') EXCLUSIVE
EVERY (INTERVAL '1 month') )
(
PARTITION usa VALUES ('usa'),
PARTITION asia VALUES ('asia'),
PARTITION europe VALUES ('europe') );
-- Clear TEMPLATE
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE ();
select relid::regclass, level, template from gp_partition_template where relid = 'qa147sales'::regclass;
relid | level | template
-------+-------+----------
(0 rows)
-- This will overwrite previous subpartition template
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END (date '2008-02-01') EXCLUSIVE );
select relid::regclass, level, template from gp_partition_template where relid = 'qa147sales'::regclass;
relid | level | template
------------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
qa147sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM :partName usam1 :boundSpec {GPPARTITIONRANGESPEC :partStart {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2008-01-01" :location 82} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 77} :location -1}) :edge 1} :partEnd {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2008-02-01" :location 116} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 111} :location -1}) :edge 2} :partEvery <>} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
(1 row)
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE EVERY (INTERVAL '1 month') );
select relid::regclass, level, template from gp_partition_template where relid = 'qa147sales'::regclass;
relid | level | template
------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
qa147sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM :partName usam1 :boundSpec {GPPARTITIONRANGESPEC :partStart {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2008-01-01" :location 82} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 77} :location -1}) :edge 1} :partEnd {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2009-01-01" :location 116} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 111} :location -1}) :edge 2} :partEvery ({TYPECAST :arg {A_CONST :val "\1\ month" :location 156} :typeName {TYPENAME :names ("pg_catalog" "interval") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 147} :location -1})} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
(1 row)
-- Invalid subpartition template
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ('asia') );
ERROR: invalid boundary specification for RANGE partition
LINE 2: ( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ...
^
select relid::regclass, level, template from gp_partition_template where relid = 'qa147sales'::regclass;
relid | level | template
------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
qa147sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM :partName usam1 :boundSpec {GPPARTITIONRANGESPEC :partStart {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2008-01-01" :location 82} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 77} :location -1}) :edge 1} :partEnd {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2009-01-01" :location 116} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 111} :location -1}) :edge 2} :partEvery ({TYPECAST :arg {A_CONST :val "\1\ month" :location 156} :typeName {TYPENAME :names ("pg_catalog" "interval") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 147} :location -1})} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
(1 row)
-- Mix and Match RANGE/LIST . Expect to Error
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
(
SUBPARTITION usa1 VALUES('usa'),
SUBPARTITION usadate start (date '2008-01-01') INCLUSIVE END(date '2009-01-01') EXCLUSIVE);
ERROR: invalid boundary specification for RANGE partition
LINE 3: SUBPARTITION usa1 VALUES('usa'),
^
select relid::regclass, level, template from gp_partition_template where relid = 'qa147sales'::regclass;
relid | level | template
------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
qa147sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM :partName usam1 :boundSpec {GPPARTITIONRANGESPEC :partStart {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2008-01-01" :location 82} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 77} :location -1}) :edge 1} :partEnd {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2009-01-01" :location 116} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 111} :location -1}) :edge 2} :partEvery ({TYPECAST :arg {A_CONST :val "\1\ month" :location 156} :typeName {TYPENAME :names ("pg_catalog" "interval") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 147} :location -1})} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
(1 row)
-- Mix and Match RANGE/LIST . Expect to Error
ALTER TABLE qa147sales SET SUBPARTITION TEMPLATE
(
SUBPARTITION usadate start (date '2008-01-01') INCLUSIVE END(date '2009-01-01') EXCLUSIVE,
SUBPARTITION usa1 VALUES('usa'));
ERROR: invalid boundary specification for RANGE partition
LINE 4: SUBPARTITION usa1 VALUES('usa'));
^
select relid::regclass, level, template from gp_partition_template where relid = 'qa147sales'::regclass;
relid | level | template
------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
qa147sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM :partName usam1 :boundSpec {GPPARTITIONRANGESPEC :partStart {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2008-01-01" :location 82} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 77} :location -1}) :edge 1} :partEnd {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2009-01-01" :location 116} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 111} :location -1}) :edge 2} :partEvery ({TYPECAST :arg {A_CONST :val "\1\ month" :location 156} :typeName {TYPENAME :names ("pg_catalog" "interval") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 147} :location -1})} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
(1 row)
drop table qa147sales;
select relid, level, template from gp_partition_template where not exists (select oid from pg_class where oid = relid);
relid | level | template
-------+-------+----------
(0 rows)
-- Now with Schema
-- Mix-Match for Alter subpartition template in a schema
create schema qa147;
CREATE TABLE qa147.sales (trans_id int, date date, amount
decimal(9,2), region text)
DISTRIBUTED BY (trans_id)
PARTITION BY RANGE (date)
SUBPARTITION BY LIST (region)
SUBPARTITION TEMPLATE
( SUBPARTITION usa VALUES ('usa'),
SUBPARTITION asia VALUES ('asia'),
SUBPARTITION europe VALUES ('europe') )
( START (date '2008-01-01') INCLUSIVE
END (date '2009-01-01') EXCLUSIVE
EVERY (INTERVAL '1 month') );
-- Clear TEMPLATE
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE ();
select relid::regclass, level, template from gp_partition_template where relid = 'qa147.sales'::regclass;
relid | level | template
-------+-------+----------
(0 rows)
-- This will overwrite previous subpartition template
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ('asia') );
select relid::regclass, level, template from gp_partition_template where relid = 'qa147.sales'::regclass;
relid | level | template
-------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
qa147.sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM :partName usa :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "usa" :location 77}))} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>} {GPPARTDEFELEM :partName asia :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "asia" :location 111}))} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
(1 row)
-- Invalid subpartition
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END (date '2008-02-01') EXCLUSIVE );
ERROR: invalid boundary specification for LIST partition
LINE 2: ( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END...
^
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE EVERY (INTERVAL '1 month') );
ERROR: invalid boundary specification for LIST partition
LINE 2: ( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END...
^
select relid::regclass, level, template from gp_partition_template where relid = 'qa147.sales'::regclass;
relid | level | template
-------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
qa147.sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM :partName usa :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "usa" :location 77}))} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>} {GPPARTDEFELEM :partName asia :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "asia" :location 111}))} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
(1 row)
-- Mix and Match RANGE/LIST . Expect to Error
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
(
SUBPARTITION usa1 VALUES('usa'),
SUBPARTITION usadate start (date '2008-01-01') INCLUSIVE END(date '2009-01-01') EXCLUSIVE);
ERROR: invalid boundary specification for LIST partition
LINE 4: SUBPARTITION usadate start (date '2008-01-01') INCLUSIVE END...
^
select relid::regclass, level, template from gp_partition_template where relid = 'qa147.sales'::regclass;
relid | level | template
-------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
qa147.sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM :partName usa :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "usa" :location 77}))} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>} {GPPARTDEFELEM :partName asia :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "asia" :location 111}))} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
(1 row)
-- Mix and Match RANGE/LIST . Expect to Error
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
(
SUBPARTITION usadate start (date '2008-01-01') INCLUSIVE END(date '2009-01-01') EXCLUSIVE,
SUBPARTITION usa1 VALUES('usa'));
ERROR: invalid boundary specification for LIST partition
LINE 3: SUBPARTITION usadate start (date '2008-01-01') INCLUSIVE END...
^
select relid::regclass, level, template from gp_partition_template where relid = 'qa147.sales'::regclass;
relid | level | template
-------------+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
qa147.sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM :partName usa :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "usa" :location 77}))} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>} {GPPARTDEFELEM :partName asia :boundSpec {GPPARTITIONLISTSPEC :partValues (({A_CONST :val "asia" :location 111}))} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
(1 row)
DROP SCHEMA qa147 cascade;
NOTICE: drop cascades to table qa147.sales
select relid, level, template from gp_partition_template where not exists (select oid from pg_class where oid = relid);
relid | level | template
-------+-------+----------
(0 rows)
CREATE SCHEMA qa147;
CREATE TABLE qa147.sales (trans_id int, date date, amount
decimal(9,2), region text)
DISTRIBUTED BY (trans_id)
PARTITION BY LIST (region)
SUBPARTITION BY RANGE (date)
SUBPARTITION TEMPLATE
( START (date '2008-01-01') INCLUSIVE
END (date '2009-01-01') EXCLUSIVE
EVERY (INTERVAL '1 month') )
(
PARTITION usa VALUES ('usa'),
PARTITION asia VALUES ('asia'),
PARTITION europe VALUES ('europe') );
-- Clear TEMPLATE
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE ();
select relid::regclass, level, template from gp_partition_template where relid = 'qa147.sales'::regclass;
relid | level | template
-------+-------+----------
(0 rows)
-- This will overwrite previous subpartition template
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END (date '2008-02-01') EXCLUSIVE );
select relid::regclass, level, template from gp_partition_template where relid = 'qa147.sales'::regclass;
relid | level | template
-------------+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
qa147.sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM :partName usam1 :boundSpec {GPPARTITIONRANGESPEC :partStart {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2008-01-01" :location 83} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 78} :location -1}) :edge 1} :partEnd {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2008-02-01" :location 117} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 112} :location -1}) :edge 2} :partEvery <>} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
(1 row)
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usam1 start (date '2008-01-01') INCLUSIVE END (date '2009-01-01') EXCLUSIVE EVERY (INTERVAL '1 month') );
select relid::regclass, level, template from gp_partition_template where relid = 'qa147.sales'::regclass;
relid | level | template
-------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
qa147.sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM :partName usam1 :boundSpec {GPPARTITIONRANGESPEC :partStart {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2008-01-01" :location 83} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 78} :location -1}) :edge 1} :partEnd {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2009-01-01" :location 117} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 112} :location -1}) :edge 2} :partEvery ({TYPECAST :arg {A_CONST :val "\1\ month" :location 157} :typeName {TYPENAME :names ("pg_catalog" "interval") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 148} :location -1})} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
(1 row)
-- Invalid subpartition template
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ('asia') );
ERROR: invalid boundary specification for RANGE partition
LINE 2: ( SUBPARTITION usa VALUES ('usa'), SUBPARTITION asia VALUES ...
^
select relid::regclass, level, template from gp_partition_template where relid = 'qa147.sales'::regclass;
relid | level | template
-------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
qa147.sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM :partName usam1 :boundSpec {GPPARTITIONRANGESPEC :partStart {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2008-01-01" :location 83} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 78} :location -1}) :edge 1} :partEnd {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2009-01-01" :location 117} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 112} :location -1}) :edge 2} :partEvery ({TYPECAST :arg {A_CONST :val "\1\ month" :location 157} :typeName {TYPENAME :names ("pg_catalog" "interval") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 148} :location -1})} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
(1 row)
-- Mix and Match RANGE/LIST . Expect to Error
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
(
SUBPARTITION usa1 VALUES('usa'),
SUBPARTITION usadate start (date '2008-01-01') INCLUSIVE END(date '2009-01-01') EXCLUSIVE);
ERROR: invalid boundary specification for RANGE partition
LINE 3: SUBPARTITION usa1 VALUES('usa'),
^
select relid::regclass, level, template from gp_partition_template where relid = 'qa147.sales'::regclass;
relid | level | template
-------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
qa147.sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM :partName usam1 :boundSpec {GPPARTITIONRANGESPEC :partStart {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2008-01-01" :location 83} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 78} :location -1}) :edge 1} :partEnd {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2009-01-01" :location 117} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 112} :location -1}) :edge 2} :partEvery ({TYPECAST :arg {A_CONST :val "\1\ month" :location 157} :typeName {TYPENAME :names ("pg_catalog" "interval") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 148} :location -1})} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
(1 row)
-- Mix and Match RANGE/LIST . Expect to Error
ALTER TABLE qa147.sales SET SUBPARTITION TEMPLATE
(
SUBPARTITION usadate start (date '2008-01-01') INCLUSIVE END(date '2009-01-01') EXCLUSIVE,
SUBPARTITION usa1 VALUES('usa'));
ERROR: invalid boundary specification for RANGE partition
LINE 4: SUBPARTITION usa1 VALUES('usa'));
^
select relid::regclass, level, template from gp_partition_template where relid = 'qa147.sales'::regclass;
relid | level | template
-------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
qa147.sales | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM :partName usam1 :boundSpec {GPPARTITIONRANGESPEC :partStart {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2008-01-01" :location 83} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 78} :location -1}) :edge 1} :partEnd {GPPARTITIONRANGEITEM :val ({TYPECAST :arg {A_CONST :val "\2009-01-01" :location 117} :typeName {TYPENAME :names ("date") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 112} :location -1}) :edge 2} :partEvery ({TYPECAST :arg {A_CONST :val "\1\ month" :location 157} :typeName {TYPENAME :names ("pg_catalog" "interval") :typeOid 0 :setof false :pct_type false :typmods <> :typemod -1 :arrayBounds <> :location 148} :location -1})} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
(1 row)
drop schema qa147 cascade;
NOTICE: drop cascades to table qa147.sales
select relid, level, template from gp_partition_template where not exists (select oid from pg_class where oid = relid);
relid | level | template
-------+-------+----------
(0 rows)
set gp_autostats_mode=on_change;
set gp_autostats_on_change_threshold=100;
create table mpp5427 (i int) partition by range (i) (start(1) end(10000000) every(100000));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'i' as the Apache Cloudberry 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 mpp5427 select i from generate_series(1, 100) i;
select * from pg_stats where tablename like 'mpp5427%';
schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram
------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------
(0 rows)
insert into mpp5427 select i from generate_series(1, 100000) i;
select * from pg_stats where tablename like 'mpp5427%';
schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram
------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------
(0 rows)
insert into mpp5427 select i from generate_series(1, 1000000) i;
select * from pg_stats where tablename like 'mpp5427%';
schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram
------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------
(0 rows)
truncate table mpp5427;
alter table mpp5427 add default partition default_part;
insert into mpp5427 select i from generate_series(1, 100) i;
select * from pg_stats where tablename like 'mpp5427%';
schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram
------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------
(0 rows)
insert into mpp5427 select i from generate_series(1, 100000) i;
select * from pg_stats where tablename like 'mpp5427%';
schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram
------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------
(0 rows)
insert into mpp5427 select i from generate_series(1, 1000000) i;
select * from pg_stats where tablename like 'mpp5427%';
schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram
------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------
(0 rows)
insert into mpp5427 select i from generate_series(10000000, 15000000) i;
select * from pg_stats where tablename like 'mpp5427%';
schemaname | tablename | attname | inherited | null_frac | avg_width | n_distinct | most_common_vals | most_common_freqs | histogram_bounds | correlation | most_common_elems | most_common_elem_freqs | elem_count_histogram
------------+-----------+---------+-----------+-----------+-----------+------------+------------------+-------------------+------------------+-------------+-------------------+------------------------+----------------------
(0 rows)
drop table mpp5427;
-- MPP-5524
create table mpp5524 (a int, b int, c int, d int) partition by range(d) (start(1) end(20) every(5));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry 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.
-- Not allowed
alter table mpp5524 alter partition for (1) set distributed by (b);
ERROR: can't set the distribution policy of "mpp5524_1_prt_1"
HINT: Distribution policy of a partition can only be the same as its parent's.
-- Not allowed
alter table mpp5524 alter partition for (2) set distributed by (c);
ERROR: can't set the distribution policy of "mpp5524_1_prt_1"
HINT: Distribution policy of a partition can only be the same as its parent's.
insert into mpp5524 select i, i+1, i+2, i+3 from generate_series(1, 10) i;
drop table mpp5524;
CREATE TABLE fff_main (id int, rank int, year int, gender char(1), count int)
partition by range (year) ( start (2001) end (2006) every ('1'));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Apache Cloudberry 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.
alter table fff_main_1_prt_1 drop oids;
ERROR: column "oids" of relation "fff_main_1_prt_1" does not exist
alter table fff_main_1_prt_1 no inherit fff_main;
ERROR: cannot change inheritance of a partition
alter table fff_main_1_prt_1 drop column rank;
ERROR: cannot drop inherited column "rank"
alter table fff_main_1_prt_1 add partition;
ERROR: table "fff_main_1_prt_1" is not partitioned
alter table fff_main_1_prt_1 drop partition for (0);
ERROR: table "fff_main_1_prt_1" is not partitioned
alter table fff_main_1_prt_1 add column c int;
ERROR: cannot add column to a partition
create table fff_main2 (like fff_main);
NOTICE: table doesn't have 'DISTRIBUTED BY' clause, defaulting to distribution columns from LIKE table
alter table fff_main_1_prt_1 inherit fff_main2;
ERROR: cannot change inheritance of a partition
alter table fff_main_1_prt_1 alter column i type bigint;
ERROR: column "i" of relation "fff_main_1_prt_1" does not exist
alter table fff_main_1_prt_1 drop constraint fff_main_1_prt_1_check;
ERROR: constraint "fff_main_1_prt_1_check" of relation "fff_main_1_prt_1" does not exist
-- Add default partition
alter table fff_main_1_prt_1 split partition def at ('2009');
ERROR: table "fff_main_1_prt_1" is not partitioned
alter table fff_main add default partition def;
alter table fff_main_1_prt_1 split partition def at ('2009');
ERROR: table "fff_main_1_prt_1" is not partitioned
-- Unable to coalesce or merge, not supported
alter table fff_main_1_prt_1 exchange partition aa with table fff_main_1_prt_2;
ERROR: table "fff_main_1_prt_1" is not partitioned
alter table fff_main add partition aa start ('2008') end ('2009');
alter table fff_main add partition bb start ('2009') end ('2010');
alter table fff_main_1_prt_1 add partition cc start ('2010') end ('2011');
ERROR: table "fff_main_1_prt_1" is not partitioned
drop table fff_main, fff_main2;
CREATE TABLE partsupp_def ( ps_partkey int,
ps_suppkey integer, ps_availqty integer,
ps_supplycost numeric, ps_comment character varying(199) )
PARTITION BY RANGE(ps_partkey)
subpartition by range (ps_partkey)
subpartition template
( subpartition sp1 start(0) end (300) every(100),
default subpartition subdef
)
( partition aa start (0) end (300) every(100),
default partition def
);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'ps_partkey' as the Apache Cloudberry 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.
alter table partsupp_def set subpartition template();
alter table partsupp_def set subpartition template( subpartition aaa start(400) end (600) every(100) );
-- Note 1: We cannot add subpartition template since we have a default partition
-- If we want to use the new subpartition template, we have to drop the default partition first, and then readd the default partition
-- Note 2: We do not support this function yet, but if we are able to split default partition with default subpartition, would we
-- be using the subpartition template to definte the "new" partition or the existing one.
select relid::regclass, level, template from gp_partition_template where relid = 'partsupp_def'::regclass;
relid | level | template
--------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
partsupp_def | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM :partName aaa :boundSpec {GPPARTITIONRANGESPEC :partStart {GPPARTITIONRANGEITEM :val ({A_CONST :val 400 :location 75}) :edge 1} :partEnd {GPPARTITIONRANGEITEM :val ({A_CONST :val 600 :location 85}) :edge 2} :partEvery ({A_CONST :val 100 :location 96})} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
(1 row)
alter table partsup_def add partition f1 start(0) end (300) every(100);
ERROR: syntax error at or near "every"
LINE 1: ...e partsup_def add partition f1 start(0) end (300) every(100)...
^
-- This works adding subpartition template with parent default partition
CREATE TABLE partsupp_def2 ( ps_partkey int,
ps_suppkey integer, ps_availqty integer,
ps_supplycost numeric, ps_comment character varying(199) )
PARTITION BY RANGE(ps_partkey)
subpartition by range (ps_partkey)
subpartition template
( subpartition sp1 start(0) end (300) every(100),
default subpartition subdef
)
( partition aa start (0) end (300) every(100)
);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'ps_partkey' as the Apache Cloudberry 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.
alter table partsupp_def2 set subpartition template();
alter table partsupp_def2 set subpartition template( subpartition aaa start(400) end (600) every(100) );
select relid::regclass, level, template from gp_partition_template where relid = 'partsupp_def2'::regclass;
relid | level | template
---------------+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
partsupp_def2 | 1 | {GPPARTITIONDEFINITION :partDefElems ({GPPARTDEFELEM :partName aaa :boundSpec {GPPARTITIONRANGESPEC :partStart {GPPARTITIONRANGEITEM :val ({A_CONST :val 400 :location 76}) :edge 1} :partEnd {GPPARTITIONRANGEITEM :val ({A_CONST :val 600 :location 86}) :edge 2} :partEvery ({A_CONST :val 100 :location 97})} :subSpec <> :isDefault false :options <> :accessMethod <> :tablespacename <> :colencs <>}) :encClauses <> :isTemplate true}
(1 row)
drop table partsupp_def;
drop table partsupp_def2;
create table mpp5431 (c1 date, c2 int) distributed by (c2) partition by range (c1) (partition p20090401 start('2009-04-01 00:00:00'::timestamp) inclusive end ('2009-04-02 00:00:00'::timestamp) exclusive);
alter table mpp5431 add partition p20090402 start('2009-04-02 00:00:00'::timestamp) inclusive end ('2009-04-03 00:00:00'::timestamp) exclusive;
create or replace function mpp5431_f1 () returns void as $$
begin
alter table mpp5431 add partition p20090403 start('2009-04-03 00:00:00'::timestamp) inclusive end ('2009-04-04 00:00:00'::timestamp) exclusive;
end;
$$ LANGUAGE 'plpgsql';
select mpp5431_f1();
mpp5431_f1
------------
(1 row)
drop function mpp5431_f1();
drop table mpp5431;
CREATE TABLE mpp6612 (
unique1 int4,
unique2 int4,
two int4,
four int4,
ten int4,
twenty int4,
hundred int4,
thousand int4,
twothousand int4,
fivethous int4,
tenthous int4,
odd int4,
even int4,
stringu1 name,
stringu2 name,
string4 name
) partition by range (unique1)
( partition aa start (0) end (1000) every (500), default partition default_part );
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'unique1' as the Apache Cloudberry 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.
-- Modify any other column
alter table mpp6612 alter column unique2 type char(15);
alter table mpp6612 alter column unique2 type char(10);
-- Show the dsecription
\d mpp6612*
Partitioned table "bfv_partition.mpp6612"
Column | Type | Collation | Nullable | Default
-------------+---------------+-----------+----------+---------
unique1 | integer | | |
unique2 | character(10) | | |
two | integer | | |
four | integer | | |
ten | integer | | |
twenty | integer | | |
hundred | integer | | |
thousand | integer | | |
twothousand | integer | | |
fivethous | integer | | |
tenthous | integer | | |
odd | integer | | |
even | integer | | |
stringu1 | name | | |
stringu2 | name | | |
string4 | name | | |
Partition key: RANGE (unique1)
Number of partitions: 3 (Use \d+ to list them.)
Distributed by: (unique1)
Table "bfv_partition.mpp6612_1_prt_aa_1"
Column | Type | Collation | Nullable | Default
-------------+---------------+-----------+----------+---------
unique1 | integer | | |
unique2 | character(10) | | |
two | integer | | |
four | integer | | |
ten | integer | | |
twenty | integer | | |
hundred | integer | | |
thousand | integer | | |
twothousand | integer | | |
fivethous | integer | | |
tenthous | integer | | |
odd | integer | | |
even | integer | | |
stringu1 | name | | |
stringu2 | name | | |
string4 | name | | |
Partition of: mpp6612 FOR VALUES FROM (0) TO (500)
Distributed by: (unique1)
Table "bfv_partition.mpp6612_1_prt_aa_2"
Column | Type | Collation | Nullable | Default
-------------+---------------+-----------+----------+---------
unique1 | integer | | |
unique2 | character(10) | | |
two | integer | | |
four | integer | | |
ten | integer | | |
twenty | integer | | |
hundred | integer | | |
thousand | integer | | |
twothousand | integer | | |
fivethous | integer | | |
tenthous | integer | | |
odd | integer | | |
even | integer | | |
stringu1 | name | | |
stringu2 | name | | |
string4 | name | | |
Partition of: mpp6612 FOR VALUES FROM (500) TO (1000)
Distributed by: (unique1)
Table "bfv_partition.mpp6612_1_prt_default_part"
Column | Type | Collation | Nullable | Default
-------------+---------------+-----------+----------+---------
unique1 | integer | | |
unique2 | character(10) | | |
two | integer | | |
four | integer | | |
ten | integer | | |
twenty | integer | | |
hundred | integer | | |
thousand | integer | | |
twothousand | integer | | |
fivethous | integer | | |
tenthous | integer | | |
odd | integer | | |
even | integer | | |
stringu1 | name | | |
stringu2 | name | | |
string4 | name | | |
Partition of: mpp6612 DEFAULT
Distributed by: (unique1)
-- Modify the partition definition. MPP-3724
-- alter table mpp6612 alter column unique1 type char(10); -- This should fail
-- alter table mpp6612 alter column unique1 type int8;
-- Show the dsecription
-- \d mpp6612*
drop table mpp6612;
-- Test that DEC is accepted as partition name.
create table mpp4048 (aaa int, bbb date)
partition by range (bbb)
subpartition by range (bbb)
subpartition by range (bbb)
(
partition y2008 start (date '2008-01-01') end (date '2008-12-05')
(
subpartition dec start (date '2008-12-01') end (date '2008-12-05') (start (date '2008-12-01') end (date '2008-12-05') every (interval '1 day'))
));
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'aaa' as the Apache Cloudberry 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.
drop table mpp4048;
-- This is only for ADD primary key for partition table
-- DROP primary key is still in progress
-- MPP-6573
CREATE TABLE mpp6573 (id int, date date, amt decimal(10,2))
DISTRIBUTED BY (id)
PARTITION BY RANGE (date)
( START (date '2008-01-01') INCLUSIVE
END (date '2008-01-03') EXCLUSIVE
EVERY (INTERVAL '1 day') );
alter table mpp6573 add primary key (id, date) ;
\d mpp6573*
Partitioned table "bfv_partition.mpp6573"
Column | Type | Collation | Nullable | Default
--------+---------------+-----------+----------+---------
id | integer | | not null |
date | date | | not null |
amt | numeric(10,2) | | |
Partition key: RANGE (date)
Indexes:
"mpp6573_pkey" PRIMARY KEY, btree (id, date)
Number of partitions: 2 (Use \d+ to list them.)
Distributed by: (id)
Table "bfv_partition.mpp6573_1_prt_1"
Column | Type | Collation | Nullable | Default
--------+---------------+-----------+----------+---------
id | integer | | not null |
date | date | | not null |
amt | numeric(10,2) | | |
Partition of: mpp6573 FOR VALUES FROM ('01-01-2008') TO ('01-02-2008')
Indexes:
"mpp6573_1_prt_1_pkey" PRIMARY KEY, btree (id, date)
Distributed by: (id)
Index "bfv_partition.mpp6573_1_prt_1_pkey"
Column | Type | Key? | Definition
--------+---------+------+------------
id | integer | yes | id
date | date | yes | date
Partition of: mpp6573_pkey
primary key, btree, for table "bfv_partition.mpp6573_1_prt_1"
Table "bfv_partition.mpp6573_1_prt_2"
Column | Type | Collation | Nullable | Default
--------+---------------+-----------+----------+---------
id | integer | | not null |
date | date | | not null |
amt | numeric(10,2) | | |
Partition of: mpp6573 FOR VALUES FROM ('01-02-2008') TO ('01-03-2008')
Indexes:
"mpp6573_1_prt_2_pkey" PRIMARY KEY, btree (id, date)
Distributed by: (id)
Index "bfv_partition.mpp6573_1_prt_2_pkey"
Column | Type | Key? | Definition
--------+---------+------+------------
id | integer | yes | id
date | date | yes | date
Partition of: mpp6573_pkey
primary key, btree, for table "bfv_partition.mpp6573_1_prt_2"
Partitioned index "bfv_partition.mpp6573_pkey"
Column | Type | Key? | Definition
--------+---------+------+------------
id | integer | yes | id
date | date | yes | date
primary key, btree, for table "bfv_partition.mpp6573"
Number of partitions: 2 (Use \d+ to list them.)
drop table mpp6573;
-- MPP-6724
-- Dummy select to give atmsort a cluebat that it's not processing a result
-- set right now. It gets confused by some of the errors from above.
select 1 as foobar;
foobar
--------
1
(1 row)
-- start_matchsubs
-- m/mpp6724_1_prt_r\d+/
-- s/(mpp6724_1_prt_r)\d+/$1xxxxxx/g
-- end_matchsubs
create table mpp6724 ( c1 int, dt DATE, c2 varchar, PRIMARY KEY ( c1,dt ) ) distributed by (c1) partition by range ( dt ) ( start ( date '2009-01-01' ) inclusive end ( date '2009-01-03' ) EXCLUSIVE EVERY ( INTERVAL '1 day' ) );
insert into mpp6724 values ( 1,'2009-01-01','One'), (2,'2009-01-02','Two'),(3,'2009-01-01','three'), (3,'2009-01-02', 'three`');
insert into mpp6724 values ( 1,'2009-01-01','One'); -- This violate the primary key, expected to fail
ERROR: duplicate key value violates unique constraint "mpp6724_1_prt_1_pkey"
DETAIL: Key (c1, dt)=(1, 01-01-2009) already exists.
alter table mpp6724 add partition start ( date '2009-01-03' ) inclusive end ( date '2009-01-04' ) ;
insert into mpp6724 values ( 4,'2009-01-03','Four');
-- Should fail because violates the primary key
insert into mpp6724 values ( 4,'2009-01-03','Four');
ERROR: duplicate key value violates unique constraint "mpp6724_1_prt_11_pkey" (seg0 127.0.1.1:7002 pid=19596)
DETAIL: Key (c1, dt)=(4, 01-03-2009) already exists.
insert into mpp6724 values ( 4,'2009-01-03','Four');
ERROR: duplicate key value violates unique constraint "mpp6724_1_prt_11_pkey" (seg0 127.0.1.1:7002 pid=19596)
DETAIL: Key (c1, dt)=(4, 01-03-2009) already exists.
insert into mpp6724 values ( 4,'2009-01-03','Four');
ERROR: duplicate key value violates unique constraint "mpp6724_1_prt_11_pkey" (seg0 127.0.1.1:7002 pid=19596)
DETAIL: Key (c1, dt)=(4, 01-03-2009) already exists.
insert into mpp6724 values ( 4,'2009-01-03','Four');
ERROR: duplicate key value violates unique constraint "mpp6724_1_prt_11_pkey" (seg0 127.0.1.1:7002 pid=19596)
DETAIL: Key (c1, dt)=(4, 01-03-2009) already exists.
select c1, dt, count(*) from mpp6724 group by 1,2 having count(*) > 1;
c1 | dt | count
----+----+-------
(0 rows)
drop table mpp6724;
-- Test for partition cleanup
create schema partition_999;
set search_path=bfv_partition,partition_999;
create table partition_cleanup1 (a int, b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int)
partition by range (a)
( partition aa start (1) end (5) every (1) );
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry 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 partition_999.partition_cleanup2(a int, b int, c int, d int, e int, f int, g int, h int, i int, j int, k int, l int, m int, n int, o int, p int, q int, r int, s int, t int, u int, v int, w int, x int, y int, z int)
partition by range (a)
subpartition by range (b) subpartition template ( start (1) end (5) every (1))
( partition aa start (1) end (5) every (1) );
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'a' as the Apache Cloudberry 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.
drop table partition_cleanup1;
drop schema partition_999 cascade;
NOTICE: drop cascades to table partition_cleanup2
-- These should be empty
select relid, level, template from gp_partition_template where not exists (select oid from pg_class where oid = relid);
relid | level | template
-------+-------+----------
(0 rows)
--
-- Check that dependencies to users are recorded correctly when operating on partitions.
--
DROP ROLE IF EXISTS part_acl_owner;
CREATE ROLE part_acl_owner;
DROP ROLE IF EXISTS part_acl_u1;
CREATE ROLE part_acl_u1;
GRANT ALL ON SCHEMA bfv_partition to part_acl_owner;
SET ROLE part_acl_owner;
CREATE TABLE part_acl_test (id int4) PARTITION BY LIST (id) (PARTITION p1 VALUES (1));
GRANT SELECT ON part_acl_test TO part_acl_u1;
ALTER TABLE part_acl_test ADD PARTITION p2 VALUES (2);
-- View permissions
\dp part_acl_*
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
---------------+------------------------+-------------------+---------------------------------------+-------------------+----------
bfv_partition | part_acl_test | partitioned table | part_acl_owner=arwdDxt/part_acl_owner+| |
| | | part_acl_u1=r/part_acl_owner | |
bfv_partition | part_acl_test_1_prt_p1 | table | part_acl_owner=arwdDxt/part_acl_owner+| |
| | | part_acl_u1=r/part_acl_owner | |
bfv_partition | part_acl_test_1_prt_p2 | table | part_acl_owner=arwdDxt/part_acl_owner+| |
| | | part_acl_u1=r/part_acl_owner | |
(3 rows)
-- View dependencies
select classid::regclass, objid::regclass,
refclassid::regclass, rolname
from pg_shdepend
inner join pg_database on pg_database.oid = pg_shdepend.dbid
left join pg_roles on pg_roles.oid = pg_shdepend.refobjid
where classid = 'pg_class'::regclass and objid::regclass::text like 'part_acl_test%'
and datname = current_database();
classid | objid | refclassid | rolname
----------+------------------------+------------+----------------
pg_class | part_acl_test_1_prt_p2 | pg_authid | part_acl_owner
pg_class | part_acl_test_1_prt_p1 | pg_authid | part_acl_owner
pg_class | part_acl_test | pg_authid | part_acl_owner
pg_class | part_acl_test_1_prt_p2 | pg_authid | part_acl_u1
pg_class | part_acl_test_1_prt_p1 | pg_authid | part_acl_u1
pg_class | part_acl_test | pg_authid | part_acl_u1
(6 rows)
-- Validate, using GrantStmt from cached plan in function works
-- fine. Using partition table is added spice to this validation as
-- for partition tables need to perform parent to all child partition
-- lookup on QD before dispatching the command to segments. There used
-- to bug where functions cached plan was scribbled during this
-- process.
CREATE TABLE grant_test (f1 int) PARTITION BY RANGE (f1) (START (2018) END (2020) EVERY (1), DEFAULT PARTITION extra);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'f1' as the Apache Cloudberry 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 FUNCTION grant_table_in_function() RETURNS void AS
$$
BEGIN
GRANT ALL ON TABLE grant_test TO part_acl_u1;
END;
$$ VOLATILE LANGUAGE plpgsql;
SELECT grant_table_in_function();
grant_table_in_function
-------------------------
(1 row)
-- calling it second time in same session should use cached plan for
-- GrantStmt
SELECT grant_table_in_function();
grant_table_in_function
-------------------------
(1 row)
-- Validate that ISSUE #14395 which manifested as a crash while deriving stats
-- on partitioned tables over CTEs is resolved.
CREATE TABLE a_partition_table_used_in_cte_test(c1 int)
PARTITION BY LIST(c1)
(
PARTITION a_part values(2,3),
DEFAULT partition other
);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'c1' 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 a_partition_table_used_in_cte_test SELECT 2;
ANALYZE a_partition_table_used_in_cte_test;
WITH cte AS (
SELECT * FROM a_partition_table_used_in_cte_test WHERE c1 < 2
) SELECT * FROM cte WHERE c1 = 1;
c1
----
(0 rows)
DROP TABLE a_partition_table_used_in_cte_test;
-- CLEANUP
-- start_ignore
drop schema if exists bfv_partition cascade;
NOTICE: drop cascades to 2 other objects
DETAIL: drop cascades to function count_operator(text,text)
drop cascades to function find_operator(text,text)
DROP USER mpp3641_user;
DROP ROLE part_acl_owner;
DROP ROLE part_acl_u1;
-- end_ignore