diff options
-rw-r--r-- | doc/src/sgml/ddl.sgml | 4 | ||||
-rw-r--r-- | src/backend/commands/tablecmds.c | 221 | ||||
-rw-r--r-- | src/backend/rewrite/rewriteHandler.c | 19 | ||||
-rw-r--r-- | src/test/modules/test_ddl_deparse/expected/alter_table.out | 6 | ||||
-rw-r--r-- | src/test/regress/expected/generated.out | 3 | ||||
-rw-r--r-- | src/test/regress/expected/identity.out | 199 | ||||
-rw-r--r-- | src/test/regress/sql/identity.sql | 110 |
7 files changed, 515 insertions, 47 deletions
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 4044f0908f0..fc03a349f0f 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -339,7 +339,9 @@ INSERT INTO people (id, name, address) VALUE (<emphasis>DEFAULT</emphasis>, 'C', </para> <para> - Identity columns are currently not supported for partitioned tables. + Partitions inherit identity columns from the partitioned table. They + cannot have their own identity columns. The properties of a given identity + column are consistent across all the partitions in the partition hierarchy. </para> </sect1> diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index 1f6073fb972..2a56a4357c9 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -359,7 +359,7 @@ static List *MergeAttributes(List *columns, const List *supers, char relpersiste bool is_partition, List **supconstr, List **supnotnulls); static List *MergeCheckConstraint(List *constraints, const char *name, Node *expr); -static void MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel); +static void MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel, bool ispartition); static void MergeConstraintsIntoExisting(Relation child_rel, Relation parent_rel); static void StoreCatalogInheritance(Oid relationId, List *supers, bool child_is_partition); @@ -456,10 +456,11 @@ static ObjectAddress ATExecColumnDefault(Relation rel, const char *colName, static ObjectAddress ATExecCookedColumnDefault(Relation rel, AttrNumber attnum, Node *newDefault); static ObjectAddress ATExecAddIdentity(Relation rel, const char *colName, - Node *def, LOCKMODE lockmode); + Node *def, LOCKMODE lockmode, bool recurse, bool recursing); static ObjectAddress ATExecSetIdentity(Relation rel, const char *colName, - Node *def, LOCKMODE lockmode); -static ObjectAddress ATExecDropIdentity(Relation rel, const char *colName, bool missing_ok, LOCKMODE lockmode); + Node *def, LOCKMODE lockmode, bool recurse, bool recursing); +static ObjectAddress ATExecDropIdentity(Relation rel, const char *colName, bool missing_ok, LOCKMODE lockmode, + bool recurse, bool recursing); static ObjectAddress ATExecSetExpression(AlteredTableInfo *tab, Relation rel, const char *colName, Node *newExpr, LOCKMODE lockmode); static void ATPrepDropExpression(Relation rel, AlterTableCmd *cmd, bool recurse, bool recursing, LOCKMODE lockmode); @@ -627,7 +628,7 @@ static PartitionSpec *transformPartitionSpec(Relation rel, PartitionSpec *partsp static void ComputePartitionAttrs(ParseState *pstate, Relation rel, List *partParams, AttrNumber *partattrs, List **partexprs, Oid *partopclass, Oid *partcollation, PartitionStrategy strategy); -static void CreateInheritance(Relation child_rel, Relation parent_rel); +static void CreateInheritance(Relation child_rel, Relation parent_rel, bool ispartition); static void RemoveInheritance(Relation child_rel, Relation parent_rel, bool expect_detached); static void ATInheritAdjustNotNulls(Relation parent_rel, Relation child_rel, @@ -2864,6 +2865,15 @@ MergeAttributes(List *columns, const List *supers, char relpersistence, def->is_not_null = true; def->storage = attribute->attstorage; def->generated = attribute->attgenerated; + + /* + * Regular inheritance children are independent enough not to + * inherit identity columns. But partitions are integral part + * of a partitioned table and inherit identity column. + */ + if (is_partition) + def->identity = attribute->attidentity; + if (CompressionMethodIsValid(attribute->attcompression)) def->compression = pstrdup(GetCompressionMethodName(attribute->attcompression)); @@ -4824,18 +4834,24 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, break; case AT_AddIdentity: ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE); - /* This command never recurses */ + /* Set up recursion for phase 2; no other prep needed */ + if (recurse) + cmd->recurse = true; pass = AT_PASS_ADD_OTHERCONSTR; break; case AT_SetIdentity: ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE); - /* This command never recurses */ + /* Set up recursion for phase 2; no other prep needed */ + if (recurse) + cmd->recurse = true; /* This should run after AddIdentity, so do it in MISC pass */ pass = AT_PASS_MISC; break; case AT_DropIdentity: ATSimplePermissions(cmd->subtype, rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE); - /* This command never recurses */ + /* Set up recursion for phase 2; no other prep needed */ + if (recurse) + cmd->recurse = true; pass = AT_PASS_DROP; break; case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */ @@ -5227,16 +5243,16 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, cmd = ATParseTransformCmd(wqueue, tab, rel, cmd, false, lockmode, cur_pass, context); Assert(cmd != NULL); - address = ATExecAddIdentity(rel, cmd->name, cmd->def, lockmode); + address = ATExecAddIdentity(rel, cmd->name, cmd->def, lockmode, cmd->recurse, false); break; case AT_SetIdentity: cmd = ATParseTransformCmd(wqueue, tab, rel, cmd, false, lockmode, cur_pass, context); Assert(cmd != NULL); - address = ATExecSetIdentity(rel, cmd->name, cmd->def, lockmode); + address = ATExecSetIdentity(rel, cmd->name, cmd->def, lockmode, cmd->recurse, false); break; case AT_DropIdentity: - address = ATExecDropIdentity(rel, cmd->name, cmd->missing_ok, lockmode); + address = ATExecDropIdentity(rel, cmd->name, cmd->missing_ok, lockmode, cmd->recurse, false); break; case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */ address = ATExecDropNotNull(rel, cmd->name, cmd->recurse, lockmode); @@ -7092,11 +7108,17 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, } /* - * Cannot add identity column if table has children, because identity does - * not inherit. (Adding column and identity separately will work.) + * Regular inheritance children are independent enough not to inherit the + * identity column from parent hence cannot recursively add identity + * column if the table has inheritance children. + * + * Partitions, on the other hand, are integral part of a partitioned table + * and inherit identity column. Hence propagate identity column down the + * partition hierarchy. */ if (colDef->identity && recurse && + rel->rd_rel->relkind != RELKIND_PARTITIONED_TABLE && find_inheritance_children(myrelid, NoLock) != NIL) ereport(ERROR, (errcode(ERRCODE_INVALID_TABLE_DEFINITION), @@ -8063,7 +8085,7 @@ ATExecCookedColumnDefault(Relation rel, AttrNumber attnum, */ static ObjectAddress ATExecAddIdentity(Relation rel, const char *colName, - Node *def, LOCKMODE lockmode) + Node *def, LOCKMODE lockmode, bool recurse, bool recursing) { Relation attrelation; HeapTuple tuple; @@ -8071,6 +8093,19 @@ ATExecAddIdentity(Relation rel, const char *colName, AttrNumber attnum; ObjectAddress address; ColumnDef *cdef = castNode(ColumnDef, def); + bool ispartitioned; + + ispartitioned = (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE); + if (ispartitioned && !recurse) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot add identity to a column of only the partitioned table"), + errhint("Do not specify the ONLY keyword."))); + + if (rel->rd_rel->relispartition && !recursing) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot add identity to a column of a partition")); attrelation = table_open(AttributeRelationId, RowExclusiveLock); @@ -8125,6 +8160,27 @@ ATExecAddIdentity(Relation rel, const char *colName, table_close(attrelation, RowExclusiveLock); + /* + * Recurse to propagate the identity column to partitions. Identity is + * not inherited in regular inheritance children. + */ + if (recurse && ispartitioned) + { + List *children; + ListCell *lc; + + children = find_inheritance_children(RelationGetRelid(rel), lockmode); + + foreach(lc, children) + { + Relation childrel; + + childrel = table_open(lfirst_oid(lc), NoLock); + ATExecAddIdentity(childrel, colName, def, lockmode, recurse, true); + table_close(childrel, NoLock); + } + } + return address; } @@ -8134,7 +8190,8 @@ ATExecAddIdentity(Relation rel, const char *colName, * Return the address of the affected column. */ static ObjectAddress -ATExecSetIdentity(Relation rel, const char *colName, Node *def, LOCKMODE lockmode) +ATExecSetIdentity(Relation rel, const char *colName, Node *def, + LOCKMODE lockmode, bool recurse, bool recursing) { ListCell *option; DefElem *generatedEl = NULL; @@ -8143,6 +8200,19 @@ ATExecSetIdentity(Relation rel, const char *colName, Node *def, LOCKMODE lockmod AttrNumber attnum; Relation attrelation; ObjectAddress address; + bool ispartitioned; + + ispartitioned = (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE); + if (ispartitioned && !recurse) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change identity column of only the partitioned table"), + errhint("Do not specify the ONLY keyword."))); + + if (rel->rd_rel->relispartition && !recursing) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot change identity column of a partition")); foreach(option, castNode(List, def)) { @@ -8207,6 +8277,27 @@ ATExecSetIdentity(Relation rel, const char *colName, Node *def, LOCKMODE lockmod heap_freetuple(tuple); table_close(attrelation, RowExclusiveLock); + /* + * Recurse to propagate the identity change to partitions. Identity is not + * inherited in regular inheritance children. + */ + if (generatedEl && recurse && ispartitioned) + { + List *children; + ListCell *lc; + + children = find_inheritance_children(RelationGetRelid(rel), lockmode); + + foreach(lc, children) + { + Relation childrel; + + childrel = table_open(lfirst_oid(lc), NoLock); + ATExecSetIdentity(childrel, colName, def, lockmode, recurse, true); + table_close(childrel, NoLock); + } + } + return address; } @@ -8216,7 +8307,8 @@ ATExecSetIdentity(Relation rel, const char *colName, Node *def, LOCKMODE lockmod * Return the address of the affected column. */ static ObjectAddress -ATExecDropIdentity(Relation rel, const char *colName, bool missing_ok, LOCKMODE lockmode) +ATExecDropIdentity(Relation rel, const char *colName, bool missing_ok, LOCKMODE lockmode, + bool recurse, bool recursing) { HeapTuple tuple; Form_pg_attribute attTup; @@ -8225,6 +8317,19 @@ ATExecDropIdentity(Relation rel, const char *colName, bool missing_ok, LOCKMODE ObjectAddress address; Oid seqid; ObjectAddress seqaddress; + bool ispartitioned; + + ispartitioned = (rel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE); + if (ispartitioned && !recurse) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot drop identity from a column of only the partitioned table"), + errhint("Do not specify the ONLY keyword."))); + + if (rel->rd_rel->relispartition && !recursing) + ereport(ERROR, + errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot drop identity from a column of a partition")); attrelation = table_open(AttributeRelationId, RowExclusiveLock); tuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), colName); @@ -8273,15 +8378,39 @@ ATExecDropIdentity(Relation rel, const char *colName, bool missing_ok, LOCKMODE table_close(attrelation, RowExclusiveLock); - /* drop the internal sequence */ - seqid = getIdentitySequence(RelationGetRelid(rel), attnum, false); - deleteDependencyRecordsForClass(RelationRelationId, seqid, - RelationRelationId, DEPENDENCY_INTERNAL); - CommandCounterIncrement(); - seqaddress.classId = RelationRelationId; - seqaddress.objectId = seqid; - seqaddress.objectSubId = 0; - performDeletion(&seqaddress, DROP_RESTRICT, PERFORM_DELETION_INTERNAL); + /* + * Recurse to drop the identity from column in partitions. Identity is + * not inherited in regular inheritance children so ignore them. + */ + if (recurse && ispartitioned) + { + List *children; + ListCell *lc; + + children = find_inheritance_children(RelationGetRelid(rel), lockmode); + + foreach(lc, children) + { + Relation childrel; + + childrel = table_open(lfirst_oid(lc), NoLock); + ATExecDropIdentity(childrel, colName, false, lockmode, recurse, true); + table_close(childrel, NoLock); + } + } + + if (!recursing) + { + /* drop the internal sequence */ + seqid = getIdentitySequence(RelationGetRelid(rel), attnum, false); + deleteDependencyRecordsForClass(RelationRelationId, seqid, + RelationRelationId, DEPENDENCY_INTERNAL); + CommandCounterIncrement(); + seqaddress.classId = RelationRelationId; + seqaddress.objectId = seqid; + seqaddress.objectSubId = 0; + performDeletion(&seqaddress, DROP_RESTRICT, PERFORM_DELETION_INTERNAL); + } return address; } @@ -15777,7 +15906,7 @@ ATExecAddInherit(Relation child_rel, RangeVar *parent, LOCKMODE lockmode) errdetail("ROW triggers with transition tables are not supported in inheritance hierarchies."))); /* OK to create inheritance */ - CreateInheritance(child_rel, parent_rel); + CreateInheritance(child_rel, parent_rel, false); /* * If parent_rel has a primary key, then child_rel has not-null @@ -15803,7 +15932,7 @@ ATExecAddInherit(Relation child_rel, RangeVar *parent, LOCKMODE lockmode) * Common to ATExecAddInherit() and ATExecAttachPartition(). */ static void -CreateInheritance(Relation child_rel, Relation parent_rel) +CreateInheritance(Relation child_rel, Relation parent_rel, bool ispartition) { Relation catalogRelation; SysScanDesc scan; @@ -15848,7 +15977,7 @@ CreateInheritance(Relation child_rel, Relation parent_rel) systable_endscan(scan); /* Match up the columns and bump attinhcount as needed */ - MergeAttributesIntoExisting(child_rel, parent_rel); + MergeAttributesIntoExisting(child_rel, parent_rel, ispartition); /* Match up the constraints and bump coninhcount as needed */ MergeConstraintsIntoExisting(child_rel, parent_rel); @@ -15926,7 +16055,7 @@ constraints_equivalent(HeapTuple a, HeapTuple b, TupleDesc tupleDesc) * the child must be as well. Defaults are not compared, however. */ static void -MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel) +MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel, bool ispartition) { Relation attrrel; TupleDesc parent_desc; @@ -15996,6 +16125,14 @@ MergeAttributesIntoExisting(Relation child_rel, Relation parent_rel) errmsg("column \"%s\" in child table must not be a generated column", parent_attname))); /* + * Regular inheritance children are independent enough not to + * inherit identity columns. But partitions are integral part of + * a partitioned table and inherit identity column. + */ + if (ispartition) + child_att->attidentity = parent_att->attidentity; + + /* * OK, bump the child column's inheritance count. (If we fail * later on, this change will just roll back.) */ @@ -18780,7 +18917,10 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd, (errcode(ERRCODE_WRONG_OBJECT_TYPE), errmsg("cannot attach temporary relation of another session as partition"))); - /* Check if there are any columns in attachrel that aren't in the parent */ + /* + * Check if attachrel has any identity columns or any columns that aren't + * in the parent. + */ tupleDesc = RelationGetDescr(attachrel); natts = tupleDesc->natts; for (attno = 1; attno <= natts; attno++) @@ -18792,6 +18932,13 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd, if (attribute->attisdropped) continue; + if (attribute->attidentity) + ereport(ERROR, + errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("table \"%s\" being attached contains an identity column \"%s\"", + RelationGetRelationName(attachrel), attributeName), + errdetail("The new partition may not contain an identity column.")); + /* Try to find the column in parent (matching on column name) */ if (!SearchSysCacheExists2(ATTNAME, ObjectIdGetDatum(RelationGetRelid(rel)), @@ -18826,7 +18973,7 @@ ATExecAttachPartition(List **wqueue, Relation rel, PartitionCmd *cmd, cmd->bound, pstate); /* OK to create inheritance. Rest of the checks performed there */ - CreateInheritance(attachrel, rel); + CreateInheritance(attachrel, rel, true); /* Update the pg_class entry. */ StorePartitionBound(attachrel, rel, cmd->bound); @@ -19650,6 +19797,18 @@ DetachPartitionFinalize(Relation rel, Relation partRel, bool concurrent, heap_freetuple(newtuple); table_close(classRel, RowExclusiveLock); + /* + * Drop identity property from all identity columns of partition. + */ + for (int attno = 0; attno < RelationGetNumberOfAttributes(partRel); attno++) + { + Form_pg_attribute attr = TupleDescAttr(partRel->rd_att, attno); + + if (!attr->attisdropped && attr->attidentity) + ATExecDropIdentity(partRel, NameStr(attr->attname), false, + AccessExclusiveLock, true, true); + } + if (OidIsValid(defaultPartOid)) { /* diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index 5e5c1c9e2d3..f60b34deb64 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -24,6 +24,7 @@ #include "access/sysattr.h" #include "access/table.h" #include "catalog/dependency.h" +#include "catalog/partition.h" #include "catalog/pg_type.h" #include "commands/trigger.h" #include "executor/executor.h" @@ -1234,8 +1235,24 @@ build_column_default(Relation rel, int attrno) if (att_tup->attidentity) { NextValueExpr *nve = makeNode(NextValueExpr); + Oid reloid; - nve->seqid = getIdentitySequence(RelationGetRelid(rel), attrno, false); + /* + * The identity sequence is associated with the topmost partitioned + * table. + */ + if (rel->rd_rel->relispartition) + { + List *ancestors = + get_partition_ancestors(RelationGetRelid(rel)); + + reloid = llast_oid(ancestors); + list_free(ancestors); + } + else + reloid = RelationGetRelid(rel); + + nve->seqid = getIdentitySequence(reloid, attrno, false); nve->typeId = att_tup->atttypid; return (Node *) nve; diff --git a/src/test/modules/test_ddl_deparse/expected/alter_table.out b/src/test/modules/test_ddl_deparse/expected/alter_table.out index ecde9d7422d..b5e71af9aae 100644 --- a/src/test/modules/test_ddl_deparse/expected/alter_table.out +++ b/src/test/modules/test_ddl_deparse/expected/alter_table.out @@ -74,14 +74,14 @@ ALTER TABLE parent ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; NOTICE: DDL test: type simple, tag CREATE SEQUENCE NOTICE: DDL test: type simple, tag ALTER SEQUENCE NOTICE: DDL test: type alter table, tag ALTER TABLE -NOTICE: subcommand: type ADD IDENTITY desc column a of table parent +NOTICE: subcommand: type ADD IDENTITY (and recurse) desc column a of table parent ALTER TABLE parent ALTER COLUMN a SET GENERATED BY DEFAULT; NOTICE: DDL test: type simple, tag ALTER SEQUENCE NOTICE: DDL test: type alter table, tag ALTER TABLE -NOTICE: subcommand: type SET IDENTITY desc column a of table parent +NOTICE: subcommand: type SET IDENTITY (and recurse) desc column a of table parent ALTER TABLE parent ALTER COLUMN a DROP IDENTITY; NOTICE: DDL test: type alter table, tag ALTER TABLE -NOTICE: subcommand: type DROP IDENTITY desc column a of table parent +NOTICE: subcommand: type DROP IDENTITY (and recurse) desc column a of table parent ALTER TABLE parent ALTER COLUMN a SET STATISTICS 100; NOTICE: DDL test: type alter table, tag ALTER TABLE NOTICE: subcommand: type SET STATS desc column a of table parent diff --git a/src/test/regress/expected/generated.out b/src/test/regress/expected/generated.out index cfc7678088c..a4f37736623 100644 --- a/src/test/regress/expected/generated.out +++ b/src/test/regress/expected/generated.out @@ -801,7 +801,8 @@ ERROR: column "f3" in child table must be a generated column DROP TABLE gtest_child3; CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS IDENTITY); ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01'); -- error -ERROR: column "f3" in child table must be a generated column +ERROR: table "gtest_child3" being attached contains an identity column "f3" +DETAIL: The new partition may not contain an identity column. DROP TABLE gtest_child3; CREATE TABLE gtest_child3 (f1 date NOT NULL, f2 bigint, f3 bigint GENERATED ALWAYS AS (f2 * 33) STORED); ALTER TABLE gtest_parent ATTACH PARTITION gtest_child3 FOR VALUES FROM ('2016-09-01') TO ('2016-10-01'); diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out index 7c6e87e8a5b..08f95674ca8 100644 --- a/src/test/regress/expected/identity.out +++ b/src/test/regress/expected/identity.out @@ -539,13 +539,202 @@ CREATE TYPE itest_type AS (f1 integer, f2 text, f3 bigint); CREATE TABLE itest12 OF itest_type (f1 WITH OPTIONS GENERATED ALWAYS AS IDENTITY); -- error ERROR: identity columns are not supported on typed tables DROP TYPE itest_type CASCADE; --- table partitions (currently not supported) -CREATE TABLE itest_parent (f1 date NOT NULL, f2 text, f3 bigint) PARTITION BY RANGE (f1); -CREATE TABLE itest_child PARTITION OF itest_parent ( +-- table partitions +-- partitions inherit identity column and share sequence +CREATE TABLE pitest1 (f1 date NOT NULL, f2 text, f3 bigint generated always as identity) PARTITION BY RANGE (f1); +-- new partition +CREATE TABLE pitest1_p1 PARTITION OF pitest1 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); +INSERT into pitest1(f1, f2) VALUES ('2016-07-2', 'from pitest1'); +INSERT into pitest1_p1 (f1, f2) VALUES ('2016-07-3', 'from pitest1_p1'); +-- attached partition +CREATE TABLE pitest1_p2 (f1 date NOT NULL, f2 text, f3 bigint); +INSERT INTO pitest1_p2 VALUES ('2016-08-2', 'before attaching', 100); +ALTER TABLE pitest1 ATTACH PARTITION pitest1_p2 FOR VALUES FROM ('2016-08-01') TO ('2016-09-01'); -- requires NOT NULL constraint +ERROR: column "f3" in child table must be marked NOT NULL +ALTER TABLE pitest1_p2 ALTER COLUMN f3 SET NOT NULL; +ALTER TABLE pitest1 ATTACH PARTITION pitest1_p2 FOR VALUES FROM ('2016-08-01') TO ('2016-09-01'); +INSERT INTO pitest1_p2 (f1, f2) VALUES ('2016-08-3', 'from pitest1_p2'); +INSERT INTO pitest1 (f1, f2) VALUES ('2016-08-4', 'from pitest1'); +SELECT tableoid::regclass, f1, f2, f3 FROM pitest1; + tableoid | f1 | f2 | f3 +------------+------------+------------------+----- + pitest1_p1 | 07-02-2016 | from pitest1 | 1 + pitest1_p1 | 07-03-2016 | from pitest1_p1 | 2 + pitest1_p2 | 08-02-2016 | before attaching | 100 + pitest1_p2 | 08-03-2016 | from pitest1_p2 | 3 + pitest1_p2 | 08-04-2016 | from pitest1 | 4 +(5 rows) + +-- add identity column +CREATE TABLE pitest2 (f1 date NOT NULL, f2 text) PARTITION BY RANGE (f1); +CREATE TABLE pitest2_p1 PARTITION OF pitest2 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); +CREATE TABLE pitest2_p2 PARTITION OF pitest2 FOR VALUES FROM ('2016-08-01') TO ('2016-09-01'); +INSERT into pitest2(f1, f2) VALUES ('2016-07-2', 'from pitest2'); +INSERT INTO pitest2 (f1, f2) VALUES ('2016-08-2', 'from pitest2'); +ALTER TABLE pitest2 ADD COLUMN f3 int GENERATED ALWAYS AS IDENTITY; +INSERT into pitest2_p1 (f1, f2) VALUES ('2016-07-3', 'from pitest2_p1'); +INSERT INTO pitest2_p2 (f1, f2) VALUES ('2016-08-3', 'from pitest2_p2'); +INSERT into pitest2(f1, f2) VALUES ('2016-07-4', 'from pitest2'); +INSERT INTO pitest2 (f1, f2) VALUES ('2016-08-4', 'from pitest2'); +SELECT tableoid::regclass, f1, f2, f3 FROM pitest2; + tableoid | f1 | f2 | f3 +------------+------------+-----------------+---- + pitest2_p1 | 07-02-2016 | from pitest2 | 1 + pitest2_p1 | 07-03-2016 | from pitest2_p1 | 3 + pitest2_p1 | 07-04-2016 | from pitest2 | 5 + pitest2_p2 | 08-02-2016 | from pitest2 | 2 + pitest2_p2 | 08-03-2016 | from pitest2_p2 | 4 + pitest2_p2 | 08-04-2016 | from pitest2 | 6 +(6 rows) + +-- SET identity column +ALTER TABLE pitest2_p1 ALTER COLUMN f3 SET GENERATED BY DEFAULT; -- fails +ERROR: cannot change identity column of a partition +ALTER TABLE pitest2_p1 ALTER COLUMN f3 SET INCREMENT BY 2; -- fails +ERROR: cannot change identity column of a partition +ALTER TABLE ONLY pitest2 ALTER COLUMN f3 SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 1000 RESTART; -- fails +ERROR: cannot change identity column of only the partitioned table +HINT: Do not specify the ONLY keyword. +ALTER TABLE pitest2 ALTER COLUMN f3 SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 1000 RESTART; +INSERT into pitest2(f1, f2, f3) VALUES ('2016-07-5', 'from pitest2', 200); +INSERT INTO pitest2(f1, f2) VALUES ('2016-08-5', 'from pitest2'); +INSERT into pitest2_p1 (f1, f2) VALUES ('2016-07-6', 'from pitest2_p1'); +INSERT INTO pitest2_p2 (f1, f2, f3) VALUES ('2016-08-6', 'from pitest2_p2', 300); +SELECT tableoid::regclass, f1, f2, f3 FROM pitest2; + tableoid | f1 | f2 | f3 +------------+------------+-----------------+------ + pitest2_p1 | 07-02-2016 | from pitest2 | 1 + pitest2_p1 | 07-03-2016 | from pitest2_p1 | 3 + pitest2_p1 | 07-04-2016 | from pitest2 | 5 + pitest2_p1 | 07-05-2016 | from pitest2 | 200 + pitest2_p1 | 07-06-2016 | from pitest2_p1 | 1002 + pitest2_p2 | 08-02-2016 | from pitest2 | 2 + pitest2_p2 | 08-03-2016 | from pitest2_p2 | 4 + pitest2_p2 | 08-04-2016 | from pitest2 | 6 + pitest2_p2 | 08-05-2016 | from pitest2 | 1000 + pitest2_p2 | 08-06-2016 | from pitest2_p2 | 300 +(10 rows) + +-- detaching a partition removes identity property +ALTER TABLE pitest2 DETACH PARTITION pitest2_p1; +INSERT into pitest2(f1, f2) VALUES ('2016-08-7', 'from pitest2'); +INSERT into pitest2_p1 (f1, f2) VALUES ('2016-07-7', 'from pitest2_p1'); -- error +ERROR: null value in column "f3" of relation "pitest2_p1" violates not-null constraint +DETAIL: Failing row contains (07-07-2016, from pitest2_p1, null). +INSERT into pitest2_p1 (f1, f2, f3) VALUES ('2016-07-7', 'from pitest2_p1', 2000); +SELECT tableoid::regclass, f1, f2, f3 FROM pitest2; + tableoid | f1 | f2 | f3 +------------+------------+-----------------+------ + pitest2_p2 | 08-02-2016 | from pitest2 | 2 + pitest2_p2 | 08-03-2016 | from pitest2_p2 | 4 + pitest2_p2 | 08-04-2016 | from pitest2 | 6 + pitest2_p2 | 08-05-2016 | from pitest2 | 1000 + pitest2_p2 | 08-06-2016 | from pitest2_p2 | 300 + pitest2_p2 | 08-07-2016 | from pitest2 | 1004 +(6 rows) + +SELECT tableoid::regclass, f1, f2, f3 FROM pitest2_p1; + tableoid | f1 | f2 | f3 +------------+------------+-----------------+------ + pitest2_p1 | 07-02-2016 | from pitest2 | 1 + pitest2_p1 | 07-03-2016 | from pitest2_p1 | 3 + pitest2_p1 | 07-04-2016 | from pitest2 | 5 + pitest2_p1 | 07-05-2016 | from pitest2 | 200 + pitest2_p1 | 07-06-2016 | from pitest2_p1 | 1002 + pitest2_p1 | 07-07-2016 | from pitest2_p1 | 2000 +(6 rows) + +DROP TABLE pitest2_p1; +-- changing a regular column to identity column in a partitioned table +CREATE TABLE pitest3 (f1 date NOT NULL, f2 text, f3 int) PARTITION BY RANGE (f1); +CREATE TABLE pitest3_p1 PARTITION OF pitest3 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); +INSERT into pitest3 VALUES ('2016-07-2', 'from pitest3', 1); +INSERT into pitest3_p1 VALUES ('2016-07-3', 'from pitest3_p1', 2); +-- fails, changing only a partition not allowed +ALTER TABLE pitest3_p1 + ALTER COLUMN f3 SET NOT NULL, + ALTER COLUMN f3 ADD GENERATED ALWAYS AS IDENTITY (START WITH 3); +ERROR: cannot add identity to a column of a partition +-- fails, changing only the partitioned table not allowed +ALTER TABLE ONLY pitest3 + ALTER COLUMN f3 SET NOT NULL, + ALTER COLUMN f3 ADD GENERATED ALWAYS AS IDENTITY (START WITH 3); +ERROR: constraint must be added to child tables too +HINT: Do not specify the ONLY keyword. +ALTER TABLE pitest3 + ALTER COLUMN f3 SET NOT NULL, + ALTER COLUMN f3 ADD GENERATED ALWAYS AS IDENTITY (START WITH 3); +INSERT into pitest3(f1, f2) VALUES ('2016-07-4', 'from pitest3'); +INSERT into pitest3_p1 (f1, f2) VALUES ('2016-07-5', 'from pitest3_p1'); +SELECT tableoid::regclass, f1, f2, f3 FROM pitest3; + tableoid | f1 | f2 | f3 +------------+------------+-----------------+---- + pitest3_p1 | 07-02-2016 | from pitest3 | 1 + pitest3_p1 | 07-03-2016 | from pitest3_p1 | 2 + pitest3_p1 | 07-04-2016 | from pitest3 | 3 + pitest3_p1 | 07-05-2016 | from pitest3_p1 | 4 +(4 rows) + +-- changing an identity column to a non-identity column in a partitioned table +ALTER TABLE pitest3_p1 ALTER COLUMN f3 DROP IDENTITY; -- fails +ERROR: cannot drop identity from a column of a partition +ALTER TABLE ONLY pitest3 ALTER COLUMN f3 DROP IDENTITY; -- fails +ERROR: cannot drop identity from a column of only the partitioned table +HINT: Do not specify the ONLY keyword. +ALTER TABLE pitest3 ALTER COLUMN f3 DROP IDENTITY; +INSERT into pitest3(f1, f2) VALUES ('2016-07-4', 'from pitest3'); -- fails +ERROR: null value in column "f3" of relation "pitest3_p1" violates not-null constraint +DETAIL: Failing row contains (07-04-2016, from pitest3, null). +INSERT into pitest3_p1 (f1, f2) VALUES ('2016-07-5', 'from pitest3_p1'); -- fails +ERROR: null value in column "f3" of relation "pitest3_p1" violates not-null constraint +DETAIL: Failing row contains (07-05-2016, from pitest3_p1, null). +INSERT into pitest3(f1, f2, f3) VALUES ('2016-07-6', 'from pitest3', 5); +INSERT into pitest3_p1 (f1, f2, f3) VALUES ('2016-07-7', 'from pitest3_p1', 6); +SELECT tableoid::regclass, f1, f2, f3 FROM pitest3; + tableoid | f1 | f2 | f3 +------------+------------+-----------------+---- + pitest3_p1 | 07-02-2016 | from pitest3 | 1 + pitest3_p1 | 07-03-2016 | from pitest3_p1 | 2 + pitest3_p1 | 07-04-2016 | from pitest3 | 3 + pitest3_p1 | 07-05-2016 | from pitest3_p1 | 4 + pitest3_p1 | 07-06-2016 | from pitest3 | 5 + pitest3_p1 | 07-07-2016 | from pitest3_p1 | 6 +(6 rows) + +-- Changing NOT NULL constraint of identity columns is not allowed +ALTER TABLE pitest1_p1 ALTER COLUMN f3 DROP NOT NULL; +ERROR: column "f3" of relation "pitest1_p1" is an identity column +ALTER TABLE pitest1 ALTER COLUMN f3 DROP NOT NULL; +ERROR: column "f3" of relation "pitest1" is an identity column +-- Identity columns have their own default +ALTER TABLE pitest1_p2 ALTER COLUMN f3 SET DEFAULT 10000; +ERROR: column "f3" of relation "pitest1_p2" is an identity column +ALTER TABLE pitest1 ALTER COLUMN f3 SET DEFAULT 10000; +ERROR: column "f3" of relation "pitest1" is an identity column +-- Adding identity to an identity column is not allowed +ALTER TABLE pitest1_p2 ALTER COLUMN f3 ADD GENERATED BY DEFAULT AS IDENTITY; +ERROR: cannot add identity to a column of a partition +ALTER TABLE pitest1 ALTER COLUMN f3 ADD GENERATED BY DEFAULT AS IDENTITY; +ERROR: column "f3" of relation "pitest1" is already an identity column +-- partitions with their own identity columns are not allowed, even if the +-- partitioned table does not have an identity column. +CREATE TABLE pitest1_pfail PARTITION OF pitest1 ( + f3 WITH OPTIONS GENERATED ALWAYS AS IDENTITY +) FOR VALUES FROM ('2016-11-01') TO ('2016-12-01'); +ERROR: identity columns are not supported on partitions +CREATE TABLE pitest_pfail PARTITION OF pitest3 ( f3 WITH OPTIONS GENERATED ALWAYS AS IDENTITY -) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error +) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); ERROR: identity columns are not supported on partitions -DROP TABLE itest_parent; +CREATE TABLE pitest1_pfail (f1 date NOT NULL, f2 text, f3 bigint GENERATED ALWAYS AS IDENTITY); +ALTER TABLE pitest1 ATTACH PARTITION pitest1_pfail FOR VALUES FROM ('2016-11-01') TO ('2016-12-01'); +ERROR: table "pitest1_pfail" being attached contains an identity column "f3" +DETAIL: The new partition may not contain an identity column. +ALTER TABLE pitest3 ATTACH PARTITION pitest1_pfail FOR VALUES FROM ('2016-11-01') TO ('2016-12-01'); +ERROR: table "pitest1_pfail" being attached contains an identity column "f3" +DETAIL: The new partition may not contain an identity column. +DROP TABLE pitest1_pfail; +DROP TABLE pitest3; -- test that sequence of half-dropped serial column is properly ignored CREATE TABLE itest14 (id serial); ALTER TABLE itest14 ALTER id DROP DEFAULT; diff --git a/src/test/regress/sql/identity.sql b/src/test/regress/sql/identity.sql index 9b8db2e4a30..9f35214751c 100644 --- a/src/test/regress/sql/identity.sql +++ b/src/test/regress/sql/identity.sql @@ -331,14 +331,114 @@ CREATE TABLE itest12 OF itest_type (f1 WITH OPTIONS GENERATED ALWAYS AS IDENTITY DROP TYPE itest_type CASCADE; --- table partitions (currently not supported) +-- table partitions + +-- partitions inherit identity column and share sequence +CREATE TABLE pitest1 (f1 date NOT NULL, f2 text, f3 bigint generated always as identity) PARTITION BY RANGE (f1); +-- new partition +CREATE TABLE pitest1_p1 PARTITION OF pitest1 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); +INSERT into pitest1(f1, f2) VALUES ('2016-07-2', 'from pitest1'); +INSERT into pitest1_p1 (f1, f2) VALUES ('2016-07-3', 'from pitest1_p1'); +-- attached partition +CREATE TABLE pitest1_p2 (f1 date NOT NULL, f2 text, f3 bigint); +INSERT INTO pitest1_p2 VALUES ('2016-08-2', 'before attaching', 100); +ALTER TABLE pitest1 ATTACH PARTITION pitest1_p2 FOR VALUES FROM ('2016-08-01') TO ('2016-09-01'); -- requires NOT NULL constraint +ALTER TABLE pitest1_p2 ALTER COLUMN f3 SET NOT NULL; +ALTER TABLE pitest1 ATTACH PARTITION pitest1_p2 FOR VALUES FROM ('2016-08-01') TO ('2016-09-01'); +INSERT INTO pitest1_p2 (f1, f2) VALUES ('2016-08-3', 'from pitest1_p2'); +INSERT INTO pitest1 (f1, f2) VALUES ('2016-08-4', 'from pitest1'); +SELECT tableoid::regclass, f1, f2, f3 FROM pitest1; + +-- add identity column +CREATE TABLE pitest2 (f1 date NOT NULL, f2 text) PARTITION BY RANGE (f1); +CREATE TABLE pitest2_p1 PARTITION OF pitest2 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); +CREATE TABLE pitest2_p2 PARTITION OF pitest2 FOR VALUES FROM ('2016-08-01') TO ('2016-09-01'); +INSERT into pitest2(f1, f2) VALUES ('2016-07-2', 'from pitest2'); +INSERT INTO pitest2 (f1, f2) VALUES ('2016-08-2', 'from pitest2'); +ALTER TABLE pitest2 ADD COLUMN f3 int GENERATED ALWAYS AS IDENTITY; +INSERT into pitest2_p1 (f1, f2) VALUES ('2016-07-3', 'from pitest2_p1'); +INSERT INTO pitest2_p2 (f1, f2) VALUES ('2016-08-3', 'from pitest2_p2'); +INSERT into pitest2(f1, f2) VALUES ('2016-07-4', 'from pitest2'); +INSERT INTO pitest2 (f1, f2) VALUES ('2016-08-4', 'from pitest2'); +SELECT tableoid::regclass, f1, f2, f3 FROM pitest2; + +-- SET identity column +ALTER TABLE pitest2_p1 ALTER COLUMN f3 SET GENERATED BY DEFAULT; -- fails +ALTER TABLE pitest2_p1 ALTER COLUMN f3 SET INCREMENT BY 2; -- fails +ALTER TABLE ONLY pitest2 ALTER COLUMN f3 SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 1000 RESTART; -- fails +ALTER TABLE pitest2 ALTER COLUMN f3 SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 1000 RESTART; +INSERT into pitest2(f1, f2, f3) VALUES ('2016-07-5', 'from pitest2', 200); +INSERT INTO pitest2(f1, f2) VALUES ('2016-08-5', 'from pitest2'); +INSERT into pitest2_p1 (f1, f2) VALUES ('2016-07-6', 'from pitest2_p1'); +INSERT INTO pitest2_p2 (f1, f2, f3) VALUES ('2016-08-6', 'from pitest2_p2', 300); +SELECT tableoid::regclass, f1, f2, f3 FROM pitest2; + +-- detaching a partition removes identity property +ALTER TABLE pitest2 DETACH PARTITION pitest2_p1; +INSERT into pitest2(f1, f2) VALUES ('2016-08-7', 'from pitest2'); +INSERT into pitest2_p1 (f1, f2) VALUES ('2016-07-7', 'from pitest2_p1'); -- error +INSERT into pitest2_p1 (f1, f2, f3) VALUES ('2016-07-7', 'from pitest2_p1', 2000); +SELECT tableoid::regclass, f1, f2, f3 FROM pitest2; +SELECT tableoid::regclass, f1, f2, f3 FROM pitest2_p1; + +DROP TABLE pitest2_p1; + +-- changing a regular column to identity column in a partitioned table +CREATE TABLE pitest3 (f1 date NOT NULL, f2 text, f3 int) PARTITION BY RANGE (f1); +CREATE TABLE pitest3_p1 PARTITION OF pitest3 FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); +INSERT into pitest3 VALUES ('2016-07-2', 'from pitest3', 1); +INSERT into pitest3_p1 VALUES ('2016-07-3', 'from pitest3_p1', 2); +-- fails, changing only a partition not allowed +ALTER TABLE pitest3_p1 + ALTER COLUMN f3 SET NOT NULL, + ALTER COLUMN f3 ADD GENERATED ALWAYS AS IDENTITY (START WITH 3); +-- fails, changing only the partitioned table not allowed +ALTER TABLE ONLY pitest3 + ALTER COLUMN f3 SET NOT NULL, + ALTER COLUMN f3 ADD GENERATED ALWAYS AS IDENTITY (START WITH 3); +ALTER TABLE pitest3 + ALTER COLUMN f3 SET NOT NULL, + ALTER COLUMN f3 ADD GENERATED ALWAYS AS IDENTITY (START WITH 3); +INSERT into pitest3(f1, f2) VALUES ('2016-07-4', 'from pitest3'); +INSERT into pitest3_p1 (f1, f2) VALUES ('2016-07-5', 'from pitest3_p1'); +SELECT tableoid::regclass, f1, f2, f3 FROM pitest3; + +-- changing an identity column to a non-identity column in a partitioned table +ALTER TABLE pitest3_p1 ALTER COLUMN f3 DROP IDENTITY; -- fails +ALTER TABLE ONLY pitest3 ALTER COLUMN f3 DROP IDENTITY; -- fails +ALTER TABLE pitest3 ALTER COLUMN f3 DROP IDENTITY; +INSERT into pitest3(f1, f2) VALUES ('2016-07-4', 'from pitest3'); -- fails +INSERT into pitest3_p1 (f1, f2) VALUES ('2016-07-5', 'from pitest3_p1'); -- fails +INSERT into pitest3(f1, f2, f3) VALUES ('2016-07-6', 'from pitest3', 5); +INSERT into pitest3_p1 (f1, f2, f3) VALUES ('2016-07-7', 'from pitest3_p1', 6); +SELECT tableoid::regclass, f1, f2, f3 FROM pitest3; + +-- Changing NOT NULL constraint of identity columns is not allowed +ALTER TABLE pitest1_p1 ALTER COLUMN f3 DROP NOT NULL; +ALTER TABLE pitest1 ALTER COLUMN f3 DROP NOT NULL; +-- Identity columns have their own default +ALTER TABLE pitest1_p2 ALTER COLUMN f3 SET DEFAULT 10000; +ALTER TABLE pitest1 ALTER COLUMN f3 SET DEFAULT 10000; +-- Adding identity to an identity column is not allowed +ALTER TABLE pitest1_p2 ALTER COLUMN f3 ADD GENERATED BY DEFAULT AS IDENTITY; +ALTER TABLE pitest1 ALTER COLUMN f3 ADD GENERATED BY DEFAULT AS IDENTITY; + +-- partitions with their own identity columns are not allowed, even if the +-- partitioned table does not have an identity column. +CREATE TABLE pitest1_pfail PARTITION OF pitest1 ( + f3 WITH OPTIONS GENERATED ALWAYS AS IDENTITY +) FOR VALUES FROM ('2016-11-01') TO ('2016-12-01'); -CREATE TABLE itest_parent (f1 date NOT NULL, f2 text, f3 bigint) PARTITION BY RANGE (f1); -CREATE TABLE itest_child PARTITION OF itest_parent ( +CREATE TABLE pitest_pfail PARTITION OF pitest3 ( f3 WITH OPTIONS GENERATED ALWAYS AS IDENTITY -) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); -- error -DROP TABLE itest_parent; +) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01'); + +CREATE TABLE pitest1_pfail (f1 date NOT NULL, f2 text, f3 bigint GENERATED ALWAYS AS IDENTITY); +ALTER TABLE pitest1 ATTACH PARTITION pitest1_pfail FOR VALUES FROM ('2016-11-01') TO ('2016-12-01'); +ALTER TABLE pitest3 ATTACH PARTITION pitest1_pfail FOR VALUES FROM ('2016-11-01') TO ('2016-12-01'); +DROP TABLE pitest1_pfail; +DROP TABLE pitest3; -- test that sequence of half-dropped serial column is properly ignored |