v3-0001-Tests-for-automatic-hash-list-partitions-creation.patch
application/octet-stream
Filename: v3-0001-Tests-for-automatic-hash-list-partitions-creation.patch
Type: application/octet-stream
Part: 1
Patch
Same data as JSON:
GET /api/v1/attachments/:id/patch
the parsed metadata as JSON — format, series position, per-file stats; never the diff bytes.
API reference →
Format: format-patch
Series: patch v3-0001
Subject: Tests for automatic hash & list partitions creation
| File | + | − |
|---|---|---|
| src/test/regress/expected/create_table.out | 372 | 0 |
| src/test/regress/sql/create_table.sql | 203 | 0 |
From 2435ab96a65a997beee283ee2029248b6cc303c4 Mon Sep 17 00:00:00 2001
From: Pavel Borisov <pashkin.elfe@gmail.com>
Date: Mon, 5 Oct 2020 12:40:43 +0400
Subject: [PATCH v3 1/2] Tests for automatic hash & list partitions creation
---
src/test/regress/expected/create_table.out | 372 +++++++++++++++++++++
src/test/regress/sql/create_table.sql | 203 +++++++++++
2 files changed, 575 insertions(+)
diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out
index 1776721b7b..4ec8e0ec0e 100644
--- a/src/test/regress/expected/create_table.out
+++ b/src/test/regress/expected/create_table.out
@@ -1352,3 +1352,375 @@ Partitions: tbl_hash_0 FOR VALUES WITH (modulus 3, remainder 0),
DROP TABLE tbl_list;
DROP TABLE tbl_hash;
+CREATE TABLE list_parted (a int) PARTITION BY LIST (a) CONFIGURATION (values in ('1'), (2), (2+1), (null) DEFAULT PARTITION part_default);
+\d+ list_parted
+ Partitioned table "public.list_parted"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | integer | | | | plain | |
+Partition key: LIST (a)
+Partitions: list_parted_0 FOR VALUES IN (1),
+ list_parted_1 FOR VALUES IN (2),
+ list_parted_2 FOR VALUES IN (3),
+ list_parted_3 FOR VALUES IN (NULL),
+ part_default DEFAULT
+
+-- forbidden expressions for partition bound with list partitioned table
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (somename));
+ERROR: cannot use column reference in partition bound expression
+LINE 2: (VALUES IN (somename));
+ ^
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (somename.somename));
+ERROR: cannot use column reference in partition bound expression
+LINE 2: (VALUES IN (somename.somename));
+ ^
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (a));
+ERROR: cannot use column reference in partition bound expression
+LINE 2: (VALUES IN (a));
+ ^
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (sum(a)));
+ERROR: cannot use column reference in partition bound expression
+LINE 2: (VALUES IN (sum(a)));
+ ^
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (sum(somename)));
+ERROR: cannot use column reference in partition bound expression
+LINE 2: (VALUES IN (sum(somename)));
+ ^
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (sum(1)));
+ERROR: aggregate functions are not allowed in partition bound
+LINE 2: (VALUES IN (sum(1)));
+ ^
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN ((select 1)));
+ERROR: cannot use subquery in partition bound
+LINE 2: (VALUES IN ((select 1)));
+ ^
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (generate_series(4, 6)));
+ERROR: set-returning functions are not allowed in partition bound
+LINE 2: (VALUES IN (generate_series(4, 6)));
+ ^
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN ('1' collate "POSIX"));
+ERROR: collations are not supported by type integer
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN ((1+1) collate "POSIX"));
+ERROR: collations are not supported by type integer
+LINE 2: (VALUES IN ((1+1) collate "POSIX"));
+ ^
+-- syntax does not allow empty list of values for list partitions
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN ());
+ERROR: syntax error at or near ")"
+LINE 2: (VALUES IN ());
+ ^
+-- trying to specify range for list partitioned table
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES FROM (1) TO (2));
+ERROR: syntax error at or near "FROM"
+LINE 2: (VALUES FROM (1) TO (2));
+ ^
+-- trying to specify modulus and remainder for list partitioned table
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(MODULUS 10);
+ERROR: invalid bound specification for a list partition
+LINE 1: CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) ...
+ ^
+-- check default partition cannot be created more than once
+CREATE TABLE fail_default_part PARTITION OF list_parted DEFAULT;
+ERROR: partition "fail_default_part" conflicts with existing default partition "part_default"
+LINE 1: ...TE TABLE fail_default_part PARTITION OF list_parted DEFAULT;
+ ^
+-- specified literal can't be cast to the partition column data type
+CREATE TABLE bools (a bool) PARTITION BY LIST (a) CONFIGURATION (VALUES IN (1));
+ERROR: specified value cannot be cast to type boolean for column "a"
+LINE 1: ...a bool) PARTITION BY LIST (a) CONFIGURATION (VALUES IN (1));
+ ^
+-- specified literal can be cast, and the cast might not be immutable
+CREATE TABLE moneyp (a money) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (10), ('11'), (to_char(12, '99')::int));
+DROP TABLE moneyp;
+-- cast is immutable
+CREATE TABLE bigintp (a bigint) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (10));
+-- fails due to overlap:
+CREATE TABLE bigintp_overlap PARTITION OF bigintp FOR VALUES IN ('10');
+ERROR: partition "bigintp_overlap" would overlap partition "bigintp_0"
+LINE 1: ...E bigintp_overlap PARTITION OF bigintp FOR VALUES IN ('10');
+ ^
+DROP TABLE bigintp;
+CREATE TABLE hash_parted (a int) PARTITION BY HASH (a) CONFIGURATION (MODULUS 10);
+-- all remainder values are already belong to partitions
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODULUS 30, REMAINDER 3);
+ERROR: partition "fail_part" would overlap partition "hash_parted_3"
+LINE 1: ...BLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODU...
+ ^
+-- trying to specify range for the hash partitioned table
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES FROM ('a', 1) TO ('z');
+ERROR: invalid bound specification for a hash partition
+LINE 1: ...BLE fail_part PARTITION OF hash_parted FOR VALUES FROM ('a',...
+ ^
+-- trying to specify list value for the hash partitioned table
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES IN (1000);
+ERROR: invalid bound specification for a hash partition
+LINE 1: ...BLE fail_part PARTITION OF hash_parted FOR VALUES IN (1000);
+ ^
+-- trying to create default partition for the hash partitioned table
+CREATE TABLE fail_part (a int) PARTITION BY HASH (a) CONFIGURATION (MODULUS 10
+ DEFAULT hash_default);
+ERROR: syntax error at or near "DEFAULT"
+LINE 2: DEFAULT hash_default);
+ ^
+-- cannot create as partition of a non-partitioned table
+CREATE TABLE fail_part (a int) CONFIGURATION (MODULUS 10);
+ERROR: syntax error at or near "CONFIGURATION"
+LINE 1: CREATE TABLE fail_part (a int) CONFIGURATION (MODULUS 10);
+ ^
+-- cannot create a permanent rel as partition of a temp rel
+CREATE TEMP TABLE fail_part (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN ('a'));
+ERROR: cannot create a permanent relation as partition of temporary relation "fail_part"
+-- check for partition bound overlap and other invalid specifications
+CREATE TABLE fail_parted2 (a varchar) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (null, 'z'),('a', 'b'),(null) DEFAULT partition tbl_default);
+ERROR: partition "fail_parted2_2" would overlap partition "fail_parted2_0"
+LINE 2: (VALUES IN (null, 'z'),('a', 'b'),(null) DEFAULT partition t...
+ ^
+CREATE TABLE fail_parted2 (a varchar) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (null, 'z'),('a', 'b'),('b', 'c') DEFAULT partition tbl_default);
+ERROR: partition "fail_parted2_2" would overlap partition "fail_parted2_1"
+LINE 2: (VALUES IN (null, 'z'),('a', 'b'),('b', 'c') DEFAULT partiti...
+ ^
+-- check default partition overlap
+CREATE TABLE list_parted2 (a varchar) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (null, 'z'),('a', 'b') DEFAULT partition tbl_default);
+INSERT INTO list_parted2 VALUES('X');
+CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN ('W', 'X', 'Y');
+ERROR: updated partition constraint for default partition "tbl_default" would be violated by some row
+-- check schema propagation from parent
+CREATE TABLE parted (a text, b int NOT NULL DEFAULT 0,
+ CONSTRAINT check_a CHECK (length(a) > 0))
+PARTITION BY LIST (a) CONFIGURATION ( VALUES IN ('a','b'),('d') );
+-- only inherited attributes (never local ones)
+SELECT attname, attislocal, attinhcount FROM pg_attribute
+ WHERE attrelid = 'parted_1'::regclass and attnum > 0
+ ORDER BY attnum;
+ attname | attislocal | attinhcount
+---------+------------+-------------
+ a | f | 1
+ b | f | 1
+(2 rows)
+
+-- able to specify column default, column constraint, and table constraint
+-- first check the "column specified more than once" error
+CREATE TABLE part_e_fail PARTITION OF parted (
+ b NOT NULL,
+ b DEFAULT 1,
+ b CHECK (b >= 0),
+ CONSTRAINT check_a CHECK (length(a) > 0)
+) FOR VALUES IN ('e');
+ERROR: column "b" specified more than once
+CREATE TABLE part_e PARTITION OF parted (
+ b NOT NULL DEFAULT 1,
+ CONSTRAINT check_a CHECK (length(a) > 0),
+ CONSTRAINT check_b CHECK (b >= 0)
+) FOR VALUES IN ('e');
+NOTICE: merging constraint "check_a" with inherited definition
+-- conislocal should be false for any merged constraints, true otherwise
+SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_e'::regclass ORDER BY conislocal, coninhcount;
+ conname | conislocal | coninhcount
+---------+------------+-------------
+ check_a | f | 1
+ check_b | t | 0
+(2 rows)
+
+-- check_a can not be dropped as it is inherited
+ALTER TABLE part_e DROP CONSTRAINT check_a;
+ERROR: cannot drop inherited constraint "check_a" of relation "part_e"
+-- check_b can be dropped as it is local
+ALTER TABLE part_e DROP CONSTRAINT check_b;
+-- Once check_b is added to the parent, it should be made non-local for part_b
+ALTER TABLE part_e ADD CONSTRAINT check_b CHECK (b >= 0);
+ALTER TABLE parted ADD CONSTRAINT check_b CHECK (b >= 0);
+NOTICE: merging constraint "check_b" with inherited definition
+SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_e'::regclass;
+ conname | conislocal | coninhcount
+---------+------------+-------------
+ check_a | f | 1
+ check_b | f | 1
+(2 rows)
+
+-- Neither check_a nor check_b are droppable from part_b
+ALTER TABLE part_e DROP CONSTRAINT check_a;
+ERROR: cannot drop inherited constraint "check_a" of relation "part_e"
+ALTER TABLE part_e DROP CONSTRAINT check_b;
+ERROR: cannot drop inherited constraint "check_b" of relation "part_e"
+-- And dropping it from parted should leave no trace of them on part_e, unlike
+-- traditional inheritance where they will be left behind, because they would
+-- be local constraints.
+ALTER TABLE parted DROP CONSTRAINT check_a, DROP CONSTRAINT check_b;
+SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_e'::regclass;
+ conislocal | coninhcount
+------------+-------------
+(0 rows)
+
+-- specify PARTITION BY for a partition
+CREATE TABLE fail_part_col_not_found PARTITION OF parted FOR VALUES IN ('c') PARTITION BY HASH(c);
+ERROR: column "c" named in partition key does not exist
+LINE 1: ...ARTITION OF parted FOR VALUES IN ('c') PARTITION BY HASH(c);
+ ^
+CREATE TABLE part_c PARTITION OF parted (b WITH OPTIONS NOT NULL DEFAULT 0) FOR VALUES IN ('c') PARTITION BY RANGE ((b));
+-- create a level-2 partition
+CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
+-- check that NOT NULL and default value are inherited correctly
+create table parted_notnull_inh_test (a int default 1, b int not null default 0) partition by list (a) CONFIGURATION (VALUES IN (1));
+insert into parted_notnull_inh_test (b) values (null);
+ERROR: null value in column "b" of relation "parted_notnull_inh_test_0" violates not-null constraint
+DETAIL: Failing row contains (1, null).
+-- note that a's default is preserved
+\d parted_notnull_inh_test1
+drop table parted_notnull_inh_test;
+-- Partition bound in describe output
+\d+ part_e
+ Table "public.part_e"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a | text | | | | extended | |
+ b | integer | | not null | 1 | plain | |
+Partition of: parted FOR VALUES IN ('e')
+Partition constraint: ((a IS NOT NULL) AND (a = 'e'::text))
+
+-- Both partition bound and partition key in describe output
+\d+ part_c
+ Partitioned table "public.part_c"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a | text | | | | extended | |
+ b | integer | | not null | 0 | plain | |
+Partition of: parted FOR VALUES IN ('c')
+Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text))
+Partition key: RANGE (b)
+Partitions: part_c_1_10 FOR VALUES FROM (1) TO (10)
+
+-- a level-2 partition's constraint will include the parent's expressions
+\d+ part_c_1_10
+ Table "public.part_c_1_10"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+----------+--------------+-------------
+ a | text | | | | extended | |
+ b | integer | | not null | 0 | plain | |
+Partition of: part_c FOR VALUES FROM (1) TO (10)
+Partition constraint: ((a IS NOT NULL) AND (a = 'c'::text) AND (b IS NOT NULL) AND (b >= 1) AND (b < 10))
+
+-- Show partition count in the parent's describe output
+-- Tempted to include \d+ output listing partitions with bound info but
+-- output could vary depending on the order in which partition oids are
+-- returned.
+\d parted
+ Partitioned table "public.parted"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | text | | |
+ b | integer | | not null | 0
+Partition key: LIST (a)
+Number of partitions: 4 (Use \d+ to list them.)
+
+\d hash_parted
+ Partitioned table "public.hash_parted"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | |
+Partition key: HASH (a)
+Number of partitions: 10 (Use \d+ to list them.)
+
+-- cleanup
+DROP TABLE parted;
+DROP TABLE list_parted, list_parted2;
+DROP TABLE hash_parted;
+-- list partitioning on array type column
+CREATE TABLE arrlp (a int[]) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN ('{1}', '{2}'));
+\d+ arrlp_1
+DROP TABLE arrlp;
+-- partition on boolean column
+create table boolspart (a bool) partition by list (a) CONFIGURATION
+(values in (true), (false));
+\d+ boolspart
+ Partitioned table "public.boolspart"
+ Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
+--------+---------+-----------+----------+---------+---------+--------------+-------------
+ a | boolean | | | | plain | |
+Partition key: LIST (a)
+Partitions: boolspart_0 FOR VALUES IN (true),
+ boolspart_1 FOR VALUES IN (false)
+
+drop table boolspart;
+-- test using a volatile expression as partition bound
+create table volatile_partbound_test (partkey timestamp) partition by list (partkey) CONFIGURATION (values in ('1970-01-01 00:00:00+00'::timestamp, current_timestamp),('1982-01-25 00:00:00+00'::timestamp));
+drop table volatile_partbound_test;
+-- tests of column drop with partition tables and indexes using
+-- predicates and expressions.
+create table part_column_drop (useless_1 int, id int, useless_2 int, d int,
+ b int, useless_3 int) partition by hash (id) CONFIGURATION (modulus 3);
+alter table part_column_drop drop column useless_1;
+alter table part_column_drop drop column useless_2;
+alter table part_column_drop drop column useless_3;
+create index part_column_drop_b_pred on part_column_drop(b) where b = 1;
+create index part_column_drop_b_expr on part_column_drop((b = 1));
+create index part_column_drop_d_pred on part_column_drop(d) where d = 2;
+create index part_column_drop_d_expr on part_column_drop((d = 2));
+create index part_column_drop_d_1_pred on part_column_drop_1(d) where d = 2;
+create index part_column_drop_d_1_expr on part_column_drop_1((d = 2));
+\d part_column_drop
+ Partitioned table "public.part_column_drop"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ id | integer | | |
+ d | integer | | |
+ b | integer | | |
+Partition key: HASH (id)
+Indexes:
+ "part_column_drop_b_expr" btree ((b = 1))
+ "part_column_drop_b_pred" btree (b) WHERE b = 1
+ "part_column_drop_d_expr" btree ((d = 2))
+ "part_column_drop_d_pred" btree (d) WHERE d = 2
+Number of partitions: 3 (Use \d+ to list them.)
+
+\d part_column_drop_1
+ Table "public.part_column_drop_1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ id | integer | | |
+ d | integer | | |
+ b | integer | | |
+Partition of: part_column_drop FOR VALUES WITH (modulus 3, remainder 1)
+Indexes:
+ "part_column_drop_1_b_idx" btree (b) WHERE b = 1
+ "part_column_drop_1_d_idx" btree (d) WHERE d = 2
+ "part_column_drop_1_expr_idx" btree ((b = 1))
+ "part_column_drop_1_expr_idx1" btree ((d = 2))
+ "part_column_drop_d_1_expr" btree ((d = 2))
+ "part_column_drop_d_1_pred" btree (d) WHERE d = 2
+
+\d part_column_drop_2
+ Table "public.part_column_drop_2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ id | integer | | |
+ d | integer | | |
+ b | integer | | |
+Partition of: part_column_drop FOR VALUES WITH (modulus 3, remainder 2)
+Indexes:
+ "part_column_drop_2_b_idx" btree (b) WHERE b = 1
+ "part_column_drop_2_d_idx" btree (d) WHERE d = 2
+ "part_column_drop_2_expr_idx" btree ((b = 1))
+ "part_column_drop_2_expr_idx1" btree ((d = 2))
+
+\d part_column_drop_3
+drop table part_column_drop;
diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql
index c82fca0a9a..61e5091f2c 100644
--- a/src/test/regress/sql/create_table.sql
+++ b/src/test/regress/sql/create_table.sql
@@ -994,3 +994,206 @@ CONFIGURATION (modulus 3);
DROP TABLE tbl_list;
DROP TABLE tbl_hash;
+
+CREATE TABLE list_parted (a int) PARTITION BY LIST (a) CONFIGURATION (values in ('1'), (2), (2+1), (null) DEFAULT PARTITION part_default);
+\d+ list_parted
+
+-- forbidden expressions for partition bound with list partitioned table
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (somename));
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (somename.somename));
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (a));
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (sum(a)));
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (sum(somename)));
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (sum(1)));
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN ((select 1)));
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (generate_series(4, 6)));
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN ('1' collate "POSIX"));
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN ((1+1) collate "POSIX"));
+
+-- syntax does not allow empty list of values for list partitions
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN ());
+-- trying to specify range for list partitioned table
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES FROM (1) TO (2));
+-- trying to specify modulus and remainder for list partitioned table
+CREATE TABLE list_parted_fail (a int) PARTITION BY LIST (a) CONFIGURATION
+(MODULUS 10);
+
+-- check default partition cannot be created more than once
+CREATE TABLE fail_default_part PARTITION OF list_parted DEFAULT;
+
+-- specified literal can't be cast to the partition column data type
+CREATE TABLE bools (a bool) PARTITION BY LIST (a) CONFIGURATION (VALUES IN (1));
+
+-- specified literal can be cast, and the cast might not be immutable
+CREATE TABLE moneyp (a money) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (10), ('11'), (to_char(12, '99')::int));
+DROP TABLE moneyp;
+
+-- cast is immutable
+CREATE TABLE bigintp (a bigint) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (10));
+-- fails due to overlap:
+CREATE TABLE bigintp_overlap PARTITION OF bigintp FOR VALUES IN ('10');
+DROP TABLE bigintp;
+
+CREATE TABLE hash_parted (a int) PARTITION BY HASH (a) CONFIGURATION (MODULUS 10);
+-- all remainder values are already belong to partitions
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES WITH (MODULUS 30, REMAINDER 3);
+-- trying to specify range for the hash partitioned table
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES FROM ('a', 1) TO ('z');
+-- trying to specify list value for the hash partitioned table
+CREATE TABLE fail_part PARTITION OF hash_parted FOR VALUES IN (1000);
+-- trying to create default partition for the hash partitioned table
+CREATE TABLE fail_part (a int) PARTITION BY HASH (a) CONFIGURATION (MODULUS 10
+ DEFAULT hash_default);
+
+-- cannot create as partition of a non-partitioned table
+CREATE TABLE fail_part (a int) CONFIGURATION (MODULUS 10);
+
+-- cannot create a permanent rel as partition of a temp rel
+CREATE TEMP TABLE fail_part (a int) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN ('a'));
+
+-- check for partition bound overlap and other invalid specifications
+CREATE TABLE fail_parted2 (a varchar) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (null, 'z'),('a', 'b'),(null) DEFAULT partition tbl_default);
+
+CREATE TABLE fail_parted2 (a varchar) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (null, 'z'),('a', 'b'),('b', 'c') DEFAULT partition tbl_default);
+
+-- check default partition overlap
+CREATE TABLE list_parted2 (a varchar) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN (null, 'z'),('a', 'b') DEFAULT partition tbl_default);
+INSERT INTO list_parted2 VALUES('X');
+CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN ('W', 'X', 'Y');
+
+-- check schema propagation from parent
+
+CREATE TABLE parted (a text, b int NOT NULL DEFAULT 0,
+ CONSTRAINT check_a CHECK (length(a) > 0))
+PARTITION BY LIST (a) CONFIGURATION ( VALUES IN ('a','b'),('d') );
+
+-- only inherited attributes (never local ones)
+SELECT attname, attislocal, attinhcount FROM pg_attribute
+ WHERE attrelid = 'parted_1'::regclass and attnum > 0
+ ORDER BY attnum;
+
+-- able to specify column default, column constraint, and table constraint
+
+-- first check the "column specified more than once" error
+CREATE TABLE part_e_fail PARTITION OF parted (
+ b NOT NULL,
+ b DEFAULT 1,
+ b CHECK (b >= 0),
+ CONSTRAINT check_a CHECK (length(a) > 0)
+) FOR VALUES IN ('e');
+
+CREATE TABLE part_e PARTITION OF parted (
+ b NOT NULL DEFAULT 1,
+ CONSTRAINT check_a CHECK (length(a) > 0),
+ CONSTRAINT check_b CHECK (b >= 0)
+) FOR VALUES IN ('e');
+-- conislocal should be false for any merged constraints, true otherwise
+SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_e'::regclass ORDER BY conislocal, coninhcount;
+
+-- check_a can not be dropped as it is inherited
+ALTER TABLE part_e DROP CONSTRAINT check_a;
+-- check_b can be dropped as it is local
+ALTER TABLE part_e DROP CONSTRAINT check_b;
+
+-- Once check_b is added to the parent, it should be made non-local for part_b
+ALTER TABLE part_e ADD CONSTRAINT check_b CHECK (b >= 0);
+ALTER TABLE parted ADD CONSTRAINT check_b CHECK (b >= 0);
+SELECT conname, conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_e'::regclass;
+
+-- Neither check_a nor check_b are droppable from part_b
+ALTER TABLE part_e DROP CONSTRAINT check_a;
+ALTER TABLE part_e DROP CONSTRAINT check_b;
+
+-- And dropping it from parted should leave no trace of them on part_e, unlike
+-- traditional inheritance where they will be left behind, because they would
+-- be local constraints.
+ALTER TABLE parted DROP CONSTRAINT check_a, DROP CONSTRAINT check_b;
+SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_e'::regclass;
+
+-- specify PARTITION BY for a partition
+CREATE TABLE fail_part_col_not_found PARTITION OF parted FOR VALUES IN ('c') PARTITION BY HASH(c);
+CREATE TABLE part_c PARTITION OF parted (b WITH OPTIONS NOT NULL DEFAULT 0) FOR VALUES IN ('c') PARTITION BY RANGE ((b));
+
+-- create a level-2 partition
+CREATE TABLE part_c_1_10 PARTITION OF part_c FOR VALUES FROM (1) TO (10);
+
+-- check that NOT NULL and default value are inherited correctly
+create table parted_notnull_inh_test (a int default 1, b int not null default 0) partition by list (a) CONFIGURATION (VALUES IN (1));
+insert into parted_notnull_inh_test (b) values (null);
+-- note that a's default is preserved
+\d parted_notnull_inh_test1
+drop table parted_notnull_inh_test;
+
+-- Partition bound in describe output
+\d+ part_e
+
+-- Both partition bound and partition key in describe output
+\d+ part_c
+
+-- a level-2 partition's constraint will include the parent's expressions
+\d+ part_c_1_10
+
+-- Show partition count in the parent's describe output
+-- Tempted to include \d+ output listing partitions with bound info but
+-- output could vary depending on the order in which partition oids are
+-- returned.
+\d parted
+\d hash_parted
+
+-- cleanup
+DROP TABLE parted;
+DROP TABLE list_parted, list_parted2;
+DROP TABLE hash_parted;
+-- list partitioning on array type column
+CREATE TABLE arrlp (a int[]) PARTITION BY LIST (a) CONFIGURATION
+(VALUES IN ('{1}', '{2}'));
+\d+ arrlp_1
+DROP TABLE arrlp;
+
+-- partition on boolean column
+create table boolspart (a bool) partition by list (a) CONFIGURATION
+(values in (true), (false));
+\d+ boolspart
+drop table boolspart;
+
+-- test using a volatile expression as partition bound
+create table volatile_partbound_test (partkey timestamp) partition by list (partkey) CONFIGURATION (values in ('1970-01-01 00:00:00+00'::timestamp, current_timestamp),('1982-01-25 00:00:00+00'::timestamp));
+drop table volatile_partbound_test;
+
+-- tests of column drop with partition tables and indexes using
+-- predicates and expressions.
+create table part_column_drop (useless_1 int, id int, useless_2 int, d int,
+ b int, useless_3 int) partition by hash (id) CONFIGURATION (modulus 3);
+alter table part_column_drop drop column useless_1;
+alter table part_column_drop drop column useless_2;
+alter table part_column_drop drop column useless_3;
+create index part_column_drop_b_pred on part_column_drop(b) where b = 1;
+create index part_column_drop_b_expr on part_column_drop((b = 1));
+create index part_column_drop_d_pred on part_column_drop(d) where d = 2;
+create index part_column_drop_d_expr on part_column_drop((d = 2));
+create index part_column_drop_d_1_pred on part_column_drop_1(d) where d = 2;
+create index part_column_drop_d_1_expr on part_column_drop_1((d = 2));
+
+\d part_column_drop
+\d part_column_drop_1
+\d part_column_drop_2
+\d part_column_drop_3
+drop table part_column_drop;
--
2.28.0