From 3217327053638085d24dd4d276e7c1f7ac2c4c6b Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Thu, 6 Apr 2017 08:33:16 -0400 Subject: [PATCH] Identity columns This is the SQL standard-conforming variant of PostgreSQL's serial columns. It fixes a few usability issues that serial columns have: - CREATE TABLE / LIKE copies default but refers to same sequence - cannot add/drop serialness with ALTER TABLE - dropping default does not drop sequence - need to grant separate privileges to sequence - other slight weirdnesses because serial is some kind of special macro Reviewed-by: Vitaly Burovoy --- doc/src/sgml/catalogs.sgml | 11 + doc/src/sgml/information_schema.sgml | 11 +- doc/src/sgml/ref/alter_table.sgml | 47 ++- doc/src/sgml/ref/copy.sgml | 7 + doc/src/sgml/ref/create_table.sgml | 65 +++- doc/src/sgml/ref/insert.sgml | 41 ++ src/backend/access/common/tupdesc.c | 6 + src/backend/catalog/dependency.c | 7 + src/backend/catalog/genbki.pl | 7 +- src/backend/catalog/heap.c | 15 +- src/backend/catalog/index.c | 1 + src/backend/catalog/information_schema.sql | 17 +- src/backend/catalog/pg_depend.c | 52 +-- src/backend/catalog/sql_features.txt | 12 +- src/backend/commands/sequence.c | 101 ++++- src/backend/commands/tablecmds.c | 295 +++++++++++++- src/backend/executor/execExpr.c | 12 + src/backend/executor/execExprInterp.c | 23 ++ src/backend/nodes/copyfuncs.c | 23 ++ src/backend/nodes/equalfuncs.c | 18 + src/backend/nodes/nodeFuncs.c | 11 + src/backend/nodes/outfuncs.c | 9 + src/backend/nodes/readfuncs.c | 1 + src/backend/parser/analyze.c | 2 + src/backend/parser/gram.y | 134 ++++++- src/backend/parser/parse_utilcmd.c | 360 ++++++++++++++---- src/backend/rewrite/rewriteHandler.c | 56 ++- src/backend/utils/adt/ruleutils.c | 8 + src/backend/utils/cache/lsyscache.c | 32 ++ src/backend/utils/cache/relcache.c | 1 + src/backend/utils/errcodes.txt | 1 + src/bin/pg_dump/pg_dump.c | 95 ++++- src/bin/pg_dump/pg_dump.h | 3 + src/bin/pg_dump/t/002_pg_dump.pl | 87 +++++ src/bin/psql/describe.c | 27 +- src/bin/psql/tab-complete.c | 18 +- src/include/catalog/catversion.h | 2 +- src/include/catalog/dependency.h | 8 +- src/include/catalog/pg_attribute.h | 24 +- src/include/catalog/pg_class.h | 2 +- src/include/commands/sequence.h | 2 + src/include/executor/execExpr.h | 8 + src/include/nodes/nodes.h | 1 + src/include/nodes/parsenodes.h | 27 +- src/include/nodes/primnodes.h | 14 + src/include/parser/kwlist.h | 2 + src/include/utils/lsyscache.h | 1 + .../regress/expected/create_table_like.out | 47 +++ src/test/regress/expected/identity.out | 322 ++++++++++++++++ src/test/regress/expected/sequence.out | 4 +- src/test/regress/expected/truncate.out | 30 ++ src/test/regress/parallel_schedule | 5 + src/test/regress/serial_schedule | 1 + src/test/regress/sql/create_table_like.sql | 14 + src/test/regress/sql/identity.sql | 192 ++++++++++ src/test/regress/sql/sequence.sql | 2 +- src/test/regress/sql/truncate.sql | 18 + 57 files changed, 2140 insertions(+), 202 deletions(-) create mode 100644 src/test/regress/expected/identity.out create mode 100644 src/test/regress/sql/identity.sql diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 5c1930c745..5883673448 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1129,6 +1129,17 @@ + + attidentity + char + + + If a zero byte (''), then not an identity column. + Otherwise, a = generated + always, d = generated by default. + + + attisdropped bool diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgml index a3a19ce8ce..02f7927436 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -1583,13 +1583,20 @@ is_identity yes_or_no - Applies to a feature not available in PostgreSQL + + If the column is an identity column, then YES, + else NO. + identity_generation character_data - Applies to a feature not available in PostgreSQL + + If the column is an identity column, then ALWAYS + or BY DEFAULT, reflecting the definition of the + column. + diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml index 7829f378bb..56ea830d41 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -46,6 +46,9 @@ ALTER TABLE [ IF EXISTS ] name ALTER [ COLUMN ] column_name SET DEFAULT expression ALTER [ COLUMN ] column_name DROP DEFAULT ALTER [ COLUMN ] column_name { SET | DROP } NOT NULL + ALTER [ COLUMN ] column_name ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] + ALTER [ COLUMN ] column_name { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_option | RESTART [ [ WITH ] restart ] } [...] + ALTER [ COLUMN ] column_name DROP IDENTITY [ IF EXISTS ] ALTER [ COLUMN ] column_name SET STATISTICS integer ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] ) ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] ) @@ -187,6 +190,38 @@ ALTER TABLE [ IF EXISTS ] name + + ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY + SET GENERATED { ALWAYS | BY DEFAULT } + DROP IDENTITY [ IF EXISTS ] + + + These forms change whether a column is an identity column or change the + generation attribute of an existing identity column. + See for details. + + + + If DROP IDENTITY IF EXISTS is specified and the + column is not an identity column, no error is thrown. In this case a + notice is issued instead. + + + + + + SET sequence_option + RESTART + + + These forms alter the sequence that underlies an existing identity + column. sequence_option is an option + supported by such + as INCREMENT BY. + + + + SET STATISTICS @@ -1160,8 +1195,11 @@ ALTER TABLE [ IF EXISTS ] name - The TRIGGER, CLUSTER, OWNER, - and TABLESPACE actions never recurse to descendant tables; + The actions for identity columns (ADD + GENERATED, SET etc., DROP + IDENTITY), as well as the actions + TRIGGER, CLUSTER, OWNER, + and TABLESPACE never recurse to descendant tables; that is, they always act as though ONLY were specified. Adding a constraint recurses only for CHECK constraints that are not marked NO INHERIT. @@ -1371,8 +1409,9 @@ ALTER TABLE cities The forms ADD (without USING INDEX), - DROP, SET DEFAULT, - and SET DATA TYPE (without USING) + DROP [COLUMN], DROP IDENTITY, RESTART, + SET DEFAULT, SET DATA TYPE (without USING), + SET GENERATED, and SET sequence_option conform with the SQL standard. The other forms are PostgreSQL extensions of the SQL standard. Also, the ability to specify more than one manipulation in a single diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgml index 7ff62f2a82..215efcd69d 100644 --- a/doc/src/sgml/ref/copy.sgml +++ b/doc/src/sgml/ref/copy.sgml @@ -479,6 +479,13 @@ COPY count constraints on the destination table. However, it will not invoke rules. + + For identity columns, the COPY FROM command will always + write the column values provided in the input data, like + the INPUT option OVERRIDING SYSTEM + VALUE. + + COPY input and output is affected by DateStyle. To ensure portability to other diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 121418b6ca..1881d9257a 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -62,6 +62,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI NULL | CHECK ( expression ) [ NO INHERIT ] | DEFAULT default_expr | + GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] | UNIQUE index_parameters | PRIMARY KEY index_parameters | REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] @@ -81,7 +82,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI and like_option is: -{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL } +{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | IDENTITY | INDEXES | STORAGE | COMMENTS | ALL } and partition_bound_spec is: @@ -412,6 +413,11 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI Column STORAGE settings are also copied from parent tables. + + If a column in the parent table is an identity column, that property is + not inherited. A column in the child table can be declared identity + column if desired. + @@ -480,6 +486,12 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI such as nextval, may create a functional linkage between the original and new tables. + + Any identity specifications of copied column definitions will only be + copied if INCLUDING IDENTITY is specified. A new + sequence is created for each identity column of the new table, separate + from the sequences associated with the old table. + Not-null constraints are always copied to the new table. CHECK constraints will be copied only if @@ -512,7 +524,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI INCLUDING ALL is an abbreviated form of - INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS. + INCLUDING DEFAULTS INCLUDING IDENTITY INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS. Note that unlike INHERITS, columns and @@ -626,6 +638,37 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI + + GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] + + + This clause creates the column as an identity + column. It will have an implicit sequence attached to it + and the column in new rows will automatically have values from the + sequence assigned to it. + + + + The clauses ALWAYS and BY DEFAULT + determine how the sequence value is given precedence over a + user-specified value in an INSERT statement. + If ALWAYS is specified, a user-specified value is + only accepted if the INSERT statement + specifies OVERRIDING SYSTEM VALUE. If BY + DEFAULT is specified, then the user-specified value takes + precedence. See for details. (In + the COPY command, user-specified values are always + used regardless of this setting.) + + + + The optional sequence_options clause can be + used to override the options of the sequence. + See for details. + + + + UNIQUE (column constraint) UNIQUE ( column_name [, ... ] ) (table constraint) @@ -1263,7 +1306,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI Using OIDs in new applications is not recommended: where - possible, using a SERIAL or other sequence + possible, using an identity column or other sequence generator as the table's primary key is preferred. However, if your application does make use of OIDs to identify specific rows of a table, it is recommended to create a unique constraint @@ -1323,7 +1366,7 @@ CREATE TABLE films ( ); CREATE TABLE distributors ( - did integer PRIMARY KEY DEFAULT nextval('serial'), + did integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, name varchar(40) NOT NULL CHECK (name <> '') ); @@ -1737,6 +1780,20 @@ CREATE TABLE cities_ab_10000_to_100000 + + Multiple Identity Columns + + + PostgreSQL allows a table to have more than one + identity column. The standard specifies that a table can have at most one + identity column. This is relaxed mainly to give more flexibility for + doing schema changes or migrations. Note that + the INSERT command supports only one override clause + that applies to the entire statement, so having multiple identity columns + with different behaviors is not well supported. + + + <literal>LIKE</> Clause diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml index 521216b5d5..95aa77b907 100644 --- a/doc/src/sgml/ref/insert.sgml +++ b/doc/src/sgml/ref/insert.sgml @@ -23,6 +23,7 @@ PostgreSQL documentation [ WITH [ RECURSIVE ] with_query [, ...] ] INSERT INTO table_name [ AS alias ] [ ( column_name [, ...] ) ] + [ OVERRIDING { SYSTEM | USER} VALUE ] { DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query } [ ON CONFLICT [ conflict_target ] conflict_action ] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] @@ -201,11 +202,44 @@ INSERT INTO table_name [ AS + + OVERRIDING SYSTEM VALUE + + + Without this clause, it is an error to specify an explicit value + (other than DEFAULT) for an identity column defined + as GENERATED ALWAYS. This clause overrides that + restriction. + + + + + + OVERRIDING USER VALUE + + + If this clause is specified, then any values supplied for identity + columns defined as GENERATED BY DEFAULT are ignored + and the default sequence-generated values are applied. + + + + This clause is useful for example when copying values between tables. + Writing INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM + tbl1 will copy from tbl1 all columns that + are not identity columns in tbl2 but will continue + the sequence counters for any identity columns. + + + + DEFAULT VALUES All columns will be filled with their default values. + (An OVERRIDING clause is not permitted in this + form.) @@ -710,6 +744,13 @@ INSERT INTO distributors (did, dname) VALUES (10, 'Conrad International') is disallowed by the standard. + + The SQL standard specifies that OVERRIDING SYSTEM VALUE + can only be specified if an identity column that is generated always + exists. PostgreSQL allows the clause in any case and ignores it if it is + not applicable. + + Possible limitations of the query clause are documented under diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c index 4e2ebe1ae7..9fd7b4e019 100644 --- a/src/backend/access/common/tupdesc.c +++ b/src/backend/access/common/tupdesc.c @@ -150,6 +150,7 @@ CreateTupleDescCopy(TupleDesc tupdesc) memcpy(desc->attrs[i], tupdesc->attrs[i], ATTRIBUTE_FIXED_PART_SIZE); desc->attrs[i]->attnotnull = false; desc->attrs[i]->atthasdef = false; + desc->attrs[i]->attidentity = '\0'; } desc->tdtypeid = tupdesc->tdtypeid; @@ -257,6 +258,7 @@ TupleDescCopyEntry(TupleDesc dst, AttrNumber dstAttno, /* since we're not copying constraints or defaults, clear these */ dst->attrs[dstAttno - 1]->attnotnull = false; dst->attrs[dstAttno - 1]->atthasdef = false; + dst->attrs[dstAttno - 1]->attidentity = '\0'; } /* @@ -401,6 +403,8 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2) return false; if (attr1->atthasdef != attr2->atthasdef) return false; + if (attr1->attidentity != attr2->attidentity) + return false; if (attr1->attisdropped != attr2->attisdropped) return false; if (attr1->attislocal != attr2->attislocal) @@ -534,6 +538,7 @@ TupleDescInitEntry(TupleDesc desc, att->attnotnull = false; att->atthasdef = false; + att->attidentity = '\0'; att->attisdropped = false; att->attislocal = true; att->attinhcount = 0; @@ -591,6 +596,7 @@ TupleDescInitBuiltinEntry(TupleDesc desc, att->attnotnull = false; att->atthasdef = false; + att->attidentity = '\0'; att->attisdropped = false; att->attislocal = true; att->attinhcount = 0; diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c index ee27cae7df..cdf453562f 100644 --- a/src/backend/catalog/dependency.c +++ b/src/backend/catalog/dependency.c @@ -1929,6 +1929,13 @@ find_expr_references_walker(Node *node, context->addrs); /* fall through to examine arguments */ } + else if (IsA(node, NextValueExpr)) + { + NextValueExpr *nve = (NextValueExpr *) node; + + add_object_address(OCLASS_CLASS, nve->seqid, 0, + context->addrs); + } return expression_tree_walker(node, find_expr_references_walker, (void *) context); diff --git a/src/backend/catalog/genbki.pl b/src/backend/catalog/genbki.pl index f9ecb02548..6e9d57aa8d 100644 --- a/src/backend/catalog/genbki.pl +++ b/src/backend/catalog/genbki.pl @@ -409,6 +409,7 @@ sub emit_pgattr_row attcacheoff => '-1', atttypmod => '-1', atthasdef => 'f', + attidentity => '', attisdropped => 'f', attislocal => 't', attinhcount => '0', @@ -424,7 +425,7 @@ sub bki_insert my $row = shift; my @attnames = @_; my $oid = $row->{oid} ? "OID = $row->{oid} " : ''; - my $bki_values = join ' ', map $row->{$_}, @attnames; + my $bki_values = join ' ', map { $_ eq '' ? '""' : $_ } map $row->{$_}, @attnames; printf $bki "insert %s( %s)\n", $oid, $bki_values; } @@ -435,10 +436,14 @@ sub emit_schemapg_row my $row = shift; my @bool_attrs = @_; + # Replace empty string by zero char constant + $row->{attidentity} ||= '\0'; + # Supply appropriate quoting for these fields. $row->{attname} = q|{"| . $row->{attname} . q|"}|; $row->{attstorage} = q|'| . $row->{attstorage} . q|'|; $row->{attalign} = q|'| . $row->{attalign} . q|'|; + $row->{attidentity} = q|'| . $row->{attidentity} . q|'|; # We don't emit initializers for the variable length fields at all. # Only the fixed-size portions of the descriptors are ever used. diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 1cbe7f907f..a264f1b9eb 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -144,37 +144,37 @@ static List *insert_ordered_unique_oid(List *list, Oid datum); static FormData_pg_attribute a1 = { 0, {"ctid"}, TIDOID, 0, sizeof(ItemPointerData), SelfItemPointerAttributeNumber, 0, -1, -1, - false, 'p', 's', true, false, false, true, 0 + false, 'p', 's', true, false, '\0', false, true, 0 }; static FormData_pg_attribute a2 = { 0, {"oid"}, OIDOID, 0, sizeof(Oid), ObjectIdAttributeNumber, 0, -1, -1, - true, 'p', 'i', true, false, false, true, 0 + true, 'p', 'i', true, false, '\0', false, true, 0 }; static FormData_pg_attribute a3 = { 0, {"xmin"}, XIDOID, 0, sizeof(TransactionId), MinTransactionIdAttributeNumber, 0, -1, -1, - true, 'p', 'i', true, false, false, true, 0 + true, 'p', 'i', true, false, '\0', false, true, 0 }; static FormData_pg_attribute a4 = { 0, {"cmin"}, CIDOID, 0, sizeof(CommandId), MinCommandIdAttributeNumber, 0, -1, -1, - true, 'p', 'i', true, false, false, true, 0 + true, 'p', 'i', true, false, '\0', false, true, 0 }; static FormData_pg_attribute a5 = { 0, {"xmax"}, XIDOID, 0, sizeof(TransactionId), MaxTransactionIdAttributeNumber, 0, -1, -1, - true, 'p', 'i', true, false, false, true, 0 + true, 'p', 'i', true, false, '\0', false, true, 0 }; static FormData_pg_attribute a6 = { 0, {"cmax"}, CIDOID, 0, sizeof(CommandId), MaxCommandIdAttributeNumber, 0, -1, -1, - true, 'p', 'i', true, false, false, true, 0 + true, 'p', 'i', true, false, '\0', false, true, 0 }; /* @@ -186,7 +186,7 @@ static FormData_pg_attribute a6 = { static FormData_pg_attribute a7 = { 0, {"tableoid"}, OIDOID, 0, sizeof(Oid), TableOidAttributeNumber, 0, -1, -1, - true, 'p', 'i', true, false, false, true, 0 + true, 'p', 'i', true, false, '\0', false, true, 0 }; static const Form_pg_attribute SysAtt[] = {&a1, &a2, &a3, &a4, &a5, &a6, &a7}; @@ -621,6 +621,7 @@ InsertPgAttributeTuple(Relation pg_attribute_rel, values[Anum_pg_attribute_attalign - 1] = CharGetDatum(new_attribute->attalign); values[Anum_pg_attribute_attnotnull - 1] = BoolGetDatum(new_attribute->attnotnull); values[Anum_pg_attribute_atthasdef - 1] = BoolGetDatum(new_attribute->atthasdef); + values[Anum_pg_attribute_attidentity - 1] = CharGetDatum(new_attribute->attidentity); values[Anum_pg_attribute_attisdropped - 1] = BoolGetDatum(new_attribute->attisdropped); values[Anum_pg_attribute_attislocal - 1] = BoolGetDatum(new_attribute->attislocal); values[Anum_pg_attribute_attinhcount - 1] = Int32GetDatum(new_attribute->attinhcount); diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 1eb163f539..2328b92b4e 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -353,6 +353,7 @@ ConstructTupleDescriptor(Relation heapRelation, to->attcacheoff = -1; to->attnotnull = false; to->atthasdef = false; + to->attidentity = '\0'; to->attislocal = true; to->attinhcount = 0; to->attcollation = collationObjectId[i]; diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql index fa2a88fc5c..2185734b48 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -728,13 +728,13 @@ CREATE VIEW columns AS CAST(a.attnum AS sql_identifier) AS dtd_identifier, CAST('NO' AS yes_or_no) AS is_self_referencing, - CAST('NO' AS yes_or_no) AS is_identity, - CAST(null AS character_data) AS identity_generation, - CAST(null AS character_data) AS identity_start, - CAST(null AS character_data) AS identity_increment, - CAST(null AS character_data) AS identity_maximum, - CAST(null AS character_data) AS identity_minimum, - CAST(null AS yes_or_no) AS identity_cycle, + CAST(CASE WHEN a.attidentity IN ('a', 'd') THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_identity, + CAST(CASE a.attidentity WHEN 'a' THEN 'ALWAYS' WHEN 'd' THEN 'BY DEFAULT' END AS character_data) AS identity_generation, + CAST(seq.seqstart AS character_data) AS identity_start, + CAST(seq.seqincrement AS character_data) AS identity_increment, + CAST(seq.seqmax AS character_data) AS identity_maximum, + CAST(seq.seqmin AS character_data) AS identity_minimum, + CAST(CASE WHEN seq.seqcycle THEN 'YES' ELSE 'NO' END AS yes_or_no) AS identity_cycle, CAST('NEVER' AS character_data) AS is_generated, CAST(null AS character_data) AS generation_expression, @@ -751,6 +751,8 @@ CREATE VIEW columns AS ON (t.typtype = 'd' AND t.typbasetype = bt.oid) LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid)) ON a.attcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default') + LEFT JOIN (pg_depend dep JOIN pg_sequence seq ON (dep.classid = 'pg_class'::regclass AND dep.objid = seq.seqrelid AND dep.deptype = 'i')) + ON (dep.refclassid = 'pg_class'::regclass AND dep.refobjid = c.oid AND dep.refobjsubid = a.attnum) WHERE (NOT pg_is_other_temp_schema(nc.oid)) @@ -1545,6 +1547,7 @@ CREATE VIEW sequences AS FROM pg_namespace nc, pg_class c, pg_sequence s WHERE c.relnamespace = nc.oid AND c.relkind = 'S' + AND NOT EXISTS (SELECT 1 FROM pg_depend WHERE classid = 'pg_class'::regclass AND objid = c.oid AND deptype = 'i') AND (NOT pg_is_other_temp_schema(nc.oid)) AND c.oid = s.seqrelid AND (pg_has_role(c.relowner, 'USAGE') diff --git a/src/backend/catalog/pg_depend.c b/src/backend/catalog/pg_depend.c index d0ee851215..aae879e355 100644 --- a/src/backend/catalog/pg_depend.c +++ b/src/backend/catalog/pg_depend.c @@ -488,7 +488,7 @@ getExtensionOfObject(Oid classId, Oid objectId) /* * Detect whether a sequence is marked as "owned" by a column * - * An ownership marker is an AUTO dependency from the sequence to the + * An ownership marker is an AUTO or INTERNAL dependency from the sequence to the * column. If we find one, store the identity of the owning column * into *tableId and *colId and return TRUE; else return FALSE. * @@ -497,7 +497,7 @@ getExtensionOfObject(Oid classId, Oid objectId) * not happen, though. */ bool -sequenceIsOwned(Oid seqId, Oid *tableId, int32 *colId) +sequenceIsOwned(Oid seqId, char deptype, Oid *tableId, int32 *colId) { bool ret = false; Relation depRel; @@ -524,7 +524,7 @@ sequenceIsOwned(Oid seqId, Oid *tableId, int32 *colId) Form_pg_depend depform = (Form_pg_depend) GETSTRUCT(tup); if (depform->refclassid == RelationRelationId && - depform->deptype == DEPENDENCY_AUTO) + depform->deptype == deptype) { *tableId = depform->refobjid; *colId = depform->refobjsubid; @@ -541,27 +541,15 @@ sequenceIsOwned(Oid seqId, Oid *tableId, int32 *colId) } /* - * Remove any existing "owned" markers for the specified sequence. - * - * Note: we don't provide a special function to install an "owned" - * marker; just use recordDependencyOn(). - */ -void -markSequenceUnowned(Oid seqId) -{ - deleteDependencyRecordsForClass(RelationRelationId, seqId, - RelationRelationId, DEPENDENCY_AUTO); -} - -/* - * Collect a list of OIDs of all sequences owned by the specified relation. + * Collect a list of OIDs of all sequences owned by the specified relation, + * and column if specified. */ List * -getOwnedSequences(Oid relid) +getOwnedSequences(Oid relid, AttrNumber attnum) { List *result = NIL; Relation depRel; - ScanKeyData key[2]; + ScanKeyData key[3]; SysScanDesc scan; HeapTuple tup; @@ -575,23 +563,28 @@ getOwnedSequences(Oid relid) Anum_pg_depend_refobjid, BTEqualStrategyNumber, F_OIDEQ, ObjectIdGetDatum(relid)); + if (attnum) + ScanKeyInit(&key[2], + Anum_pg_depend_refobjsubid, + BTEqualStrategyNumber, F_INT4EQ, + Int32GetDatum(attnum)); scan = systable_beginscan(depRel, DependReferenceIndexId, true, - NULL, 2, key); + NULL, attnum ? 3 : 2, key); while (HeapTupleIsValid(tup = systable_getnext(scan))) { Form_pg_depend deprec = (Form_pg_depend) GETSTRUCT(tup); /* - * We assume any auto dependency of a sequence on a column must be + * We assume any auto or internal dependency of a sequence on a column must be * what we are looking for. (We need the relkind test because indexes * can also have auto dependencies on columns.) */ if (deprec->classid == RelationRelationId && deprec->objsubid == 0 && deprec->refobjsubid != 0 && - deprec->deptype == DEPENDENCY_AUTO && + (deprec->deptype == DEPENDENCY_AUTO || deprec->deptype == DEPENDENCY_INTERNAL) && get_rel_relkind(deprec->objid) == RELKIND_SEQUENCE) { result = lappend_oid(result, deprec->objid); @@ -605,6 +598,21 @@ getOwnedSequences(Oid relid) return result; } +/* + * Get owned sequence, error if not exactly one. + */ +Oid +getOwnedSequence(Oid relid, AttrNumber attnum) +{ + List *seqlist = getOwnedSequences(relid, attnum); + + if (list_length(seqlist) > 1) + elog(ERROR, "more than one owned sequence found"); + else if (list_length(seqlist) < 1) + elog(ERROR, "no owned sequence found"); + else + return linitial_oid(seqlist); +} /* * get_constraint_index diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt index 8956ba9304..2821b9b702 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -200,7 +200,7 @@ F181 Multiple module support NO F191 Referential delete actions YES F200 TRUNCATE TABLE statement YES F201 CAST function YES -F202 TRUNCATE TABLE: identity column restart option NO +F202 TRUNCATE TABLE: identity column restart option YES F221 Explicit defaults YES F222 INSERT statement: DEFAULT VALUES clause YES F231 Privilege tables YES @@ -241,9 +241,9 @@ F381 Extended schema manipulation 02 ALTER TABLE statement: ADD CONSTRAINT claus F381 Extended schema manipulation 03 ALTER TABLE statement: DROP CONSTRAINT clause YES F382 Alter column data type YES F383 Set column not null clause YES -F384 Drop identity property clause NO +F384 Drop identity property clause YES F385 Drop column generation expression clause NO -F386 Set identity column generation clause NO +F386 Set identity column generation clause YES F391 Long identifiers YES F392 Unicode escapes in identifiers YES F393 Unicode escapes in literals YES @@ -420,11 +420,11 @@ T152 DISTINCT predicate with negation YES T171 LIKE clause in table definition YES T172 AS subquery clause in table definition YES T173 Extended LIKE clause in table definition YES -T174 Identity columns NO +T174 Identity columns YES T175 Generated columns NO T176 Sequence generator support NO -T177 Sequence generator support: simple restart option NO -T178 Identity columns: simple restart option NO +T177 Sequence generator support: simple restart option YES +T178 Identity columns: simple restart option YES T180 System-versioned tables NO T181 Application-time period tables NO T191 Referential action RESTRICT YES diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c index 89b810bbb7..ad28225b36 100644 --- a/src/backend/commands/sequence.c +++ b/src/backend/commands/sequence.c @@ -93,17 +93,17 @@ static HTAB *seqhashtab = NULL; /* hash table for SeqTable items */ static SeqTableData *last_used_seq = NULL; static void fill_seq_with_data(Relation rel, HeapTuple tuple); -static int64 nextval_internal(Oid relid); static Relation open_share_lock(SeqTable seq); static void create_seq_hashtable(void); static void init_sequence(Oid relid, SeqTable *p_elm, Relation *p_rel); static Form_pg_sequence_data read_seq_tuple(Relation rel, Buffer *buf, HeapTuple seqdatatuple); -static void init_params(ParseState *pstate, List *options, bool isInit, +static void init_params(ParseState *pstate, List *options, bool for_identity, + bool isInit, Form_pg_sequence seqform, Form_pg_sequence_data seqdataform, List **owned_by); static void do_setval(Oid relid, int64 next, bool iscalled); -static void process_owned_by(Relation seqrel, List *owned_by); +static void process_owned_by(Relation seqrel, List *owned_by, bool for_identity); /* @@ -153,7 +153,7 @@ DefineSequence(ParseState *pstate, CreateSeqStmt *seq) } /* Check and set all option values */ - init_params(pstate, seq->options, true, &seqform, &seqdataform, &owned_by); + init_params(pstate, seq->options, seq->for_identity, true, &seqform, &seqdataform, &owned_by); /* * Create relation (and fill value[] and null[] for the tuple) @@ -219,7 +219,7 @@ DefineSequence(ParseState *pstate, CreateSeqStmt *seq) /* process OWNED BY if given */ if (owned_by) - process_owned_by(rel, owned_by); + process_owned_by(rel, owned_by, seq->for_identity); heap_close(rel, NoLock); @@ -455,7 +455,7 @@ AlterSequence(ParseState *pstate, AlterSeqStmt *stmt) seqform = (Form_pg_sequence) GETSTRUCT(tuple); /* Check and set new values */ - init_params(pstate, stmt->options, false, seqform, &newseqdata, &owned_by); + init_params(pstate, stmt->options, stmt->for_identity, false, seqform, &newseqdata, &owned_by); /* Clear local cache so that we don't think we have cached numbers */ /* Note that we do not change the currval() state */ @@ -498,7 +498,7 @@ AlterSequence(ParseState *pstate, AlterSeqStmt *stmt) /* process OWNED BY if given */ if (owned_by) - process_owned_by(seqrel, owned_by); + process_owned_by(seqrel, owned_by, stmt->for_identity); InvokeObjectPostAlterHook(RelationRelationId, relid, 0); @@ -554,7 +554,7 @@ nextval(PG_FUNCTION_ARGS) */ relid = RangeVarGetRelid(sequence, NoLock, false); - PG_RETURN_INT64(nextval_internal(relid)); + PG_RETURN_INT64(nextval_internal(relid, true)); } Datum @@ -562,11 +562,11 @@ nextval_oid(PG_FUNCTION_ARGS) { Oid relid = PG_GETARG_OID(0); - PG_RETURN_INT64(nextval_internal(relid)); + PG_RETURN_INT64(nextval_internal(relid, true)); } -static int64 -nextval_internal(Oid relid) +int64 +nextval_internal(Oid relid, bool check_permissions) { SeqTable elm; Relation seqrel; @@ -592,7 +592,8 @@ nextval_internal(Oid relid) /* open and AccessShareLock sequence */ init_sequence(relid, &elm, &seqrel); - if (pg_class_aclcheck(elm->relid, GetUserId(), + if (check_permissions && + pg_class_aclcheck(elm->relid, GetUserId(), ACL_USAGE | ACL_UPDATE) != ACLCHECK_OK) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), @@ -1219,7 +1220,8 @@ read_seq_tuple(Relation rel, Buffer *buf, HeapTuple seqdatatuple) * otherwise, do not change existing options that aren't explicitly overridden. */ static void -init_params(ParseState *pstate, List *options, bool isInit, +init_params(ParseState *pstate, List *options, bool for_identity, + bool isInit, Form_pg_sequence seqform, Form_pg_sequence_data seqdataform, List **owned_by) { @@ -1322,6 +1324,18 @@ init_params(ParseState *pstate, List *options, bool isInit, parser_errposition(pstate, defel->location))); *owned_by = defGetQualifiedName(defel); } + else if (strcmp(defel->defname, "sequence_name") == 0) + { + /* + * The parser allows this, but it is only for identity columns, in + * which case it is filtered out in parse_utilcmd.c. We only get + * here if someone puts it into a CREATE SEQUENCE. + */ + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid sequence option SEQUENCE NAME"), + parser_errposition(pstate, defel->location))); + } else elog(ERROR, "option \"%s\" not recognized", defel->defname); @@ -1344,7 +1358,9 @@ init_params(ParseState *pstate, List *options, bool isInit, newtypid != INT8OID) ereport(ERROR, (errcode(ERRCODE_INVALID_PARAMETER_VALUE), - errmsg("sequence type must be smallint, integer, or bigint"))); + for_identity + ? errmsg("identity column type must be smallint, integer, or bigint") + : errmsg("sequence type must be smallint, integer, or bigint"))); if (!isInit) { @@ -1588,12 +1604,15 @@ init_params(ParseState *pstate, List *options, bool isInit, * as the sequence. */ static void -process_owned_by(Relation seqrel, List *owned_by) +process_owned_by(Relation seqrel, List *owned_by, bool for_identity) { + DependencyType deptype; int nnames; Relation tablerel; AttrNumber attnum; + deptype = for_identity ? DEPENDENCY_INTERNAL : DEPENDENCY_AUTO; + nnames = list_length(owned_by); Assert(nnames > 0); if (nnames == 1) @@ -1624,6 +1643,7 @@ process_owned_by(Relation seqrel, List *owned_by) /* Must be a regular or foreign table */ if (!(tablerel->rd_rel->relkind == RELKIND_RELATION || tablerel->rd_rel->relkind == RELKIND_FOREIGN_TABLE || + tablerel->rd_rel->relkind == RELKIND_VIEW || tablerel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE)) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), @@ -1650,10 +1670,28 @@ process_owned_by(Relation seqrel, List *owned_by) } /* - * OK, we are ready to update pg_depend. First remove any existing AUTO + * Catch user explicitly running OWNED BY on identity sequence. + */ + if (deptype == DEPENDENCY_AUTO) + { + Oid tableId; + int32 colId; + + if (sequenceIsOwned(RelationGetRelid(seqrel), DEPENDENCY_INTERNAL, &tableId, &colId)) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot change ownership of identity sequence"), + errdetail("Sequence \"%s\" is linked to table \"%s\".", + RelationGetRelationName(seqrel), + get_rel_name(tableId)))); + } + + /* + * OK, we are ready to update pg_depend. First remove any existing * dependencies for the sequence, then optionally add a new one. */ - markSequenceUnowned(RelationGetRelid(seqrel)); + deleteDependencyRecordsForClass(RelationRelationId, RelationGetRelid(seqrel), + RelationRelationId, deptype); if (tablerel) { @@ -1666,7 +1704,7 @@ process_owned_by(Relation seqrel, List *owned_by) depobject.classId = RelationRelationId; depobject.objectId = RelationGetRelid(seqrel); depobject.objectSubId = 0; - recordDependencyOn(&depobject, &refobject, DEPENDENCY_AUTO); + recordDependencyOn(&depobject, &refobject, deptype); } /* Done, but hold lock until commit */ @@ -1675,6 +1713,33 @@ process_owned_by(Relation seqrel, List *owned_by) } +/* + * Return sequence parameters in a list of the form created by the parser. + */ +List * +sequence_options(Oid relid) +{ + HeapTuple pgstuple; + Form_pg_sequence pgsform; + List *options = NIL; + + pgstuple = SearchSysCache1(SEQRELID, relid); + if (!HeapTupleIsValid(pgstuple)) + elog(ERROR, "cache lookup failed for sequence %u", relid); + pgsform = (Form_pg_sequence) GETSTRUCT(pgstuple); + + options = lappend(options, makeDefElem("cache", (Node *) makeInteger(pgsform->seqcache), -1)); + options = lappend(options, makeDefElem("cycle", (Node *) makeInteger(pgsform->seqcycle), -1)); + options = lappend(options, makeDefElem("increment", (Node *) makeInteger(pgsform->seqincrement), -1)); + options = lappend(options, makeDefElem("maxvalue", (Node *) makeInteger(pgsform->seqmax), -1)); + options = lappend(options, makeDefElem("minvalue", (Node *) makeInteger(pgsform->seqmin), -1)); + options = lappend(options, makeDefElem("start", (Node *) makeInteger(pgsform->seqstart), -1)); + + ReleaseSysCache(pgstuple); + + return options; +} + /* * Return sequence parameters (formerly for use by information schema) */ diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index d418d56b54..49a73707bc 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -361,6 +361,11 @@ static ObjectAddress ATExecSetNotNull(AlteredTableInfo *tab, Relation rel, const char *colName, LOCKMODE lockmode); static ObjectAddress ATExecColumnDefault(Relation rel, const char *colName, Node *newDefault, LOCKMODE lockmode); +static ObjectAddress ATExecAddIdentity(Relation rel, const char *colName, + Node *def, LOCKMODE lockmode); +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); static void ATPrepSetStatistics(Relation rel, const char *colName, Node *newValue, LOCKMODE lockmode); static ObjectAddress ATExecSetStatistics(Relation rel, const char *colName, @@ -696,6 +701,9 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId, cookedDefaults = lappend(cookedDefaults, cooked); descriptor->attrs[attnum - 1]->atthasdef = true; } + + if (colDef->identity) + descriptor->attrs[attnum - 1]->attidentity = colDef->identity; } /* @@ -1281,7 +1289,7 @@ ExecuteTruncate(TruncateStmt *stmt) foreach(cell, rels) { Relation rel = (Relation) lfirst(cell); - List *seqlist = getOwnedSequences(RelationGetRelid(rel)); + List *seqlist = getOwnedSequences(RelationGetRelid(rel), 0); ListCell *seqcell; foreach(seqcell, seqlist) @@ -2078,6 +2086,12 @@ MergeAttributes(List *schema, List *supers, char relpersistence, get_collation_name(defcollid), get_collation_name(newcollid)))); + /* + * Identity is never inherited. The new column can have an + * identity definition, so we always just take that one. + */ + def->identity = newdef->identity; + /* Copy storage parameter */ if (def->storage == 0) def->storage = newdef->storage; @@ -3217,6 +3231,9 @@ AlterTableGetLockLevel(List *cmds) case AT_DisableRowSecurity: case AT_ForceRowSecurity: case AT_NoForceRowSecurity: + case AT_AddIdentity: + case AT_DropIdentity: + case AT_SetIdentity: cmd_lockmode = AccessExclusiveLock; break; @@ -3447,6 +3464,18 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd, /* No command-specific prep needed */ pass = cmd->def ? AT_PASS_ADD_CONSTR : AT_PASS_DROP; break; + case AT_AddIdentity: + ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE); + pass = AT_PASS_ADD_CONSTR; + break; + case AT_DropIdentity: + ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE); + pass = AT_PASS_DROP; + break; + case AT_SetIdentity: + ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE); + pass = AT_PASS_COL_ATTRS; + break; case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */ ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE); ATPrepDropNotNull(rel, recurse, recursing); @@ -3772,6 +3801,15 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel, case AT_ColumnDefault: /* ALTER COLUMN DEFAULT */ address = ATExecColumnDefault(rel, cmd->name, cmd->def, lockmode); break; + case AT_AddIdentity: + address = ATExecAddIdentity(rel, cmd->name, cmd->def, lockmode); + break; + case AT_SetIdentity: + address = ATExecSetIdentity(rel, cmd->name, cmd->def, lockmode); + break; + case AT_DropIdentity: + address = ATExecDropIdentity(rel, cmd->name, cmd->missing_ok, lockmode); + break; case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */ address = ATExecDropNotNull(rel, cmd->name, lockmode); break; @@ -5120,6 +5158,17 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, elog(ERROR, "cache lookup failed for relation %u", myrelid); relkind = ((Form_pg_class) GETSTRUCT(reltup))->relkind; + /* + * Cannot add identity column if table has children, because identity does + * not inherit. (Adding column and identity separately will work.) + */ + if (colDef->identity && + recurse && + find_inheritance_children(myrelid, NoLock) != NIL) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot recursively add identity column to table that has child tables"))); + /* skip if the name already exists and if_not_exists is true */ if (!check_for_column_name_collision(rel, colDef->colname, if_not_exists)) { @@ -5172,6 +5221,7 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel, attribute.attalign = tform->typalign; attribute.attnotnull = colDef->is_not_null; attribute.atthasdef = false; + attribute.attidentity = colDef->identity; attribute.attisdropped = false; attribute.attislocal = colDef->is_local; attribute.attinhcount = colDef->inhcount; @@ -5539,6 +5589,12 @@ ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode) errmsg("cannot alter system column \"%s\"", colName))); + if (get_attidentity(RelationGetRelid(rel), attnum)) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("column \"%s\" of relation \"%s\" is an identity column", + colName, RelationGetRelationName(rel)))); + /* * Check that the attribute is not in a primary key * @@ -5755,6 +5811,13 @@ ATExecColumnDefault(Relation rel, const char *colName, errmsg("cannot alter system column \"%s\"", colName))); + if (get_attidentity(RelationGetRelid(rel), attnum)) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("column \"%s\" of relation \"%s\" is an identity column", + colName, RelationGetRelationName(rel)), + newDefault ? 0 : errhint("Use ALTER TABLE ... ALTER COLUMN ... DROP IDENTITY instead."))); + /* * Remove any old default for the column. We use RESTRICT here for * safety, but at present we do not expect anything to depend on the @@ -5789,6 +5852,224 @@ ATExecColumnDefault(Relation rel, const char *colName, return address; } +/* + * ALTER TABLE ALTER COLUMN ADD IDENTITY + * + * Return the address of the affected column. + */ +static ObjectAddress +ATExecAddIdentity(Relation rel, const char *colName, + Node *def, LOCKMODE lockmode) +{ + Relation attrelation; + HeapTuple tuple; + Form_pg_attribute attTup; + AttrNumber attnum; + ObjectAddress address; + ColumnDef *cdef = castNode(ColumnDef, def); + + attrelation = heap_open(AttributeRelationId, RowExclusiveLock); + + tuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), colName); + if (!HeapTupleIsValid(tuple)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("column \"%s\" of relation \"%s\" does not exist", + colName, RelationGetRelationName(rel)))); + attTup = (Form_pg_attribute) GETSTRUCT(tuple); + attnum = attTup->attnum; + + /* Can't alter a system attribute */ + if (attnum <= 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot alter system column \"%s\"", + colName))); + + /* + * Creating a column as identity implies NOT NULL, so adding the identity + * to an existing column that is not NOT NULL would create a state that + * cannot be reproduced without contortions. + */ + if (!attTup->attnotnull) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("column \"%s\" of relation \"%s\" must be declared NOT NULL before identity can be added", + colName, RelationGetRelationName(rel)))); + + if (attTup->attidentity) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("column \"%s\" of relation \"%s\" is already an identity column", + colName, RelationGetRelationName(rel)))); + + if (attTup->atthasdef) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("column \"%s\" of relation \"%s\" already has a default value", + colName, RelationGetRelationName(rel)))); + + attTup->attidentity = cdef->identity; + CatalogTupleUpdate(attrelation, &tuple->t_self, tuple); + + InvokeObjectPostAlterHook(RelationRelationId, + RelationGetRelid(rel), + attTup->attnum); + ObjectAddressSubSet(address, RelationRelationId, + RelationGetRelid(rel), attnum); + heap_freetuple(tuple); + + heap_close(attrelation, RowExclusiveLock); + + return address; +} + +static ObjectAddress +ATExecSetIdentity(Relation rel, const char *colName, Node *def, LOCKMODE lockmode) +{ + ListCell *option; + DefElem *generatedEl = NULL; + HeapTuple tuple; + Form_pg_attribute attTup; + AttrNumber attnum; + Relation attrelation; + ObjectAddress address; + + foreach(option, castNode(List, def)) + { + DefElem *defel = castNode(DefElem, lfirst(option)); + + if (strcmp(defel->defname, "generated") == 0) + { + if (generatedEl) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + generatedEl = defel; + } + else + elog(ERROR, "option \"%s\" not recognized", + defel->defname); + } + + /* + * Even if there is nothing to change here, we run all the checks. There + * will be a subsequent ALTER SEQUENCE that relies on everything being + * there. + */ + + attrelation = heap_open(AttributeRelationId, RowExclusiveLock); + tuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), colName); + if (!HeapTupleIsValid(tuple)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("column \"%s\" of relation \"%s\" does not exist", + colName, RelationGetRelationName(rel)))); + + attTup = (Form_pg_attribute) GETSTRUCT(tuple); + attnum = attTup->attnum; + + if (attnum <= 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot alter system column \"%s\"", + colName))); + + if (!attTup->attidentity) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("column \"%s\" of relation \"%s\" is not an identity column", + colName, RelationGetRelationName(rel)))); + + if (generatedEl) + { + attTup->attidentity = defGetInt32(generatedEl); + CatalogTupleUpdate(attrelation, &tuple->t_self, tuple); + + InvokeObjectPostAlterHook(RelationRelationId, + RelationGetRelid(rel), + attTup->attnum); + ObjectAddressSubSet(address, RelationRelationId, + RelationGetRelid(rel), attnum); + } + + heap_freetuple(tuple); + heap_close(attrelation, RowExclusiveLock); + + return address; +} + +static ObjectAddress +ATExecDropIdentity(Relation rel, const char *colName, bool missing_ok, LOCKMODE lockmode) +{ + HeapTuple tuple; + Form_pg_attribute attTup; + AttrNumber attnum; + Relation attrelation; + ObjectAddress address; + Oid seqid; + ObjectAddress seqaddress; + + attrelation = heap_open(AttributeRelationId, RowExclusiveLock); + tuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), colName); + if (!HeapTupleIsValid(tuple)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_COLUMN), + errmsg("column \"%s\" of relation \"%s\" does not exist", + colName, RelationGetRelationName(rel)))); + + attTup = (Form_pg_attribute) GETSTRUCT(tuple); + attnum = attTup->attnum; + + if (attnum <= 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot alter system column \"%s\"", + colName))); + + if (!attTup->attidentity) + { + if (!missing_ok) + ereport(ERROR, + (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE), + errmsg("column \"%s\" of relation \"%s\" is not an identity column", + colName, RelationGetRelationName(rel)))); + else + { + ereport(NOTICE, + (errmsg("column \"%s\" of relation \"%s\" is not an identity column, skipping", + colName, RelationGetRelationName(rel)))); + heap_freetuple(tuple); + heap_close(attrelation, RowExclusiveLock); + return InvalidObjectAddress; + } + } + + attTup->attidentity = '\0'; + CatalogTupleUpdate(attrelation, &tuple->t_self, tuple); + + InvokeObjectPostAlterHook(RelationRelationId, + RelationGetRelid(rel), + attTup->attnum); + ObjectAddressSubSet(address, RelationRelationId, + RelationGetRelid(rel), attnum); + heap_freetuple(tuple); + + heap_close(attrelation, RowExclusiveLock); + + /* drop the internal sequence */ + seqid = getOwnedSequence(RelationGetRelid(rel), attnum); + 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; +} + /* * ALTER TABLE ALTER COLUMN SET STATISTICS */ @@ -9539,7 +9820,8 @@ ATExecChangeOwner(Oid relationOid, Oid newOwnerId, bool recursing, LOCKMODE lock Oid tableId; int32 colId; - if (sequenceIsOwned(relationOid, &tableId, &colId)) + if (sequenceIsOwned(relationOid, DEPENDENCY_AUTO, &tableId, &colId) || + sequenceIsOwned(relationOid, DEPENDENCY_INTERNAL, &tableId, &colId)) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot change owner of sequence \"%s\"", @@ -9810,7 +10092,7 @@ change_owner_recurse_to_sequences(Oid relationOid, Oid newOwnerId, LOCKMODE lock if (depForm->refobjsubid == 0 || depForm->classid != RelationRelationId || depForm->objsubid != 0 || - depForm->deptype != DEPENDENCY_AUTO) + !(depForm->deptype == DEPENDENCY_AUTO || depForm->deptype == DEPENDENCY_INTERNAL)) continue; /* Use relation_open just in case it's an index */ @@ -12115,7 +12397,8 @@ AlterTableNamespace(AlterObjectSchemaStmt *stmt, Oid *oldschema) Oid tableId; int32 colId; - if (sequenceIsOwned(relid, &tableId, &colId)) + if (sequenceIsOwned(relid, DEPENDENCY_AUTO, &tableId, &colId) || + sequenceIsOwned(relid, DEPENDENCY_INTERNAL, &tableId, &colId)) ereport(ERROR, (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), errmsg("cannot move an owned sequence into another schema"), @@ -12298,7 +12581,7 @@ AlterIndexNamespaces(Relation classRel, Relation rel, } /* - * Move all SERIAL-column sequences of the specified relation to another + * Move all identity and SERIAL-column sequences of the specified relation to another * namespace. * * Note: we assume adequate permission checking was done by the caller, @@ -12342,7 +12625,7 @@ AlterSeqNamespaces(Relation classRel, Relation rel, if (depForm->refobjsubid == 0 || depForm->classid != RelationRelationId || depForm->objsubid != 0 || - depForm->deptype != DEPENDENCY_AUTO) + !(depForm->deptype == DEPENDENCY_AUTO || depForm->deptype == DEPENDENCY_INTERNAL)) continue; /* Use relation_open just in case it's an index */ diff --git a/src/backend/executor/execExpr.c b/src/backend/executor/execExpr.c index 5a84742d14..cd0dce150d 100644 --- a/src/backend/executor/execExpr.c +++ b/src/backend/executor/execExpr.c @@ -1993,6 +1993,18 @@ ExecInitExprRec(Expr *node, PlanState *parent, ExprState *state, break; } + case T_NextValueExpr: + { + NextValueExpr *nve = (NextValueExpr *) node; + + scratch.opcode = EEOP_NEXTVALUEEXPR; + scratch.d.nextvalueexpr.seqid = nve->seqid; + scratch.d.nextvalueexpr.seqtypid = nve->typeId; + + ExprEvalPushStep(state, &scratch); + break; + } + default: elog(ERROR, "unrecognized node type: %d", (int) nodeTag(node)); diff --git a/src/backend/executor/execExprInterp.c b/src/backend/executor/execExprInterp.c index 4fbb5c1e74..22eb81edad 100644 --- a/src/backend/executor/execExprInterp.c +++ b/src/backend/executor/execExprInterp.c @@ -60,6 +60,7 @@ #include "access/tuptoaster.h" #include "catalog/pg_type.h" +#include "commands/sequence.h" #include "executor/execExpr.h" #include "executor/nodeSubplan.h" #include "funcapi.h" @@ -337,6 +338,7 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull) &&CASE_EEOP_NULLIF, &&CASE_EEOP_SQLVALUEFUNCTION, &&CASE_EEOP_CURRENTOFEXPR, + &&CASE_EEOP_NEXTVALUEEXPR, &&CASE_EEOP_ARRAYEXPR, &&CASE_EEOP_ARRAYCOERCE, &&CASE_EEOP_ROW, @@ -1228,6 +1230,27 @@ ExecInterpExpr(ExprState *state, ExprContext *econtext, bool *isnull) EEO_NEXT(); } + EEO_CASE(EEOP_NEXTVALUEEXPR) + { + switch (op->d.nextvalueexpr.seqtypid) + { + case INT2OID: + *op->resvalue = Int16GetDatum((int16) nextval_internal(op->d.nextvalueexpr.seqid, false)); + break; + case INT4OID: + *op->resvalue = Int32GetDatum((int32) nextval_internal(op->d.nextvalueexpr.seqid, false)); + break; + case INT8OID: + *op->resvalue = Int64GetDatum((int64) nextval_internal(op->d.nextvalueexpr.seqid, false)); + break; + default: + elog(ERROR, "unsupported sequence type %u", op->d.nextvalueexpr.seqtypid); + } + *op->resnull = false; + + EEO_NEXT(); + } + EEO_CASE(EEOP_ARRAYEXPR) { /* too complex for an inline implementation */ diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 61bc5025e2..96619da8a9 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -2003,6 +2003,20 @@ _copyCurrentOfExpr(const CurrentOfExpr *from) return newnode; } + /* + * _copyNextValueExpr + */ +static NextValueExpr * +_copyNextValueExpr(const NextValueExpr *from) +{ + NextValueExpr *newnode = makeNode(NextValueExpr); + + COPY_SCALAR_FIELD(seqid); + COPY_SCALAR_FIELD(typeId); + + return newnode; +} + /* * _copyInferenceElem */ @@ -2790,6 +2804,7 @@ _copyColumnDef(const ColumnDef *from) COPY_SCALAR_FIELD(storage); COPY_NODE_FIELD(raw_default); COPY_NODE_FIELD(cooked_default); + COPY_SCALAR_FIELD(identity); COPY_NODE_FIELD(collClause); COPY_SCALAR_FIELD(collOid); COPY_NODE_FIELD(constraints); @@ -2812,6 +2827,7 @@ _copyConstraint(const Constraint *from) COPY_SCALAR_FIELD(is_no_inherit); COPY_NODE_FIELD(raw_expr); COPY_STRING_FIELD(cooked_expr); + COPY_SCALAR_FIELD(generated_when); COPY_NODE_FIELD(keys); COPY_NODE_FIELD(exclusions); COPY_NODE_FIELD(options); @@ -2920,6 +2936,7 @@ _copyQuery(const Query *from) COPY_NODE_FIELD(rtable); COPY_NODE_FIELD(jointree); COPY_NODE_FIELD(targetList); + COPY_SCALAR_FIELD(override); COPY_NODE_FIELD(onConflict); COPY_NODE_FIELD(returningList); COPY_NODE_FIELD(groupClause); @@ -2963,6 +2980,7 @@ _copyInsertStmt(const InsertStmt *from) COPY_NODE_FIELD(onConflictClause); COPY_NODE_FIELD(returningList); COPY_NODE_FIELD(withClause); + COPY_SCALAR_FIELD(override); return newnode; } @@ -3811,6 +3829,7 @@ _copyCreateSeqStmt(const CreateSeqStmt *from) COPY_NODE_FIELD(sequence); COPY_NODE_FIELD(options); COPY_SCALAR_FIELD(ownerId); + COPY_SCALAR_FIELD(for_identity); COPY_SCALAR_FIELD(if_not_exists); return newnode; @@ -3823,6 +3842,7 @@ _copyAlterSeqStmt(const AlterSeqStmt *from) COPY_NODE_FIELD(sequence); COPY_NODE_FIELD(options); + COPY_SCALAR_FIELD(for_identity); COPY_SCALAR_FIELD(missing_ok); return newnode; @@ -4927,6 +4947,9 @@ copyObjectImpl(const void *from) case T_CurrentOfExpr: retval = _copyCurrentOfExpr(from); break; + case T_NextValueExpr: + retval = _copyNextValueExpr(from); + break; case T_InferenceElem: retval = _copyInferenceElem(from); break; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 5941b7a2bf..46573ae767 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -733,6 +733,15 @@ _equalCurrentOfExpr(const CurrentOfExpr *a, const CurrentOfExpr *b) return true; } +static bool +_equalNextValueExpr(const NextValueExpr *a, const NextValueExpr *b) +{ + COMPARE_SCALAR_FIELD(seqid); + COMPARE_SCALAR_FIELD(typeId); + + return true; +} + static bool _equalInferenceElem(const InferenceElem *a, const InferenceElem *b) { @@ -963,6 +972,7 @@ _equalQuery(const Query *a, const Query *b) COMPARE_NODE_FIELD(rtable); COMPARE_NODE_FIELD(jointree); COMPARE_NODE_FIELD(targetList); + COMPARE_SCALAR_FIELD(override); COMPARE_NODE_FIELD(onConflict); COMPARE_NODE_FIELD(returningList); COMPARE_NODE_FIELD(groupClause); @@ -1002,6 +1012,7 @@ _equalInsertStmt(const InsertStmt *a, const InsertStmt *b) COMPARE_NODE_FIELD(onConflictClause); COMPARE_NODE_FIELD(returningList); COMPARE_NODE_FIELD(withClause); + COMPARE_SCALAR_FIELD(override); return true; } @@ -1713,6 +1724,7 @@ _equalCreateSeqStmt(const CreateSeqStmt *a, const CreateSeqStmt *b) COMPARE_NODE_FIELD(sequence); COMPARE_NODE_FIELD(options); COMPARE_SCALAR_FIELD(ownerId); + COMPARE_SCALAR_FIELD(for_identity); COMPARE_SCALAR_FIELD(if_not_exists); return true; @@ -1723,6 +1735,7 @@ _equalAlterSeqStmt(const AlterSeqStmt *a, const AlterSeqStmt *b) { COMPARE_NODE_FIELD(sequence); COMPARE_NODE_FIELD(options); + COMPARE_SCALAR_FIELD(for_identity); COMPARE_SCALAR_FIELD(missing_ok); return true; @@ -2530,6 +2543,7 @@ _equalColumnDef(const ColumnDef *a, const ColumnDef *b) COMPARE_SCALAR_FIELD(storage); COMPARE_NODE_FIELD(raw_default); COMPARE_NODE_FIELD(cooked_default); + COMPARE_SCALAR_FIELD(identity); COMPARE_NODE_FIELD(collClause); COMPARE_SCALAR_FIELD(collOid); COMPARE_NODE_FIELD(constraints); @@ -2550,6 +2564,7 @@ _equalConstraint(const Constraint *a, const Constraint *b) COMPARE_SCALAR_FIELD(is_no_inherit); COMPARE_NODE_FIELD(raw_expr); COMPARE_STRING_FIELD(cooked_expr); + COMPARE_SCALAR_FIELD(generated_when); COMPARE_NODE_FIELD(keys); COMPARE_NODE_FIELD(exclusions); COMPARE_NODE_FIELD(options); @@ -3099,6 +3114,9 @@ equal(const void *a, const void *b) case T_CurrentOfExpr: retval = _equalCurrentOfExpr(a, b); break; + case T_NextValueExpr: + retval = _equalNextValueExpr(a, b); + break; case T_InferenceElem: retval = _equalInferenceElem(a, b); break; diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index d5293a1a78..4149e9fd1c 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -246,6 +246,9 @@ exprType(const Node *expr) case T_CurrentOfExpr: type = BOOLOID; break; + case T_NextValueExpr: + type = ((const NextValueExpr *) expr)->typeId; + break; case T_InferenceElem: { const InferenceElem *n = (const InferenceElem *) expr; @@ -919,6 +922,9 @@ exprCollation(const Node *expr) case T_CurrentOfExpr: coll = InvalidOid; /* result is always boolean */ break; + case T_NextValueExpr: + coll = InvalidOid; /* result is always an integer type */ + break; case T_InferenceElem: coll = exprCollation((Node *) ((const InferenceElem *) expr)->expr); break; @@ -1122,6 +1128,9 @@ exprSetCollation(Node *expr, Oid collation) case T_CurrentOfExpr: Assert(!OidIsValid(collation)); /* result is always boolean */ break; + case T_NextValueExpr: + Assert(!OidIsValid(collation)); /* result is always an integer type */ + break; default: elog(ERROR, "unrecognized node type: %d", (int) nodeTag(expr)); break; @@ -1881,6 +1890,7 @@ expression_tree_walker(Node *node, case T_CaseTestExpr: case T_SetToDefault: case T_CurrentOfExpr: + case T_NextValueExpr: case T_SQLValueFunction: case T_RangeTblRef: case T_SortGroupClause: @@ -2476,6 +2486,7 @@ expression_tree_mutator(Node *node, case T_CaseTestExpr: case T_SetToDefault: case T_CurrentOfExpr: + case T_NextValueExpr: case T_SQLValueFunction: case T_RangeTblRef: case T_SortGroupClause: diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 83fb39fe18..13672979b4 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -2763,6 +2763,7 @@ _outColumnDef(StringInfo str, const ColumnDef *node) WRITE_CHAR_FIELD(storage); WRITE_NODE_FIELD(raw_default); WRITE_NODE_FIELD(cooked_default); + WRITE_CHAR_FIELD(identity); WRITE_NODE_FIELD(collClause); WRITE_OID_FIELD(collOid); WRITE_NODE_FIELD(constraints); @@ -2868,6 +2869,7 @@ _outQuery(StringInfo str, const Query *node) WRITE_NODE_FIELD(rtable); WRITE_NODE_FIELD(jointree); WRITE_NODE_FIELD(targetList); + WRITE_ENUM_FIELD(override, OverridingKind); WRITE_NODE_FIELD(onConflict); WRITE_NODE_FIELD(returningList); WRITE_NODE_FIELD(groupClause); @@ -3405,6 +3407,13 @@ _outConstraint(StringInfo str, const Constraint *node) WRITE_STRING_FIELD(cooked_expr); break; + case CONSTR_IDENTITY: + appendStringInfoString(str, "IDENTITY"); + WRITE_NODE_FIELD(raw_expr); + WRITE_STRING_FIELD(cooked_expr); + WRITE_CHAR_FIELD(generated_when); + break; + case CONSTR_CHECK: appendStringInfoString(str, "CHECK"); WRITE_BOOL_FIELD(is_no_inherit); diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index 766f2d8db1..e027154e67 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -247,6 +247,7 @@ _readQuery(void) READ_NODE_FIELD(rtable); READ_NODE_FIELD(jointree); READ_NODE_FIELD(targetList); + READ_ENUM_FIELD(override, OverridingKind); READ_NODE_FIELD(onConflict); READ_NODE_FIELD(returningList); READ_NODE_FIELD(groupClause); diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c index 811fccaec9..c4140a65d2 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -487,6 +487,8 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt) qry->hasModifyingCTE = pstate->p_hasModifyingCTE; } + qry->override = stmt->override; + isOnConflictUpdate = (stmt->onConflictClause && stmt->onConflictClause->action == ONCONFLICT_UPDATE); diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 5ecb6997b3..29ca5f13ea 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -292,6 +292,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); %type alter_table_cmd alter_type_cmd opt_collate_clause replica_identity partition_cmd %type alter_table_cmds alter_type_cmds +%type alter_identity_column_option_list +%type alter_identity_column_option %type opt_drop_behavior @@ -449,7 +451,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); select_offset_value2 opt_select_fetch_first_value %type row_or_rows first_or_next -%type OptSeqOptList SeqOptList +%type OptSeqOptList SeqOptList OptParenthesizedSeqOptList %type SeqOptElem %type insert_rest @@ -569,6 +571,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); opt_frame_clause frame_extent frame_bound %type opt_existing_window_name %type opt_if_not_exists +%type generated_when override_kind %type PartitionSpec OptPartitionSpec %type part_strategy %type part_elem @@ -631,7 +634,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); FALSE_P FAMILY FETCH FILTER FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD FREEZE FROM FULL FUNCTION FUNCTIONS - GLOBAL GRANT GRANTED GREATEST GROUP_P GROUPING + GENERATED GLOBAL GRANT GRANTED GREATEST GROUP_P GROUPING HANDLER HAVING HEADER_P HOLD HOUR_P @@ -655,7 +658,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); NULLS_P NUMERIC OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OPTIONS OR - ORDER ORDINALITY OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER + ORDER ORDINALITY OUT_P OUTER_P OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POLICY POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY @@ -726,6 +729,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); * same as if they weren't keywords). We need to do this for PARTITION, * RANGE, ROWS to support opt_existing_window_name; and for RANGE, ROWS * so that they can follow a_expr without creating postfix-operator problems; + * for GENERATED so that it can follow b_expr; * and for NULL so that it can follow b_expr in ColQualList without creating * postfix-operator problems. * @@ -744,7 +748,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query); * blame any funny behavior of UNBOUNDED on the SQL standard, though. */ %nonassoc UNBOUNDED /* ideally should have same precedence as IDENT */ -%nonassoc IDENT NULL_P PARTITION RANGE ROWS PRECEDING FOLLOWING CUBE ROLLUP +%nonassoc IDENT GENERATED NULL_P PARTITION RANGE ROWS PRECEDING FOLLOWING CUBE ROLLUP %left Op OPERATOR /* multi-character ops and user-defined operators */ %left '+' '-' %left '*' '/' '%' @@ -2128,6 +2132,50 @@ alter_table_cmd: n->def = (Node *) makeString($6); $$ = (Node *)n; } + /* ALTER TABLE ALTER [COLUMN] ADD GENERATED ... AS IDENTITY ... */ + | ALTER opt_column ColId ADD_P GENERATED generated_when AS IDENTITY_P OptParenthesizedSeqOptList + { + AlterTableCmd *n = makeNode(AlterTableCmd); + Constraint *c = makeNode(Constraint); + + c->contype = CONSTR_IDENTITY; + c->generated_when = $6; + c->options = $9; + c->location = @5; + + n->subtype = AT_AddIdentity; + n->name = $3; + n->def = (Node *) c; + + $$ = (Node *)n; + } + /* ALTER TABLE ALTER [COLUMN] SET /RESET */ + | ALTER opt_column ColId alter_identity_column_option_list + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_SetIdentity; + n->name = $3; + n->def = (Node *) $4; + $$ = (Node *)n; + } + /* ALTER TABLE ALTER [COLUMN] DROP IDENTITY */ + | ALTER opt_column ColId DROP IDENTITY_P + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_DropIdentity; + n->name = $3; + n->missing_ok = false; + $$ = (Node *)n; + } + /* ALTER TABLE ALTER [COLUMN] DROP IDENTITY IF EXISTS */ + | ALTER opt_column ColId DROP IDENTITY_P IF_P EXISTS + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_DropIdentity; + n->name = $3; + n->missing_ok = true; + $$ = (Node *)n; + } /* ALTER TABLE DROP [COLUMN] IF EXISTS [RESTRICT|CASCADE] */ | DROP opt_column IF_P EXISTS ColId opt_drop_behavior { @@ -2565,6 +2613,39 @@ reloption_elem: } ; +alter_identity_column_option_list: + alter_identity_column_option + { $$ = list_make1($1); } + | alter_identity_column_option_list alter_identity_column_option + { $$ = lappend($1, $2); } + ; + +alter_identity_column_option: + RESTART + { + $$ = makeDefElem("restart", NULL, @1); + } + | RESTART opt_with NumericOnly + { + $$ = makeDefElem("restart", (Node *)$3, @1); + } + | SET SeqOptElem + { + if (strcmp($2->defname, "as") == 0 || + strcmp($2->defname, "restart") == 0 || + strcmp($2->defname, "owned_by") == 0) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("sequence option \"%s\" not supported here", $2->defname), + parser_errposition(@2))); + $$ = $2; + } + | SET GENERATED generated_when + { + $$ = makeDefElem("generated", (Node *) makeInteger($3), @1); + } + ; + ForValues: /* a LIST partition */ FOR VALUES IN_P '(' partbound_datum_list ')' @@ -3347,6 +3428,15 @@ ColConstraintElem: n->cooked_expr = NULL; $$ = (Node *)n; } + | GENERATED generated_when AS IDENTITY_P OptParenthesizedSeqOptList + { + Constraint *n = makeNode(Constraint); + n->contype = CONSTR_IDENTITY; + n->generated_when = $2; + n->options = $5; + n->location = @1; + $$ = (Node *)n; + } | REFERENCES qualified_name opt_column_list key_match key_actions { Constraint *n = makeNode(Constraint); @@ -3364,6 +3454,11 @@ ColConstraintElem: } ; +generated_when: + ALWAYS { $$ = ATTRIBUTE_IDENTITY_ALWAYS; } + | BY DEFAULT { $$ = ATTRIBUTE_IDENTITY_BY_DEFAULT; } + ; + /* * ConstraintAttr represents constraint attributes, which we parse as if * they were independent constraint clauses, in order to avoid shift/reduce @@ -3430,6 +3525,7 @@ TableLikeOptionList: TableLikeOption: DEFAULTS { $$ = CREATE_TABLE_LIKE_DEFAULTS; } | CONSTRAINTS { $$ = CREATE_TABLE_LIKE_CONSTRAINTS; } + | IDENTITY_P { $$ = CREATE_TABLE_LIKE_IDENTITY; } | INDEXES { $$ = CREATE_TABLE_LIKE_INDEXES; } | STORAGE { $$ = CREATE_TABLE_LIKE_STORAGE; } | COMMENTS { $$ = CREATE_TABLE_LIKE_COMMENTS; } @@ -3967,6 +4063,10 @@ OptSeqOptList: SeqOptList { $$ = $1; } | /*EMPTY*/ { $$ = NIL; } ; +OptParenthesizedSeqOptList: '(' SeqOptList ')' { $$ = $2; } + | /*EMPTY*/ { $$ = NIL; } + ; + SeqOptList: SeqOptElem { $$ = list_make1($1); } | SeqOptList SeqOptElem { $$ = lappend($1, $2); } ; @@ -4011,6 +4111,11 @@ SeqOptElem: AS SimpleTypename { $$ = makeDefElem("owned_by", (Node *)$3, @1); } + | SEQUENCE NAME_P any_name + { + /* not documented, only used by pg_dump */ + $$ = makeDefElem("sequence_name", (Node *)$3, @1); + } | START opt_with NumericOnly { $$ = makeDefElem("start", (Node *)$3, @1); @@ -10412,12 +10517,26 @@ insert_rest: $$->cols = NIL; $$->selectStmt = $1; } + | OVERRIDING override_kind VALUE_P SelectStmt + { + $$ = makeNode(InsertStmt); + $$->cols = NIL; + $$->override = $2; + $$->selectStmt = $4; + } | '(' insert_column_list ')' SelectStmt { $$ = makeNode(InsertStmt); $$->cols = $2; $$->selectStmt = $4; } + | '(' insert_column_list ')' OVERRIDING override_kind VALUE_P SelectStmt + { + $$ = makeNode(InsertStmt); + $$->cols = $2; + $$->override = $5; + $$->selectStmt = $7; + } | DEFAULT VALUES { $$ = makeNode(InsertStmt); @@ -10426,6 +10545,11 @@ insert_rest: } ; +override_kind: + USER { $$ = OVERRIDING_USER_VALUE; } + | SYSTEM_P { $$ = OVERRIDING_SYSTEM_VALUE; } + ; + insert_column_list: insert_column_item { $$ = list_make1($1); } @@ -14597,6 +14721,7 @@ unreserved_keyword: | FORWARD | FUNCTION | FUNCTIONS + | GENERATED | GLOBAL | GRANTED | HANDLER @@ -14666,6 +14791,7 @@ unreserved_keyword: | OPTIONS | ORDINALITY | OVER + | OVERRIDING | OWNED | OWNER | PARALLEL diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c index 1ae43dc25d..926699608b 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -42,6 +42,7 @@ #include "catalog/pg_type.h" #include "commands/comment.h" #include "commands/defrem.h" +#include "commands/sequence.h" #include "commands/tablecmds.h" #include "commands/tablespace.h" #include "miscadmin.h" @@ -356,6 +357,132 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString) return result; } +static void +generateSerialExtraStmts(CreateStmtContext *cxt, ColumnDef *column, + Oid seqtypid, List *seqoptions, bool for_identity, + char **snamespace_p, char **sname_p) +{ + ListCell *option; + DefElem *nameEl = NULL; + Oid snamespaceid; + char *snamespace; + char *sname; + CreateSeqStmt *seqstmt; + AlterSeqStmt *altseqstmt; + List *attnamelist; + + /* + * Determine namespace and name to use for the sequence. + * + * First, check if a sequence name was passed in as an option. This is + * used by pg_dump. Else, generate a name. + * + * Although we use ChooseRelationName, it's not guaranteed that the + * selected sequence name won't conflict; given sufficiently long + * field names, two different serial columns in the same table could + * be assigned the same sequence name, and we'd not notice since we + * aren't creating the sequence quite yet. In practice this seems + * quite unlikely to be a problem, especially since few people would + * need two serial columns in one table. + */ + + foreach(option, seqoptions) + { + DefElem *defel = castNode(DefElem, lfirst(option)); + + if (strcmp(defel->defname, "sequence_name") == 0) + { + if (nameEl) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + nameEl = defel; + } + } + + if (nameEl) + { + RangeVar *rv = makeRangeVarFromNameList(castNode(List, nameEl->arg)); + snamespace = rv->schemaname; + sname = rv->relname; + seqoptions = list_delete_ptr(seqoptions, nameEl); + } + else + { + if (cxt->rel) + snamespaceid = RelationGetNamespace(cxt->rel); + else + { + snamespaceid = RangeVarGetCreationNamespace(cxt->relation); + RangeVarAdjustRelationPersistence(cxt->relation, snamespaceid); + } + snamespace = get_namespace_name(snamespaceid); + sname = ChooseRelationName(cxt->relation->relname, + column->colname, + "seq", + snamespaceid); + } + + ereport(DEBUG1, + (errmsg("%s will create implicit sequence \"%s\" for serial column \"%s.%s\"", + cxt->stmtType, sname, + cxt->relation->relname, column->colname))); + + /* + * Build a CREATE SEQUENCE command to create the sequence object, and + * add it to the list of things to be done before this CREATE/ALTER + * TABLE. + */ + seqstmt = makeNode(CreateSeqStmt); + seqstmt->for_identity = for_identity; + seqstmt->sequence = makeRangeVar(snamespace, sname, -1); + seqstmt->options = seqoptions; + /* + * If a sequence data type was specified, add it to the options. Prepend + * to the list rather than append; in case a user supplied their own AS + * clause, the "redundant options" error will point to their occurrence, + * not our synthetic one. + */ + if (seqtypid) + seqstmt->options = lcons(makeDefElem("as", (Node *) makeTypeNameFromOid(seqtypid, -1), -1), + seqstmt->options); + + /* + * If this is ALTER ADD COLUMN, make sure the sequence will be owned + * by the table's owner. The current user might be someone else + * (perhaps a superuser, or someone who's only a member of the owning + * role), but the SEQUENCE OWNED BY mechanisms will bleat unless table + * and sequence have exactly the same owning role. + */ + if (cxt->rel) + seqstmt->ownerId = cxt->rel->rd_rel->relowner; + else + seqstmt->ownerId = InvalidOid; + + cxt->blist = lappend(cxt->blist, seqstmt); + + /* + * Build an ALTER SEQUENCE ... OWNED BY command to mark the sequence + * as owned by this column, and add it to the list of things to be + * done after this CREATE/ALTER TABLE. + */ + altseqstmt = makeNode(AlterSeqStmt); + altseqstmt->sequence = makeRangeVar(snamespace, sname, -1); + attnamelist = list_make3(makeString(snamespace), + makeString(cxt->relation->relname), + makeString(column->colname)); + altseqstmt->options = list_make1(makeDefElem("owned_by", + (Node *) attnamelist, -1)); + altseqstmt->for_identity = for_identity; + + cxt->alist = lappend(cxt->alist, altseqstmt); + + if (snamespace_p) + *snamespace_p = snamespace; + if (sname_p) + *sname_p = sname; +} + /* * transformColumnDefinition - * transform a single ColumnDef within CREATE TABLE @@ -367,7 +494,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) bool is_serial; bool saw_nullable; bool saw_default; - Constraint *constraint; + bool saw_identity; ListCell *clist; cxt->columns = lappend(cxt->columns, column); @@ -422,83 +549,17 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) /* Special actions for SERIAL pseudo-types */ if (is_serial) { - Oid snamespaceid; char *snamespace; char *sname; char *qstring; A_Const *snamenode; TypeCast *castnode; FuncCall *funccallnode; - CreateSeqStmt *seqstmt; - AlterSeqStmt *altseqstmt; - List *attnamelist; + Constraint *constraint; - /* - * Determine namespace and name to use for the sequence. - * - * Although we use ChooseRelationName, it's not guaranteed that the - * selected sequence name won't conflict; given sufficiently long - * field names, two different serial columns in the same table could - * be assigned the same sequence name, and we'd not notice since we - * aren't creating the sequence quite yet. In practice this seems - * quite unlikely to be a problem, especially since few people would - * need two serial columns in one table. - */ - if (cxt->rel) - snamespaceid = RelationGetNamespace(cxt->rel); - else - { - snamespaceid = RangeVarGetCreationNamespace(cxt->relation); - RangeVarAdjustRelationPersistence(cxt->relation, snamespaceid); - } - snamespace = get_namespace_name(snamespaceid); - sname = ChooseRelationName(cxt->relation->relname, - column->colname, - "seq", - snamespaceid); - - ereport(DEBUG1, - (errmsg("%s will create implicit sequence \"%s\" for serial column \"%s.%s\"", - cxt->stmtType, sname, - cxt->relation->relname, column->colname))); - - /* - * Build a CREATE SEQUENCE command to create the sequence object, and - * add it to the list of things to be done before this CREATE/ALTER - * TABLE. - */ - seqstmt = makeNode(CreateSeqStmt); - seqstmt->sequence = makeRangeVar(snamespace, sname, -1); - seqstmt->options = list_make1(makeDefElem("as", (Node *) makeTypeNameFromOid(column->typeName->typeOid, -1), -1)); - - /* - * If this is ALTER ADD COLUMN, make sure the sequence will be owned - * by the table's owner. The current user might be someone else - * (perhaps a superuser, or someone who's only a member of the owning - * role), but the SEQUENCE OWNED BY mechanisms will bleat unless table - * and sequence have exactly the same owning role. - */ - if (cxt->rel) - seqstmt->ownerId = cxt->rel->rd_rel->relowner; - else - seqstmt->ownerId = InvalidOid; - - cxt->blist = lappend(cxt->blist, seqstmt); - - /* - * Build an ALTER SEQUENCE ... OWNED BY command to mark the sequence - * as owned by this column, and add it to the list of things to be - * done after this CREATE/ALTER TABLE. - */ - altseqstmt = makeNode(AlterSeqStmt); - altseqstmt->sequence = makeRangeVar(snamespace, sname, -1); - attnamelist = list_make3(makeString(snamespace), - makeString(cxt->relation->relname), - makeString(column->colname)); - altseqstmt->options = list_make1(makeDefElem("owned_by", - (Node *) attnamelist, -1)); - - cxt->alist = lappend(cxt->alist, altseqstmt); + generateSerialExtraStmts(cxt, column, + column->typeName->typeOid, NIL, false, + &snamespace, &sname); /* * Create appropriate constraints for SERIAL. We do this in full, @@ -540,10 +601,11 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) saw_nullable = false; saw_default = false; + saw_identity = false; foreach(clist, column->constraints) { - constraint = castNode(Constraint, lfirst(clist)); + Constraint *constraint = castNode(Constraint, lfirst(clist)); switch (constraint->contype) { @@ -584,6 +646,33 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) saw_default = true; break; + case CONSTR_IDENTITY: + { + Type ctype; + Oid typeOid; + + ctype = typenameType(cxt->pstate, column->typeName, NULL); + typeOid = HeapTupleGetOid(ctype); + ReleaseSysCache(ctype); + + if (saw_identity) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("multiple identity specifications for column \"%s\" of table \"%s\"", + column->colname, cxt->relation->relname), + parser_errposition(cxt->pstate, + constraint->location))); + + generateSerialExtraStmts(cxt, column, + typeOid, constraint->options, true, + NULL, NULL); + + column->identity = constraint->generated_when; + saw_identity = true; + column->is_not_null = TRUE; + break; + } + case CONSTR_CHECK: cxt->ckconstraints = lappend(cxt->ckconstraints, constraint); break; @@ -660,6 +749,14 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column) constraint->contype); break; } + + if (saw_default && saw_identity) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("both default and identity specified for column \"%s\" of table \"%s\"", + column->colname, cxt->relation->relname), + parser_errposition(cxt->pstate, + constraint->location))); } /* @@ -932,6 +1029,27 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla def->cooked_default = this_default; } + /* + * Copy identity if requested + */ + if (attribute->attidentity && + (table_like_clause->options & CREATE_TABLE_LIKE_IDENTITY)) + { + Oid seq_relid; + List *seq_options; + + /* + * find sequence owned by old column; extract sequence parameters; + * build new create sequence command + */ + seq_relid = getOwnedSequence(RelationGetRelid(relation), attribute->attnum); + seq_options = sequence_options(seq_relid); + generateSerialExtraStmts(cxt, def, + InvalidOid, seq_options, true, + NULL, NULL); + def->identity = attribute->attidentity; + } + /* Likewise, copy storage if requested */ if (table_like_clause->options & CREATE_TABLE_LIKE_STORAGE) def->storage = attribute->attstorage; @@ -2628,6 +2746,7 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt, case AT_AlterColumnType: { ColumnDef *def = (ColumnDef *) cmd->def; + AttrNumber attnum; /* * For ALTER COLUMN TYPE, transform the USING clause if @@ -2640,6 +2759,103 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt, EXPR_KIND_ALTER_COL_TRANSFORM); } + /* + * For identity column, create ALTER SEQUENCE command to + * change the data type of the sequence. + */ + attnum = get_attnum(relid, cmd->name); + /* if attribute not found, something will error about it later */ + if (attnum != InvalidAttrNumber && get_attidentity(relid, attnum)) + { + Oid seq_relid = getOwnedSequence(relid, attnum); + Oid typeOid = typenameTypeId(pstate, def->typeName); + AlterSeqStmt *altseqstmt = makeNode(AlterSeqStmt); + + altseqstmt->sequence = makeRangeVar(get_namespace_name(get_rel_namespace(seq_relid)), + get_rel_name(seq_relid), + -1); + altseqstmt->options = list_make1(makeDefElem("as", (Node *) makeTypeNameFromOid(typeOid, -1), -1)); + altseqstmt->for_identity = true; + cxt.blist = lappend(cxt.blist, altseqstmt); + } + + newcmds = lappend(newcmds, cmd); + break; + } + + case AT_AddIdentity: + { + Constraint *def = castNode(Constraint, cmd->def); + ColumnDef *newdef = makeNode(ColumnDef); + AttrNumber attnum; + + newdef->colname = cmd->name; + newdef->identity = def->generated_when; + cmd->def = (Node *) newdef; + + attnum = get_attnum(relid, cmd->name); + /* if attribute not found, something will error about it later */ + if (attnum != InvalidAttrNumber) + generateSerialExtraStmts(&cxt, newdef, + get_atttype(relid, attnum), + def->options, true, + NULL, NULL); + + newcmds = lappend(newcmds, cmd); + break; + } + + case AT_SetIdentity: + { + /* + * Create an ALTER SEQUENCE statement for the internal + * sequence of the identity column. + */ + ListCell *lc; + List *newseqopts = NIL; + List *newdef = NIL; + List *seqlist; + AttrNumber attnum; + + /* + * Split options into those handled by ALTER SEQUENCE and + * those for ALTER TABLE proper. + */ + foreach(lc, castNode(List, cmd->def)) + { + DefElem *def = castNode(DefElem, lfirst(lc)); + + if (strcmp(def->defname, "generated") == 0) + newdef = lappend(newdef, def); + else + newseqopts = lappend(newseqopts, def); + } + + attnum = get_attnum(relid, cmd->name); + + if (attnum) + { + seqlist = getOwnedSequences(relid, attnum); + if (seqlist) + { + AlterSeqStmt *seqstmt; + Oid seq_relid; + + seqstmt = makeNode(AlterSeqStmt); + seq_relid = linitial_oid(seqlist); + seqstmt->sequence = makeRangeVar(get_namespace_name(get_rel_namespace(seq_relid)), + get_rel_name(seq_relid), -1); + seqstmt->options = newseqopts; + seqstmt->for_identity = true; + seqstmt->missing_ok = false; + + cxt.alist = lappend(cxt.alist, seqstmt); + } + } + /* If column was not found or was not an identity column, we + * just let the ALTER TABLE command error out later. */ + + cmd->def = (Node *) newdef; newcmds = lappend(newcmds, cmd); break; } diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c index 424be0c768..cb860ec4e5 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -21,6 +21,7 @@ #include "postgres.h" #include "access/sysattr.h" +#include "catalog/dependency.h" #include "catalog/pg_type.h" #include "commands/trigger.h" #include "foreign/fdwapi.h" @@ -61,6 +62,7 @@ static Query *rewriteRuleAction(Query *parsetree, static List *adjustJoinTreeList(Query *parsetree, bool removert, int rt_index); static List *rewriteTargetListIU(List *targetList, CmdType commandType, + OverridingKind override, Relation target_relation, int result_rti, List **attrno_list); @@ -709,6 +711,7 @@ adjustJoinTreeList(Query *parsetree, bool removert, int rt_index) static List * rewriteTargetListIU(List *targetList, CmdType commandType, + OverridingKind override, Relation target_relation, int result_rti, List **attrno_list) @@ -789,6 +792,7 @@ rewriteTargetListIU(List *targetList, for (attrno = 1; attrno <= numattrs; attrno++) { TargetEntry *new_tle = new_tles[attrno - 1]; + bool apply_default; att_tup = target_relation->rd_att->attrs[attrno - 1]; @@ -801,12 +805,51 @@ rewriteTargetListIU(List *targetList, * it's an INSERT and there's no tlist entry for the column, or the * tlist entry is a DEFAULT placeholder node. */ - if ((new_tle == NULL && commandType == CMD_INSERT) || - (new_tle && new_tle->expr && IsA(new_tle->expr, SetToDefault))) + apply_default = ((new_tle == NULL && commandType == CMD_INSERT) || + (new_tle && new_tle->expr && IsA(new_tle->expr, SetToDefault))); + + if (commandType == CMD_INSERT) + { + if (att_tup->attidentity == ATTRIBUTE_IDENTITY_ALWAYS && !apply_default) + { + if (override != OVERRIDING_SYSTEM_VALUE) + ereport(ERROR, + (errcode(ERRCODE_GENERATED_ALWAYS), + errmsg("cannot insert into column \"%s\"", NameStr(att_tup->attname)), + errdetail("Column \"%s\" is an identity column defined as GENERATED ALWAYS.", + NameStr(att_tup->attname)), + errhint("Use OVERRIDING SYSTEM VALUE to override."))); + } + + if (att_tup->attidentity == ATTRIBUTE_IDENTITY_BY_DEFAULT && override == OVERRIDING_USER_VALUE) + apply_default = true; + } + + if (commandType == CMD_UPDATE) + { + if (att_tup->attidentity == ATTRIBUTE_IDENTITY_ALWAYS && !apply_default) + ereport(ERROR, + (errcode(ERRCODE_GENERATED_ALWAYS), + errmsg("column \"%s\" can only be updated to DEFAULT", NameStr(att_tup->attname)), + errdetail("Column \"%s\" is an identity column defined as GENERATED ALWAYS.", + NameStr(att_tup->attname)))); + } + + if (apply_default) { Node *new_expr; - new_expr = build_column_default(target_relation, attrno); + if (att_tup->attidentity) + { + NextValueExpr *nve = makeNode(NextValueExpr); + + nve->seqid = getOwnedSequence(RelationGetRelid(target_relation), attrno); + nve->typeId = att_tup->atttypid; + + new_expr = (Node *) nve; + } + else + new_expr = build_column_default(target_relation, attrno); /* * If there is no default (ie, default is effectively NULL), we @@ -3232,6 +3275,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events) /* Process the main targetlist ... */ parsetree->targetList = rewriteTargetListIU(parsetree->targetList, parsetree->commandType, + parsetree->override, rt_entry_relation, parsetree->resultRelation, &attrnos); @@ -3244,6 +3288,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events) parsetree->targetList = rewriteTargetListIU(parsetree->targetList, parsetree->commandType, + parsetree->override, rt_entry_relation, parsetree->resultRelation, NULL); } @@ -3254,6 +3299,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events) parsetree->onConflict->onConflictSet = rewriteTargetListIU(parsetree->onConflict->onConflictSet, CMD_UPDATE, + parsetree->override, rt_entry_relation, parsetree->resultRelation, NULL); @@ -3263,7 +3309,9 @@ RewriteQuery(Query *parsetree, List *rewrite_events) { parsetree->targetList = rewriteTargetListIU(parsetree->targetList, - parsetree->commandType, rt_entry_relation, + parsetree->commandType, + parsetree->override, + rt_entry_relation, parsetree->resultRelation, NULL); rewriteTargetListUD(parsetree, rt_entry, rt_entry_relation); } diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 342e52bcf7..241b81a48f 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -5936,6 +5936,14 @@ get_insert_query_def(Query *query, deparse_context *context) if (query->targetList) appendStringInfoString(buf, ") "); + if (query->override) + { + if (query->override == OVERRIDING_SYSTEM_VALUE) + appendStringInfoString(buf, "OVERRIDING SYSTEM VALUE "); + else if (query->override == OVERRIDING_USER_VALUE) + appendStringInfoString(buf, "OVERRIDING USER VALUE "); + } + if (select_rte) { /* Add the SELECT */ diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c index b891f388e5..0667ef5a81 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -836,6 +836,38 @@ get_attnum(Oid relid, const char *attname) return InvalidAttrNumber; } +/* + * get_attidentity + * + * Given the relation id and the attribute name, + * return the "attidentity" field from the attribute relation. + * + * Returns '\0' if not found. + * + * Since no identity is represented by '\0', this can also be used as a + * Boolean test. + */ +char +get_attidentity(Oid relid, AttrNumber attnum) +{ + HeapTuple tp; + + tp = SearchSysCache2(ATTNUM, + ObjectIdGetDatum(relid), + Int16GetDatum(attnum)); + if (HeapTupleIsValid(tp)) + { + Form_pg_attribute att_tup = (Form_pg_attribute) GETSTRUCT(tp); + char result; + + result = att_tup->attidentity; + ReleaseSysCache(tp); + return result; + } + else + return '\0'; +} + /* * get_atttype * diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c index bc220989a1..24ffea8f40 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -3268,6 +3268,7 @@ RelationBuildLocalRelation(const char *relname, has_not_null = false; for (i = 0; i < natts; i++) { + rel->rd_att->attrs[i]->attidentity = tupDesc->attrs[i]->attidentity; rel->rd_att->attrs[i]->attnotnull = tupDesc->attrs[i]->attnotnull; has_not_null |= tupDesc->attrs[i]->attnotnull; } diff --git a/src/backend/utils/errcodes.txt b/src/backend/utils/errcodes.txt index b6e0e987a8..4f35471762 100644 --- a/src/backend/utils/errcodes.txt +++ b/src/backend/utils/errcodes.txt @@ -327,6 +327,7 @@ Section: Class 42 - Syntax Error or Access Rule Violation 42P21 E ERRCODE_COLLATION_MISMATCH collation_mismatch 42P22 E ERRCODE_INDETERMINATE_COLLATION indeterminate_collation 42809 E ERRCODE_WRONG_OBJECT_TYPE wrong_object_type +428C9 E ERRCODE_GENERATED_ALWAYS generated_always # Note: for ERRCODE purposes, we divide namable objects into these categories: # databases, schemas, prepared statements, cursors, tables, columns, diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c index 262f5539bc..65a2f2307a 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -43,6 +43,7 @@ #include "access/sysattr.h" #include "access/transam.h" #include "catalog/pg_am.h" +#include "catalog/pg_attribute.h" #include "catalog/pg_cast.h" #include "catalog/pg_class.h" #include "catalog/pg_default_acl.h" @@ -1925,6 +1926,9 @@ dumpTableData_insert(Archive *fout, void *dcontext) appendPQExpBufferStr(insertStmt, ") "); } + if (tbinfo->needs_override) + appendPQExpBufferStr(insertStmt, "OVERRIDING SYSTEM VALUE "); + appendPQExpBufferStr(insertStmt, "VALUES ("); } } @@ -5451,6 +5455,7 @@ getTables(Archive *fout, int *numTables) int i_toastreloptions; int i_reloftype; int i_relpages; + int i_is_identity_sequence; int i_changed_acl; /* Make sure we are in proper schema */ @@ -5528,6 +5533,7 @@ getTables(Archive *fout, int *numTables) "CASE WHEN 'check_option=local' = ANY (c.reloptions) THEN 'LOCAL'::text " "WHEN 'check_option=cascaded' = ANY (c.reloptions) THEN 'CASCADED'::text ELSE NULL END AS checkoption, " "tc.reloptions AS toast_reloptions, " + "c.relkind = '%c' AND EXISTS (SELECT 1 FROM pg_depend WHERE classid = 'pg_class'::regclass AND objid = c.oid AND objsubid = 0 AND refclassid = 'pg_class'::regclass AND deptype = 'i') AS is_identity_sequence, " "EXISTS (SELECT 1 FROM pg_attribute at LEFT JOIN pg_init_privs pip ON " "(c.oid = pip.objoid " "AND pip.classoid = 'pg_class'::regclass " @@ -5544,7 +5550,7 @@ getTables(Archive *fout, int *numTables) "(c.relkind = '%c' AND " "d.classid = c.tableoid AND d.objid = c.oid AND " "d.objsubid = 0 AND " - "d.refclassid = c.tableoid AND d.deptype = 'a') " + "d.refclassid = c.tableoid AND d.deptype IN ('a', 'i')) " "LEFT JOIN pg_class tc ON (c.reltoastrelid = tc.oid) " "LEFT JOIN pg_init_privs pip ON " "(c.oid = pip.objoid " @@ -5557,6 +5563,7 @@ getTables(Archive *fout, int *numTables) initacl_subquery->data, initracl_subquery->data, username_subquery, + RELKIND_SEQUENCE, attacl_subquery->data, attracl_subquery->data, attinitacl_subquery->data, @@ -5979,6 +5986,7 @@ getTables(Archive *fout, int *numTables) i_checkoption = PQfnumber(res, "checkoption"); i_toastreloptions = PQfnumber(res, "toast_reloptions"); i_reloftype = PQfnumber(res, "reloftype"); + i_is_identity_sequence = PQfnumber(res, "is_identity_sequence"); i_changed_acl = PQfnumber(res, "changed_acl"); if (dopt->lockWaitTimeout) @@ -6079,6 +6087,9 @@ getTables(Archive *fout, int *numTables) tblinfo[i].dummy_view = false; /* might get set during sort */ tblinfo[i].postponed_def = false; /* might get set during sort */ + tblinfo[i].is_identity_sequence = (i_is_identity_sequence >= 0 && + strcmp(PQgetvalue(res, i, i_is_identity_sequence), "t") == 0); + /* * Read-lock target tables to make sure they aren't DROPPED or altered * in schema before we get around to dumping them. @@ -7735,6 +7746,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) int i_typstorage; int i_attnotnull; int i_atthasdef; + int i_attidentity; int i_attisdropped; int i_attlen; int i_attalign; @@ -7777,7 +7789,34 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) resetPQExpBuffer(q); - if (fout->remoteVersion >= 90200) + if (fout->remoteVersion >= 100000) + { + /* + * attidentity is new in version 10. + */ + appendPQExpBuffer(q, "SELECT a.attnum, a.attname, a.atttypmod, " + "a.attstattarget, a.attstorage, t.typstorage, " + "a.attnotnull, a.atthasdef, a.attisdropped, " + "a.attlen, a.attalign, a.attislocal, " + "pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname, " + "array_to_string(a.attoptions, ', ') AS attoptions, " + "CASE WHEN a.attcollation <> t.typcollation " + "THEN a.attcollation ELSE 0 END AS attcollation, " + "a.attidentity, " + "pg_catalog.array_to_string(ARRAY(" + "SELECT pg_catalog.quote_ident(option_name) || " + "' ' || pg_catalog.quote_literal(option_value) " + "FROM pg_catalog.pg_options_to_table(attfdwoptions) " + "ORDER BY option_name" + "), E',\n ') AS attfdwoptions " + "FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t " + "ON a.atttypid = t.oid " + "WHERE a.attrelid = '%u'::pg_catalog.oid " + "AND a.attnum > 0::pg_catalog.int2 " + "ORDER BY a.attnum", + tbinfo->dobj.catId.oid); + } + else if (fout->remoteVersion >= 90200) { /* * attfdwoptions is new in 9.2. @@ -7876,6 +7915,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) i_typstorage = PQfnumber(res, "typstorage"); i_attnotnull = PQfnumber(res, "attnotnull"); i_atthasdef = PQfnumber(res, "atthasdef"); + i_attidentity = PQfnumber(res, "attidentity"); i_attisdropped = PQfnumber(res, "attisdropped"); i_attlen = PQfnumber(res, "attlen"); i_attalign = PQfnumber(res, "attalign"); @@ -7891,6 +7931,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) tbinfo->attstattarget = (int *) pg_malloc(ntups * sizeof(int)); tbinfo->attstorage = (char *) pg_malloc(ntups * sizeof(char)); tbinfo->typstorage = (char *) pg_malloc(ntups * sizeof(char)); + tbinfo->attidentity = (char *) pg_malloc(ntups * sizeof(bool)); tbinfo->attisdropped = (bool *) pg_malloc(ntups * sizeof(bool)); tbinfo->attlen = (int *) pg_malloc(ntups * sizeof(int)); tbinfo->attalign = (char *) pg_malloc(ntups * sizeof(char)); @@ -7915,6 +7956,8 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables) tbinfo->attstattarget[j] = atoi(PQgetvalue(res, j, i_attstattarget)); tbinfo->attstorage[j] = *(PQgetvalue(res, j, i_attstorage)); tbinfo->typstorage[j] = *(PQgetvalue(res, j, i_typstorage)); + tbinfo->attidentity[j] = (i_attidentity >= 0 ? *(PQgetvalue(res, j, i_attidentity)) : '\0'); + tbinfo->needs_override = tbinfo->needs_override || (tbinfo->attidentity[j] == ATTRIBUTE_IDENTITY_ALWAYS); tbinfo->attisdropped[j] = (PQgetvalue(res, j, i_attisdropped)[0] == 't'); tbinfo->attlen[j] = atoi(PQgetvalue(res, j, i_attlen)); tbinfo->attalign[j] = *(PQgetvalue(res, j, i_attalign)); @@ -16307,10 +16350,13 @@ dumpSequence(Archive *fout, TableInfo *tbinfo) /* * DROP must be fully qualified in case same name appears in pg_catalog */ - appendPQExpBuffer(delqry, "DROP SEQUENCE %s.", - fmtId(tbinfo->dobj.namespace->dobj.name)); - appendPQExpBuffer(delqry, "%s;\n", - fmtId(tbinfo->dobj.name)); + if (!tbinfo->is_identity_sequence) + { + appendPQExpBuffer(delqry, "DROP SEQUENCE %s.", + fmtId(tbinfo->dobj.namespace->dobj.name)); + appendPQExpBuffer(delqry, "%s;\n", + fmtId(tbinfo->dobj.name)); + } resetPQExpBuffer(query); @@ -16322,12 +16368,32 @@ dumpSequence(Archive *fout, TableInfo *tbinfo) tbinfo->dobj.catId.oid); } - appendPQExpBuffer(query, - "CREATE SEQUENCE %s\n", - fmtId(tbinfo->dobj.name)); + if (tbinfo->is_identity_sequence) + { + TableInfo *owning_tab = findTableByOid(tbinfo->owning_tab); - if (strcmp(seqtype, "bigint") != 0) - appendPQExpBuffer(query, " AS %s\n", seqtype); + appendPQExpBuffer(query, + "ALTER TABLE %s ", + fmtId(owning_tab->dobj.name)); + appendPQExpBuffer(query, + "ALTER COLUMN %s ADD GENERATED ", + fmtId(owning_tab->attnames[tbinfo->owning_col - 1])); + if (owning_tab->attidentity[tbinfo->owning_col - 1] == ATTRIBUTE_IDENTITY_ALWAYS) + appendPQExpBuffer(query, "ALWAYS"); + else if (owning_tab->attidentity[tbinfo->owning_col - 1] == ATTRIBUTE_IDENTITY_BY_DEFAULT) + appendPQExpBuffer(query, "BY DEFAULT"); + appendPQExpBuffer(query, " AS IDENTITY (\n SEQUENCE NAME %s\n", + fmtId(tbinfo->dobj.name)); + } + else + { + appendPQExpBuffer(query, + "CREATE SEQUENCE %s\n", + fmtId(tbinfo->dobj.name)); + + if (strcmp(seqtype, "bigint") != 0) + appendPQExpBuffer(query, " AS %s\n", seqtype); + } if (fout->remoteVersion >= 80400) appendPQExpBuffer(query, " START WITH %s\n", startv); @@ -16348,7 +16414,10 @@ dumpSequence(Archive *fout, TableInfo *tbinfo) " CACHE %s%s", cache, (cycled ? "\n CYCLE" : "")); - appendPQExpBufferStr(query, ";\n"); + if (tbinfo->is_identity_sequence) + appendPQExpBufferStr(query, "\n);\n"); + else + appendPQExpBufferStr(query, ";\n"); appendPQExpBuffer(labelq, "SEQUENCE %s", fmtId(tbinfo->dobj.name)); @@ -16381,7 +16450,7 @@ dumpSequence(Archive *fout, TableInfo *tbinfo) * We need not schema-qualify the table reference because both sequence * and table must be in the same schema. */ - if (OidIsValid(tbinfo->owning_tab)) + if (OidIsValid(tbinfo->owning_tab) && !tbinfo->is_identity_sequence) { TableInfo *owning_tab = findTableByOid(tbinfo->owning_tab); diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index cb22f63bd6..61097e6d99 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -288,6 +288,7 @@ typedef struct _tableInfo /* these two are set only if table is a sequence owned by a column: */ Oid owning_tab; /* OID of table owning sequence */ int owning_col; /* attr # of column owning sequence */ + bool is_identity_sequence; int relpages; /* table's size in pages (from pg_class) */ bool interesting; /* true if need to collect more data */ @@ -306,6 +307,7 @@ typedef struct _tableInfo char *attstorage; /* attribute storage scheme */ char *typstorage; /* type storage scheme */ bool *attisdropped; /* true if attr is dropped; don't dump it */ + char *attidentity; int *attlen; /* attribute length, used by binary_upgrade */ char *attalign; /* attribute align, used by binary_upgrade */ bool *attislocal; /* true if attr has local definition */ @@ -317,6 +319,7 @@ typedef struct _tableInfo struct _attrDefInfo **attrdefs; /* DEFAULT expressions */ struct _constraintInfo *checkexprs; /* CHECK constraints */ char *partkeydef; /* partition key definition */ + bool needs_override; /* has GENERATED ALWAYS AS IDENTITY */ /* * Stuff computed only for dumpable tables. diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl index 366737440c..5030bc204c 100644 --- a/src/bin/pg_dump/t/002_pg_dump.pl +++ b/src/bin/pg_dump/t/002_pg_dump.pl @@ -2263,6 +2263,37 @@ my %tests = ( only_dump_test_table => 1, role => 1, }, }, + 'COPY test_table_identity' => { + all_runs => 1, + catch_all => 'COPY ... commands', + create_order => 54, + create_sql => +'INSERT INTO dump_test.test_table_identity (col2) VALUES (\'test\');', + regexp => qr/^ + \QCOPY test_table_identity (col1, col2) FROM stdin;\E + \n1\ttest\n\\\.\n + /xm, + like => { + clean => 1, + clean_if_exists => 1, + createdb => 1, + data_only => 1, + defaults => 1, + exclude_test_table => 1, + exclude_test_table_data => 1, + no_blobs => 1, + no_privs => 1, + no_owner => 1, + only_dump_test_schema => 1, + pg_dumpall_dbprivs => 1, + section_data => 1, + test_schema_plus_blobs => 1, + with_oids => 1, }, + unlike => { + exclude_dump_test_schema => 1, + only_dump_test_table => 1, + role => 1, }, }, + 'COPY ... commands' => { # catch-all for COPY all_runs => 0, # catch-all regexp => qr/^COPY /m, @@ -2318,6 +2349,14 @@ qr/^\QINSERT INTO test_fifth_table (col1, col2, col3, col4, col5) VALUES (NULL, like => { column_inserts => 1, }, unlike => {}, }, + 'INSERT INTO test_table_identity' => { + all_runs => 1, + catch_all => 'INSERT INTO ...', + regexp => +qr/^\QINSERT INTO test_table_identity (col1, col2) OVERRIDING SYSTEM VALUE VALUES (1, 'test');\E/m, + like => { column_inserts => 1, }, + unlike => {}, }, + # INSERT INTO catch-all 'INSERT INTO ...' => { all_runs => 0, # catch-all @@ -4709,6 +4748,54 @@ qr/CREATE TRANSFORM FOR integer LANGUAGE sql \(FROM SQL WITH FUNCTION pg_catalog role => 1, section_post_data => 1, }, }, + 'CREATE TABLE test_table_identity' => { + all_runs => 1, + catch_all => 'CREATE ... commands', + create_order => 3, + create_sql => 'CREATE TABLE dump_test.test_table_identity ( + col1 int generated always as identity primary key, + col2 text + );', + regexp => qr/^ + \QCREATE TABLE test_table_identity (\E\n + \s+\Qcol1 integer NOT NULL,\E\n + \s+\Qcol2 text\E\n + \); + .* + \QALTER TABLE test_table_identity ALTER COLUMN col1 ADD GENERATED ALWAYS AS IDENTITY (\E\n + \s+\QSEQUENCE NAME test_table_identity_col1_seq\E\n + \s+\QSTART WITH 1\E\n + \s+\QINCREMENT BY 1\E\n + \s+\QNO MINVALUE\E\n + \s+\QNO MAXVALUE\E\n + \s+\QCACHE 1\E\n + \); + /xms, + like => { + binary_upgrade => 1, + clean => 1, + clean_if_exists => 1, + createdb => 1, + defaults => 1, + exclude_test_table => 1, + exclude_test_table_data => 1, + no_blobs => 1, + no_privs => 1, + no_owner => 1, + only_dump_test_schema => 1, + pg_dumpall_dbprivs => 1, + schema_only => 1, + section_pre_data => 1, + test_schema_plus_blobs => 1, + with_oids => 1, }, + unlike => { + exclude_dump_test_schema => 1, + only_dump_test_table => 1, + pg_dumpall_globals => 1, + pg_dumpall_globals_clean => 1, + role => 1, + section_post_data => 1, }, }, + 'CREATE SEQUENCE test_table_col1_seq' => { all_runs => 1, catch_all => 'CREATE ... commands', diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 2ef06261e6..ddb3942e95 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -14,6 +14,7 @@ #include +#include "catalog/pg_attribute.h" #include "catalog/pg_class.h" #include "catalog/pg_default_acl.h" #include "fe_utils/string_utils.h" @@ -1592,6 +1593,10 @@ describeOneTableDetails(const char *schemaname, " WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation"); else appendPQExpBufferStr(&buf, "\n NULL AS attcollation"); + if (pset.sversion >= 100000) + appendPQExpBufferStr(&buf, ", a.attidentity"); + else + appendPQExpBufferStr(&buf, ", ''::\"char\" AS attidentity"); if (tableinfo.relkind == RELKIND_INDEX) appendPQExpBufferStr(&buf, ",\n pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef"); else @@ -1778,12 +1783,24 @@ describeOneTableDetails(const char *schemaname, /* Collation, Nullable, Default */ if (show_column_details) { + char *identity; + char *default_str = ""; + printTableAddCell(&cont, PQgetvalue(res, i, 5), false, false); printTableAddCell(&cont, strcmp(PQgetvalue(res, i, 3), "t") == 0 ? "not null" : "", false, false); - /* (note: above we cut off the 'default' string at 128) */ - printTableAddCell(&cont, PQgetvalue(res, i, 2), false, false); + identity = PQgetvalue(res, i, 6); + + if (!identity[0]) + /* (note: above we cut off the 'default' string at 128) */ + default_str = PQgetvalue(res, i, 2); + else if (identity[0] == ATTRIBUTE_IDENTITY_ALWAYS) + default_str = "generated always as identity"; + else if (identity[0] == ATTRIBUTE_IDENTITY_BY_DEFAULT) + default_str = "generated by default as identity"; + + printTableAddCell(&cont, default_str, false, false); } /* Value: for sequences only */ @@ -1792,16 +1809,16 @@ describeOneTableDetails(const char *schemaname, /* Expression for index column */ if (tableinfo.relkind == RELKIND_INDEX) - printTableAddCell(&cont, PQgetvalue(res, i, 6), false, false); + printTableAddCell(&cont, PQgetvalue(res, i, 7), false, false); /* FDW options for foreign table column, only for 9.2 or later */ if (tableinfo.relkind == RELKIND_FOREIGN_TABLE && pset.sversion >= 90200) - printTableAddCell(&cont, PQgetvalue(res, i, 7), false, false); + printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false); /* Storage and Description */ if (verbose) { - int firstvcol = 8; + int firstvcol = 9; char *storage = PQgetvalue(res, i, firstvcol); /* these strings are literal in our syntax, so not translated. */ diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c index 813f719b9b..9dbb4a0074 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -1895,7 +1895,7 @@ psql_completion(const char *text, int start, int end) /* ALTER TABLE ALTER [COLUMN] */ else if (Matches6("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny) || Matches5("ALTER", "TABLE", MatchAny, "ALTER", MatchAny)) - COMPLETE_WITH_LIST4("TYPE", "SET", "RESET", "DROP"); + COMPLETE_WITH_LIST6("TYPE", "SET", "RESET", "RESTART", "ADD", "DROP"); /* ALTER TABLE ALTER [COLUMN] SET */ else if (Matches7("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "SET") || Matches6("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET")) @@ -1911,7 +1911,7 @@ psql_completion(const char *text, int start, int end) /* ALTER TABLE ALTER [COLUMN] DROP */ else if (Matches7("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "DROP") || Matches6("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "DROP")) - COMPLETE_WITH_LIST2("DEFAULT", "NOT NULL"); + COMPLETE_WITH_LIST3("DEFAULT", "IDENTITY", "NOT NULL"); else if (Matches4("ALTER", "TABLE", MatchAny, "CLUSTER")) COMPLETE_WITH_CONST("ON"); else if (Matches5("ALTER", "TABLE", MatchAny, "CLUSTER", "ON")) @@ -2920,17 +2920,25 @@ psql_completion(const char *text, int start, int end) /* * Complete INSERT INTO with "(" or "VALUES" or "SELECT" or - * "TABLE" or "DEFAULT VALUES" + * "TABLE" or "DEFAULT VALUES" or "OVERRIDING" */ else if (TailMatches3("INSERT", "INTO", MatchAny)) - COMPLETE_WITH_LIST5("(", "DEFAULT VALUES", "SELECT", "TABLE", "VALUES"); + COMPLETE_WITH_LIST6("(", "DEFAULT VALUES", "SELECT", "TABLE", "VALUES", "OVERRIDING"); /* * Complete INSERT INTO
(attribs) with "VALUES" or "SELECT" or - * "TABLE" + * "TABLE" or "OVERRIDING" */ else if (TailMatches4("INSERT", "INTO", MatchAny, MatchAny) && ends_with(prev_wd, ')')) + COMPLETE_WITH_LIST4("SELECT", "TABLE", "VALUES", "OVERRIDING"); + + /* Complete OVERRIDING */ + else if (TailMatches1("OVERRIDING")) + COMPLETE_WITH_LIST2("SYSTEM VALUE", "USER VALUE"); + + /* Complete after OVERRIDING clause */ + else if (TailMatches3("OVERRIDING", MatchAny, "VALUE")) COMPLETE_WITH_LIST3("SELECT", "TABLE", "VALUES"); /* Insert an open parenthesis after "VALUES" */ diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catversion.h index 1db7a4d715..4dd571a2e2 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ /* yyyymmddN */ -#define CATALOG_VERSION_NO 201704012 +#define CATALOG_VERSION_NO 201704061 #endif diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h index 9effbce2f1..33361ffce9 100644 --- a/src/include/catalog/dependency.h +++ b/src/include/catalog/dependency.h @@ -238,11 +238,9 @@ extern long changeDependencyFor(Oid classId, Oid objectId, extern Oid getExtensionOfObject(Oid classId, Oid objectId); -extern bool sequenceIsOwned(Oid seqId, Oid *tableId, int32 *colId); - -extern void markSequenceUnowned(Oid seqId); - -extern List *getOwnedSequences(Oid relid); +extern bool sequenceIsOwned(Oid seqId, char deptype, Oid *tableId, int32 *colId); +extern List *getOwnedSequences(Oid relid, AttrNumber attnum); +extern Oid getOwnedSequence(Oid relid, AttrNumber attnum); extern Oid get_constraint_index(Oid constraintId); diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_attribute.h index e861f4c589..753d45f2d1 100644 --- a/src/include/catalog/pg_attribute.h +++ b/src/include/catalog/pg_attribute.h @@ -133,6 +133,9 @@ CATALOG(pg_attribute,1249) BKI_BOOTSTRAP BKI_WITHOUT_OIDS BKI_ROWTYPE_OID(75) BK /* Has DEFAULT value or not */ bool atthasdef; + /* One of the ATTRIBUTE_IDENTITY_* constants below, or '\0' */ + char attidentity; + /* Is dropped (ie, logically invisible) or not */ bool attisdropped; @@ -188,7 +191,7 @@ typedef FormData_pg_attribute *Form_pg_attribute; * ---------------- */ -#define Natts_pg_attribute 21 +#define Natts_pg_attribute 22 #define Anum_pg_attribute_attrelid 1 #define Anum_pg_attribute_attname 2 #define Anum_pg_attribute_atttypid 3 @@ -203,13 +206,14 @@ typedef FormData_pg_attribute *Form_pg_attribute; #define Anum_pg_attribute_attalign 12 #define Anum_pg_attribute_attnotnull 13 #define Anum_pg_attribute_atthasdef 14 -#define Anum_pg_attribute_attisdropped 15 -#define Anum_pg_attribute_attislocal 16 -#define Anum_pg_attribute_attinhcount 17 -#define Anum_pg_attribute_attcollation 18 -#define Anum_pg_attribute_attacl 19 -#define Anum_pg_attribute_attoptions 20 -#define Anum_pg_attribute_attfdwoptions 21 +#define Anum_pg_attribute_attidentity 15 +#define Anum_pg_attribute_attisdropped 16 +#define Anum_pg_attribute_attislocal 17 +#define Anum_pg_attribute_attinhcount 18 +#define Anum_pg_attribute_attcollation 19 +#define Anum_pg_attribute_attacl 20 +#define Anum_pg_attribute_attoptions 21 +#define Anum_pg_attribute_attfdwoptions 22 /* ---------------- @@ -220,4 +224,8 @@ typedef FormData_pg_attribute *Form_pg_attribute; * ---------------- */ + +#define ATTRIBUTE_IDENTITY_ALWAYS 'a' +#define ATTRIBUTE_IDENTITY_BY_DEFAULT 'd' + #endif /* PG_ATTRIBUTE_H */ diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h index d1d493ee05..5a288830e8 100644 --- a/src/include/catalog/pg_class.h +++ b/src/include/catalog/pg_class.h @@ -149,7 +149,7 @@ typedef FormData_pg_class *Form_pg_class; */ DATA(insert OID = 1247 ( pg_type PGNSP 71 0 PGUID 0 0 0 0 0 0 0 f f p r 30 0 t f f f f f f t n f 3 1 _null_ _null_ _null_)); DESCR(""); -DATA(insert OID = 1249 ( pg_attribute PGNSP 75 0 PGUID 0 0 0 0 0 0 0 f f p r 21 0 f f f f f f f t n f 3 1 _null_ _null_ _null_)); +DATA(insert OID = 1249 ( pg_attribute PGNSP 75 0 PGUID 0 0 0 0 0 0 0 f f p r 22 0 f f f f f f f t n f 3 1 _null_ _null_ _null_)); DESCR(""); DATA(insert OID = 1255 ( pg_proc PGNSP 81 0 PGUID 0 0 0 0 0 0 0 f f p r 29 0 t f f f f f f t n f 3 1 _null_ _null_ _null_)); DESCR(""); diff --git a/src/include/commands/sequence.h b/src/include/commands/sequence.h index 49a77c42fc..304586e48e 100644 --- a/src/include/commands/sequence.h +++ b/src/include/commands/sequence.h @@ -51,7 +51,9 @@ typedef struct xl_seq_rec /* SEQUENCE TUPLE DATA FOLLOWS AT THE END */ } xl_seq_rec; +extern int64 nextval_internal(Oid relid, bool check_permissions); extern Datum nextval(PG_FUNCTION_ARGS); +extern List *sequence_options(Oid relid); extern ObjectAddress DefineSequence(ParseState *pstate, CreateSeqStmt *stmt); extern ObjectAddress AlterSequence(ParseState *pstate, AlterSeqStmt *stmt); diff --git a/src/include/executor/execExpr.h b/src/include/executor/execExpr.h index a665388232..86fdb33cd3 100644 --- a/src/include/executor/execExpr.h +++ b/src/include/executor/execExpr.h @@ -144,6 +144,7 @@ typedef enum ExprEvalOp EEOP_NULLIF, EEOP_SQLVALUEFUNCTION, EEOP_CURRENTOFEXPR, + EEOP_NEXTVALUEEXPR, EEOP_ARRAYEXPR, EEOP_ARRAYCOERCE, EEOP_ROW, @@ -361,6 +362,13 @@ typedef struct ExprEvalStep SQLValueFunction *svf; } sqlvaluefunction; + /* for EEOP_NEXTVALUEXPR */ + struct + { + Oid seqid; + Oid seqtypid; + } nextvalueexpr; + /* for EEOP_ARRAYEXPR */ struct { diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 177853b3bf..f59d719923 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -190,6 +190,7 @@ typedef enum NodeTag T_FromExpr, T_OnConflictExpr, T_IntoClause, + T_NextValueExpr, /* * TAGS FOR EXPRESSION STATE NODES (execnodes.h) diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index b2afd50818..9f57388781 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -27,6 +27,13 @@ #include "nodes/primnodes.h" #include "nodes/value.h" +typedef enum OverridingKind +{ + OVERRIDING_NOT_SET = 0, + OVERRIDING_USER_VALUE, + OVERRIDING_SYSTEM_VALUE +} OverridingKind; + /* Possible sources of a Query */ typedef enum QuerySource { @@ -130,6 +137,8 @@ typedef struct Query List *targetList; /* target list (of TargetEntry) */ + OverridingKind override; /* OVERRIDING clause */ + OnConflictExpr *onConflict; /* ON CONFLICT DO [NOTHING | UPDATE] */ List *returningList; /* return-values list (of TargetEntry) */ @@ -637,6 +646,7 @@ typedef struct ColumnDef char storage; /* attstorage setting, or 0 for default */ Node *raw_default; /* default value (untransformed parse tree) */ Node *cooked_default; /* default value (transformed expr tree) */ + char identity; /* attidentity setting */ CollateClause *collClause; /* untransformed COLLATE spec, if any */ Oid collOid; /* collation OID (InvalidOid if not set) */ List *constraints; /* other constraints on column */ @@ -658,9 +668,10 @@ typedef enum TableLikeOption { CREATE_TABLE_LIKE_DEFAULTS = 1 << 0, CREATE_TABLE_LIKE_CONSTRAINTS = 1 << 1, - CREATE_TABLE_LIKE_INDEXES = 1 << 2, - CREATE_TABLE_LIKE_STORAGE = 1 << 3, - CREATE_TABLE_LIKE_COMMENTS = 1 << 4, + CREATE_TABLE_LIKE_IDENTITY = 1 << 2, + CREATE_TABLE_LIKE_INDEXES = 1 << 3, + CREATE_TABLE_LIKE_STORAGE = 1 << 4, + CREATE_TABLE_LIKE_COMMENTS = 1 << 5, CREATE_TABLE_LIKE_ALL = PG_INT32_MAX } TableLikeOption; @@ -1403,6 +1414,7 @@ typedef struct InsertStmt OnConflictClause *onConflictClause; /* ON CONFLICT clause */ List *returningList; /* list of expressions to return */ WithClause *withClause; /* WITH clause */ + OverridingKind override; /* OVERRIDING clause */ } InsertStmt; /* ---------------------- @@ -1713,7 +1725,10 @@ typedef enum AlterTableType AT_NoForceRowSecurity, /* NO FORCE ROW SECURITY */ AT_GenericOptions, /* OPTIONS (...) */ AT_AttachPartition, /* ATTACH PARTITION */ - AT_DetachPartition /* DETACH PARTITION */ + AT_DetachPartition, /* DETACH PARTITION */ + AT_AddIdentity, /* ADD IDENTITY */ + AT_SetIdentity, /* SET identity column options */ + AT_DropIdentity /* DROP IDENTITY */ } AlterTableType; typedef struct ReplicaIdentityStmt @@ -2000,6 +2015,7 @@ typedef enum ConstrType /* types of constraints */ * expect it */ CONSTR_NOTNULL, CONSTR_DEFAULT, + CONSTR_IDENTITY, CONSTR_CHECK, CONSTR_PRIMARY, CONSTR_UNIQUE, @@ -2038,6 +2054,7 @@ typedef struct Constraint bool is_no_inherit; /* is constraint non-inheritable? */ Node *raw_expr; /* expr, as untransformed parse tree */ char *cooked_expr; /* expr, as nodeToString representation */ + char generated_when; /* Fields used for unique constraints (UNIQUE and PRIMARY KEY): */ List *keys; /* String nodes naming referenced column(s) */ @@ -2416,6 +2433,7 @@ typedef struct CreateSeqStmt RangeVar *sequence; /* the sequence to create */ List *options; Oid ownerId; /* ID of owner, or InvalidOid for default */ + bool for_identity; bool if_not_exists; /* just do nothing if it already exists? */ } CreateSeqStmt; @@ -2424,6 +2442,7 @@ typedef struct AlterSeqStmt NodeTag type; RangeVar *sequence; /* the sequence to alter */ List *options; + bool for_identity; bool missing_ok; /* skip error if a role is missing? */ } AlterSeqStmt; diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index d57b4fab3d..b87fe84545 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1292,6 +1292,20 @@ typedef struct InferenceElem Oid inferopclass; /* OID of att opclass, or InvalidOid */ } InferenceElem; +/* + * NextValueExpr - get next value from sequence + * + * This has the same effect as calling the nextval() function, but it does not + * check permissions on the sequence. This is used for identity columns, + * where the sequence is an implicit dependency without its own permissions. + */ +typedef struct NextValueExpr +{ + Expr xpr; + Oid seqid; + Oid typeId; +} NextValueExpr; + /*-------------------- * TargetEntry - * a target entry (used in query target lists) diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index cd21a789d5..37542aaee4 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -174,6 +174,7 @@ PG_KEYWORD("from", FROM, RESERVED_KEYWORD) PG_KEYWORD("full", FULL, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("function", FUNCTION, UNRESERVED_KEYWORD) PG_KEYWORD("functions", FUNCTIONS, UNRESERVED_KEYWORD) +PG_KEYWORD("generated", GENERATED, UNRESERVED_KEYWORD) PG_KEYWORD("global", GLOBAL, UNRESERVED_KEYWORD) PG_KEYWORD("grant", GRANT, RESERVED_KEYWORD) PG_KEYWORD("granted", GRANTED, UNRESERVED_KEYWORD) @@ -287,6 +288,7 @@ PG_KEYWORD("outer", OUTER_P, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("over", OVER, UNRESERVED_KEYWORD) PG_KEYWORD("overlaps", OVERLAPS, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("overlay", OVERLAY, COL_NAME_KEYWORD) +PG_KEYWORD("overriding", OVERRIDING, UNRESERVED_KEYWORD) PG_KEYWORD("owned", OWNED, UNRESERVED_KEYWORD) PG_KEYWORD("owner", OWNER, UNRESERVED_KEYWORD) PG_KEYWORD("parallel", PARALLEL, UNRESERVED_KEYWORD) diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h index b6d1fca2fa..88629d99aa 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -64,6 +64,7 @@ extern Oid get_opfamily_proc(Oid opfamily, Oid lefttype, Oid righttype, extern char *get_attname(Oid relid, AttrNumber attnum); extern char *get_relid_attribute_name(Oid relid, AttrNumber attnum); extern AttrNumber get_attnum(Oid relid, const char *attname); +extern char get_attidentity(Oid relid, AttrNumber attnum); extern Oid get_atttype(Oid relid, AttrNumber attnum); extern int32 get_atttypmod(Oid relid, AttrNumber attnum); extern void get_atttypetypmodcoll(Oid relid, AttrNumber attnum, diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out index a25b221703..3f405c94ce 100644 --- a/src/test/regress/expected/create_table_like.out +++ b/src/test/regress/expected/create_table_like.out @@ -66,6 +66,53 @@ SELECT * FROM inhg; /* Two records with three columns in order x=x, xx=text, y=y (2 rows) DROP TABLE inhg; +CREATE TABLE test_like_id_1 (a int GENERATED ALWAYS AS IDENTITY, b text); +\d test_like_id_1 + Table "public.test_like_id_1" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------ + a | integer | | not null | generated always as identity + b | text | | | + +INSERT INTO test_like_id_1 (b) VALUES ('b1'); +SELECT * FROM test_like_id_1; + a | b +---+---- + 1 | b1 +(1 row) + +CREATE TABLE test_like_id_2 (LIKE test_like_id_1); +\d test_like_id_2 + Table "public.test_like_id_2" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+--------- + a | integer | | not null | + b | text | | | + +INSERT INTO test_like_id_2 (b) VALUES ('b2'); +ERROR: null value in column "a" violates not-null constraint +DETAIL: Failing row contains (null, b2). +SELECT * FROM test_like_id_2; -- identity was not copied + a | b +---+--- +(0 rows) + +CREATE TABLE test_like_id_3 (LIKE test_like_id_1 INCLUDING IDENTITY); +\d test_like_id_3 + Table "public.test_like_id_3" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------ + a | integer | | not null | generated always as identity + b | text | | | + +INSERT INTO test_like_id_3 (b) VALUES ('b3'); +SELECT * FROM test_like_id_3; -- identity was copied and applied + a | b +---+---- + 1 | b3 +(1 row) + +DROP TABLE test_like_id_1, test_like_id_2, test_like_id_3; CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */ INSERT INTO inhg VALUES (5, 10); INSERT INTO inhg VALUES (20, 10); -- should fail diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out new file mode 100644 index 0000000000..88b56dad93 --- /dev/null +++ b/src/test/regress/expected/identity.out @@ -0,0 +1,322 @@ +-- sanity check of system catalog +SELECT attrelid, attname, attidentity FROM pg_attribute WHERE attidentity NOT IN ('', 'a', 'd'); + attrelid | attname | attidentity +----------+---------+------------- +(0 rows) + +CREATE TABLE itest1 (a int generated by default as identity, b text); +CREATE TABLE itest2 (a bigint generated always as identity, b text); +CREATE TABLE itest3 (a smallint generated by default as identity (start with 7 increment by 5), b text); +ALTER TABLE itest3 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error +ERROR: column "a" of relation "itest3" is already an identity column +SELECT table_name, column_name, column_default, is_nullable, is_identity, identity_generation, identity_start, identity_increment, identity_maximum, identity_minimum, identity_cycle FROM information_schema.columns WHERE table_name LIKE 'itest_' ORDER BY 1, 2; + table_name | column_name | column_default | is_nullable | is_identity | identity_generation | identity_start | identity_increment | identity_maximum | identity_minimum | identity_cycle +------------+-------------+----------------+-------------+-------------+---------------------+----------------+--------------------+---------------------+------------------+---------------- + itest1 | a | | NO | YES | BY DEFAULT | 1 | 1 | 2147483647 | 1 | NO + itest1 | b | | YES | NO | | | | | | NO + itest2 | a | | NO | YES | ALWAYS | 1 | 1 | 9223372036854775807 | 1 | NO + itest2 | b | | YES | NO | | | | | | NO + itest3 | a | | NO | YES | BY DEFAULT | 7 | 5 | 32767 | 1 | NO + itest3 | b | | YES | NO | | | | | | NO +(6 rows) + +-- internal sequences should not be shown here +SELECT sequence_name FROM information_schema.sequences WHERE sequence_name LIKE 'itest%'; + sequence_name +--------------- +(0 rows) + +CREATE TABLE itest4 (a int, b text); +ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error, requires NOT NULL +ERROR: column "a" of relation "itest4" must be declared NOT NULL before identity can be added +ALTER TABLE itest4 ALTER COLUMN a SET NOT NULL; +ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- ok +ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL; -- error, disallowed +ERROR: column "a" of relation "itest4" is an identity column +ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error, already set +ERROR: column "a" of relation "itest4" is already an identity column +ALTER TABLE itest4 ALTER COLUMN b ADD GENERATED ALWAYS AS IDENTITY; -- error, wrong data type +ERROR: identity column type must be smallint, integer, or bigint +-- for later +ALTER TABLE itest4 ALTER COLUMN b SET DEFAULT ''; +-- invalid column type +CREATE TABLE itest_err_1 (a text generated by default as identity); +ERROR: identity column type must be smallint, integer, or bigint +-- duplicate identity +CREATE TABLE itest_err_2 (a int generated always as identity generated by default as identity); +ERROR: multiple identity specifications for column "a" of table "itest_err_2" +LINE 1: ...E itest_err_2 (a int generated always as identity generated ... + ^ +-- cannot have default and identity +CREATE TABLE itest_err_3 (a int default 5 generated by default as identity); +ERROR: both default and identity specified for column "a" of table "itest_err_3" +LINE 1: CREATE TABLE itest_err_3 (a int default 5 generated by defau... + ^ +-- cannot combine serial and identity +CREATE TABLE itest_err_4 (a serial generated by default as identity); +ERROR: both default and identity specified for column "a" of table "itest_err_4" +INSERT INTO itest1 DEFAULT VALUES; +INSERT INTO itest1 DEFAULT VALUES; +INSERT INTO itest2 DEFAULT VALUES; +INSERT INTO itest2 DEFAULT VALUES; +INSERT INTO itest3 DEFAULT VALUES; +INSERT INTO itest3 DEFAULT VALUES; +INSERT INTO itest4 DEFAULT VALUES; +INSERT INTO itest4 DEFAULT VALUES; +SELECT * FROM itest1; + a | b +---+--- + 1 | + 2 | +(2 rows) + +SELECT * FROM itest2; + a | b +---+--- + 1 | + 2 | +(2 rows) + +SELECT * FROM itest3; + a | b +----+--- + 7 | + 12 | +(2 rows) + +SELECT * FROM itest4; + a | b +---+--- + 1 | + 2 | +(2 rows) + +-- OVERRIDING tests +INSERT INTO itest1 VALUES (10, 'xyz'); +INSERT INTO itest1 OVERRIDING USER VALUE VALUES (10, 'xyz'); +SELECT * FROM itest1; + a | b +----+----- + 1 | + 2 | + 10 | xyz + 3 | xyz +(4 rows) + +INSERT INTO itest2 VALUES (10, 'xyz'); +ERROR: cannot insert into column "a" +DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS. +HINT: Use OVERRIDING SYSTEM VALUE to override. +INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (10, 'xyz'); +SELECT * FROM itest2; + a | b +----+----- + 1 | + 2 | + 10 | xyz +(3 rows) + +-- UPDATE tests +UPDATE itest1 SET a = 101 WHERE a = 1; +UPDATE itest1 SET a = DEFAULT WHERE a = 2; +SELECT * FROM itest1; + a | b +-----+----- + 10 | xyz + 3 | xyz + 101 | + 4 | +(4 rows) + +UPDATE itest2 SET a = 101 WHERE a = 1; +ERROR: column "a" can only be updated to DEFAULT +DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS. +UPDATE itest2 SET a = DEFAULT WHERE a = 2; +SELECT * FROM itest2; + a | b +----+----- + 1 | + 10 | xyz + 3 | +(3 rows) + +-- DROP IDENTITY tests +ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY; +ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY; -- error +ERROR: column "a" of relation "itest4" is not an identity column +ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY IF EXISTS; -- noop +NOTICE: column "a" of relation "itest4" is not an identity column, skipping +INSERT INTO itest4 DEFAULT VALUES; -- fails because NOT NULL is not dropped +ERROR: null value in column "a" violates not-null constraint +DETAIL: Failing row contains (null, ). +ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL; +INSERT INTO itest4 DEFAULT VALUES; +SELECT * FROM itest4; + a | b +---+--- + 1 | + 2 | + | +(3 rows) + +-- check that sequence is removed +SELECT sequence_name FROM itest4_a_seq; +ERROR: relation "itest4_a_seq" does not exist +LINE 1: SELECT sequence_name FROM itest4_a_seq; + ^ +-- test views +CREATE TABLE itest10 (a int generated by default as identity, b text); +CREATE TABLE itest11 (a int generated always as identity, b text); +CREATE VIEW itestv10 AS SELECT * FROM itest10; +CREATE VIEW itestv11 AS SELECT * FROM itest11; +INSERT INTO itestv10 DEFAULT VALUES; +INSERT INTO itestv10 DEFAULT VALUES; +INSERT INTO itestv11 DEFAULT VALUES; +INSERT INTO itestv11 DEFAULT VALUES; +SELECT * FROM itestv10; + a | b +---+--- + 1 | + 2 | +(2 rows) + +SELECT * FROM itestv11; + a | b +---+--- + 1 | + 2 | +(2 rows) + +INSERT INTO itestv10 VALUES (10, 'xyz'); +INSERT INTO itestv10 OVERRIDING USER VALUE VALUES (11, 'xyz'); +SELECT * FROM itestv10; + a | b +----+----- + 1 | + 2 | + 10 | xyz + 3 | xyz +(4 rows) + +INSERT INTO itestv11 VALUES (10, 'xyz'); +ERROR: cannot insert into column "a" +DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS. +HINT: Use OVERRIDING SYSTEM VALUE to override. +INSERT INTO itestv11 OVERRIDING SYSTEM VALUE VALUES (11, 'xyz'); +SELECT * FROM itestv11; + a | b +----+----- + 1 | + 2 | + 11 | xyz +(3 rows) + +-- various ALTER COLUMN tests +-- fail, not allowed for identity columns +ALTER TABLE itest1 ALTER COLUMN a SET DEFAULT 1; +ERROR: column "a" of relation "itest1" is an identity column +-- fail, not allowed, already has a default +CREATE TABLE itest5 (a serial, b text); +ALTER TABLE itest5 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; +ERROR: column "a" of relation "itest5" already has a default value +ALTER TABLE itest3 ALTER COLUMN a TYPE int; +SELECT seqtypid::regtype FROM pg_sequence WHERE seqrelid = 'itest3_a_seq'::regclass; + seqtypid +---------- + integer +(1 row) + +\d itest3 + Table "public.itest3" + Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------------------------------- + a | integer | | not null | generated by default as identity + b | text | | | + +ALTER TABLE itest3 ALTER COLUMN a TYPE text; -- error +ERROR: identity column type must be smallint, integer, or bigint +-- ALTER COLUMN ... SET +CREATE TABLE itest6 (a int GENERATED ALWAYS AS IDENTITY, b text); +INSERT INTO itest6 DEFAULT VALUES; +ALTER TABLE itest6 ALTER COLUMN a SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 100 RESTART; +INSERT INTO itest6 DEFAULT VALUES; +INSERT INTO itest6 DEFAULT VALUES; +SELECT * FROM itest6; + a | b +-----+--- + 1 | + 100 | + 102 | +(3 rows) + +SELECT table_name, column_name, is_identity, identity_generation FROM information_schema.columns WHERE table_name = 'itest6'; + table_name | column_name | is_identity | identity_generation +------------+-------------+-------------+--------------------- + itest6 | a | YES | BY DEFAULT + itest6 | b | NO | +(2 rows) + +ALTER TABLE itest6 ALTER COLUMN b SET INCREMENT BY 2; -- fail, not identity +ERROR: column "b" of relation "itest6" is not an identity column +-- prohibited direct modification of sequence +ALTER SEQUENCE itest6_a_seq OWNED BY NONE; +ERROR: cannot change ownership of identity sequence +DETAIL: Sequence "itest6_a_seq" is linked to table "itest6". +-- inheritance +CREATE TABLE itest7 (a int GENERATED ALWAYS AS IDENTITY); +INSERT INTO itest7 DEFAULT VALUES; +SELECT * FROM itest7; + a +--- + 1 +(1 row) + +-- identity property is not inherited +CREATE TABLE itest7a (b text) INHERITS (itest7); +-- make column identity in child table +CREATE TABLE itest7b (a int); +CREATE TABLE itest7c (a int GENERATED ALWAYS AS IDENTITY) INHERITS (itest7b); +NOTICE: merging column "a" with inherited definition +INSERT INTO itest7c DEFAULT VALUES; +SELECT * FROM itest7c; + a +--- + 1 +(1 row) + +CREATE TABLE itest7d (a int not null); +CREATE TABLE itest7e () INHERITS (itest7d); +ALTER TABLE itest7d ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; +ALTER TABLE itest7d ADD COLUMN b int GENERATED ALWAYS AS IDENTITY; -- error +ERROR: cannot recursively add identity column to table that has child tables +SELECT table_name, column_name, is_nullable, is_identity, identity_generation FROM information_schema.columns WHERE table_name LIKE 'itest7%' ORDER BY 1, 2; + table_name | column_name | is_nullable | is_identity | identity_generation +------------+-------------+-------------+-------------+--------------------- + itest7 | a | NO | YES | ALWAYS + itest7a | a | NO | NO | + itest7a | b | YES | NO | + itest7b | a | YES | NO | + itest7c | a | NO | YES | ALWAYS + itest7d | a | NO | YES | ALWAYS + itest7e | a | NO | NO | +(7 rows) + +-- These ALTER TABLE variants will not recurse. +ALTER TABLE itest7 ALTER COLUMN a SET GENERATED BY DEFAULT; +ALTER TABLE itest7 ALTER COLUMN a RESTART; +ALTER TABLE itest7 ALTER COLUMN a DROP IDENTITY; +-- privileges +CREATE USER regress_user1; +CREATE TABLE itest8 (a int GENERATED ALWAYS AS IDENTITY, b text); +GRANT SELECT, INSERT ON itest8 TO regress_user1; +SET ROLE regress_user1; +INSERT INTO itest8 DEFAULT VALUES; +SELECT * FROM itest8; + a | b +---+--- + 1 | +(1 row) + +RESET ROLE; +DROP TABLE itest8; +DROP USER regress_user1; diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.out index 1d8d02b800..16c12f3434 100644 --- a/src/test/regress/expected/sequence.out +++ b/src/test/regress/expected/sequence.out @@ -20,8 +20,8 @@ ERROR: CACHE (0) must be greater than zero CREATE SEQUENCE sequence_testx OWNED BY nobody; -- nonsense word ERROR: invalid OWNED BY option HINT: Specify OWNED BY table.column or OWNED BY NONE. -CREATE SEQUENCE sequence_testx OWNED BY pg_tables.tablename; -- not a table -ERROR: referenced relation "pg_tables" is not a table or foreign table +CREATE SEQUENCE sequence_testx OWNED BY pg_class_oid_index.oid; -- not a table +ERROR: referenced relation "pg_class_oid_index" is not a table or foreign table CREATE SEQUENCE sequence_testx OWNED BY pg_class.relname; -- not same schema ERROR: sequence must be in same schema as table it is linked to CREATE TABLE sequence_test_table (a int); diff --git a/src/test/regress/expected/truncate.out b/src/test/regress/expected/truncate.out index 81612d8c88..b652562f5b 100644 --- a/src/test/regress/expected/truncate.out +++ b/src/test/regress/expected/truncate.out @@ -393,6 +393,36 @@ SELECT * FROM truncate_a; 2 | 34 (2 rows) +CREATE TABLE truncate_b (id int GENERATED ALWAYS AS IDENTITY (START WITH 44)); +INSERT INTO truncate_b DEFAULT VALUES; +INSERT INTO truncate_b DEFAULT VALUES; +SELECT * FROM truncate_b; + id +---- + 44 + 45 +(2 rows) + +TRUNCATE truncate_b; +INSERT INTO truncate_b DEFAULT VALUES; +INSERT INTO truncate_b DEFAULT VALUES; +SELECT * FROM truncate_b; + id +---- + 46 + 47 +(2 rows) + +TRUNCATE truncate_b RESTART IDENTITY; +INSERT INTO truncate_b DEFAULT VALUES; +INSERT INTO truncate_b DEFAULT VALUES; +SELECT * FROM truncate_b; + id +---- + 44 + 45 +(2 rows) + -- check rollback of a RESTART IDENTITY operation BEGIN; TRUNCATE truncate_a RESTART IDENTITY; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 9f95b016fd..1f8f0987e3 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -111,6 +111,11 @@ test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combo # ---------- test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml +# ---------- +# Another group of parallel tests +# ---------- +test: identity + # event triggers cannot run concurrently with any test that runs DDL test: event_trigger diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index e026b7cc90..04206c3162 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -170,6 +170,7 @@ test: conversion test: truncate test: alter_table test: sequence +test: identity test: polymorphism test: rowtypes test: returning diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql index 900ca804cb..557040bbe7 100644 --- a/src/test/regress/sql/create_table_like.sql +++ b/src/test/regress/sql/create_table_like.sql @@ -37,6 +37,20 @@ INSERT INTO inhg VALUES ('x', 'foo', 'y'); /* fails due to constraint */ SELECT * FROM inhg; /* Two records with three columns in order x=x, xx=text, y=y */ DROP TABLE inhg; +CREATE TABLE test_like_id_1 (a int GENERATED ALWAYS AS IDENTITY, b text); +\d test_like_id_1 +INSERT INTO test_like_id_1 (b) VALUES ('b1'); +SELECT * FROM test_like_id_1; +CREATE TABLE test_like_id_2 (LIKE test_like_id_1); +\d test_like_id_2 +INSERT INTO test_like_id_2 (b) VALUES ('b2'); +SELECT * FROM test_like_id_2; -- identity was not copied +CREATE TABLE test_like_id_3 (LIKE test_like_id_1 INCLUDING IDENTITY); +\d test_like_id_3 +INSERT INTO test_like_id_3 (b) VALUES ('b3'); +SELECT * FROM test_like_id_3; -- identity was copied and applied +DROP TABLE test_like_id_1, test_like_id_2, test_like_id_3; + CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */ INSERT INTO inhg VALUES (5, 10); INSERT INTO inhg VALUES (20, 10); -- should fail diff --git a/src/test/regress/sql/identity.sql b/src/test/regress/sql/identity.sql new file mode 100644 index 0000000000..a7e7b15737 --- /dev/null +++ b/src/test/regress/sql/identity.sql @@ -0,0 +1,192 @@ +-- sanity check of system catalog +SELECT attrelid, attname, attidentity FROM pg_attribute WHERE attidentity NOT IN ('', 'a', 'd'); + + +CREATE TABLE itest1 (a int generated by default as identity, b text); +CREATE TABLE itest2 (a bigint generated always as identity, b text); +CREATE TABLE itest3 (a smallint generated by default as identity (start with 7 increment by 5), b text); +ALTER TABLE itest3 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error + +SELECT table_name, column_name, column_default, is_nullable, is_identity, identity_generation, identity_start, identity_increment, identity_maximum, identity_minimum, identity_cycle FROM information_schema.columns WHERE table_name LIKE 'itest_' ORDER BY 1, 2; + +-- internal sequences should not be shown here +SELECT sequence_name FROM information_schema.sequences WHERE sequence_name LIKE 'itest%'; + +CREATE TABLE itest4 (a int, b text); +ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error, requires NOT NULL +ALTER TABLE itest4 ALTER COLUMN a SET NOT NULL; +ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- ok +ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL; -- error, disallowed +ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error, already set +ALTER TABLE itest4 ALTER COLUMN b ADD GENERATED ALWAYS AS IDENTITY; -- error, wrong data type + +-- for later +ALTER TABLE itest4 ALTER COLUMN b SET DEFAULT ''; + +-- invalid column type +CREATE TABLE itest_err_1 (a text generated by default as identity); + +-- duplicate identity +CREATE TABLE itest_err_2 (a int generated always as identity generated by default as identity); + +-- cannot have default and identity +CREATE TABLE itest_err_3 (a int default 5 generated by default as identity); + +-- cannot combine serial and identity +CREATE TABLE itest_err_4 (a serial generated by default as identity); + +INSERT INTO itest1 DEFAULT VALUES; +INSERT INTO itest1 DEFAULT VALUES; +INSERT INTO itest2 DEFAULT VALUES; +INSERT INTO itest2 DEFAULT VALUES; +INSERT INTO itest3 DEFAULT VALUES; +INSERT INTO itest3 DEFAULT VALUES; +INSERT INTO itest4 DEFAULT VALUES; +INSERT INTO itest4 DEFAULT VALUES; + +SELECT * FROM itest1; +SELECT * FROM itest2; +SELECT * FROM itest3; +SELECT * FROM itest4; + + +-- OVERRIDING tests + +INSERT INTO itest1 VALUES (10, 'xyz'); +INSERT INTO itest1 OVERRIDING USER VALUE VALUES (10, 'xyz'); + +SELECT * FROM itest1; + +INSERT INTO itest2 VALUES (10, 'xyz'); +INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (10, 'xyz'); + +SELECT * FROM itest2; + + +-- UPDATE tests + +UPDATE itest1 SET a = 101 WHERE a = 1; +UPDATE itest1 SET a = DEFAULT WHERE a = 2; +SELECT * FROM itest1; + +UPDATE itest2 SET a = 101 WHERE a = 1; +UPDATE itest2 SET a = DEFAULT WHERE a = 2; +SELECT * FROM itest2; + + +-- DROP IDENTITY tests + +ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY; +ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY; -- error +ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY IF EXISTS; -- noop + +INSERT INTO itest4 DEFAULT VALUES; -- fails because NOT NULL is not dropped +ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL; +INSERT INTO itest4 DEFAULT VALUES; +SELECT * FROM itest4; + +-- check that sequence is removed +SELECT sequence_name FROM itest4_a_seq; + + +-- test views + +CREATE TABLE itest10 (a int generated by default as identity, b text); +CREATE TABLE itest11 (a int generated always as identity, b text); + +CREATE VIEW itestv10 AS SELECT * FROM itest10; +CREATE VIEW itestv11 AS SELECT * FROM itest11; + +INSERT INTO itestv10 DEFAULT VALUES; +INSERT INTO itestv10 DEFAULT VALUES; + +INSERT INTO itestv11 DEFAULT VALUES; +INSERT INTO itestv11 DEFAULT VALUES; + +SELECT * FROM itestv10; +SELECT * FROM itestv11; + +INSERT INTO itestv10 VALUES (10, 'xyz'); +INSERT INTO itestv10 OVERRIDING USER VALUE VALUES (11, 'xyz'); + +SELECT * FROM itestv10; + +INSERT INTO itestv11 VALUES (10, 'xyz'); +INSERT INTO itestv11 OVERRIDING SYSTEM VALUE VALUES (11, 'xyz'); + +SELECT * FROM itestv11; + + +-- various ALTER COLUMN tests + +-- fail, not allowed for identity columns +ALTER TABLE itest1 ALTER COLUMN a SET DEFAULT 1; + +-- fail, not allowed, already has a default +CREATE TABLE itest5 (a serial, b text); +ALTER TABLE itest5 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; + +ALTER TABLE itest3 ALTER COLUMN a TYPE int; +SELECT seqtypid::regtype FROM pg_sequence WHERE seqrelid = 'itest3_a_seq'::regclass; +\d itest3 + +ALTER TABLE itest3 ALTER COLUMN a TYPE text; -- error + + +-- ALTER COLUMN ... SET + +CREATE TABLE itest6 (a int GENERATED ALWAYS AS IDENTITY, b text); +INSERT INTO itest6 DEFAULT VALUES; + +ALTER TABLE itest6 ALTER COLUMN a SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 100 RESTART; +INSERT INTO itest6 DEFAULT VALUES; +INSERT INTO itest6 DEFAULT VALUES; +SELECT * FROM itest6; + +SELECT table_name, column_name, is_identity, identity_generation FROM information_schema.columns WHERE table_name = 'itest6'; + +ALTER TABLE itest6 ALTER COLUMN b SET INCREMENT BY 2; -- fail, not identity + + +-- prohibited direct modification of sequence + +ALTER SEQUENCE itest6_a_seq OWNED BY NONE; + + +-- inheritance + +CREATE TABLE itest7 (a int GENERATED ALWAYS AS IDENTITY); +INSERT INTO itest7 DEFAULT VALUES; +SELECT * FROM itest7; + +-- identity property is not inherited +CREATE TABLE itest7a (b text) INHERITS (itest7); + +-- make column identity in child table +CREATE TABLE itest7b (a int); +CREATE TABLE itest7c (a int GENERATED ALWAYS AS IDENTITY) INHERITS (itest7b); +INSERT INTO itest7c DEFAULT VALUES; +SELECT * FROM itest7c; + +CREATE TABLE itest7d (a int not null); +CREATE TABLE itest7e () INHERITS (itest7d); +ALTER TABLE itest7d ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; +ALTER TABLE itest7d ADD COLUMN b int GENERATED ALWAYS AS IDENTITY; -- error + +SELECT table_name, column_name, is_nullable, is_identity, identity_generation FROM information_schema.columns WHERE table_name LIKE 'itest7%' ORDER BY 1, 2; + +-- These ALTER TABLE variants will not recurse. +ALTER TABLE itest7 ALTER COLUMN a SET GENERATED BY DEFAULT; +ALTER TABLE itest7 ALTER COLUMN a RESTART; +ALTER TABLE itest7 ALTER COLUMN a DROP IDENTITY; + +-- privileges +CREATE USER regress_user1; +CREATE TABLE itest8 (a int GENERATED ALWAYS AS IDENTITY, b text); +GRANT SELECT, INSERT ON itest8 TO regress_user1; +SET ROLE regress_user1; +INSERT INTO itest8 DEFAULT VALUES; +SELECT * FROM itest8; +RESET ROLE; +DROP TABLE itest8; +DROP USER regress_user1; diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sql index 74663d7351..d53e33d779 100644 --- a/src/test/regress/sql/sequence.sql +++ b/src/test/regress/sql/sequence.sql @@ -13,7 +13,7 @@ CREATE SEQUENCE sequence_testx CACHE 0; -- OWNED BY errors CREATE SEQUENCE sequence_testx OWNED BY nobody; -- nonsense word -CREATE SEQUENCE sequence_testx OWNED BY pg_tables.tablename; -- not a table +CREATE SEQUENCE sequence_testx OWNED BY pg_class_oid_index.oid; -- not a table CREATE SEQUENCE sequence_testx OWNED BY pg_class.relname; -- not same schema CREATE TABLE sequence_test_table (a int); CREATE SEQUENCE sequence_testx OWNED BY sequence_test_table.b; -- wrong column diff --git a/src/test/regress/sql/truncate.sql b/src/test/regress/sql/truncate.sql index d61eea1a42..9d3d8de54a 100644 --- a/src/test/regress/sql/truncate.sql +++ b/src/test/regress/sql/truncate.sql @@ -202,6 +202,24 @@ INSERT INTO truncate_a DEFAULT VALUES; INSERT INTO truncate_a DEFAULT VALUES; SELECT * FROM truncate_a; +CREATE TABLE truncate_b (id int GENERATED ALWAYS AS IDENTITY (START WITH 44)); + +INSERT INTO truncate_b DEFAULT VALUES; +INSERT INTO truncate_b DEFAULT VALUES; +SELECT * FROM truncate_b; + +TRUNCATE truncate_b; + +INSERT INTO truncate_b DEFAULT VALUES; +INSERT INTO truncate_b DEFAULT VALUES; +SELECT * FROM truncate_b; + +TRUNCATE truncate_b RESTART IDENTITY; + +INSERT INTO truncate_b DEFAULT VALUES; +INSERT INTO truncate_b DEFAULT VALUES; +SELECT * FROM truncate_b; + -- check rollback of a RESTART IDENTITY operation BEGIN; TRUNCATE truncate_a RESTART IDENTITY; -- 2.40.0