From 4a7a720839402a64446fcca4f8504ef558498d1d Mon Sep 17 00:00:00 2001 From: jian he Date: Wed, 26 Aug 2026 16:57:26 +0800 Subject: [PATCH v8 1/1] Disallow more cases for partition merge/split MERGE/SPLIT PARTITION creates the new partition using the partitioned table as a template and relocates the existing rows as-is. Anything defined only on the source partition is therefore not carried over, and we have no mechanism to recreate most of it. Rather than silently dropping such objects, error out for now. Specifically, ALTER TABLE MERGE/SPLIT PARTITION(S) is now rejected when: * The partitioned table, or any source partition, has a trigger. Internal triggers too, so this also rejects any partitioned table taking part in a foreign key constraint, on either the referencing or the referenced side. * A source partition has a constraint with (conislocal = true), that is, a CHECK or NOT NULL constraint defined directly on the partition rather than inherited from the partitioned table. * A source partition has an index whose (pg_class.relispartition == false), that is, a local index created directly on the partition and not part of the partitioned index hierarchy. * A column default on a source partition differs from the corresponding default on the partitioned table. Previously only generation expressions were compared; the check now covers plain defaults, in both directions. * A column generation expression on a source partition differs from the partitioned table's. This check already existed and is retained. * The access methods of the source partitions differ from each other, or, when the partitioned table has an access method of its own, differ from that one. * Partition is unlogged table is not supported, otherwise MERGE/SPLIT PARTITION is produce a LOGGED table. As a consequence of the last item, the new partition now takes its access method from the source partition(s) rather than falling back to default_table_access_method when the partitioned table has none of its own. Allow STATISTICS and COMMENTS, since these objects were not cascaded to child tables when they were created. Some regression tests for currently unsupported features are commented out. They can be uncommented when the corresponding features are supported, so there is no need to add additional SQL tests for these cases next time. Discussion: https://postgr.es/m/CACJufxG0Kqu2Qnei_xZ%2BDsQVohX95eO9FRNB9ncKOmOPFsFF-A%40mail.gmail.com --- src/backend/commands/tablecmds.c | 207 ++++++++++++++++-- src/test/regress/expected/partition_merge.out | 116 +++++----- src/test/regress/expected/partition_split.out | 122 ++++++----- src/test/regress/sql/partition_merge.sql | 78 ++++--- src/test/regress/sql/partition_split.sql | 61 ++++-- 5 files changed, 414 insertions(+), 170 deletions(-) diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index e9eef00ad7c..8c41cdc2d21 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -23078,24 +23078,36 @@ static void checkPartitionGenExprMatchesParent(Relation parent_rel, Relation partRel) { TupleDesc parentDesc = RelationGetDescr(parent_rel); + TupleDesc childDesc = RelationGetDescr(partRel); TupleConstr *constr = parentDesc->constr; + TupleConstr *childConstr = RelationGetDescr(partRel)->constr; AttrMap *attmap = NULL; /* Nothing to compare if the partitioned table has no generated columns. */ - if (constr == NULL || - !(constr->has_generated_stored || constr->has_generated_virtual)) + if (constr == NULL || constr->num_defval == 0) + { + if (childConstr && childConstr->num_defval > 0) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot merge or split partitions when a partition's column default expression differs from the partitioned table"), + errdetail("Partition \"%s\" has a default expression while partitioned table \"%s\" does not have one.", + RelationGetRelationName(partRel), + RelationGetRelationName(parent_rel))); return; + } for (AttrNumber parent_attno = 1; parent_attno <= parentDesc->natts; parent_attno++) { Form_pg_attribute pattr = TupleDescAttr(parentDesc, parent_attno - 1); + Form_pg_attribute childattr; AttrNumber child_attno; Node *parentExpr; Node *childExpr; bool found_whole_row; - if (pattr->attisdropped || pattr->attgenerated == '\0') + /* if (pattr->attisdropped || !pattr->atthasdef) */ + if (pattr->attisdropped) continue; /* @@ -23106,6 +23118,28 @@ checkPartitionGenExprMatchesParent(Relation parent_rel, Relation partRel) child_attno = get_attnum(RelationGetRelid(partRel), NameStr(pattr->attname)); Assert(child_attno != InvalidAttrNumber); + childattr = TupleDescAttr(childDesc, parent_attno - 1); + + if (!pattr->atthasdef && !childattr->atthasdef) + continue; + + if (pattr->attgenerated == '\0') + { + parentExpr = build_column_default(parent_rel, parent_attno); + childExpr = build_column_default(partRel, child_attno); + + if (equal(parentExpr, childExpr)) + continue; + + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot merge or split partitions when a partition's column default expression differs from the partitioned table"), + errdetail("Column \"%s\" of partition \"%s\" has a default expression different from table \"%s\".", + NameStr(pattr->attname), + RelationGetRelationName(partRel), + RelationGetRelationName(parent_rel))); + } + parentExpr = build_generation_expression(parent_rel, parent_attno); childExpr = build_generation_expression(partRel, child_attno); @@ -23127,6 +23161,89 @@ checkPartitionGenExprMatchesParent(Relation parent_rel, Relation partRel) } } +static void +checkPartitionIndexMatchesParent(Relation parent_rel, Relation partRel) +{ + List *mergingrelIdxs = NIL; + Relation pg_index; + SysScanDesc indscan; + HeapTuple indexTuple; + ScanKeyData skey; + + pg_index = table_open(IndexRelationId, AccessShareLock); + + /* + * Prepare to scan pg_index for entries having pg_indexid = this rel. + */ + ScanKeyInit(&skey, + Anum_pg_index_indrelid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(RelationGetRelid(partRel))); + + indscan = systable_beginscan(pg_index, IndexIndrelidIndexId, true, + NULL, 1, &skey); + while (HeapTupleIsValid(indexTuple = systable_getnext(indscan))) + { + Form_pg_index index = (Form_pg_index) GETSTRUCT(indexTuple); + + mergingrelIdxs = lappend_oid(mergingrelIdxs, index->indexrelid); + } + systable_endscan(indscan); + + foreach_oid(mergingPartitionIdxs, mergingrelIdxs) + { + Form_pg_class classForm; + + indexTuple = SearchSysCache1(RELOID, ObjectIdGetDatum(mergingPartitionIdxs)); + if (!HeapTupleIsValid(indexTuple)) + elog(ERROR, "cache lookup failed for relation %u", mergingPartitionIdxs); + classForm = (Form_pg_class) GETSTRUCT(indexTuple); + + if (!classForm->relispartition) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot merge or split partition when a partition has a local index")); + + ReleaseSysCache(indexTuple); + } + table_close(pg_index, AccessShareLock); +} + +static void +checkPartitionConstrMatchesParent(Relation parent_rel, Relation partRel) +{ + Relation pg_constraint; + SysScanDesc conscan; + HeapTuple constrTuple; + ScanKeyData skey; + + pg_constraint = table_open(ConstraintRelationId, AccessShareLock); + + ScanKeyInit(&skey, + Anum_pg_constraint_conrelid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(RelationGetRelid(partRel))); + conscan = systable_beginscan(pg_constraint, ConstraintRelidTypidNameIndexId, + true, NULL, 1, &skey); + while (HeapTupleIsValid(constrTuple = systable_getnext(conscan))) + { + Form_pg_constraint con = (Form_pg_constraint) GETSTRUCT(constrTuple); + + if (!con->conislocal) + continue; + + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot merge or split partition when a partition has local constraints"), + errdetail("Constraint %s is defined on partition \"%s\" but not defined on the partitioned table \"%s\".", + NameStr(con->conname), + RelationGetRelationName(partRel), + RelationGetRelationName(parent_rel))); + } + systable_endscan(conscan); + table_close(pg_constraint, AccessShareLock); +} + /* * createTableConstraints: * create check constraints and column defaults (including generation @@ -23311,7 +23428,7 @@ createTableConstraints(Relation parent_rel, Relation newRel) */ static Relation createPartitionTable(RangeVar *newPartName, - Relation parent_rel, Oid ownerId) + Relation parent_rel, Oid ownerId, Oid accessmtd) { Relation newRel; Oid newRelId; @@ -23319,7 +23436,6 @@ createPartitionTable(RangeVar *newPartName, Oid tablespaceId; TupleDesc descriptor; List *colList = NIL; - Oid relamId; Oid namespaceId; Form_pg_class parent_relform = parent_rel->rd_rel; @@ -23335,12 +23451,6 @@ createPartitionTable(RangeVar *newPartName, /* Create a tuple descriptor from the relation schema. */ descriptor = BuildDescForRelation(colList); - /* Look up the access method for the new relation. */ - if (OidIsValid(parent_relform->relam)) - relamId = parent_relform->relam; - else - relamId = get_table_am_oid(default_table_access_method, false); - /* Look up the namespace in which we are supposed to create the relation. */ namespaceId = RangeVarGetAndCheckCreationNamespace(newPartName, NoLock, &existingRelid); @@ -23406,7 +23516,7 @@ createPartitionTable(RangeVar *newPartName, InvalidOid, InvalidOid, ownerId, - relamId, + accessmtd, descriptor, NIL, RELKIND_RELATION, @@ -24022,6 +24132,7 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel, Oid save_userid; int save_sec_context; int save_nestlevel; + Oid partrelaccessmtd = InvalidOid; /* * The rows are relocated as-is, but a generated column or CHECK @@ -24030,6 +24141,12 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel, */ checkPartitionSystemColumnRefs(rel); + if (rel->trigdesc) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot merge or split partition because partitioned table \"%s\" has triggers on it", + RelationGetRelationName(rel))); + /* * Check ownership of merged partitions - partitions with different owners * cannot be merged. Also, collect the OIDs of these partitions during the @@ -24047,6 +24164,20 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel, mergingPartition = table_openrv_extended(name, NoLock, false); Assert(CheckRelationLockedByMe(mergingPartition, AccessExclusiveLock, false)); + if (mergingPartition->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot merge unlogged partition \"%s\"", + RelationGetRelationName(mergingPartition)), + errdetail("The new partition is created as a logged table, which would change the persistence of the merged rows.")); + + if (!OidIsValid(partrelaccessmtd)) + partrelaccessmtd = mergingPartition->rd_rel->relam; + else if (partrelaccessmtd != mergingPartition->rd_rel->relam) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot merge partition because the access method of merging partition differs from each other")); + if (OidIsValid(ownerId)) { /* Do the partitions being merged have different owners? */ @@ -24058,6 +24189,20 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel, else ownerId = mergingPartition->rd_rel->relowner; + if (mergingPartition->trigdesc) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot merge or split partition because partition table \"%s\" has triggers on it", + RelationGetRelationName(mergingPartition))); + + /* Look up the access method for the new relation. */ + if (OidIsValid(rel->rd_rel->relam) && (rel->rd_rel->relam != mergingPartition->rd_rel->relam)) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot merge or split partition because the access method of partition \"%s\" differs from the partitioned table \"%s\"", + RelationGetRelationName(mergingPartition), + RelationGetRelationName(rel))); + /* * The new partition inherits the partitioned table's generation * expressions, but rows are moved as-is; reject a partition whose @@ -24066,6 +24211,9 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel, */ checkPartitionGenExprMatchesParent(rel, mergingPartition); + checkPartitionIndexMatchesParent(rel, mergingPartition); + checkPartitionConstrMatchesParent(rel, mergingPartition); + /* Store the next merging partition into the list. */ mergingPartitions = lappend_oid(mergingPartitions, RelationGetRelid(mergingPartition)); @@ -24171,7 +24319,7 @@ ATExecMergePartitions(List **wqueue, AlteredTableInfo *tab, Relation rel, * model. */ Assert(OidIsValid(ownerId)); - newPartRel = createPartitionTable(cmd->name, rel, ownerId); + newPartRel = createPartitionTable(cmd->name, rel, ownerId, partrelaccessmtd); /* * Carry the source partitions' replica identity over to the new @@ -24501,6 +24649,12 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel, int save_nestlevel; List *splitPartList; + if (rel->trigdesc) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot merge or split partition because partitioned table \"%s\" has triggers", + RelationGetRelationName(rel))); + defaultPartOid = get_default_oid_from_partdesc(RelationGetPartitionDesc(rel, true)); /* @@ -24509,6 +24663,27 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel, */ splitRel = table_openrv(cmd->name, NoLock); + if (splitRel->rd_rel->relpersistence == RELPERSISTENCE_UNLOGGED) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot split unlogged partition \"%s\"", + RelationGetRelationName(splitRel)), + errdetail("The new partitions are created as logged tables, which would change the persistence of the split rows.")); + + if (splitRel->trigdesc) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot merge or split partition because partition table \"%s\" has triggers on it", + RelationGetRelationName(splitRel))); + + /* Look up the access method for the new relation. */ + if (OidIsValid(rel->rd_rel->relam) && (rel->rd_rel->relam != splitRel->rd_rel->relam)) + ereport(ERROR, + errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot merge or split partition because the access method of partition \"%s\" differs from the partitioned table \"%s\"", + RelationGetRelationName(splitRel), + RelationGetRelationName(rel))); + splitRelOid = RelationGetRelid(splitRel); /* @@ -24527,6 +24702,9 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel, checkPartitionSystemColumnRefs(rel); checkPartitionGenExprMatchesParent(rel, splitRel); + checkPartitionIndexMatchesParent(rel, splitRel); + checkPartitionConstrMatchesParent(rel, splitRel); + /* Check descriptions of new partitions. */ foreach_node(SinglePartitionSpec, sps, cmd->partlist) { @@ -24603,7 +24781,8 @@ ATExecSplitPartition(List **wqueue, AlteredTableInfo *tab, Relation rel, Relation newPartRel; newPartRel = createPartitionTable(sps->name, rel, - splitRel->rd_rel->relowner); + splitRel->rd_rel->relowner, + splitRel->rd_rel->relam); newPartRels = lappend(newPartRels, newPartRel); } diff --git a/src/test/regress/expected/partition_merge.out b/src/test/regress/expected/partition_merge.out index bc4963b06c3..91b8c39160a 100644 --- a/src/test/regress/expected/partition_merge.out +++ b/src/test/regress/expected/partition_merge.out @@ -330,14 +330,15 @@ NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = STA -- 1 trigger should fire here (row): INSERT INTO salespeople10_20 VALUES (19, 'Ivanov'); NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW -ALTER TABLE salespeople MERGE PARTITIONS (salespeople10_20, salespeople20_30, salespeople30_40) INTO salespeople10_40; --- 2 triggers should fire here (row + statement): -INSERT INTO salespeople VALUES (20, 'Smirnoff'); -NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW -NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = STATEMENT --- 1 trigger should fire here (row): -INSERT INTO salespeople10_40 VALUES (30, 'Ford'); -NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW +ALTER TABLE salespeople MERGE PARTITIONS (salespeople10_20, salespeople20_30, salespeople30_40) INTO salespeople10_40; -- error +ERROR: cannot merge or split partition because partitioned table "salespeople" has triggers on it +DROP TRIGGER salespeople_after_insert_statement_trigger ON salespeople; +DROP TRIGGER salespeople_after_insert_row_trigger ON salespeople; +ALTER TABLE salespeople MERGE PARTITIONS (salespeople10_20, salespeople20_30, salespeople30_40) INTO salespeople10_40; -- ok +-- 2 triggers should fire here (row + statement): (not supported) +-- INSERT INTO salespeople VALUES (20, 'Smirnoff'); +-- 1 trigger should fire here (row): (not supported) +-- INSERT INTO salespeople10_40 VALUES (30, 'Ford'); SELECT * FROM salespeople01_10; salesperson_id | salesperson_name ----------------+------------------ @@ -349,9 +350,7 @@ SELECT * FROM salespeople10_40; ----------------+------------------ 10 | May 19 | Ivanov - 20 | Smirnoff - 30 | Ford -(4 rows) +(2 rows) DROP TABLE salespeople; DROP FUNCTION after_insert_row_trigger(); @@ -647,7 +646,11 @@ CREATE TABLE t (i int, PRIMARY KEY(i)) PARTITION BY RANGE (i); CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1); CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2); CREATE INDEX tidx ON t(i); -ALTER TABLE t MERGE PARTITIONS (tp_1_2, tp_0_1) INTO tp_1_2; +CREATE INDEX tp_1_2_idx ON tp_1_2(i); +ALTER TABLE t MERGE PARTITIONS (tp_1_2, tp_0_1) INTO tp_1_2; -- error, not supported +ERROR: cannot merge or split partition when a partition has a local index +DROP INDEX tp_1_2_idx; +ALTER TABLE t MERGE PARTITIONS (tp_1_2, tp_0_1) INTO tp_1_2; -- ok -- Indexname values should be 'tp_1_2_pkey' and 'tp_1_2_i_idx'. \d+ tp_1_2 Table "partitions_merge_schema.tp_1_2" @@ -793,22 +796,24 @@ ORDER BY c.relname COLLATE "C"; DROP TABLE t; -- With no access method on the partitioned table, the new partition falls --- back to default_table_access_method, just as CREATE TABLE ... PARTITION OF --- would. +-- back to merging partition CREATE TABLE t (i int) PARTITION BY RANGE (i); CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1); CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2); BEGIN; SET LOCAL default_table_access_method = partitions_merge_heap; +CREATE TABLE tp_1_3 PARTITION OF t FOR VALUES FROM (2) TO (3); ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2; SELECT c.relname, a.amname FROM pg_class c JOIN pg_am a ON c.relam = a.oid WHERE c.oid = 'tp_0_2'::regclass; - relname | amname ----------+----------------------- - tp_0_2 | partitions_merge_heap + relname | amname +---------+-------- + tp_0_2 | heap (1 row) +ALTER TABLE t MERGE PARTITIONS (tp_0_2, tp_1_3) INTO tp_0_3; -- error +ERROR: cannot merge partition because the access method of merging partition differs from each other COMMIT; DROP TABLE t; DROP ACCESS METHOD partitions_merge_heap; @@ -923,6 +928,7 @@ ALTER TABLE t ADD CONSTRAINT t_b_check CHECK (b > 0); ALTER TABLE t ADD CONSTRAINT t_b_check1 CHECK (b > 0) NOT ENFORCED; ALTER TABLE t ADD CONSTRAINT t_b_check2 CHECK (b > 0) NOT VALID; ALTER TABLE t ADD CONSTRAINT t_b_nn NOT NULL b NOT VALID; +ALTER TABLE tp_1_2 ADD CONSTRAINT t_b_check3 CHECK (b > 0) NOT VALID; INSERT INTO tp_0_1(i, t, b) VALUES(0, DEFAULT, 1); INSERT INTO tp_1_2(i, t, b) VALUES(1, DEFAULT, 2); CREATE OR REPLACE FUNCTION trigger_function() RETURNS trigger LANGUAGE 'plpgsql' AS @@ -932,12 +938,12 @@ BEGIN RETURN new; END; $BODY$; -CREATE TRIGGER t_before_insert_row_trigger BEFORE INSERT ON t FOR EACH ROW - EXECUTE PROCEDURE trigger_function('t'); -CREATE TRIGGER tp_0_1_before_insert_row_trigger BEFORE INSERT ON tp_0_1 FOR EACH ROW - EXECUTE PROCEDURE trigger_function('tp_0_1'); -CREATE TRIGGER tp_1_2_before_insert_row_trigger BEFORE INSERT ON tp_1_2 FOR EACH ROW - EXECUTE PROCEDURE trigger_function('tp_1_2'); +-- CREATE TRIGGER t_before_insert_row_trigger BEFORE INSERT ON t FOR EACH ROW +-- EXECUTE PROCEDURE trigger_function('t'); +-- CREATE TRIGGER tp_0_1_before_insert_row_trigger BEFORE INSERT ON tp_0_1 FOR EACH ROW +-- EXECUTE PROCEDURE trigger_function('tp_0_1'); +-- CREATE TRIGGER tp_1_2_before_insert_row_trigger BEFORE INSERT ON tp_1_2 FOR EACH ROW +-- EXECUTE PROCEDURE trigger_function('tp_1_2'); \d+ tp_0_1 Table "partitions_merge_schema.tp_0_1" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description @@ -957,11 +963,17 @@ Statistics objects: Not-null constraints: "tp_0_1_i_not_null" NOT NULL "i" (inherited) "t_b_nn" NOT NULL "b" (inherited) NOT VALID -Triggers: - t_before_insert_row_trigger BEFORE INSERT ON tp_0_1 FOR EACH ROW EXECUTE FUNCTION trigger_function('t'), ON TABLE t - tp_0_1_before_insert_row_trigger BEFORE INSERT ON tp_0_1 FOR EACH ROW EXECUTE FUNCTION trigger_function('tp_0_1') -ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_1; +ALTER TABLE tp_0_1 ALTER COLUMN t SET DEFAULT 'default_t'; +ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_1; -- error +ERROR: cannot merge or split partitions when a partition's column default expression differs from the partitioned table +DETAIL: Column "t" of partition "tp_1_2" has a default expression different from table "t". +ALTER TABLE tp_1_2 ALTER COLUMN t SET DEFAULT 'default_t'; +ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_1; -- error +ERROR: cannot merge or split partition when a partition has local constraints +DETAIL: Constraint t_b_check3 is defined on partition "tp_1_2" but not defined on the partitioned table "t". +ALTER TABLE tp_1_2 DROP CONSTRAINT t_b_check3; +ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_1; -- ok \d+ tp_0_1 Table "partitions_merge_schema.tp_0_1" Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description @@ -979,11 +991,8 @@ Check constraints: Not-null constraints: "t_i_not_null" NOT NULL "i" (inherited) "t_b_nn" NOT NULL "b" (inherited) NOT VALID -Triggers: - t_before_insert_row_trigger BEFORE INSERT ON tp_0_1 FOR EACH ROW EXECUTE FUNCTION trigger_function('t'), ON TABLE t INSERT INTO t(i, t, b) VALUES(1, DEFAULT, 3); -NOTICE: trigger(t) called: action = INSERT, when = BEFORE, level = ROW SELECT tableoid::regclass, * FROM t ORDER BY b; tableoid | i | t | b | d ----------+---+----------------+---+------------ @@ -996,32 +1005,20 @@ DROP TABLE t; DROP FUNCTION trigger_function(); \set HIDE_TOAST_COMPRESSION true -- Test MERGE PARTITIONS with not valid foreign key constraint -CREATE TABLE t (i INT PRIMARY KEY) PARTITION BY RANGE (i); -CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1); -CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2); -INSERT INTO t VALUES (0), (1); -CREATE TABLE t_fk (i INT); -INSERT INTO t_fk VALUES (1), (2); -ALTER TABLE t_fk ADD CONSTRAINT t_fk_i_fkey FOREIGN KEY (i) REFERENCES t NOT VALID; -ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2; --- Should be NOT VALID FOREIGN KEY -\d tp_0_2 - Table "partitions_merge_schema.tp_0_2" - Column | Type | Collation | Nullable | Default ---------+---------+-----------+----------+--------- - i | integer | | not null | -Partition of: t FOR VALUES FROM (0) TO (2) -Indexes: - "tp_0_2_pkey" PRIMARY KEY, btree (i) -Referenced by: - TABLE "t_fk" CONSTRAINT "t_fk_i_fkey" FOREIGN KEY (i) REFERENCES t(i) NOT VALID - --- ERROR -ALTER TABLE t_fk VALIDATE CONSTRAINT t_fk_i_fkey; -ERROR: insert or update on table "t_fk" violates foreign key constraint "t_fk_i_fkey" -DETAIL: Key (i)=(2) is not present in table "t". -DROP TABLE t_fk; -DROP TABLE t; +-- CREATE TABLE t (i INT PRIMARY KEY) PARTITION BY RANGE (i); +-- CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1); +-- CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2); +-- INSERT INTO t VALUES (0), (1); +-- CREATE TABLE t_fk (i INT); +-- INSERT INTO t_fk VALUES (1), (2); +-- ALTER TABLE t_fk ADD CONSTRAINT t_fk_i_fkey FOREIGN KEY (i) REFERENCES t NOT VALID; +-- ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2; +-- -- Should be NOT VALID FOREIGN KEY (not supported) +-- \d tp_0_2 +-- -- ERROR +-- ALTER TABLE t_fk VALIDATE CONSTRAINT t_fk_i_fkey; +-- DROP TABLE t_fk; +-- DROP TABLE t; -- Test MERGE PARTITIONS with not enforced foreign key constraint CREATE TABLE t (i INT PRIMARY KEY) PARTITION BY RANGE (i); CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1); @@ -1319,6 +1316,13 @@ ALTER TABLE t ENABLE ROW LEVEL SECURITY; CREATE POLICY hide_secret ON t FOR SELECT USING (secret IS NOT TRUE); ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2; DROP TABLE t; +CREATE TABLE t (i int) PARTITION BY RANGE (i); +CREATE UNLOGGED TABLE t_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1); +CREATE UNLOGGED TABLE t_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2); +ALTER TABLE t MERGE PARTITIONS (t_0_1, t_1_2) INTO t_0_2; -- error +ERROR: cannot merge unlogged partition "t_0_1" +DETAIL: The new partition is created as a logged table, which would change the persistence of the merged rows. +DROP TABLE t, t_0_1, t_1_2; RESET search_path; -- DROP SCHEMA partitions_merge_schema; diff --git a/src/test/regress/expected/partition_split.out b/src/test/regress/expected/partition_split.out index 49e63d66aef..e6e0345786e 100644 --- a/src/test/regress/expected/partition_split.out +++ b/src/test/regress/expected/partition_split.out @@ -532,18 +532,18 @@ DROP TABLE sales_range; CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)); INSERT INTO salespeople VALUES (1, 'Poirot'); CREATE TABLE sales_range ( -salesperson_id INT REFERENCES salespeople(salesperson_id), +-- salesperson_id INT REFERENCES salespeople(salesperson_id), -- (currently not supported) +salesperson_id INT, sales_amount INT CHECK (sales_amount > 1), sales_date DATE) PARTITION BY RANGE (sales_date); CREATE TABLE sales_jan2022 PARTITION OF sales_range FOR VALUES FROM ('2022-01-01') TO ('2022-02-01'); CREATE TABLE sales_feb_mar_apr2022 PARTITION OF sales_range FOR VALUES FROM ('2022-02-01') TO ('2022-05-01'); CREATE TABLE sales_others PARTITION OF sales_range DEFAULT; SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_feb_mar_apr2022'::regclass::oid ORDER BY conname COLLATE "C"; - pg_get_constraintdef | conname | conkey ----------------------------------------------------------------------+---------------------------------+-------- - CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2} - FOREIGN KEY (salesperson_id) REFERENCES salespeople(salesperson_id) | sales_range_salesperson_id_fkey | {1} -(2 rows) + pg_get_constraintdef | conname | conkey +----------------------------+--------------------------------+-------- + CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2} +(1 row) ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO (PARTITION sales_feb2022 FOR VALUES FROM ('2022-02-01') TO ('2022-03-01'), @@ -551,25 +551,22 @@ ALTER TABLE sales_range SPLIT PARTITION sales_feb_mar_apr2022 INTO PARTITION sales_apr2022 FOR VALUES FROM ('2022-04-01') TO ('2022-05-01')); -- We should see the same CONSTRAINTs as on sales_feb_mar_apr2022 partition SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_feb2022'::regclass::oid ORDER BY conname COLLATE "C"; - pg_get_constraintdef | conname | conkey ----------------------------------------------------------------------+---------------------------------+-------- - CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2} - FOREIGN KEY (salesperson_id) REFERENCES salespeople(salesperson_id) | sales_range_salesperson_id_fkey | {1} -(2 rows) + pg_get_constraintdef | conname | conkey +----------------------------+--------------------------------+-------- + CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2} +(1 row) SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_mar2022'::regclass::oid ORDER BY conname COLLATE "C"; - pg_get_constraintdef | conname | conkey ----------------------------------------------------------------------+---------------------------------+-------- - CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2} - FOREIGN KEY (salesperson_id) REFERENCES salespeople(salesperson_id) | sales_range_salesperson_id_fkey | {1} -(2 rows) + pg_get_constraintdef | conname | conkey +----------------------------+--------------------------------+-------- + CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2} +(1 row) SELECT pg_get_constraintdef(oid), conname, conkey FROM pg_constraint WHERE conrelid = 'sales_apr2022'::regclass::oid ORDER BY conname COLLATE "C"; - pg_get_constraintdef | conname | conkey ----------------------------------------------------------------------+---------------------------------+-------- - CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2} - FOREIGN KEY (salesperson_id) REFERENCES salespeople(salesperson_id) | sales_range_salesperson_id_fkey | {1} -(2 rows) + pg_get_constraintdef | conname | conkey +----------------------------+--------------------------------+-------- + CHECK ((sales_amount > 1)) | sales_range_sales_amount_check | {2} +(1 row) -- ERROR INSERT INTO sales_range VALUES (1, 0, '2022-03-11'); @@ -577,8 +574,6 @@ ERROR: new row for relation "sales_mar2022" violates check constraint "sales_ra DETAIL: Failing row contains (1, 0, 03-11-2022). -- ERROR INSERT INTO sales_range VALUES (-1, 10, '2022-03-11'); -ERROR: insert or update on table "sales_mar2022" violates foreign key constraint "sales_range_salesperson_id_fkey" -DETAIL: Key (salesperson_id)=(-1) is not present in table "salespeople". -- ok INSERT INTO sales_range VALUES (1, 10, '2022-03-11'); DROP TABLE sales_range CASCADE; @@ -586,8 +581,9 @@ DROP TABLE salespeople CASCADE; -- -- Test: split partition on partitioned table in case of existing FOREIGN KEY reference from another table -- +-- (currently not supported) CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id); -CREATE TABLE sales (salesperson_id INT REFERENCES salespeople(salesperson_id), sales_amount INT, sales_date DATE); +-- CREATE TABLE sales (salesperson_id INT REFERENCES salespeople(salesperson_id), sales_amount INT, sales_date DATE); CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10); CREATE TABLE salespeople10_40 PARTITION OF salespeople FOR VALUES FROM (10) TO (40); INSERT INTO salespeople VALUES @@ -596,15 +592,15 @@ INSERT INTO salespeople VALUES (19, 'Ivanov'), (20, 'Smirnoff'), (30, 'Ford'); -INSERT INTO sales VALUES - (1, 100, '2022-03-01'), - (1, 110, '2022-03-02'), - (10, 150, '2022-03-01'), - (10, 90, '2022-03-03'), - (19, 200, '2022-03-04'), - (20, 50, '2022-03-12'), - (20, 170, '2022-03-02'), - (30, 30, '2022-03-04'); +-- INSERT INTO sales VALUES +-- (1, 100, '2022-03-01'), +-- (1, 110, '2022-03-02'), +-- (10, 150, '2022-03-01'), +-- (10, 90, '2022-03-03'), +-- (19, 200, '2022-03-04'), +-- (20, 50, '2022-03-12'), +-- (20, 170, '2022-03-02'), +-- (30, 30, '2022-03-04'); SELECT tableoid::regclass, * FROM salespeople ORDER BY tableoid::regclass::text COLLATE "C", salesperson_id; tableoid | salesperson_id | salesperson_name ------------------+----------------+------------------ @@ -630,12 +626,10 @@ SELECT tableoid::regclass, * FROM salespeople ORDER BY tableoid::regclass::text (5 rows) -- ERROR -INSERT INTO sales VALUES (40, 50, '2022-03-04'); -ERROR: insert or update on table "sales" violates foreign key constraint "sales_salesperson_id_fkey" -DETAIL: Key (salesperson_id)=(40) is not present in table "salespeople". +-- INSERT INTO sales VALUES (40, 50, '2022-03-04'); -- ok -INSERT INTO sales VALUES (30, 50, '2022-03-04'); -DROP TABLE sales CASCADE; +-- INSERT INTO sales VALUES (30, 50, '2022-03-04'); +-- DROP TABLE sales CASCADE; DROP TABLE salespeople CASCADE; -- -- Test: split partition of partitioned table with triggers @@ -667,26 +661,29 @@ NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = STA -- 1 trigger should fire here (row): INSERT INTO salespeople10_40 VALUES (19, 'Ivanov'); NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW +-- (currently not supported) ALTER TABLE salespeople SPLIT PARTITION salespeople10_40 INTO (PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20), PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30), PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40)); +ERROR: cannot merge or split partition because partitioned table "salespeople" has triggers +DROP TRIGGER salespeople_after_insert_statement_trigger ON salespeople; +DROP TRIGGER salespeople_after_insert_row_trigger ON salespeople; +ALTER TABLE salespeople SPLIT PARTITION salespeople10_40 INTO + (PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20), + PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30), + PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40)); -- ok -- 2 triggers should fire here (row + statement): -INSERT INTO salespeople VALUES (20, 'Smirnoff'); -NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW -NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = STATEMENT +-- INSERT INTO salespeople VALUES (20, 'Smirnoff'); -- 1 trigger should fire here (row): -INSERT INTO salespeople30_40 VALUES (30, 'Ford'); -NOTICE: trigger(salespeople) called: action = INSERT, when = AFTER, level = ROW +-- INSERT INTO salespeople30_40 VALUES (30, 'Ford'); SELECT tableoid::regclass, * FROM salespeople ORDER BY tableoid::regclass::text COLLATE "C", salesperson_id; tableoid | salesperson_id | salesperson_name ------------------+----------------+------------------ salespeople01_10 | 1 | Poirot salespeople10_20 | 10 | May salespeople10_20 | 19 | Ivanov - salespeople20_30 | 20 | Smirnoff - salespeople30_40 | 30 | Ford -(5 rows) +(3 rows) DROP TABLE salespeople CASCADE; DROP FUNCTION after_insert_row_trigger(); @@ -1370,10 +1367,10 @@ SELECT c.relname, a.amname FROM pg_class c JOIN pg_am a ON c.relam = a.oid WHERE c.oid IN ('tp_0_1'::regclass, 'tp_1_2'::regclass) ORDER BY c.relname COLLATE "C"; - relname | amname ----------+---------------------- - tp_0_1 | partition_split_heap - tp_1_2 | partition_split_heap + relname | amname +---------+-------- + tp_0_1 | heap + tp_1_2 | heap (2 rows) COMMIT; @@ -1616,7 +1613,19 @@ Triggers: ALTER TABLE t SPLIT PARTITION tp_x INTO (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1), - PARTITION tp_x FOR VALUES FROM (1) TO (2)); + PARTITION tp_x FOR VALUES FROM (1) TO (2)); -- error, triggers not supported +ERROR: cannot merge or split partition because partitioned table "t" has triggers +DROP TRIGGER t_before_insert_row_trigger ON t; +DROP TRIGGER tp_x_before_insert_row_trigger ON tp_x; +ALTER TABLE t SPLIT PARTITION tp_x INTO + (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1), + PARTITION tp_x FOR VALUES FROM (1) TO (2)); -- error, different column default not supported +ERROR: cannot merge or split partitions when a partition's column default expression differs from the partitioned table +DETAIL: Column "t" of partition "tp_x" has a default expression different from table "t". +ALTER TABLE tp_x ALTER COLUMN t SET DEFAULT 'default_t'; +ALTER TABLE t SPLIT PARTITION tp_x INTO + (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1), + PARTITION tp_x FOR VALUES FROM (1) TO (2)); -- ok \d+ tp_x Table "partition_split_schema.tp_x" Column | Type | Collation | Nullable | Default | Storage | Stats target | Description @@ -1634,11 +1643,8 @@ Check constraints: Not-null constraints: "t_i_not_null" NOT NULL "i" (inherited) "t_b_nn" NOT NULL "b" (inherited) NOT VALID -Triggers: - t_before_insert_row_trigger BEFORE INSERT ON tp_x FOR EACH ROW EXECUTE FUNCTION trigger_function('t'), ON TABLE t INSERT INTO t(i, t, b) VALUES(1, DEFAULT, 3); -NOTICE: trigger(t) called: action = INSERT, when = BEFORE, level = ROW SELECT tableoid::regclass, * FROM t ORDER BY tableoid::regclass::text COLLATE "C", b; tableoid | i | t | b | d ----------+---+--------------+---+------------ @@ -1889,6 +1895,14 @@ ALTER TABLE t SPLIT PARTITION tp_0_2 INTO (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1), PARTITION tp_1_2 FOR VALUES FROM (1) TO (2)); DROP TABLE t; +CREATE TABLE t (i int) PARTITION BY RANGE (i); +CREATE UNLOGGED TABLE tp_0_2 PARTITION OF t FOR VALUES FROM (0) TO (2); +ALTER TABLE t SPLIT PARTITION tp_0_2 INTO + (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1), + PARTITION tp_1_2 FOR VALUES FROM (1) TO (2)); -- fails +ERROR: cannot split unlogged partition "tp_0_2" +DETAIL: The new partitions are created as logged tables, which would change the persistence of the split rows. +DROP TABLE t, tp_0_2; RESET search_path; -- DROP SCHEMA partition_split_schema; diff --git a/src/test/regress/sql/partition_merge.sql b/src/test/regress/sql/partition_merge.sql index 558f5a12a86..0c3867a11f7 100644 --- a/src/test/regress/sql/partition_merge.sql +++ b/src/test/regress/sql/partition_merge.sql @@ -235,12 +235,15 @@ INSERT INTO salespeople VALUES (10, 'May'); -- 1 trigger should fire here (row): INSERT INTO salespeople10_20 VALUES (19, 'Ivanov'); -ALTER TABLE salespeople MERGE PARTITIONS (salespeople10_20, salespeople20_30, salespeople30_40) INTO salespeople10_40; +ALTER TABLE salespeople MERGE PARTITIONS (salespeople10_20, salespeople20_30, salespeople30_40) INTO salespeople10_40; -- error +DROP TRIGGER salespeople_after_insert_statement_trigger ON salespeople; +DROP TRIGGER salespeople_after_insert_row_trigger ON salespeople; +ALTER TABLE salespeople MERGE PARTITIONS (salespeople10_20, salespeople20_30, salespeople30_40) INTO salespeople10_40; -- ok --- 2 triggers should fire here (row + statement): -INSERT INTO salespeople VALUES (20, 'Smirnoff'); --- 1 trigger should fire here (row): -INSERT INTO salespeople10_40 VALUES (30, 'Ford'); +-- 2 triggers should fire here (row + statement): (not supported) +-- INSERT INTO salespeople VALUES (20, 'Smirnoff'); +-- 1 trigger should fire here (row): (not supported) +-- INSERT INTO salespeople10_40 VALUES (30, 'Ford'); SELECT * FROM salespeople01_10; SELECT * FROM salespeople10_40; @@ -456,7 +459,11 @@ CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1); CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2); CREATE INDEX tidx ON t(i); -ALTER TABLE t MERGE PARTITIONS (tp_1_2, tp_0_1) INTO tp_1_2; +CREATE INDEX tp_1_2_idx ON tp_1_2(i); + +ALTER TABLE t MERGE PARTITIONS (tp_1_2, tp_0_1) INTO tp_1_2; -- error, not supported +DROP INDEX tp_1_2_idx; +ALTER TABLE t MERGE PARTITIONS (tp_1_2, tp_0_1) INTO tp_1_2; -- ok -- Indexname values should be 'tp_1_2_pkey' and 'tp_1_2_i_idx'. \d+ tp_1_2 @@ -552,17 +559,18 @@ ORDER BY c.relname COLLATE "C"; DROP TABLE t; -- With no access method on the partitioned table, the new partition falls --- back to default_table_access_method, just as CREATE TABLE ... PARTITION OF --- would. +-- back to merging partition CREATE TABLE t (i int) PARTITION BY RANGE (i); CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1); CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2); BEGIN; SET LOCAL default_table_access_method = partitions_merge_heap; +CREATE TABLE tp_1_3 PARTITION OF t FOR VALUES FROM (2) TO (3); ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2; SELECT c.relname, a.amname FROM pg_class c JOIN pg_am a ON c.relam = a.oid WHERE c.oid = 'tp_0_2'::regclass; +ALTER TABLE t MERGE PARTITIONS (tp_0_2, tp_1_3) INTO tp_0_3; -- error COMMIT; DROP TABLE t; DROP ACCESS METHOD partitions_merge_heap; @@ -684,6 +692,7 @@ ALTER TABLE t ADD CONSTRAINT t_b_check CHECK (b > 0); ALTER TABLE t ADD CONSTRAINT t_b_check1 CHECK (b > 0) NOT ENFORCED; ALTER TABLE t ADD CONSTRAINT t_b_check2 CHECK (b > 0) NOT VALID; ALTER TABLE t ADD CONSTRAINT t_b_nn NOT NULL b NOT VALID; +ALTER TABLE tp_1_2 ADD CONSTRAINT t_b_check3 CHECK (b > 0) NOT VALID; INSERT INTO tp_0_1(i, t, b) VALUES(0, DEFAULT, 1); INSERT INTO tp_1_2(i, t, b) VALUES(1, DEFAULT, 2); @@ -695,15 +704,20 @@ BEGIN END; $BODY$; -CREATE TRIGGER t_before_insert_row_trigger BEFORE INSERT ON t FOR EACH ROW - EXECUTE PROCEDURE trigger_function('t'); -CREATE TRIGGER tp_0_1_before_insert_row_trigger BEFORE INSERT ON tp_0_1 FOR EACH ROW - EXECUTE PROCEDURE trigger_function('tp_0_1'); -CREATE TRIGGER tp_1_2_before_insert_row_trigger BEFORE INSERT ON tp_1_2 FOR EACH ROW - EXECUTE PROCEDURE trigger_function('tp_1_2'); +-- CREATE TRIGGER t_before_insert_row_trigger BEFORE INSERT ON t FOR EACH ROW +-- EXECUTE PROCEDURE trigger_function('t'); +-- CREATE TRIGGER tp_0_1_before_insert_row_trigger BEFORE INSERT ON tp_0_1 FOR EACH ROW +-- EXECUTE PROCEDURE trigger_function('tp_0_1'); +-- CREATE TRIGGER tp_1_2_before_insert_row_trigger BEFORE INSERT ON tp_1_2 FOR EACH ROW +-- EXECUTE PROCEDURE trigger_function('tp_1_2'); \d+ tp_0_1 -ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_1; +ALTER TABLE tp_0_1 ALTER COLUMN t SET DEFAULT 'default_t'; +ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_1; -- error +ALTER TABLE tp_1_2 ALTER COLUMN t SET DEFAULT 'default_t'; +ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_1; -- error +ALTER TABLE tp_1_2 DROP CONSTRAINT t_b_check3; +ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_1; -- ok \d+ tp_0_1 INSERT INTO t(i, t, b) VALUES(1, DEFAULT, 3); @@ -714,22 +728,22 @@ DROP FUNCTION trigger_function(); -- Test MERGE PARTITIONS with not valid foreign key constraint -CREATE TABLE t (i INT PRIMARY KEY) PARTITION BY RANGE (i); -CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1); -CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2); -INSERT INTO t VALUES (0), (1); -CREATE TABLE t_fk (i INT); -INSERT INTO t_fk VALUES (1), (2); -ALTER TABLE t_fk ADD CONSTRAINT t_fk_i_fkey FOREIGN KEY (i) REFERENCES t NOT VALID; -ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2; +-- CREATE TABLE t (i INT PRIMARY KEY) PARTITION BY RANGE (i); +-- CREATE TABLE tp_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1); +-- CREATE TABLE tp_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2); +-- INSERT INTO t VALUES (0), (1); +-- CREATE TABLE t_fk (i INT); +-- INSERT INTO t_fk VALUES (1), (2); +-- ALTER TABLE t_fk ADD CONSTRAINT t_fk_i_fkey FOREIGN KEY (i) REFERENCES t NOT VALID; +-- ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2; --- Should be NOT VALID FOREIGN KEY -\d tp_0_2 --- ERROR -ALTER TABLE t_fk VALIDATE CONSTRAINT t_fk_i_fkey; +-- -- Should be NOT VALID FOREIGN KEY (not supported) +-- \d tp_0_2 +-- -- ERROR +-- ALTER TABLE t_fk VALIDATE CONSTRAINT t_fk_i_fkey; -DROP TABLE t_fk; -DROP TABLE t; +-- DROP TABLE t_fk; +-- DROP TABLE t; -- Test MERGE PARTITIONS with not enforced foreign key constraint CREATE TABLE t (i INT PRIMARY KEY) PARTITION BY RANGE (i); @@ -972,6 +986,12 @@ CREATE POLICY hide_secret ON t FOR SELECT USING (secret IS NOT TRUE); ALTER TABLE t MERGE PARTITIONS (tp_0_1, tp_1_2) INTO tp_0_2; DROP TABLE t; +CREATE TABLE t (i int) PARTITION BY RANGE (i); +CREATE UNLOGGED TABLE t_0_1 PARTITION OF t FOR VALUES FROM (0) TO (1); +CREATE UNLOGGED TABLE t_1_2 PARTITION OF t FOR VALUES FROM (1) TO (2); +ALTER TABLE t MERGE PARTITIONS (t_0_1, t_1_2) INTO t_0_2; -- error +DROP TABLE t, t_0_1, t_1_2; + RESET search_path; diff --git a/src/test/regress/sql/partition_split.sql b/src/test/regress/sql/partition_split.sql index e255cff077c..015742c4f5f 100644 --- a/src/test/regress/sql/partition_split.sql +++ b/src/test/regress/sql/partition_split.sql @@ -359,7 +359,8 @@ CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHA INSERT INTO salespeople VALUES (1, 'Poirot'); CREATE TABLE sales_range ( -salesperson_id INT REFERENCES salespeople(salesperson_id), +-- salesperson_id INT REFERENCES salespeople(salesperson_id), -- (currently not supported) +salesperson_id INT, sales_amount INT CHECK (sales_amount > 1), sales_date DATE) PARTITION BY RANGE (sales_date); @@ -392,8 +393,9 @@ DROP TABLE salespeople CASCADE; -- -- Test: split partition on partitioned table in case of existing FOREIGN KEY reference from another table -- +-- (currently not supported) CREATE TABLE salespeople(salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR(30)) PARTITION BY RANGE (salesperson_id); -CREATE TABLE sales (salesperson_id INT REFERENCES salespeople(salesperson_id), sales_amount INT, sales_date DATE); +-- CREATE TABLE sales (salesperson_id INT REFERENCES salespeople(salesperson_id), sales_amount INT, sales_date DATE); CREATE TABLE salespeople01_10 PARTITION OF salespeople FOR VALUES FROM (1) TO (10); CREATE TABLE salespeople10_40 PARTITION OF salespeople FOR VALUES FROM (10) TO (40); @@ -405,15 +407,15 @@ INSERT INTO salespeople VALUES (20, 'Smirnoff'), (30, 'Ford'); -INSERT INTO sales VALUES - (1, 100, '2022-03-01'), - (1, 110, '2022-03-02'), - (10, 150, '2022-03-01'), - (10, 90, '2022-03-03'), - (19, 200, '2022-03-04'), - (20, 50, '2022-03-12'), - (20, 170, '2022-03-02'), - (30, 30, '2022-03-04'); +-- INSERT INTO sales VALUES +-- (1, 100, '2022-03-01'), +-- (1, 110, '2022-03-02'), +-- (10, 150, '2022-03-01'), +-- (10, 90, '2022-03-03'), +-- (19, 200, '2022-03-04'), +-- (20, 50, '2022-03-12'), +-- (20, 170, '2022-03-02'), +-- (30, 30, '2022-03-04'); SELECT tableoid::regclass, * FROM salespeople ORDER BY tableoid::regclass::text COLLATE "C", salesperson_id; @@ -425,11 +427,11 @@ ALTER TABLE salespeople SPLIT PARTITION salespeople10_40 INTO SELECT tableoid::regclass, * FROM salespeople ORDER BY tableoid::regclass::text COLLATE "C", salesperson_id; -- ERROR -INSERT INTO sales VALUES (40, 50, '2022-03-04'); +-- INSERT INTO sales VALUES (40, 50, '2022-03-04'); -- ok -INSERT INTO sales VALUES (30, 50, '2022-03-04'); +-- INSERT INTO sales VALUES (30, 50, '2022-03-04'); -DROP TABLE sales CASCADE; +-- DROP TABLE sales CASCADE; DROP TABLE salespeople CASCADE; -- @@ -466,15 +468,23 @@ INSERT INTO salespeople VALUES (10, 'May'); -- 1 trigger should fire here (row): INSERT INTO salespeople10_40 VALUES (19, 'Ivanov'); +-- (currently not supported) ALTER TABLE salespeople SPLIT PARTITION salespeople10_40 INTO (PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20), PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30), PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40)); +DROP TRIGGER salespeople_after_insert_statement_trigger ON salespeople; +DROP TRIGGER salespeople_after_insert_row_trigger ON salespeople; + +ALTER TABLE salespeople SPLIT PARTITION salespeople10_40 INTO + (PARTITION salespeople10_20 FOR VALUES FROM (10) TO (20), + PARTITION salespeople20_30 FOR VALUES FROM (20) TO (30), + PARTITION salespeople30_40 FOR VALUES FROM (30) TO (40)); -- ok -- 2 triggers should fire here (row + statement): -INSERT INTO salespeople VALUES (20, 'Smirnoff'); +-- INSERT INTO salespeople VALUES (20, 'Smirnoff'); -- 1 trigger should fire here (row): -INSERT INTO salespeople30_40 VALUES (30, 'Ford'); +-- INSERT INTO salespeople30_40 VALUES (30, 'Ford'); SELECT tableoid::regclass, * FROM salespeople ORDER BY tableoid::regclass::text COLLATE "C", salesperson_id; @@ -1170,7 +1180,17 @@ CREATE TRIGGER tp_x_before_insert_row_trigger BEFORE INSERT ON tp_x FOR EACH ROW \d+ tp_x ALTER TABLE t SPLIT PARTITION tp_x INTO (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1), - PARTITION tp_x FOR VALUES FROM (1) TO (2)); + PARTITION tp_x FOR VALUES FROM (1) TO (2)); -- error, triggers not supported +DROP TRIGGER t_before_insert_row_trigger ON t; +DROP TRIGGER tp_x_before_insert_row_trigger ON tp_x; +ALTER TABLE t SPLIT PARTITION tp_x INTO + (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1), + PARTITION tp_x FOR VALUES FROM (1) TO (2)); -- error, different column default not supported + +ALTER TABLE tp_x ALTER COLUMN t SET DEFAULT 'default_t'; +ALTER TABLE t SPLIT PARTITION tp_x INTO + (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1), + PARTITION tp_x FOR VALUES FROM (1) TO (2)); -- ok \d+ tp_x INSERT INTO t(i, t, b) VALUES(1, DEFAULT, 3); @@ -1374,6 +1394,13 @@ ALTER TABLE t SPLIT PARTITION tp_0_2 INTO PARTITION tp_1_2 FOR VALUES FROM (1) TO (2)); DROP TABLE t; +CREATE TABLE t (i int) PARTITION BY RANGE (i); +CREATE UNLOGGED TABLE tp_0_2 PARTITION OF t FOR VALUES FROM (0) TO (2); +ALTER TABLE t SPLIT PARTITION tp_0_2 INTO + (PARTITION tp_0_1 FOR VALUES FROM (0) TO (1), + PARTITION tp_1_2 FOR VALUES FROM (1) TO (2)); -- fails +DROP TABLE t, tp_0_2; + RESET search_path; -- -- 2.34.1