diff options
Diffstat (limited to 'src/test')
| -rw-r--r-- | src/test/regress/expected/alter_table.out | 343 | ||||
| -rw-r--r-- | src/test/regress/expected/create_table.out | 413 | ||||
| -rw-r--r-- | src/test/regress/expected/inherit.out | 272 | ||||
| -rw-r--r-- | src/test/regress/expected/insert.out | 140 | ||||
| -rw-r--r-- | src/test/regress/expected/sanity_check.out | 1 | ||||
| -rw-r--r-- | src/test/regress/expected/update.out | 27 | ||||
| -rw-r--r-- | src/test/regress/sql/alter_table.sql | 294 | ||||
| -rw-r--r-- | src/test/regress/sql/create_table.sql | 315 | ||||
| -rw-r--r-- | src/test/regress/sql/inherit.sql | 52 | ||||
| -rw-r--r-- | src/test/regress/sql/insert.sql | 86 | ||||
| -rw-r--r-- | src/test/regress/sql/update.sql | 21 |
11 files changed, 1964 insertions, 0 deletions
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out index cf9f6d3dfaf..09cc193f2f8 100644 --- a/src/test/regress/expected/alter_table.out +++ b/src/test/regress/expected/alter_table.out @@ -2974,3 +2974,346 @@ NOTICE: column "c3" of relation "test_add_column" already exists, skipping c4 | integer | | | DROP TABLE test_add_column; +-- unsupported constraint types for partitioned tables +CREATE TABLE partitioned ( + a int, + b int +) PARTITION BY RANGE (a, (a+b+1)); +ALTER TABLE partitioned ADD UNIQUE (a); +ERROR: unique constraints are not supported on partitioned tables +LINE 1: ALTER TABLE partitioned ADD UNIQUE (a); + ^ +ALTER TABLE partitioned ADD PRIMARY KEY (a); +ERROR: primary key constraints are not supported on partitioned tables +LINE 1: ALTER TABLE partitioned ADD PRIMARY KEY (a); + ^ +ALTER TABLE partitioned ADD FOREIGN KEY (a) REFERENCES blah; +ERROR: foreign key constraints are not supported on partitioned tables +LINE 1: ALTER TABLE partitioned ADD FOREIGN KEY (a) REFERENCES blah; + ^ +ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&); +ERROR: exclusion constraints are not supported on partitioned tables +LINE 1: ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&); + ^ +-- cannot drop column that is part of the partition key +ALTER TABLE partitioned DROP COLUMN a; +ERROR: cannot drop column named in partition key +ALTER TABLE partitioned ALTER COLUMN a TYPE char(5); +ERROR: cannot alter type of column named in partition key +ALTER TABLE partitioned DROP COLUMN b; +ERROR: cannot drop column referenced in partition key expression +ALTER TABLE partitioned ALTER COLUMN b TYPE char(5); +ERROR: cannot alter type of column referenced in partition key expression +-- cannot drop NOT NULL on columns in the range partition key +ALTER TABLE partitioned ALTER COLUMN a DROP NOT NULL; +ERROR: column "a" is in range partition key +-- partitioned table cannot partiticipate in regular inheritance +CREATE TABLE foo ( + a int, + b int +); +ALTER TABLE partitioned INHERIT foo; +ERROR: cannot change inheritance of partitioned table +ALTER TABLE foo INHERIT partitioned; +ERROR: cannot inherit from partitioned table "partitioned" +-- cannot add NO INHERIT constraint to partitioned tables +ALTER TABLE partitioned ADD CONSTRAINT chk_a CHECK (a > 0) NO INHERIT; +ERROR: cannot add NO INHERIT constraint to partitioned table "partitioned" +DROP TABLE partitioned, foo; +-- +-- ATTACH PARTITION +-- +-- check that target table is partitioned +CREATE TABLE unparted ( + a int +); +CREATE TABLE fail_part (like unparted); +ALTER TABLE unparted ATTACH PARTITION fail_part FOR VALUES IN ('a'); +ERROR: "unparted" is not partitioned +DROP TABLE unparted, fail_part; +-- check that partition bound is compatible +CREATE TABLE list_parted ( + a int NOT NULL, + b char(2) COLLATE "en_US", + CONSTRAINT check_a CHECK (a > 0) +) PARTITION BY LIST (a); +CREATE TABLE fail_part (LIKE list_parted); +ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES FROM (1) TO (10); +ERROR: invalid bound specification for a list partition +LINE 1: ...list_parted ATTACH PARTITION fail_part FOR VALUES FROM (1) T... + ^ +DROP TABLE fail_part; +-- check that the table being attached exists +ALTER TABLE list_parted ATTACH PARTITION nonexistant FOR VALUES IN (1); +ERROR: relation "nonexistant" does not exist +-- check ownership of the source table +CREATE ROLE regress_test_me; +CREATE ROLE regress_test_not_me; +CREATE TABLE not_owned_by_me (LIKE list_parted); +ALTER TABLE not_owned_by_me OWNER TO regress_test_not_me; +SET SESSION AUTHORIZATION regress_test_me; +CREATE TABLE owned_by_me ( + a int +) PARTITION BY LIST (a); +ALTER TABLE owned_by_me ATTACH PARTITION not_owned_by_me FOR VALUES IN (1); +ERROR: must be owner of relation not_owned_by_me +RESET SESSION AUTHORIZATION; +DROP TABLE owned_by_me, not_owned_by_me; +DROP ROLE regress_test_not_me; +DROP ROLE regress_test_me; +-- check that the table being attached is not part of regular inheritance +CREATE TABLE parent (LIKE list_parted); +CREATE TABLE child () INHERITS (parent); +ALTER TABLE list_parted ATTACH PARTITION child FOR VALUES IN (1); +ERROR: cannot attach inheritance child as partition +ALTER TABLE list_parted ATTACH PARTITION parent FOR VALUES IN (1); +ERROR: cannot attach inheritance parent as partition +DROP TABLE parent CASCADE; +NOTICE: drop cascades to table child +-- check any TEMP-ness +CREATE TEMP TABLE temp_parted (a int) PARTITION BY LIST (a); +CREATE TABLE perm_part (a int); +ALTER TABLE temp_parted ATTACH PARTITION perm_part FOR VALUES IN (1); +ERROR: cannot attach a permanent relation as partition of temporary relation "temp_parted" +DROP TABLE temp_parted, perm_part; +-- check that the table being attached is not a typed table +CREATE TYPE mytype AS (a int); +CREATE TABLE fail_part OF mytype; +ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); +ERROR: cannot attach a typed table as partition +DROP TYPE mytype CASCADE; +NOTICE: drop cascades to table fail_part +-- check existence (or non-existence) of oid column +ALTER TABLE list_parted SET WITH OIDS; +CREATE TABLE fail_part (a int); +ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); +ERROR: cannot attach table "fail_part" without OIDs as partition of table "list_parted" with OIDs +ALTER TABLE list_parted SET WITHOUT OIDS; +ALTER TABLE fail_part SET WITH OIDS; +ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); +ERROR: cannot attach table "fail_part" with OIDs as partition of table "list_parted" without OIDs +DROP TABLE fail_part; +-- check that the table being attached has only columns present in the parent +CREATE TABLE fail_part (like list_parted, c int); +ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); +ERROR: table "fail_part" contains column "c" not found in parent "list_parted" +DETAIL: New partition should contain only the columns present in parent. +DROP TABLE fail_part; +-- check that the table being attached has every column of the parent +CREATE TABLE fail_part (a int NOT NULL); +ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); +ERROR: child table is missing column "b" +DROP TABLE fail_part; +-- check that columns match in type, collation and NOT NULL status +CREATE TABLE fail_part ( + b char(3), + a int NOT NULL +); +ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); +ERROR: child table "fail_part" has different type for column "b" +ALTER TABLE fail_part ALTER b TYPE char (2) COLLATE "en_CA"; +ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); +ERROR: child table "fail_part" has different collation for column "b" +DROP TABLE fail_part; +-- check that the table being attached has all constraints of the parent +CREATE TABLE fail_part ( + b char(2) COLLATE "en_US", + a int NOT NULL +); +ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); +ERROR: child table is missing constraint "check_a" +-- check that the constraint matches in definition with parent's constraint +ALTER TABLE fail_part ADD CONSTRAINT check_a CHECK (a >= 0); +ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); +ERROR: child table "fail_part" has different definition for check constraint "check_a" +DROP TABLE fail_part; +-- check the attributes and constraints after partition is attached +CREATE TABLE part_1 ( + a int NOT NULL, + b char(2) COLLATE "en_US", + CONSTRAINT check_a CHECK (a > 0) +); +ALTER TABLE list_parted ATTACH PARTITION part_1 FOR VALUES IN (1); +-- attislocal and conislocal are always false for merged attributes and constraints respectively. +SELECT attislocal, attinhcount FROM pg_attribute WHERE attrelid = 'part_1'::regclass AND attnum > 0; + attislocal | attinhcount +------------+------------- + f | 1 + f | 1 +(2 rows) + +SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_1'::regclass AND conname = 'check_a'; + conislocal | coninhcount +------------+------------- + f | 1 +(1 row) + +-- check that the new partition won't overlap with an existing partition +CREATE TABLE fail_part (LIKE part_1 INCLUDING CONSTRAINTS); +ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); +ERROR: partition "fail_part" would overlap partition "part_1" +-- check validation when attaching list partitions +CREATE TABLE list_parted2 ( + a int, + b char +) PARTITION BY LIST (a); +-- check that violating rows are correctly reported +CREATE TABLE part_2 (LIKE list_parted2); +INSERT INTO part_2 VALUES (3, 'a'); +ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2); +ERROR: partition constraint is violated by some row +-- should be ok after deleting the bad row +DELETE FROM part_2; +ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2); +-- adding constraints that describe the desired partition constraint +-- (or more restrictive) will help skip the validation scan +CREATE TABLE part_3_4 ( + LIKE list_parted2, + CONSTRAINT check_a CHECK (a IN (3)) +); +-- however, if a list partition does not accept nulls, there should be +-- an explicit NOT NULL constraint on the partition key column for the +-- validation scan to be skipped; +ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4); +-- adding a NOT NULL constraint will cause the scan to be skipped +ALTER TABLE list_parted2 DETACH PARTITION part_3_4; +ALTER TABLE part_3_4 ALTER a SET NOT NULL; +ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4); +NOTICE: skipping scan to validate partition constraint +-- check validation when attaching range partitions +CREATE TABLE range_parted ( + a int, + b int +) PARTITION BY RANGE (a, b); +-- check that violating rows are correctly reported +CREATE TABLE part1 ( + a int NOT NULL CHECK (a = 1), + b int NOT NULL CHECK (b >= 1 AND b <= 10) +); +INSERT INTO part1 VALUES (1, 10); +-- Remember the TO bound is exclusive +ALTER TABLE range_parted ATTACH PARTITION part1 FOR VALUES FROM (1, 1) TO (1, 10); +ERROR: partition constraint is violated by some row +-- should be ok after deleting the bad row +DELETE FROM part1; +ALTER TABLE range_parted ATTACH PARTITION part1 FOR VALUES FROM (1, 1) TO (1, 10); +-- adding constraints that describe the desired partition constraint +-- (or more restrictive) will help skip the validation scan +CREATE TABLE part2 ( + a int NOT NULL CHECK (a = 1), + b int NOT NULL CHECK (b >= 10 AND b < 18) +); +ALTER TABLE range_parted ATTACH PARTITION part2 FOR VALUES FROM (1, 10) TO (1, 20); +NOTICE: skipping scan to validate partition constraint +-- check that leaf partitions are scanned when attaching a partitioned +-- table +CREATE TABLE part_5 ( + LIKE list_parted2 +) PARTITION BY LIST (b); +-- check that violating rows are correctly reported +CREATE TABLE part_5_a PARTITION OF part_5 FOR VALUES IN ('a'); +INSERT INTO part_5_a (a, b) VALUES (6, 'a'); +ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5); +ERROR: partition constraint is violated by some row +-- delete the faulting row and also add a constraint to skip the scan +DELETE FROM part_5_a WHERE a NOT IN (3); +ALTER TABLE part_5 ADD CONSTRAINT check_a CHECK (a IN (5)), ALTER a SET NOT NULL; +ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5); +NOTICE: skipping scan to validate partition constraint +-- check that the table being attached is not already a partition +ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2); +ERROR: "part_2" is already a partition +-- check that circular inheritance is not allowed +ALTER TABLE part_5 ATTACH PARTITION list_parted2 FOR VALUES IN ('b'); +ERROR: circular inheritance not allowed +DETAIL: "part_5" is already a child of "list_parted2". +ALTER TABLE list_parted2 ATTACH PARTITION list_parted2 FOR VALUES IN (0); +ERROR: circular inheritance not allowed +DETAIL: "list_parted2" is already a child of "list_parted2". +-- +-- DETACH PARTITION +-- +-- check that the partition being detached exists at all +ALTER TABLE list_parted2 DETACH PARTITION part_4; +ERROR: relation "part_4" does not exist +-- check that the partition being detached is actually a partition of the parent +CREATE TABLE not_a_part (a int); +ALTER TABLE list_parted2 DETACH PARTITION not_a_part; +ERROR: relation "not_a_part" is not a partition of relation "list_parted2" +ALTER TABLE list_parted2 DETACH PARTITION part_1; +ERROR: relation "part_1" is not a partition of relation "list_parted2" +-- check that, after being detached, attinhcount/coninhcount is dropped to 0 and +-- attislocal/conislocal is set to true +ALTER TABLE list_parted2 DETACH PARTITION part_3_4; +SELECT attinhcount, attislocal FROM pg_attribute WHERE attrelid = 'part_3_4'::regclass AND attnum > 0; + attinhcount | attislocal +-------------+------------ + 0 | t + 0 | t +(2 rows) + +SELECT coninhcount, conislocal FROM pg_constraint WHERE conrelid = 'part_3_4'::regclass AND conname = 'check_a'; + coninhcount | conislocal +-------------+------------ + 0 | t +(1 row) + +DROP TABLE part_3_4; +-- Check ALTER TABLE commands for partitioned tables and partitions +-- cannot add/drop column to/from *only* the parent +ALTER TABLE ONLY list_parted2 ADD COLUMN c int; +ERROR: column must be added to child tables too +ALTER TABLE ONLY list_parted2 DROP COLUMN b; +ERROR: column must be dropped from child tables too +-- cannot add a column to partition or drop an inherited one +ALTER TABLE part_2 ADD COLUMN c text; +ERROR: cannot add column to a partition +ALTER TABLE part_2 DROP COLUMN b; +ERROR: cannot drop inherited column "b" +-- Nor rename, alter type +ALTER TABLE part_2 RENAME COLUMN b to c; +ERROR: cannot rename inherited column "b" +ALTER TABLE part_2 ALTER COLUMN b TYPE text; +ERROR: cannot alter inherited column "b" +-- cannot add NOT NULL or check constraints to *only* the parent (ie, non-inherited) +ALTER TABLE ONLY list_parted2 ALTER b SET NOT NULL; +ERROR: constraint must be added to child tables too +ALTER TABLE ONLY list_parted2 add constraint check_b check (b <> 'zz'); +ERROR: constraint must be added to child tables too +ALTER TABLE list_parted2 add constraint check_b check (b <> 'zz') NO INHERIT; +ERROR: cannot add NO INHERIT constraint to partitioned table "list_parted2" +-- cannot drop inherited NOT NULL or check constraints from partition +ALTER TABLE list_parted2 ALTER b SET NOT NULL, ADD CONSTRAINT check_a2 CHECK (a > 0); +ALTER TABLE part_2 ALTER b DROP NOT NULL; +ERROR: column "b" is marked NOT NULL in parent table +ALTER TABLE part_2 DROP CONSTRAINT check_a2; +ERROR: cannot drop inherited constraint "check_a2" of relation "part_2" +-- cannot drop NOT NULL or check constraints from *only* the parent +ALTER TABLE ONLY list_parted2 ALTER a DROP NOT NULL; +ERROR: constraint must be dropped from child tables too +ALTER TABLE ONLY list_parted2 DROP CONSTRAINT check_a2; +ERROR: constraint must be dropped from child tables too +-- check that a partition cannot participate in regular inheritance +CREATE TABLE inh_test () INHERITS (part_2); +ERROR: cannot inherit from partition "part_2" +CREATE TABLE inh_test (LIKE part_2); +ALTER TABLE inh_test INHERIT part_2; +ERROR: cannot inherit from a partition +ALTER TABLE part_2 INHERIT inh_test; +ERROR: cannot change inheritance of a partition +-- cannot drop or alter type of partition key columns of lower level +-- partitioned tables; for example, part_5, which is list_parted2's +-- partition, is partitioned on b; +ALTER TABLE list_parted2 DROP COLUMN b; +ERROR: cannot drop column named in partition key +ALTER TABLE list_parted2 ALTER COLUMN b TYPE text; +ERROR: cannot alter type of column named in partition key +-- cleanup +DROP TABLE list_parted, list_parted2, range_parted CASCADE; +NOTICE: drop cascades to 6 other objects +DETAIL: drop cascades to table part1 +drop cascades to table part2 +drop cascades to table part_2 +drop cascades to table part_5 +drop cascades to table part_5_a +drop cascades to table part_1 diff --git a/src/test/regress/expected/create_table.out b/src/test/regress/expected/create_table.out index 41ceb874e8f..b40a18aec2b 100644 --- a/src/test/regress/expected/create_table.out +++ b/src/test/regress/expected/create_table.out @@ -253,3 +253,416 @@ DROP TABLE as_select1; -- check that the oid column is added before the primary key is checked CREATE TABLE oid_pk (f1 INT, PRIMARY KEY(oid)) WITH OIDS; DROP TABLE oid_pk; +-- +-- Partitioned tables +-- +-- cannot combine INHERITS and PARTITION BY (although grammar allows) +CREATE TABLE partitioned ( + a int +) INHERITS (some_table) PARTITION BY LIST (a); +ERROR: cannot create partitioned table as inheritance child +-- cannot use more than 1 column as partition key for list partitioned table +CREATE TABLE partitioned ( + a1 int, + a2 int +) PARTITION BY LIST (a1, a2); -- fail +ERROR: cannot list partition using more than one column +-- unsupported constraint type for partitioned tables +CREATE TABLE partitioned ( + a int PRIMARY KEY +) PARTITION BY RANGE (a); +ERROR: primary key constraints are not supported on partitioned tables +LINE 2: a int PRIMARY KEY + ^ +CREATE TABLE pkrel ( + a int PRIMARY KEY +); +CREATE TABLE partitioned ( + a int REFERENCES pkrel(a) +) PARTITION BY RANGE (a); +ERROR: foreign key constraints are not supported on partitioned tables +LINE 2: a int REFERENCES pkrel(a) + ^ +DROP TABLE pkrel; +CREATE TABLE partitioned ( + a int UNIQUE +) PARTITION BY RANGE (a); +ERROR: unique constraints are not supported on partitioned tables +LINE 2: a int UNIQUE + ^ +CREATE TABLE partitioned ( + a int, + EXCLUDE USING gist (a WITH &&) +) PARTITION BY RANGE (a); +ERROR: exclusion constraints are not supported on partitioned tables +LINE 3: EXCLUDE USING gist (a WITH &&) + ^ +-- prevent column from being used twice in the partition key +CREATE TABLE partitioned ( + a int +) PARTITION BY RANGE (a, a); +ERROR: column "a" appears more than once in partition key +-- prevent using prohibited expressions in the key +CREATE FUNCTION retset (a int) RETURNS SETOF int AS $$ SELECT 1; $$ LANGUAGE SQL IMMUTABLE; +CREATE TABLE partitioned ( + a int +) PARTITION BY RANGE (retset(a)); +ERROR: set-returning functions are not allowed in partition key expression +DROP FUNCTION retset(int); +CREATE TABLE partitioned ( + a int +) PARTITION BY RANGE ((avg(a))); +ERROR: aggregate functions are not allowed in partition key expression +CREATE TABLE partitioned ( + a int, + b int +) PARTITION BY RANGE ((avg(a) OVER (PARTITION BY b))); +ERROR: window functions are not allowed in partition key expression +CREATE TABLE partitioned ( + a int +) PARTITION BY LIST ((a LIKE (SELECT 1))); +ERROR: cannot use subquery in partition key expression +CREATE TABLE partitioned ( + a int +) PARTITION BY RANGE (('a')); +ERROR: cannot use constant expression as partition key +CREATE FUNCTION const_func () RETURNS int AS $$ SELECT 1; $$ LANGUAGE SQL IMMUTABLE; +CREATE TABLE partitioned ( + a int +) PARTITION BY RANGE (const_func()); +ERROR: cannot use constant expression as partition key +DROP FUNCTION const_func(); +-- only accept "list" and "range" as partitioning strategy +CREATE TABLE partitioned ( + a int +) PARTITION BY HASH (a); +ERROR: unrecognized partitioning strategy "hash" +-- specified column must be present in the table +CREATE TABLE partitioned ( + a int +) PARTITION BY RANGE (b); +ERROR: column "b" named in partition key does not exist +-- cannot use system columns in partition key +CREATE TABLE partitioned ( + a int +) PARTITION BY RANGE (xmin); +ERROR: cannot use system column "xmin" in partition key +-- functions in key must be immutable +CREATE FUNCTION immut_func (a int) RETURNS int AS $$ SELECT a + random()::int; $$ LANGUAGE SQL; +CREATE TABLE partitioned ( + a int +) PARTITION BY RANGE (immut_func(a)); +ERROR: functions in partition key expression must be marked IMMUTABLE +DROP FUNCTION immut_func(int); +-- cannot contain whole-row references +CREATE TABLE partitioned ( + a int +) PARTITION BY RANGE ((partitioned)); +ERROR: partition key expressions cannot contain whole-row references +-- prevent using columns of unsupported types in key (type must have a btree operator class) +CREATE TABLE partitioned ( + a point +) PARTITION BY LIST (a); +ERROR: data type point has no default btree operator class +HINT: You must specify a btree operator class or define a default btree operator class for the data type. +CREATE TABLE partitioned ( + a point +) PARTITION BY LIST (a point_ops); +ERROR: operator class "point_ops" does not exist for access method "btree" +CREATE TABLE partitioned ( + a point +) PARTITION BY RANGE (a); +ERROR: data type point has no default btree operator class +HINT: You must specify a btree operator class or define a default btree operator class for the data type. +CREATE TABLE partitioned ( + a point +) PARTITION BY RANGE (a point_ops); +ERROR: operator class "point_ops" does not exist for access method "btree" +-- cannot add NO INHERIT constraints to partitioned tables +CREATE TABLE partitioned ( + a int, + CONSTRAINT check_a CHECK (a > 0) NO INHERIT +) PARTITION BY RANGE (a); +ERROR: cannot add NO INHERIT constraint to partitioned table "partitioned" +-- some checks after successful creation of a partitioned table +CREATE FUNCTION plusone(a int) RETURNS INT AS $$ SELECT a+1; $$ LANGUAGE SQL; +CREATE TABLE partitioned ( + a int, + b int, + c text, + d text +) PARTITION BY RANGE (a oid_ops, plusone(b), c collate "default", d collate "en_US"); +-- check relkind +SELECT relkind FROM pg_class WHERE relname = 'partitioned'; + relkind +--------- + P +(1 row) + +-- check that range partition key columns are marked NOT NULL +SELECT attname, attnotnull FROM pg_attribute WHERE attrelid = 'partitioned'::regclass AND attnum > 0; + attname | attnotnull +---------+------------ + a | t + b | f + c | t + d | t +(4 rows) + +-- prevent a function referenced in partition key from being dropped +DROP FUNCTION plusone(int); +ERROR: cannot drop function plusone(integer) because other objects depend on it +DETAIL: table partitioned depends on function plusone(integer) +HINT: Use DROP ... CASCADE to drop the dependent objects too. +-- partitioned table cannot partiticipate in regular inheritance +CREATE TABLE partitioned2 ( + a int +) PARTITION BY LIST ((a+1)); +CREATE TABLE fail () INHERITS (partitioned2); +ERROR: cannot inherit from partitioned table "partitioned2" +-- Partition key in describe output +\d partitioned + Table "public.partitioned" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | not null | + b | integer | | | + c | text | | not null | + d | text | | not null | +Partition key: RANGE (a oid_ops, plusone(b), c, d COLLATE "en_US") + +\d partitioned2 + Table "public.partitioned2" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | | +Partition key: LIST ((a + 1)) + +DROP TABLE partitioned, partitioned2; +-- +-- Partitions +-- +-- check partition bound syntax +CREATE TABLE list_parted ( + a int +) PARTITION BY LIST (a); +-- syntax allows only string literal, numeric literal and null to be +-- specified for a partition bound value +CREATE TABLE part_1 PARTITION OF list_parted FOR VALUES IN ('1'); +CREATE TABLE part_2 PARTITION OF list_parted FOR VALUES IN (2); +CREATE TABLE part_null PARTITION OF list_parted FOR VALUES IN (null); +CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN (int '1'); +ERROR: syntax error at or near "int" +LINE 1: ... fail_part PARTITION OF list_parted FOR VALUES IN (int '1'); + ^ +CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN ('1'::int); +ERROR: syntax error at or near "::" +LINE 1: ...fail_part PARTITION OF list_parted FOR VALUES IN ('1'::int); + ^ +-- syntax does not allow empty list of values for list partitions +CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN (); +ERROR: syntax error at or near ")" +LINE 1: ...E TABLE fail_part PARTITION OF list_parted FOR VALUES IN (); + ^ +-- trying to specify range for list partitioned table +CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES FROM (1) TO (2); +ERROR: invalid bound specification for a list partition +LINE 1: ...BLE fail_part PARTITION OF list_parted FOR VALUES FROM (1) T... + ^ +-- specified literal can't be cast to the partition column data type +CREATE TABLE bools ( + a bool +) PARTITION BY LIST (a); +CREATE TABLE bools_true PARTITION OF bools FOR VALUES IN (1); +ERROR: specified value cannot be cast to type "boolean" of column "a" +LINE 1: ...REATE TABLE bools_true PARTITION OF bools FOR VALUES IN (1); + ^ +DROP TABLE bools; +CREATE TABLE range_parted ( + a date +) PARTITION BY RANGE (a); +-- trying to specify list for range partitioned table +CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES IN ('a'); +ERROR: invalid bound specification for a range partition +LINE 1: ...BLE fail_part PARTITION OF range_parted FOR VALUES IN ('a'); + ^ +-- each of start and end bounds must have same number of values as the +-- length of the partition key +CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a', 1) TO ('z'); +ERROR: FROM must specify exactly one value per partitioning column +CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a') TO ('z', 1); +ERROR: TO must specify exactly one value per partitioning column +-- cannot specify null values in range bounds +CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM (null) TO (unbounded); +ERROR: cannot specify NULL in range bound +-- check if compatible with the specified parent +-- cannot create as partition of a non-partitioned table +CREATE TABLE unparted ( + a int +); +CREATE TABLE fail_part PARTITION OF unparted FOR VALUES IN ('a'); +ERROR: "unparted" is not partitioned +DROP TABLE unparted; +-- cannot create a permanent rel as partition of a temp rel +CREATE TEMP TABLE temp_parted ( + a int +) PARTITION BY LIST (a); +CREATE TABLE fail_part PARTITION OF temp_parted FOR VALUES IN ('a'); +ERROR: cannot create a permanent relation as partition of temporary relation "temp_parted" +DROP TABLE temp_parted; +-- cannot create a table with oids as partition of table without oids +CREATE TABLE no_oids_parted ( + a int +) PARTITION BY RANGE (a) WITHOUT OIDS; +CREATE TABLE fail_part PARTITION OF no_oids_parted FOR VALUES FROM (1) TO (10 )WITH OIDS; +ERROR: cannot create table with OIDs as partition of table without OIDs +DROP TABLE no_oids_parted; +-- likewise, the reverse if also true +CREATE TABLE oids_parted ( + a int +) PARTITION BY RANGE (a) WITH OIDS; +CREATE TABLE fail_part PARTITION OF oids_parted FOR VALUES FROM (1) TO (10 ) WITHOUT OIDS; +ERROR: cannot create table without OIDs as partition of table with OIDs +DROP TABLE oids_parted; +-- check for partition bound overlap and other invalid specifications +CREATE TABLE list_parted2 ( + a varchar +) PARTITION BY LIST (a); +CREATE TABLE part_null_z PARTITION OF list_parted2 FOR VALUES IN (null, 'z'); +CREATE TABLE part_ab PARTITION OF list_parted2 FOR VALUES IN ('a', 'b'); +CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN (null); +ERROR: partition "fail_part" would overlap partition "part_null_z" +CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN ('b', 'c'); +ERROR: partition "fail_part" would overlap partition "part_ab" +CREATE TABLE range_parted2 ( + a int +) PARTITION BY RANGE (a); +-- trying to create range partition with empty range +CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (0); +ERROR: cannot create range partition with empty range +-- note that the range '[1, 1)' has no elements +CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (1); +ERROR: cannot create range partition with empty range +CREATE TABLE part0 PARTITION OF range_parted2 FOR VALUES FROM (unbounded) TO (1); +CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (unbounded) TO (2); +ERROR: partition "fail_part" would overlap partition "part0" +CREATE TABLE part1 PARTITION OF range_parted2 FOR VALUES FROM (1) TO (10); +CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (9) TO (unbounded); +ERROR: partition "fail_part" would overlap partition "part1" +-- now check for multi-column range partition key +CREATE TABLE range_parted3 ( + a int, + b int +) PARTITION BY RANGE (a, (b+1)); +CREATE TABLE part00 PARTITION OF range_parted3 FOR VALUES FROM (0, unbounded) TO (0, unbounded); +CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (0, unbounded) TO (0, 1); +ERROR: partition "fail_part" would overlap partition "part00" +CREATE TABLE part10 PARTITION OF range_parted3 FOR VALUES FROM (1, unbounded) TO (1, 1); +CREATE TABLE part11 PARTITION OF range_parted3 FOR VALUES FROM (1, 1) TO (1, 10); +CREATE TABLE part12 PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, unbounded); +CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, 20); +ERROR: partition "fail_part" would overlap partition "part12" +-- cannot create a partition that says column b is allowed to range +-- from -infinity to +infinity, while there exist partitions that have +-- more specific ranges +CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, unbounded) TO (1, unbounded); +ERROR: partition "fail_part" would overlap partition "part10" +-- 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); +CREATE TABLE part_a PARTITION OF parted FOR VALUES IN ('a'); +-- only inherited attributes (never local ones) +SELECT attname, attislocal, attinhcount FROM pg_attribute WHERE attrelid = 'part_a'::regclass and attnum > 0; + attname | attislocal | attinhcount +---------+------------+------------- + a | f | 1 + b | f | 1 +(2 rows) + +-- able to specify column default, column constraint, and table constraint +CREATE TABLE part_b PARTITION OF parted ( + b NOT NULL DEFAULT 1 CHECK (b >= 0), + CONSTRAINT check_a CHECK (length(a) > 0) +) FOR VALUES IN ('b'); +NOTICE: merging constraint "check_a" with inherited definition +-- conislocal should be false for any merged constraints +SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass AND conname = 'check_a'; + conislocal | coninhcount +------------+------------- + f | 1 +(1 row) + +-- specify PARTITION BY for a partition +CREATE TABLE fail_part_col_not_found PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (c); +ERROR: column "c" named in partition key does not exist +CREATE TABLE part_c PARTITION OF parted 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); +-- Partition bound in describe output +\d part_b + Table "public.part_b" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | text | | | + b | integer | | not null | 1 +Partition of: parted FOR VALUES IN ('b') +Check constraints: + "check_a" CHECK (length(a) > 0) + "part_b_b_check" CHECK (b >= 0) + +-- Both partition bound and partition key in describe output +\d part_c + Table "public.part_c" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | text | | | + b | integer | | not null | 0 +Partition of: parted FOR VALUES IN ('c') +Partition key: RANGE (b) +Check constraints: + "check_a" CHECK (length(a) > 0) +Number of partitions: 1 (Use \d+ to list them.) + +-- 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 + Table "public.parted" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | text | | | + b | integer | | not null | 0 +Partition key: LIST (a) +Check constraints: + "check_a" CHECK (length(a) > 0) +Number of partitions: 3 (Use \d+ to list them.) + +-- partitions cannot be dropped directly +DROP TABLE part_a; +-- need to specify CASCADE to drop partitions along with the parent +DROP TABLE parted; +ERROR: cannot drop table parted because other objects depend on it +DETAIL: table part_b depends on table parted +table part_c depends on table parted +table part_c_1_10 depends on table part_c +HINT: Use DROP ... CASCADE to drop the dependent objects too. +DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3 CASCADE; +NOTICE: drop cascades to 14 other objects +DETAIL: drop cascades to table part00 +drop cascades to table part10 +drop cascades to table part11 +drop cascades to table part12 +drop cascades to table part0 +drop cascades to table part1 +drop cascades to table part_null_z +drop cascades to table part_ab +drop cascades to table part_1 +drop cascades to table part_2 +drop cascades to table part_null +drop cascades to table part_b +drop cascades to table part_c +drop cascades to table part_c_1_10 diff --git a/src/test/regress/expected/inherit.out b/src/test/regress/expected/inherit.out index b331828e5d2..38ea8e86f3c 100644 --- a/src/test/regress/expected/inherit.out +++ b/src/test/regress/expected/inherit.out @@ -1542,3 +1542,275 @@ FROM generate_series(1, 3) g(i); reset enable_seqscan; reset enable_indexscan; reset enable_bitmapscan; +-- +-- Check that constraint exclusion works correctly with partitions using +-- implicit constraints generated from the partition bound information. +-- +create table list_parted ( + a varchar +) partition by list (a); +create table part_ab_cd partition of list_parted for values in ('ab', 'cd'); +create table part_ef_gh partition of list_parted for values in ('ef', 'gh'); +create table part_null_xy partition of list_parted for values in (null, 'xy'); +explain (costs off) select * from list_parted; + QUERY PLAN +-------------------------------- + Append + -> Seq Scan on list_parted + -> Seq Scan on part_ab_cd + -> Seq Scan on part_ef_gh + -> Seq Scan on part_null_xy +(5 rows) + +explain (costs off) select * from list_parted where a is null; + QUERY PLAN +-------------------------------- + Append + -> Seq Scan on list_parted + Filter: (a IS NULL) + -> Seq Scan on part_null_xy + Filter: (a IS NULL) +(5 rows) + +explain (costs off) select * from list_parted where a is not null; + QUERY PLAN +--------------------------------- + Append + -> Seq Scan on list_parted + Filter: (a IS NOT NULL) + -> Seq Scan on part_ab_cd + Filter: (a IS NOT NULL) + -> Seq Scan on part_ef_gh + Filter: (a IS NOT NULL) + -> Seq Scan on part_null_xy + Filter: (a IS NOT NULL) +(9 rows) + +explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef'); + QUERY PLAN +---------------------------------------------------------- + Append + -> Seq Scan on list_parted + Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) + -> Seq Scan on part_ab_cd + Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) + -> Seq Scan on part_ef_gh + Filter: ((a)::text = ANY ('{ab,cd,ef}'::text[])) +(7 rows) + +explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd'); + QUERY PLAN +--------------------------------------------------------------------------------------- + Append + -> Seq Scan on list_parted + Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) + -> Seq Scan on part_ab_cd + Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) + -> Seq Scan on part_ef_gh + Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) + -> Seq Scan on part_null_xy + Filter: (((a)::text = 'ab'::text) OR ((a)::text = ANY ('{NULL,cd}'::text[]))) +(9 rows) + +explain (costs off) select * from list_parted where a = 'ab'; + QUERY PLAN +------------------------------------------ + Append + -> Seq Scan on list_parted + Filter: ((a)::text = 'ab'::text) + -> Seq Scan on part_ab_cd + Filter: ((a)::text = 'ab'::text) +(5 rows) + +create table range_list_parted ( + a int, + b char(2) +) partition by range (a); +create table part_1_10 partition of range_list_parted for values from (1) to (10) partition by list (b); +create table part_1_10_ab partition of part_1_10 for values in ('ab'); +create table part_1_10_cd partition of part_1_10 for values in ('cd'); +create table part_10_20 partition of range_list_parted for values from (10) to (20) partition by list (b); +create table part_10_20_ab partition of part_10_20 for values in ('ab'); +create table part_10_20_cd partition of part_10_20 for values in ('cd'); +create table part_21_30 partition of range_list_parted for values from (21) to (30) partition by list (b); +create table part_21_30_ab partition of part_21_30 for values in ('ab'); +create table part_21_30_cd partition of part_21_30 for values in ('cd'); +create table part_40_inf partition of range_list_parted for values from (40) to (unbounded) partition by list (b); +create table part_40_inf_ab partition of part_40_inf for values in ('ab'); +create table part_40_inf_cd partition of part_40_inf for values in ('cd'); +create table part_40_inf_null partition of part_40_inf for values in (null); +explain (costs off) select * from range_list_parted; + QUERY PLAN +------------------------------------- + Append + -> Seq Scan on range_list_parted + -> Seq Scan on part_1_10 + -> Seq Scan on part_10_20 + -> Seq Scan on part_21_30 + -> Seq Scan on part_40_inf + -> Seq Scan on part_1_10_ab + -> Seq Scan on part_1_10_cd + -> Seq Scan on part_10_20_ab + -> Seq Scan on part_10_20_cd + -> Seq Scan on part_21_30_ab + -> Seq Scan on part_21_30_cd + -> Seq Scan on part_40_inf_ab + -> Seq Scan on part_40_inf_cd + -> Seq Scan on part_40_inf_null +(15 rows) + +explain (costs off) select * from range_list_parted where a = 5; + QUERY PLAN +------------------------------------- + Append + -> Seq Scan on range_list_parted + Filter: (a = 5) + -> Seq Scan on part_1_10 + Filter: (a = 5) + -> Seq Scan on part_1_10_ab + Filter: (a = 5) + -> Seq Scan on part_1_10_cd + Filter: (a = 5) +(9 rows) + +explain (costs off) select * from range_list_parted where b = 'ab'; + QUERY PLAN +------------------------------------- + Append + -> Seq Scan on range_list_parted + Filter: (b = 'ab'::bpchar) + -> Seq Scan on part_1_10 + Filter: (b = 'ab'::bpchar) + -> Seq Scan on part_10_20 + Filter: (b = 'ab'::bpchar) + -> Seq Scan on part_21_30 + Filter: (b = 'ab'::bpchar) + -> Seq Scan on part_40_inf + Filter: (b = 'ab'::bpchar) + -> Seq Scan on part_1_10_ab + Filter: (b = 'ab'::bpchar) + -> Seq Scan on part_10_20_ab + Filter: (b = 'ab'::bpchar) + -> Seq Scan on part_21_30_ab + Filter: (b = 'ab'::bpchar) + -> Seq Scan on part_40_inf_ab + Filter: (b = 'ab'::bpchar) +(19 rows) + +explain (costs off) select * from range_list_parted where a between 3 and 23 and b in ('ab'); + QUERY PLAN +----------------------------------------------------------------- + Append + -> Seq Scan on range_list_parted + Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) + -> Seq Scan on part_1_10 + Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) + -> Seq Scan on part_10_20 + Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) + -> Seq Scan on part_21_30 + Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) + -> Seq Scan on part_1_10_ab + Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) + -> Seq Scan on part_10_20_ab + Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) + -> Seq Scan on part_21_30_ab + Filter: ((a >= 3) AND (a <= 23) AND (b = 'ab'::bpchar)) +(15 rows) + +/* Should select no rows because range partition key cannot be null */ +explain (costs off) select * from range_list_parted where a is null; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +/* Should only select rows from the null-accepting partition */ +explain (costs off) select * from range_list_parted where b is null; + QUERY PLAN +------------------------------------- + Append + -> Seq Scan on range_list_parted + Filter: (b IS NULL) + -> Seq Scan on part_1_10 + Filter: (b IS NULL) + -> Seq Scan on part_10_20 + Filter: (b IS NULL) + -> Seq Scan on part_21_30 + Filter: (b IS NULL) + -> Seq Scan on part_40_inf + Filter: (b IS NULL) + -> Seq Scan on part_40_inf_null + Filter: (b IS NULL) +(13 rows) + +explain (costs off) select * from range_list_parted where a is not null and a < 67; + QUERY PLAN +------------------------------------------------ + Append + -> Seq Scan on range_list_parted + Filter: ((a IS NOT NULL) AND (a < 67)) + -> Seq Scan on part_1_10 + Filter: ((a IS NOT NULL) AND (a < 67)) + -> Seq Scan on part_10_20 + Filter: ((a IS NOT NULL) AND (a < 67)) + -> Seq Scan on part_21_30 + Filter: ((a IS NOT NULL) AND (a < 67)) + -> Seq Scan on part_40_inf + Filter: ((a IS NOT NULL) AND (a < 67)) + -> Seq Scan on part_1_10_ab + Filter: ((a IS NOT NULL) AND (a < 67)) + -> Seq Scan on part_1_10_cd + Filter: ((a IS NOT NULL) AND (a < 67)) + -> Seq Scan on part_10_20_ab + Filter: ((a IS NOT NULL) AND (a < 67)) + -> Seq Scan on part_10_20_cd + Filter: ((a IS NOT NULL) AND (a < 67)) + -> Seq Scan on part_21_30_ab + Filter: ((a IS NOT NULL) AND (a < 67)) + -> Seq Scan on part_21_30_cd + Filter: ((a IS NOT NULL) AND (a < 67)) + -> Seq Scan on part_40_inf_ab + Filter: ((a IS NOT NULL) AND (a < 67)) + -> Seq Scan on part_40_inf_cd + Filter: ((a IS NOT NULL) AND (a < 67)) + -> Seq Scan on part_40_inf_null + Filter: ((a IS NOT NULL) AND (a < 67)) +(29 rows) + +explain (costs off) select * from range_list_parted where a >= 30; + QUERY PLAN +------------------------------------- + Append + -> Seq Scan on range_list_parted + Filter: (a >= 30) + -> Seq Scan on part_40_inf + Filter: (a >= 30) + -> Seq Scan on part_40_inf_ab + Filter: (a >= 30) + -> Seq Scan on part_40_inf_cd + Filter: (a >= 30) + -> Seq Scan on part_40_inf_null + Filter: (a >= 30) +(11 rows) + +drop table list_parted cascade; +NOTICE: drop cascades to 3 other objects +DETAIL: drop cascades to table part_ab_cd +drop cascades to table part_ef_gh +drop cascades to table part_null_xy +drop table range_list_parted cascade; +NOTICE: drop cascades to 13 other objects +DETAIL: drop cascades to table part_1_10 +drop cascades to table part_1_10_ab +drop cascades to table part_1_10_cd +drop cascades to table part_10_20 +drop cascades to table part_10_20_ab +drop cascades to table part_10_20_cd +drop cascades to table part_21_30 +drop cascades to table part_21_30_ab +drop cascades to table part_21_30_cd +drop cascades to table part_40_inf +drop cascades to table part_40_inf_ab +drop cascades to table part_40_inf_cd +drop cascades to table part_40_inf_null diff --git a/src/test/regress/expected/insert.out b/src/test/regress/expected/insert.out index 03619d71c36..561cefa3c4d 100644 --- a/src/test/regress/expected/insert.out +++ b/src/test/regress/expected/insert.out @@ -160,3 +160,143 @@ Rules: drop table inserttest2; drop table inserttest; drop type insert_test_type; +-- direct partition inserts should check partition bound constraint +create table range_parted ( + a text, + b int +) partition by range (a, (b+0)); +create table part1 partition of range_parted for values from ('a', 1) to ('a', 10); +create table part2 partition of range_parted for values from ('a', 10) to ('a', 20); +create table part3 partition of range_parted for values from ('b', 1) to ('b', 10); +create table part4 partition of range_parted for values from ('b', 10) to ('b', 20); +-- fail +insert into part1 values ('a', 11); +ERROR: new row for relation "part1" violates partition constraint +DETAIL: Failing row contains (a, 11). +insert into part1 values ('b', 1); +ERROR: new row for relation "part1" violates partition constraint +DETAIL: Failing row contains (b, 1). +-- ok +insert into part1 values ('a', 1); +-- fail +insert into part4 values ('b', 21); +ERROR: new row for relation "part4" violates partition constraint +DETAIL: Failing row contains (b, 21). +insert into part4 values ('a', 10); +ERROR: new row for relation "part4" violates partition constraint +DETAIL: Failing row contains (a, 10). +-- ok +insert into part4 values ('b', 10); +-- fail (partition key a has a NOT NULL constraint) +insert into part1 values (null); +ERROR: null value in column "a" violates not-null constraint +DETAIL: Failing row contains (null, null). +-- fail (expression key (b+0) cannot be null either) +insert into part1 values (1); +ERROR: new row for relation "part1" violates partition constraint +DETAIL: Failing row contains (1, null). +create table list_parted ( + a text, + b int +) partition by list (lower(a)); +create table part_aa_bb partition of list_parted FOR VALUES IN ('aa', 'bb'); +create table part_cc_dd partition of list_parted FOR VALUES IN ('cc', 'dd'); +create table part_null partition of list_parted FOR VALUES IN (null); +-- fail +insert into part_aa_bb values ('cc', 1); +ERROR: new row for relation "part_aa_bb" violates partition constraint +DETAIL: Failing row contains (cc, 1). +insert into part_aa_bb values ('AAa', 1); +ERROR: new row for relation "part_aa_bb" violates partition constraint +DETAIL: Failing row contains (AAa, 1). +insert into part_aa_bb values (null); +ERROR: new row for relation "part_aa_bb" violates partition constraint +DETAIL: Failing row contains (null, null). +-- ok +insert into part_cc_dd values ('cC', 1); +insert into part_null values (null, 0); +-- check in case of multi-level partitioned table +create table part_ee_ff partition of list_parted for values in ('ee', 'ff') partition by range (b); +create table part_ee_ff1 partition of part_ee_ff for values from (1) to (10); +create table part_ee_ff2 partition of part_ee_ff for values from (10) to (20); +-- fail +insert into part_ee_ff1 values ('EE', 11); +ERROR: new row for relation "part_ee_ff1" violates partition constraint +DETAIL: Failing row contains (EE, 11). +-- fail (even the parent's, ie, part_ee_ff's partition constraint applies) +insert into part_ee_ff1 values ('cc', 1); +ERROR: new row for relation "part_ee_ff1" violates partition constraint +DETAIL: Failing row contains (cc, 1). +-- ok +insert into part_ee_ff1 values ('ff', 1); +insert into part_ee_ff2 values ('ff', 11); +-- Check tuple routing for partitioned tables +-- fail +insert into range_parted values ('a', 0); +ERROR: no partition of relation "range_parted" found for row +DETAIL: Failing row contains (a, 0). +-- ok +insert into range_parted values ('a', 1); +insert into range_parted values ('a', 10); +-- fail +insert into range_parted values ('a', 20); +ERROR: no partition of relation "range_parted" found for row +DETAIL: Failing row contains (a, 20). +-- ok +insert into range_parted values ('b', 1); +insert into range_parted values ('b', 10); +-- fail (partition key (b+0) is null) +insert into range_parted values ('a'); +ERROR: range partition key of row contains null +select tableoid::regclass, * from range_parted; + tableoid | a | b +----------+---+---- + part1 | a | 1 + part1 | a | 1 + part2 | a | 10 + part3 | b | 1 + part4 | b | 10 + part4 | b | 10 +(6 rows) + +-- ok +insert into list_parted values (null, 1); +insert into list_parted (a) values ('aA'); +-- fail (partition of part_ee_ff not found in both cases) +insert into list_parted values ('EE', 0); +ERROR: no partition of relation "part_ee_ff" found for row +DETAIL: Failing row contains (EE, 0). +insert into part_ee_ff values ('EE', 0); +ERROR: no partition of relation "part_ee_ff" found for row +DETAIL: Failing row contains (EE, 0). +-- ok +insert into list_parted values ('EE', 1); +insert into part_ee_ff values ('EE', 10); +select tableoid::regclass, * from list_parted; + tableoid | a | b +-------------+----+---- + part_aa_bb | aA | + part_cc_dd | cC | 1 + part_null | | 0 + part_null | | 1 + part_ee_ff1 | ff | 1 + part_ee_ff1 | EE | 1 + part_ee_ff2 | ff | 11 + part_ee_ff2 | EE | 10 +(8 rows) + +-- cleanup +drop table range_parted cascade; +NOTICE: drop cascades to 4 other objects +DETAIL: drop cascades to table part1 +drop cascades to table part2 +drop cascades to table part3 +drop cascades to table part4 +drop table list_parted cascade; +NOTICE: drop cascades to 6 other objects +DETAIL: drop cascades to table part_aa_bb +drop cascades to table part_cc_dd +drop cascades to table part_null +drop cascades to table part_ee_ff +drop cascades to table part_ee_ff1 +drop cascades to table part_ee_ff2 diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out index b1ebcf60d25..8fa929a6aa3 100644 --- a/src/test/regress/expected/sanity_check.out +++ b/src/test/regress/expected/sanity_check.out @@ -120,6 +120,7 @@ pg_namespace|t pg_opclass|t pg_operator|t pg_opfamily|t +pg_partitioned_table|t pg_pltemplate|t pg_policy|t pg_proc|t diff --git a/src/test/regress/expected/update.out b/src/test/regress/expected/update.out index 609899e1f74..a1e92554505 100644 --- a/src/test/regress/expected/update.out +++ b/src/test/regress/expected/update.out @@ -198,3 +198,30 @@ INSERT INTO upsert_test VALUES (1, 'Bat') ON CONFLICT(a) DROP TABLE update_test; DROP TABLE upsert_test; +-- update to a partition should check partition bound constraint for the new tuple +create table range_parted ( + a text, + b int +) partition by range (a, b); +create table part_a_1_a_10 partition of range_parted for values from ('a', 1) to ('a', 10); +create table part_a_10_a_20 partition of range_parted for values from ('a', 10) to ('a', 20); +create table part_b_1_b_10 partition of range_parted for values from ('b', 1) to ('b', 10); +create table part_b_10_b_20 partition of range_parted for values from ('b', 10) to ('b', 20); +insert into part_a_1_a_10 values ('a', 1); +insert into part_b_10_b_20 values ('b', 10); +-- fail +update part_a_1_a_10 set a = 'b' where a = 'a'; +ERROR: new row for relation "part_a_1_a_10" violates partition constraint +DETAIL: Failing row contains (b, 1). +update range_parted set b = b - 1 where b = 10; +ERROR: new row for relation "part_b_10_b_20" violates partition constraint +DETAIL: Failing row contains (b, 9). +-- ok +update range_parted set b = b + 1 where b = 10; +-- cleanup +drop table range_parted cascade; +NOTICE: drop cascades to 4 other objects +DETAIL: drop cascades to table part_a_1_a_10 +drop cascades to table part_a_10_a_20 +drop cascades to table part_b_1_b_10 +drop cascades to table part_b_10_b_20 diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql index c8eed3ec646..c4ed69304fc 100644 --- a/src/test/regress/sql/alter_table.sql +++ b/src/test/regress/sql/alter_table.sql @@ -1875,3 +1875,297 @@ ALTER TABLE test_add_column ADD COLUMN c4 integer; \d test_add_column DROP TABLE test_add_column; + +-- unsupported constraint types for partitioned tables +CREATE TABLE partitioned ( + a int, + b int +) PARTITION BY RANGE (a, (a+b+1)); +ALTER TABLE partitioned ADD UNIQUE (a); +ALTER TABLE partitioned ADD PRIMARY KEY (a); +ALTER TABLE partitioned ADD FOREIGN KEY (a) REFERENCES blah; +ALTER TABLE partitioned ADD EXCLUDE USING gist (a WITH &&); + +-- cannot drop column that is part of the partition key +ALTER TABLE partitioned DROP COLUMN a; +ALTER TABLE partitioned ALTER COLUMN a TYPE char(5); +ALTER TABLE partitioned DROP COLUMN b; +ALTER TABLE partitioned ALTER COLUMN b TYPE char(5); + +-- cannot drop NOT NULL on columns in the range partition key +ALTER TABLE partitioned ALTER COLUMN a DROP NOT NULL; + +-- partitioned table cannot partiticipate in regular inheritance +CREATE TABLE foo ( + a int, + b int +); +ALTER TABLE partitioned INHERIT foo; +ALTER TABLE foo INHERIT partitioned; + +-- cannot add NO INHERIT constraint to partitioned tables +ALTER TABLE partitioned ADD CONSTRAINT chk_a CHECK (a > 0) NO INHERIT; + +DROP TABLE partitioned, foo; + +-- +-- ATTACH PARTITION +-- + +-- check that target table is partitioned +CREATE TABLE unparted ( + a int +); +CREATE TABLE fail_part (like unparted); +ALTER TABLE unparted ATTACH PARTITION fail_part FOR VALUES IN ('a'); +DROP TABLE unparted, fail_part; + +-- check that partition bound is compatible +CREATE TABLE list_parted ( + a int NOT NULL, + b char(2) COLLATE "en_US", + CONSTRAINT check_a CHECK (a > 0) +) PARTITION BY LIST (a); +CREATE TABLE fail_part (LIKE list_parted); +ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES FROM (1) TO (10); +DROP TABLE fail_part; + +-- check that the table being attached exists +ALTER TABLE list_parted ATTACH PARTITION nonexistant FOR VALUES IN (1); + +-- check ownership of the source table +CREATE ROLE regress_test_me; +CREATE ROLE regress_test_not_me; +CREATE TABLE not_owned_by_me (LIKE list_parted); +ALTER TABLE not_owned_by_me OWNER TO regress_test_not_me; +SET SESSION AUTHORIZATION regress_test_me; +CREATE TABLE owned_by_me ( + a int +) PARTITION BY LIST (a); +ALTER TABLE owned_by_me ATTACH PARTITION not_owned_by_me FOR VALUES IN (1); +RESET SESSION AUTHORIZATION; +DROP TABLE owned_by_me, not_owned_by_me; +DROP ROLE regress_test_not_me; +DROP ROLE regress_test_me; + +-- check that the table being attached is not part of regular inheritance +CREATE TABLE parent (LIKE list_parted); +CREATE TABLE child () INHERITS (parent); +ALTER TABLE list_parted ATTACH PARTITION child FOR VALUES IN (1); +ALTER TABLE list_parted ATTACH PARTITION parent FOR VALUES IN (1); +DROP TABLE parent CASCADE; + +-- check any TEMP-ness +CREATE TEMP TABLE temp_parted (a int) PARTITION BY LIST (a); +CREATE TABLE perm_part (a int); +ALTER TABLE temp_parted ATTACH PARTITION perm_part FOR VALUES IN (1); +DROP TABLE temp_parted, perm_part; + +-- check that the table being attached is not a typed table +CREATE TYPE mytype AS (a int); +CREATE TABLE fail_part OF mytype; +ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); +DROP TYPE mytype CASCADE; + +-- check existence (or non-existence) of oid column +ALTER TABLE list_parted SET WITH OIDS; +CREATE TABLE fail_part (a int); +ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); + +ALTER TABLE list_parted SET WITHOUT OIDS; +ALTER TABLE fail_part SET WITH OIDS; +ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); +DROP TABLE fail_part; + +-- check that the table being attached has only columns present in the parent +CREATE TABLE fail_part (like list_parted, c int); +ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); +DROP TABLE fail_part; + +-- check that the table being attached has every column of the parent +CREATE TABLE fail_part (a int NOT NULL); +ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); +DROP TABLE fail_part; + +-- check that columns match in type, collation and NOT NULL status +CREATE TABLE fail_part ( + b char(3), + a int NOT NULL +); +ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); +ALTER TABLE fail_part ALTER b TYPE char (2) COLLATE "en_CA"; +ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); +DROP TABLE fail_part; + +-- check that the table being attached has all constraints of the parent +CREATE TABLE fail_part ( + b char(2) COLLATE "en_US", + a int NOT NULL +); +ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); + +-- check that the constraint matches in definition with parent's constraint +ALTER TABLE fail_part ADD CONSTRAINT check_a CHECK (a >= 0); +ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); +DROP TABLE fail_part; + +-- check the attributes and constraints after partition is attached +CREATE TABLE part_1 ( + a int NOT NULL, + b char(2) COLLATE "en_US", + CONSTRAINT check_a CHECK (a > 0) +); +ALTER TABLE list_parted ATTACH PARTITION part_1 FOR VALUES IN (1); +-- attislocal and conislocal are always false for merged attributes and constraints respectively. +SELECT attislocal, attinhcount FROM pg_attribute WHERE attrelid = 'part_1'::regclass AND attnum > 0; +SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_1'::regclass AND conname = 'check_a'; + +-- check that the new partition won't overlap with an existing partition +CREATE TABLE fail_part (LIKE part_1 INCLUDING CONSTRAINTS); +ALTER TABLE list_parted ATTACH PARTITION fail_part FOR VALUES IN (1); + +-- check validation when attaching list partitions +CREATE TABLE list_parted2 ( + a int, + b char +) PARTITION BY LIST (a); + +-- check that violating rows are correctly reported +CREATE TABLE part_2 (LIKE list_parted2); +INSERT INTO part_2 VALUES (3, 'a'); +ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2); + +-- should be ok after deleting the bad row +DELETE FROM part_2; +ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2); + +-- adding constraints that describe the desired partition constraint +-- (or more restrictive) will help skip the validation scan +CREATE TABLE part_3_4 ( + LIKE list_parted2, + CONSTRAINT check_a CHECK (a IN (3)) +); + +-- however, if a list partition does not accept nulls, there should be +-- an explicit NOT NULL constraint on the partition key column for the +-- validation scan to be skipped; +ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4); + +-- adding a NOT NULL constraint will cause the scan to be skipped +ALTER TABLE list_parted2 DETACH PARTITION part_3_4; +ALTER TABLE part_3_4 ALTER a SET NOT NULL; +ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4); + + +-- check validation when attaching range partitions +CREATE TABLE range_parted ( + a int, + b int +) PARTITION BY RANGE (a, b); + +-- check that violating rows are correctly reported +CREATE TABLE part1 ( + a int NOT NULL CHECK (a = 1), + b int NOT NULL CHECK (b >= 1 AND b <= 10) +); +INSERT INTO part1 VALUES (1, 10); +-- Remember the TO bound is exclusive +ALTER TABLE range_parted ATTACH PARTITION part1 FOR VALUES FROM (1, 1) TO (1, 10); + +-- should be ok after deleting the bad row +DELETE FROM part1; +ALTER TABLE range_parted ATTACH PARTITION part1 FOR VALUES FROM (1, 1) TO (1, 10); + +-- adding constraints that describe the desired partition constraint +-- (or more restrictive) will help skip the validation scan +CREATE TABLE part2 ( + a int NOT NULL CHECK (a = 1), + b int NOT NULL CHECK (b >= 10 AND b < 18) +); +ALTER TABLE range_parted ATTACH PARTITION part2 FOR VALUES FROM (1, 10) TO (1, 20); + +-- check that leaf partitions are scanned when attaching a partitioned +-- table +CREATE TABLE part_5 ( + LIKE list_parted2 +) PARTITION BY LIST (b); + +-- check that violating rows are correctly reported +CREATE TABLE part_5_a PARTITION OF part_5 FOR VALUES IN ('a'); +INSERT INTO part_5_a (a, b) VALUES (6, 'a'); +ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5); + +-- delete the faulting row and also add a constraint to skip the scan +DELETE FROM part_5_a WHERE a NOT IN (3); +ALTER TABLE part_5 ADD CONSTRAINT check_a CHECK (a IN (5)), ALTER a SET NOT NULL; +ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5); + + +-- check that the table being attached is not already a partition +ALTER TABLE list_parted2 ATTACH PARTITION part_2 FOR VALUES IN (2); + +-- check that circular inheritance is not allowed +ALTER TABLE part_5 ATTACH PARTITION list_parted2 FOR VALUES IN ('b'); +ALTER TABLE list_parted2 ATTACH PARTITION list_parted2 FOR VALUES IN (0); + +-- +-- DETACH PARTITION +-- + +-- check that the partition being detached exists at all +ALTER TABLE list_parted2 DETACH PARTITION part_4; + +-- check that the partition being detached is actually a partition of the parent +CREATE TABLE not_a_part (a int); +ALTER TABLE list_parted2 DETACH PARTITION not_a_part; +ALTER TABLE list_parted2 DETACH PARTITION part_1; + +-- check that, after being detached, attinhcount/coninhcount is dropped to 0 and +-- attislocal/conislocal is set to true +ALTER TABLE list_parted2 DETACH PARTITION part_3_4; +SELECT attinhcount, attislocal FROM pg_attribute WHERE attrelid = 'part_3_4'::regclass AND attnum > 0; +SELECT coninhcount, conislocal FROM pg_constraint WHERE conrelid = 'part_3_4'::regclass AND conname = 'check_a'; +DROP TABLE part_3_4; + +-- Check ALTER TABLE commands for partitioned tables and partitions + +-- cannot add/drop column to/from *only* the parent +ALTER TABLE ONLY list_parted2 ADD COLUMN c int; +ALTER TABLE ONLY list_parted2 DROP COLUMN b; + +-- cannot add a column to partition or drop an inherited one +ALTER TABLE part_2 ADD COLUMN c text; +ALTER TABLE part_2 DROP COLUMN b; + +-- Nor rename, alter type +ALTER TABLE part_2 RENAME COLUMN b to c; +ALTER TABLE part_2 ALTER COLUMN b TYPE text; + +-- cannot add NOT NULL or check constraints to *only* the parent (ie, non-inherited) +ALTER TABLE ONLY list_parted2 ALTER b SET NOT NULL; +ALTER TABLE ONLY list_parted2 add constraint check_b check (b <> 'zz'); +ALTER TABLE list_parted2 add constraint check_b check (b <> 'zz') NO INHERIT; + +-- cannot drop inherited NOT NULL or check constraints from partition +ALTER TABLE list_parted2 ALTER b SET NOT NULL, ADD CONSTRAINT check_a2 CHECK (a > 0); +ALTER TABLE part_2 ALTER b DROP NOT NULL; +ALTER TABLE part_2 DROP CONSTRAINT check_a2; + +-- cannot drop NOT NULL or check constraints from *only* the parent +ALTER TABLE ONLY list_parted2 ALTER a DROP NOT NULL; +ALTER TABLE ONLY list_parted2 DROP CONSTRAINT check_a2; + +-- check that a partition cannot participate in regular inheritance +CREATE TABLE inh_test () INHERITS (part_2); +CREATE TABLE inh_test (LIKE part_2); +ALTER TABLE inh_test INHERIT part_2; +ALTER TABLE part_2 INHERIT inh_test; + +-- cannot drop or alter type of partition key columns of lower level +-- partitioned tables; for example, part_5, which is list_parted2's +-- partition, is partitioned on b; +ALTER TABLE list_parted2 DROP COLUMN b; +ALTER TABLE list_parted2 ALTER COLUMN b TYPE text; + +-- cleanup +DROP TABLE list_parted, list_parted2, range_parted CASCADE; diff --git a/src/test/regress/sql/create_table.sql b/src/test/regress/sql/create_table.sql index 78bdc8bf5e7..69848e30948 100644 --- a/src/test/regress/sql/create_table.sql +++ b/src/test/regress/sql/create_table.sql @@ -269,3 +269,318 @@ DROP TABLE as_select1; -- check that the oid column is added before the primary key is checked CREATE TABLE oid_pk (f1 INT, PRIMARY KEY(oid)) WITH OIDS; DROP TABLE oid_pk; + +-- +-- Partitioned tables +-- + +-- cannot combine INHERITS and PARTITION BY (although grammar allows) +CREATE TABLE partitioned ( + a int +) INHERITS (some_table) PARTITION BY LIST (a); + +-- cannot use more than 1 column as partition key for list partitioned table +CREATE TABLE partitioned ( + a1 int, + a2 int +) PARTITION BY LIST (a1, a2); -- fail + +-- unsupported constraint type for partitioned tables +CREATE TABLE partitioned ( + a int PRIMARY KEY +) PARTITION BY RANGE (a); + +CREATE TABLE pkrel ( + a int PRIMARY KEY +); +CREATE TABLE partitioned ( + a int REFERENCES pkrel(a) +) PARTITION BY RANGE (a); +DROP TABLE pkrel; + +CREATE TABLE partitioned ( + a int UNIQUE +) PARTITION BY RANGE (a); + +CREATE TABLE partitioned ( + a int, + EXCLUDE USING gist (a WITH &&) +) PARTITION BY RANGE (a); + +-- prevent column from being used twice in the partition key +CREATE TABLE partitioned ( + a int +) PARTITION BY RANGE (a, a); + +-- prevent using prohibited expressions in the key +CREATE FUNCTION retset (a int) RETURNS SETOF int AS $$ SELECT 1; $$ LANGUAGE SQL IMMUTABLE; +CREATE TABLE partitioned ( + a int +) PARTITION BY RANGE (retset(a)); +DROP FUNCTION retset(int); + +CREATE TABLE partitioned ( + a int +) PARTITION BY RANGE ((avg(a))); + +CREATE TABLE partitioned ( + a int, + b int +) PARTITION BY RANGE ((avg(a) OVER (PARTITION BY b))); + +CREATE TABLE partitioned ( + a int +) PARTITION BY LIST ((a LIKE (SELECT 1))); + +CREATE TABLE partitioned ( + a int +) PARTITION BY RANGE (('a')); + +CREATE FUNCTION const_func () RETURNS int AS $$ SELECT 1; $$ LANGUAGE SQL IMMUTABLE; +CREATE TABLE partitioned ( + a int +) PARTITION BY RANGE (const_func()); +DROP FUNCTION const_func(); + +-- only accept "list" and "range" as partitioning strategy +CREATE TABLE partitioned ( + a int +) PARTITION BY HASH (a); + +-- specified column must be present in the table +CREATE TABLE partitioned ( + a int +) PARTITION BY RANGE (b); + +-- cannot use system columns in partition key +CREATE TABLE partitioned ( + a int +) PARTITION BY RANGE (xmin); + +-- functions in key must be immutable +CREATE FUNCTION immut_func (a int) RETURNS int AS $$ SELECT a + random()::int; $$ LANGUAGE SQL; +CREATE TABLE partitioned ( + a int +) PARTITION BY RANGE (immut_func(a)); +DROP FUNCTION immut_func(int); + +-- cannot contain whole-row references +CREATE TABLE partitioned ( + a int +) PARTITION BY RANGE ((partitioned)); + +-- prevent using columns of unsupported types in key (type must have a btree operator class) +CREATE TABLE partitioned ( + a point +) PARTITION BY LIST (a); +CREATE TABLE partitioned ( + a point +) PARTITION BY LIST (a point_ops); +CREATE TABLE partitioned ( + a point +) PARTITION BY RANGE (a); +CREATE TABLE partitioned ( + a point +) PARTITION BY RANGE (a point_ops); + +-- cannot add NO INHERIT constraints to partitioned tables +CREATE TABLE partitioned ( + a int, + CONSTRAINT check_a CHECK (a > 0) NO INHERIT +) PARTITION BY RANGE (a); + +-- some checks after successful creation of a partitioned table +CREATE FUNCTION plusone(a int) RETURNS INT AS $$ SELECT a+1; $$ LANGUAGE SQL; + +CREATE TABLE partitioned ( + a int, + b int, + c text, + d text +) PARTITION BY RANGE (a oid_ops, plusone(b), c collate "default", d collate "en_US"); + +-- check relkind +SELECT relkind FROM pg_class WHERE relname = 'partitioned'; + +-- check that range partition key columns are marked NOT NULL +SELECT attname, attnotnull FROM pg_attribute WHERE attrelid = 'partitioned'::regclass AND attnum > 0; + +-- prevent a function referenced in partition key from being dropped +DROP FUNCTION plusone(int); + +-- partitioned table cannot partiticipate in regular inheritance +CREATE TABLE partitioned2 ( + a int +) PARTITION BY LIST ((a+1)); +CREATE TABLE fail () INHERITS (partitioned2); + +-- Partition key in describe output +\d partitioned +\d partitioned2 + +DROP TABLE partitioned, partitioned2; + +-- +-- Partitions +-- + +-- check partition bound syntax + +CREATE TABLE list_parted ( + a int +) PARTITION BY LIST (a); +-- syntax allows only string literal, numeric literal and null to be +-- specified for a partition bound value +CREATE TABLE part_1 PARTITION OF list_parted FOR VALUES IN ('1'); +CREATE TABLE part_2 PARTITION OF list_parted FOR VALUES IN (2); +CREATE TABLE part_null PARTITION OF list_parted FOR VALUES IN (null); +CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN (int '1'); +CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN ('1'::int); + +-- syntax does not allow empty list of values for list partitions +CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES IN (); +-- trying to specify range for list partitioned table +CREATE TABLE fail_part PARTITION OF list_parted FOR VALUES FROM (1) TO (2); + +-- specified literal can't be cast to the partition column data type +CREATE TABLE bools ( + a bool +) PARTITION BY LIST (a); +CREATE TABLE bools_true PARTITION OF bools FOR VALUES IN (1); +DROP TABLE bools; + +CREATE TABLE range_parted ( + a date +) PARTITION BY RANGE (a); + +-- trying to specify list for range partitioned table +CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES IN ('a'); +-- each of start and end bounds must have same number of values as the +-- length of the partition key +CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a', 1) TO ('z'); +CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM ('a') TO ('z', 1); + +-- cannot specify null values in range bounds +CREATE TABLE fail_part PARTITION OF range_parted FOR VALUES FROM (null) TO (unbounded); + +-- check if compatible with the specified parent + +-- cannot create as partition of a non-partitioned table +CREATE TABLE unparted ( + a int +); +CREATE TABLE fail_part PARTITION OF unparted FOR VALUES IN ('a'); +DROP TABLE unparted; + +-- cannot create a permanent rel as partition of a temp rel +CREATE TEMP TABLE temp_parted ( + a int +) PARTITION BY LIST (a); +CREATE TABLE fail_part PARTITION OF temp_parted FOR VALUES IN ('a'); +DROP TABLE temp_parted; + +-- cannot create a table with oids as partition of table without oids +CREATE TABLE no_oids_parted ( + a int +) PARTITION BY RANGE (a) WITHOUT OIDS; +CREATE TABLE fail_part PARTITION OF no_oids_parted FOR VALUES FROM (1) TO (10 )WITH OIDS; +DROP TABLE no_oids_parted; + +-- likewise, the reverse if also true +CREATE TABLE oids_parted ( + a int +) PARTITION BY RANGE (a) WITH OIDS; +CREATE TABLE fail_part PARTITION OF oids_parted FOR VALUES FROM (1) TO (10 ) WITHOUT OIDS; +DROP TABLE oids_parted; + +-- check for partition bound overlap and other invalid specifications + +CREATE TABLE list_parted2 ( + a varchar +) PARTITION BY LIST (a); +CREATE TABLE part_null_z PARTITION OF list_parted2 FOR VALUES IN (null, 'z'); +CREATE TABLE part_ab PARTITION OF list_parted2 FOR VALUES IN ('a', 'b'); + +CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN (null); +CREATE TABLE fail_part PARTITION OF list_parted2 FOR VALUES IN ('b', 'c'); + +CREATE TABLE range_parted2 ( + a int +) PARTITION BY RANGE (a); + +-- trying to create range partition with empty range +CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (0); +-- note that the range '[1, 1)' has no elements +CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (1) TO (1); + +CREATE TABLE part0 PARTITION OF range_parted2 FOR VALUES FROM (unbounded) TO (1); +CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (unbounded) TO (2); +CREATE TABLE part1 PARTITION OF range_parted2 FOR VALUES FROM (1) TO (10); +CREATE TABLE fail_part PARTITION OF range_parted2 FOR VALUES FROM (9) TO (unbounded); + +-- now check for multi-column range partition key +CREATE TABLE range_parted3 ( + a int, + b int +) PARTITION BY RANGE (a, (b+1)); + +CREATE TABLE part00 PARTITION OF range_parted3 FOR VALUES FROM (0, unbounded) TO (0, unbounded); +CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (0, unbounded) TO (0, 1); + +CREATE TABLE part10 PARTITION OF range_parted3 FOR VALUES FROM (1, unbounded) TO (1, 1); +CREATE TABLE part11 PARTITION OF range_parted3 FOR VALUES FROM (1, 1) TO (1, 10); +CREATE TABLE part12 PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, unbounded); +CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, 10) TO (1, 20); + +-- cannot create a partition that says column b is allowed to range +-- from -infinity to +infinity, while there exist partitions that have +-- more specific ranges +CREATE TABLE fail_part PARTITION OF range_parted3 FOR VALUES FROM (1, unbounded) TO (1, unbounded); + +-- 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); + +CREATE TABLE part_a PARTITION OF parted FOR VALUES IN ('a'); + +-- only inherited attributes (never local ones) +SELECT attname, attislocal, attinhcount FROM pg_attribute WHERE attrelid = 'part_a'::regclass and attnum > 0; + +-- able to specify column default, column constraint, and table constraint +CREATE TABLE part_b PARTITION OF parted ( + b NOT NULL DEFAULT 1 CHECK (b >= 0), + CONSTRAINT check_a CHECK (length(a) > 0) +) FOR VALUES IN ('b'); +-- conislocal should be false for any merged constraints +SELECT conislocal, coninhcount FROM pg_constraint WHERE conrelid = 'part_b'::regclass AND conname = 'check_a'; + +-- specify PARTITION BY for a partition +CREATE TABLE fail_part_col_not_found PARTITION OF parted FOR VALUES IN ('c') PARTITION BY RANGE (c); +CREATE TABLE part_c PARTITION OF parted 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); + +-- Partition bound in describe output +\d part_b + +-- Both partition bound and partition key in describe output +\d part_c + +-- 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 + +-- partitions cannot be dropped directly +DROP TABLE part_a; + +-- need to specify CASCADE to drop partitions along with the parent +DROP TABLE parted; + +DROP TABLE parted, list_parted, range_parted, list_parted2, range_parted2, range_parted3 CASCADE; diff --git a/src/test/regress/sql/inherit.sql b/src/test/regress/sql/inherit.sql index f45aab1ac69..e22a14ebda8 100644 --- a/src/test/regress/sql/inherit.sql +++ b/src/test/regress/sql/inherit.sql @@ -536,3 +536,55 @@ FROM generate_series(1, 3) g(i); reset enable_seqscan; reset enable_indexscan; reset enable_bitmapscan; + +-- +-- Check that constraint exclusion works correctly with partitions using +-- implicit constraints generated from the partition bound information. +-- +create table list_parted ( + a varchar +) partition by list (a); +create table part_ab_cd partition of list_parted for values in ('ab', 'cd'); +create table part_ef_gh partition of list_parted for values in ('ef', 'gh'); +create table part_null_xy partition of list_parted for values in (null, 'xy'); + +explain (costs off) select * from list_parted; +explain (costs off) select * from list_parted where a is null; +explain (costs off) select * from list_parted where a is not null; +explain (costs off) select * from list_parted where a in ('ab', 'cd', 'ef'); +explain (costs off) select * from list_parted where a = 'ab' or a in (null, 'cd'); +explain (costs off) select * from list_parted where a = 'ab'; + +create table range_list_parted ( + a int, + b char(2) +) partition by range (a); +create table part_1_10 partition of range_list_parted for values from (1) to (10) partition by list (b); +create table part_1_10_ab partition of part_1_10 for values in ('ab'); +create table part_1_10_cd partition of part_1_10 for values in ('cd'); +create table part_10_20 partition of range_list_parted for values from (10) to (20) partition by list (b); +create table part_10_20_ab partition of part_10_20 for values in ('ab'); +create table part_10_20_cd partition of part_10_20 for values in ('cd'); +create table part_21_30 partition of range_list_parted for values from (21) to (30) partition by list (b); +create table part_21_30_ab partition of part_21_30 for values in ('ab'); +create table part_21_30_cd partition of part_21_30 for values in ('cd'); +create table part_40_inf partition of range_list_parted for values from (40) to (unbounded) partition by list (b); +create table part_40_inf_ab partition of part_40_inf for values in ('ab'); +create table part_40_inf_cd partition of part_40_inf for values in ('cd'); +create table part_40_inf_null partition of part_40_inf for values in (null); + +explain (costs off) select * from range_list_parted; +explain (costs off) select * from range_list_parted where a = 5; +explain (costs off) select * from range_list_parted where b = 'ab'; +explain (costs off) select * from range_list_parted where a between 3 and 23 and b in ('ab'); + +/* Should select no rows because range partition key cannot be null */ +explain (costs off) select * from range_list_parted where a is null; + +/* Should only select rows from the null-accepting partition */ +explain (costs off) select * from range_list_parted where b is null; +explain (costs off) select * from range_list_parted where a is not null and a < 67; +explain (costs off) select * from range_list_parted where a >= 30; + +drop table list_parted cascade; +drop table range_list_parted cascade; diff --git a/src/test/regress/sql/insert.sql b/src/test/regress/sql/insert.sql index 7924d5d46de..846bb5897a3 100644 --- a/src/test/regress/sql/insert.sql +++ b/src/test/regress/sql/insert.sql @@ -84,3 +84,89 @@ create rule irule3 as on insert to inserttest2 do also drop table inserttest2; drop table inserttest; drop type insert_test_type; + +-- direct partition inserts should check partition bound constraint +create table range_parted ( + a text, + b int +) partition by range (a, (b+0)); +create table part1 partition of range_parted for values from ('a', 1) to ('a', 10); +create table part2 partition of range_parted for values from ('a', 10) to ('a', 20); +create table part3 partition of range_parted for values from ('b', 1) to ('b', 10); +create table part4 partition of range_parted for values from ('b', 10) to ('b', 20); + +-- fail +insert into part1 values ('a', 11); +insert into part1 values ('b', 1); +-- ok +insert into part1 values ('a', 1); +-- fail +insert into part4 values ('b', 21); +insert into part4 values ('a', 10); +-- ok +insert into part4 values ('b', 10); + +-- fail (partition key a has a NOT NULL constraint) +insert into part1 values (null); +-- fail (expression key (b+0) cannot be null either) +insert into part1 values (1); + +create table list_parted ( + a text, + b int +) partition by list (lower(a)); +create table part_aa_bb partition of list_parted FOR VALUES IN ('aa', 'bb'); +create table part_cc_dd partition of list_parted FOR VALUES IN ('cc', 'dd'); +create table part_null partition of list_parted FOR VALUES IN (null); + +-- fail +insert into part_aa_bb values ('cc', 1); +insert into part_aa_bb values ('AAa', 1); +insert into part_aa_bb values (null); +-- ok +insert into part_cc_dd values ('cC', 1); +insert into part_null values (null, 0); + +-- check in case of multi-level partitioned table +create table part_ee_ff partition of list_parted for values in ('ee', 'ff') partition by range (b); +create table part_ee_ff1 partition of part_ee_ff for values from (1) to (10); +create table part_ee_ff2 partition of part_ee_ff for values from (10) to (20); + +-- fail +insert into part_ee_ff1 values ('EE', 11); +-- fail (even the parent's, ie, part_ee_ff's partition constraint applies) +insert into part_ee_ff1 values ('cc', 1); +-- ok +insert into part_ee_ff1 values ('ff', 1); +insert into part_ee_ff2 values ('ff', 11); + +-- Check tuple routing for partitioned tables + +-- fail +insert into range_parted values ('a', 0); +-- ok +insert into range_parted values ('a', 1); +insert into range_parted values ('a', 10); +-- fail +insert into range_parted values ('a', 20); +-- ok +insert into range_parted values ('b', 1); +insert into range_parted values ('b', 10); +-- fail (partition key (b+0) is null) +insert into range_parted values ('a'); +select tableoid::regclass, * from range_parted; + +-- ok +insert into list_parted values (null, 1); +insert into list_parted (a) values ('aA'); +-- fail (partition of part_ee_ff not found in both cases) +insert into list_parted values ('EE', 0); +insert into part_ee_ff values ('EE', 0); +-- ok +insert into list_parted values ('EE', 1); +insert into part_ee_ff values ('EE', 10); +select tableoid::regclass, * from list_parted; + +-- cleanup +drop table range_parted cascade; +drop table list_parted cascade; diff --git a/src/test/regress/sql/update.sql b/src/test/regress/sql/update.sql index ad58273b385..d7721ed3760 100644 --- a/src/test/regress/sql/update.sql +++ b/src/test/regress/sql/update.sql @@ -106,3 +106,24 @@ INSERT INTO upsert_test VALUES (1, 'Bat') ON CONFLICT(a) DROP TABLE update_test; DROP TABLE upsert_test; + +-- update to a partition should check partition bound constraint for the new tuple +create table range_parted ( + a text, + b int +) partition by range (a, b); +create table part_a_1_a_10 partition of range_parted for values from ('a', 1) to ('a', 10); +create table part_a_10_a_20 partition of range_parted for values from ('a', 10) to ('a', 20); +create table part_b_1_b_10 partition of range_parted for values from ('b', 1) to ('b', 10); +create table part_b_10_b_20 partition of range_parted for values from ('b', 10) to ('b', 20); +insert into part_a_1_a_10 values ('a', 1); +insert into part_b_10_b_20 values ('b', 10); + +-- fail +update part_a_1_a_10 set a = 'b' where a = 'a'; +update range_parted set b = b - 1 where b = 10; +-- ok +update range_parted set b = b + 1 where b = 10; + +-- cleanup +drop table range_parted cascade; |