Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1c1QFp-0003z2-T5 for pgsql-hackers@arkaria.postgresql.org; Tue, 01 Nov 2016 04:00:54 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1c1QFp-0001Rr-1T for pgsql-hackers@arkaria.postgresql.org; Tue, 01 Nov 2016 04:00:53 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1c1QFn-0001Rg-AM for pgsql-hackers@postgresql.org; Tue, 01 Nov 2016 04:00:51 +0000 Received: from out2-smtp.messagingengine.com ([66.111.4.26]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1c1QFh-0000n7-7n for pgsql-hackers@postgresql.org; Tue, 01 Nov 2016 04:00:49 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.42]) by mailout.nyi.internal (Postfix) with ESMTP id 5E4E820A7C; Tue, 1 Nov 2016 00:00:44 -0400 (EDT) Received: from frontend2 ([10.202.2.161]) by compute2.internal (MEProxy); Tue, 01 Nov 2016 00:00:44 -0400 DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d= messagingengine.com; h=x-me-sender:x-sasl-enc:subject:to :references:cc:from:message-id:date:mime-version:in-reply-to :content-type; s=smtpout; bh=cYC/cjxgCI/k4v0V5bLFecV3QOA=; b=UXU QVsh7r+LQNJr0WI3XMBitKEb6lYKs46PB38mrsceIgpmP1nRFglw24gHPmLyaCBJ kELC6g+NGF37RGps4I8wwyZ0JpPnF6TUYnn8iLQZhE5J8ktwCA6s7YU1IhlXF3yU nneiBpa2zF0+qOYRqSpEI55312cm6A19pflElk5U= X-ME-Sender: X-Sasl-enc: EyZzPpHh0CUPBm3oIJDXu6WO3bvhYiSR3NRy3CCkixIh 1477972836 Received: from april.local (c-73-13-66-39.hsd1.pa.comcast.net [73.13.66.39]) by mail.messagingengine.com (Postfix) with ESMTPA id DFFAFCC024; Tue, 1 Nov 2016 00:00:36 -0400 (EDT) Subject: Re: identity columns To: Vitaly Burovoy References: <6adbacbf-73bc-dd1a-2033-63409180fd18@2ndquadrant.com> Cc: pgsql-hackers From: Peter Eisentraut Organization: 2ndQuadrant Message-ID: <4ef801f2-64ce-1a58-277d-d9389ae7770d@2ndquadrant.com> Date: Tue, 1 Nov 2016 00:00:36 -0400 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.12; rv:45.0) Gecko/20100101 Thunderbird/45.4.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/mixed; boundary="------------04BC72F34EAF6B53CB7A5963" X-Pg-Spam-Score: -2.6 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-hackers Precedence: bulk Sender: pgsql-hackers-owner@postgresql.org This is a multi-part message in MIME format. --------------04BC72F34EAF6B53CB7A5963 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit New patch. On 9/9/16 11:45 PM, Vitaly Burovoy wrote: > 1. The standard requires "... ALTER COLUMN ... SET GENERATED { ALWAYS > | BY DEFAULT }" (9075-2:2011 subcl 11.20), but the patch implements it > as "... ALTER COLUMN ... ADD GENERATED { ALWAYS | BY DEFAULT } AS > IDENTITY" Has both now. They do different things, as documented. > 2. The standard requires not more than one identity column, the patch > does not follow that requirement, but it does not mentioned in the > doc. fixed > 3. Changes in the table "information_schema.columns" is not full. fixed > 4. "" is not fully implemented > because "" is implemented > whereas "" is not. done > 5. According to 9075-2:2011 subcl 14.11 Syntax Rule 11)c) for a column > with an indication that values are generated by default the only > possible "" is "OVERRIDING USER VALUE". > Implementation allows to use "OVERRIDING SYSTEM VALUE" (as "do > nothing"), but it should be mentioned in "Compatibility" part in the > doc. done (documented) > 6. "CREATE TABLE ... (LIKE ... INCLUDING ALL)" fails Assertion at > src/backend/commands/tablecmds.c:631 fixed > 7. Changing default is allowed but a column is still "identity": fixed > 8. Changing a column to be "identity" raises "duplicate key" exception: fixed > 9. Changing type of a column deletes linked sequence but leaves > "default" and "identity" marks: fixed > 10. "identity" modifier is lost when the table inherits another one: fixed, but I invite more testing of inheritance-related things > 11. The documentation says "OVERRIDING ... VALUE" can be placed even > before "DEFAULT VALUES", but it is against SQL spec and the > implementation: fixed > 12. Dump/restore is broken for some cases: fixed > 13. doc/src/sgml/ref/create_table.sgml (5th chunk) has "TODO". Why? fixed > 14. It would be fine if psql has support of new clauses. done > 15. Initializing attidentity in most places is ' ' but makefuncs.c has > "n->identity = 0;". Is it correct? fixed > 16. I think it is a good idea to not raise exceptions for "SET > GENERATED/DROP IDENTITY" if a column has the same type of identity/not > an identity. To be consistent with "SET/DROP NOT NULL". The present behavior is per SQL standard. -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services --------------04BC72F34EAF6B53CB7A5963 Content-Type: text/x-patch; name="v2-identity-columns.patch" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="v2-identity-columns.patch" diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 29738b0..027c73e 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1095,6 +1095,17 @@ <structname>pg_attribute</> Columns =20 + attidentity + char + + + If a space character, then not an identity column. Otherwise, + a =3D generated always, d =3D + generated by default. + + + + attisdropped bool diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/informatio= n_schema.sgml index c43e325..8ece439 100644 --- a/doc/src/sgml/information_schema.sgml +++ b/doc/src/sgml/information_schema.sgml @@ -1583,13 +1583,20 @@ <literal>columns</literal> Columns is_identity yes_or_no - Applies to a feature not available in PostgreSQL= + + If the column is an identity column, then YES, + else NO. + =20 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. + =20 diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_tab= le.sgml index e48ccf2..b272633 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -42,6 +42,9 @@ 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 [ ( se= quence_options ) ] + ALTER [ COLUMN ] column_name { SET GENERATED { ALWAYS | BY DEFAULT } | SET sequence_o= ption | RESET } [...] + ALTER [ COLUMN ] column_name DROP IDENTITY ALTER [ COLUMN ] column_name SET STATISTICS integer ALTER [ COLUMN ] column_name SET ( attribute_option= =3D value [, ... ] ) ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] ) @@ -170,6 +173,32 @@ Description =20 + ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY + SET GENERATED { ALWAYS | BY DEFAULT } + DROP IDENTITY + + + These forms change whether a column is an identity column or change = the + generation attribute of an existing identity column. + See for details. + + + + + + SET sequence_option + RESET + + + These forms alter the sequence that underlies an existing identity + column. sequence_option is an option + supported by such + as INCREMENT BY. + + + + + SET STATISTICS diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_t= able.sgml index bf2ad64..1c04f3d 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -49,6 +49,7 @@ 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 [ ( <= replaceable class=3D"PARAMETER">refcolumn ) ] [ MATCH FULL | = MATCH PARTIAL | MATCH SIMPLE ] @@ -68,7 +69,7 @@ =20 and like_option is:= =20 -{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | C= OMMENTS | ALL } +{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | IDENTITY | INDEXES | = STORAGE | COMMENTS | ALL } =20 index_parameters in= UNIQUE, PRIMARY KEY, and EX= CLUDE constraints are: =20 @@ -338,6 +339,12 @@ Parameters 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, separ= ate + 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 INCLUDING CONSTRAINTS is specified. @@ -369,7 +376,7 @@ Parameters 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 @@ -484,6 +491,35 @@ Parameters =20 + 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. + + + + 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) =20 @@ -1116,7 +1152,7 @@ Notes =20 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 @@ -1176,7 +1212,7 @@ Examples ); =20 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 <> '') ); @@ -1509,6 +1545,20 @@ Zero-column Tables =20 + 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 colu= mns + with different behaviors is not well supported. + + + + <literal>LIKE</> Clause =20 diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml index 06f4160..69b15d0 100644 --- a/doc/src/sgml/ref/insert.sgml +++ b/doc/src/sgml/ref/insert.sgml @@ -23,6 +23,7 @@ [ WITH [ RECURSIVE ] with_query [, ...] ] INSERT INTO table_name [ AS= alias ] [ ( column_name [, ...] ) ] + [ OVERRIDING { SYSTEM | USER} VALUE ] { DEFAULT VALUES | VALUES ( { express= ion | DEFAULT } [, ...] ) [, ...] | query } [ ON CONFLICT [ conflict_target ] conflict_action ] [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ] @@ -202,10 +203,43 @@ Inserting</t= itle> </varlistentry> =20 <varlistentry> + <term><literal>OVERRIDING SYSTEM VALUE</literal></term> + <listitem> + <para> + Without this clause, it is an error to specify an explicit value + (other than <literal>DEFAULT</literal>) for an identity column def= ined + as <literal>GENERATED ALWAYS</literal>. This clause overrides that + restriction. + </para> + </listitem> + </varlistentry> + + <varlistentry> + <term><literal>OVERRIDING USER VALUE</literal></term> + <listitem> + <para> + If this clause is specified, then any values supplied for identity + columns defined as <literal>GENERATED BY DEFAULT</literal> are ign= ored + and the default sequence-generated values are applied. + </para> + + <para> + This clause is useful for example when copying values between tabl= es. + Writing <literal>INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * F= ROM + tbl1</literal> will copy from <literal>tbl1</literal> all columns = that + are not identity columns in <literal>tbl2</literal> but will conti= nue + the sequence counters for any identity columns. + </para> + </listitem> + </varlistentry> + + <varlistentry> <term><literal>DEFAULT VALUES</literal></term> <listitem> <para> All columns will be filled with their default values. + (An <literal>OVERRIDING</literal> clause is not permitted in this + form.) </para> </listitem> </varlistentry> @@ -700,6 +734,13 @@ <title>Compatibility =20 + 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/commo= n/tupdesc.c index b56d0e3..8a8244e 100644 --- a/src/backend/access/common/tupdesc.c +++ b/src/backend/access/common/tupdesc.c @@ -149,6 +149,7 @@ CreateTupleDescCopy(TupleDesc tupdesc) memcpy(desc->attrs[i], tupdesc->attrs[i], ATTRIBUTE_FIXED_PART_SIZE); desc->attrs[i]->attnotnull =3D false; desc->attrs[i]->atthasdef =3D false; + desc->attrs[i]->attidentity =3D ' '; } =20 desc->tdtypeid =3D tupdesc->tdtypeid; @@ -256,6 +257,7 @@ TupleDescCopyEntry(TupleDesc dst, AttrNumber dstAttno, /* since we're not copying constraints or defaults, clear these */ dst->attrs[dstAttno - 1]->attnotnull =3D false; dst->attrs[dstAttno - 1]->atthasdef =3D false; + dst->attrs[dstAttno - 1]->attidentity =3D ' '; } =20 /* @@ -400,6 +402,8 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2) return false; if (attr1->atthasdef !=3D attr2->atthasdef) return false; + if (attr1->attidentity !=3D attr2->attidentity) + return false; if (attr1->attisdropped !=3D attr2->attisdropped) return false; if (attr1->attislocal !=3D attr2->attislocal) @@ -533,6 +537,7 @@ TupleDescInitEntry(TupleDesc desc, =20 att->attnotnull =3D false; att->atthasdef =3D false; + att->attidentity =3D ' '; att->attisdropped =3D false; att->attislocal =3D true; att->attinhcount =3D 0; diff --git a/src/backend/catalog/genbki.pl b/src/backend/catalog/genbki.pl index 26d1652..f0d79c3 100644 --- a/src/backend/catalog/genbki.pl +++ b/src/backend/catalog/genbki.pl @@ -409,6 +409,7 @@ sub emit_pgattr_row attcacheoff =3D> '-1', atttypmod =3D> '-1', atthasdef =3D> 'f', + attidentity =3D> "' '", attisdropped =3D> 'f', attislocal =3D> 't', attinhcount =3D> '0', @@ -425,6 +426,7 @@ sub bki_insert my @attnames =3D @_; my $oid =3D $row->{oid} ? "OID =3D $row->{oid} " : ''; my $bki_values =3D join ' ', map $row->{$_}, @attnames; + $bki_values =3D~ s/'/"/g; printf BKI "insert %s( %s)\n", $oid, $bki_values; } =20 diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c index 0cf7b9e..c88771f 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -139,37 +139,37 @@ static List *insert_ordered_unique_oid(List *list, Oi= d datum); static FormData_pg_attribute a1 =3D { 0, {"ctid"}, TIDOID, 0, sizeof(ItemPointerData), SelfItemPointerAttributeNumber, 0, -1, -1, - false, 'p', 's', true, false, false, true, 0 + false, 'p', 's', true, false, ' ', false, true, 0 }; =20 static FormData_pg_attribute a2 =3D { 0, {"oid"}, OIDOID, 0, sizeof(Oid), ObjectIdAttributeNumber, 0, -1, -1, - true, 'p', 'i', true, false, false, true, 0 + true, 'p', 'i', true, false, ' ', false, true, 0 }; =20 static FormData_pg_attribute a3 =3D { 0, {"xmin"}, XIDOID, 0, sizeof(TransactionId), MinTransactionIdAttributeNumber, 0, -1, -1, - true, 'p', 'i', true, false, false, true, 0 + true, 'p', 'i', true, false, ' ', false, true, 0 }; =20 static FormData_pg_attribute a4 =3D { 0, {"cmin"}, CIDOID, 0, sizeof(CommandId), MinCommandIdAttributeNumber, 0, -1, -1, - true, 'p', 'i', true, false, false, true, 0 + true, 'p', 'i', true, false, ' ', false, true, 0 }; =20 static FormData_pg_attribute a5 =3D { 0, {"xmax"}, XIDOID, 0, sizeof(TransactionId), MaxTransactionIdAttributeNumber, 0, -1, -1, - true, 'p', 'i', true, false, false, true, 0 + true, 'p', 'i', true, false, ' ', false, true, 0 }; =20 static FormData_pg_attribute a6 =3D { 0, {"cmax"}, CIDOID, 0, sizeof(CommandId), MaxCommandIdAttributeNumber, 0, -1, -1, - true, 'p', 'i', true, false, false, true, 0 + true, 'p', 'i', true, false, ' ', false, true, 0 }; =20 /* @@ -181,7 +181,7 @@ static FormData_pg_attribute a6 =3D { static FormData_pg_attribute a7 =3D { 0, {"tableoid"}, OIDOID, 0, sizeof(Oid), TableOidAttributeNumber, 0, -1, -1, - true, 'p', 'i', true, false, false, true, 0 + true, 'p', 'i', true, false, ' ', false, true, 0 }; =20 static const Form_pg_attribute SysAtt[] =3D {&a1, &a2, &a3, &a4, &a5, &a6,= &a7}; @@ -625,6 +625,7 @@ InsertPgAttributeTuple(Relation pg_attribute_rel, values[Anum_pg_attribute_attalign - 1] =3D CharGetDatum(new_attribute->at= talign); values[Anum_pg_attribute_attnotnull - 1] =3D BoolGetDatum(new_attribute->= attnotnull); values[Anum_pg_attribute_atthasdef - 1] =3D BoolGetDatum(new_attribute->a= tthasdef); + values[Anum_pg_attribute_attidentity - 1] =3D CharGetDatum(new_attribute-= >attidentity); values[Anum_pg_attribute_attisdropped - 1] =3D BoolGetDatum(new_attribute= ->attisdropped); values[Anum_pg_attribute_attislocal - 1] =3D BoolGetDatum(new_attribute->= attislocal); values[Anum_pg_attribute_attinhcount - 1] =3D Int32GetDatum(new_attribute= ->attinhcount); diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c index 08b646d..479fb6f 100644 --- a/src/backend/catalog/index.c +++ b/src/backend/catalog/index.c @@ -353,6 +353,7 @@ ConstructTupleDescriptor(Relation heapRelation, to->attcacheoff =3D -1; to->attnotnull =3D false; to->atthasdef =3D false; + to->attidentity =3D ' '; to->attislocal =3D true; to->attinhcount =3D 0; to->attcollation =3D collationObjectId[i]; diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catal= og/information_schema.sql index 00550eb..bd4cf7a 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -646,7 +646,7 @@ CREATE VIEW columns AS CAST(c.relname AS sql_identifier) AS table_name, CAST(a.attname AS sql_identifier) AS column_name, CAST(a.attnum AS cardinal_number) AS ordinal_position, - CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS co= lumn_default, + CAST(CASE WHEN a.atthasdef AND a.attidentity =3D ' ' THEN pg_ge= t_expr(ad.adbin, ad.adrelid) END AS character_data) AS column_default, CAST(CASE WHEN a.attnotnull OR (t.typtype =3D 'd' AND t.typnotn= ull) THEN 'NO' ELSE 'YES' END AS yes_or_no) AS is_nullable, @@ -727,13 +727,13 @@ CREATE VIEW columns AS CAST(a.attnum AS sql_identifier) AS dtd_identifier, CAST('NO' AS yes_or_no) AS is_self_referencing, =20 - 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 'B= Y DEFAULT' END AS character_data) AS identity_generation, + CAST(p.start_value AS character_data) AS identity_start, + CAST(p.increment AS character_data) AS identity_increment, + CAST(p.maximum_value AS character_data) AS identity_maximum, + CAST(p.minimum_value AS character_data) AS identity_minimum, + CAST(CASE WHEN p.cycle_option THEN 'YES' ELSE 'NO' END AS yes_o= r_no) AS identity_cycle, =20 CAST('NEVER' AS character_data) AS is_generated, CAST(null AS character_data) AS generation_expression, @@ -750,6 +750,8 @@ CREATE VIEW columns AS ON (t.typtype =3D 'd' AND t.typbasetype =3D bt.oid) LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnames= pace =3D nco.oid)) ON a.attcollation =3D co.oid AND (nco.nspname, co.collname) <> = ('pg_catalog', 'default') + LEFT JOIN (pg_depend dep JOIN pg_class seq ON (dep.classid =3D 'p= g_class'::regclass AND dep.objid =3D seq.oid AND dep.deptype =3D 'i') JOIN = LATERAL pg_sequence_parameters(seq.oid) p ON true) + ON (dep.refclassid =3D 'pg_attrdef'::regclass AND dep.refobjid = =3D ad.oid) =20 WHERE (NOT pg_is_other_temp_schema(nc.oid)) =20 @@ -1543,6 +1545,7 @@ CREATE VIEW sequences AS FROM pg_namespace nc, pg_class c, LATERAL pg_sequence_parameters(c.oid= ) p WHERE c.relnamespace =3D nc.oid AND c.relkind =3D 'S' + AND NOT EXISTS (SELECT 1 FROM pg_depend WHERE classid =3D 'pg_cl= ass'::regclass AND objid =3D c.oid AND deptype =3D 'i') AND (NOT pg_is_other_temp_schema(nc.oid)) AND (pg_has_role(c.relowner, 'USAGE') OR has_sequence_privilege(c.oid, 'SELECT, UPDATE, USAGE') ); diff --git a/src/backend/catalog/pg_depend.c b/src/backend/catalog/pg_depen= d.c index 7a0713e..0823b88 100644 --- a/src/backend/catalog/pg_depend.c +++ b/src/backend/catalog/pg_depend.c @@ -19,6 +19,7 @@ #include "access/htup_details.h" #include "catalog/dependency.h" #include "catalog/indexing.h" +#include "catalog/pg_attrdef.h" #include "catalog/pg_constraint.h" #include "catalog/pg_depend.h" #include "catalog/pg_extension.h" @@ -554,17 +555,20 @@ markSequenceUnowned(Oid seqId) { deleteDependencyRecordsForClass(RelationRelationId, seqId, RelationRelationId, DEPENDENCY_AUTO); + deleteDependencyRecordsForClass(RelationRelationId, seqId, + AttrDefaultRelationId, DEPENDENCY_INTERNAL); } =20 /* - * 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 =3D NIL; Relation depRel; - ScanKeyData key[2]; + ScanKeyData key[3]; SysScanDesc scan; HeapTuple tup; =20 @@ -578,9 +582,14 @@ 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)); =20 scan =3D systable_beginscan(depRel, DependReferenceIndexId, true, - NULL, 2, key); + NULL, attnum ? 3 : 2, key); =20 while (HeapTupleIsValid(tup =3D systable_getnext(scan))) { diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequenc= e.c index fc3a8ee..7c965c0 100644 --- a/src/backend/commands/sequence.c +++ b/src/backend/commands/sequence.c @@ -22,8 +22,10 @@ #include "access/xloginsert.h" #include "access/xlogutils.h" #include "catalog/dependency.h" +#include "catalog/indexing.h" #include "catalog/namespace.h" #include "catalog/objectaccess.h" +#include "catalog/pg_attrdef.h" #include "catalog/pg_type.h" #include "commands/defrem.h" #include "commands/sequence.h" @@ -36,6 +38,7 @@ #include "storage/smgr.h" #include "utils/acl.h" #include "utils/builtins.h" +#include "utils/fmgroids.h" #include "utils/lsyscache.h" #include "utils/resowner.h" #include "utils/syscache.h" @@ -95,9 +98,9 @@ static void init_sequence(Oid relid, SeqTable *p_elm, Rel= ation *p_rel); static Form_pg_sequence read_seq_tuple(SeqTable elm, Relation rel, Buffer *buf, HeapTuple seqtuple); static void init_params(ParseState *pstate, List *options, bool isInit, - Form_pg_sequence new, List **owned_by); + Form_pg_sequence new, List **owned_by, DependencyType *deptype); 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, DependencyTy= pe deptype); =20 =20 /* @@ -109,6 +112,7 @@ DefineSequence(ParseState *pstate, CreateSeqStmt *seq) { FormData_pg_sequence new; List *owned_by; + DependencyType deptype; CreateStmt *stmt =3D makeNode(CreateStmt); Oid seqoid; ObjectAddress address; @@ -145,7 +149,7 @@ DefineSequence(ParseState *pstate, CreateSeqStmt *seq) } =20 /* Check and set all option values */ - init_params(pstate, seq->options, true, &new, &owned_by); + init_params(pstate, seq->options, true, &new, &owned_by, &deptype); =20 /* * Create relation (and fill value[] and null[] for the tuple) @@ -162,6 +166,7 @@ DefineSequence(ParseState *pstate, CreateSeqStmt *seq) coldef->storage =3D 0; coldef->raw_default =3D NULL; coldef->cooked_default =3D NULL; + coldef->identity =3D ' '; coldef->collClause =3D NULL; coldef->collOid =3D InvalidOid; coldef->constraints =3D NIL; @@ -247,7 +252,7 @@ DefineSequence(ParseState *pstate, CreateSeqStmt *seq) =20 /* process OWNED BY if given */ if (owned_by) - process_owned_by(rel, owned_by); + process_owned_by(rel, owned_by, deptype); =20 heap_close(rel, NoLock); =20 @@ -414,6 +419,7 @@ AlterSequence(ParseState *pstate, AlterSeqStmt *stmt) Form_pg_sequence seq; FormData_pg_sequence new; List *owned_by; + DependencyType deptype; ObjectAddress address; =20 /* Open and lock sequence. */ @@ -440,7 +446,7 @@ AlterSequence(ParseState *pstate, AlterSeqStmt *stmt) memcpy(&new, seq, sizeof(FormData_pg_sequence)); =20 /* Check and set new values */ - init_params(pstate, stmt->options, false, &new, &owned_by); + init_params(pstate, stmt->options, false, &new, &owned_by, &deptype); =20 /* Clear local cache so that we don't think we have cached numbers */ /* Note that we do not change the currval() state */ @@ -483,7 +489,7 @@ AlterSequence(ParseState *pstate, AlterSeqStmt *stmt) =20 /* process OWNED BY if given */ if (owned_by) - process_owned_by(seqrel, owned_by); + process_owned_by(seqrel, owned_by, deptype); =20 InvokeObjectPostAlterHook(RelationRelationId, relid, 0); =20 @@ -1164,7 +1170,7 @@ read_seq_tuple(SeqTable elm, Relation rel, Buffer *bu= f, HeapTuple seqtuple) */ static void init_params(ParseState *pstate, List *options, bool isInit, - Form_pg_sequence new, List **owned_by) + Form_pg_sequence new, List **owned_by, DependencyType *deptype) { DefElem *start_value =3D NULL; DefElem *restart_value =3D NULL; @@ -1173,9 +1179,11 @@ init_params(ParseState *pstate, List *options, bool = isInit, DefElem *min_value =3D NULL; DefElem *cache_value =3D NULL; DefElem *is_cycled =3D NULL; + DefElem *deptype_el =3D NULL; ListCell *option; =20 *owned_by =3D NIL; + *deptype =3D DEPENDENCY_AUTO; =20 foreach(option, options) { @@ -1253,6 +1261,15 @@ init_params(ParseState *pstate, List *options, bool = isInit, parser_errposition(pstate, defel->location))); *owned_by =3D defGetQualifiedName(defel); } + else if (strcmp(defel->defname, "deptype") =3D=3D 0) + { + if (deptype_el) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + deptype_el =3D defel; + *deptype =3D intVal(deptype_el->arg); + } else elog(ERROR, "option \"%s\" not recognized", defel->defname); @@ -1431,6 +1448,38 @@ init_params(ParseState *pstate, List *options, bool = isInit, new->cache_value =3D 1; } =20 +static Oid +get_attrdef_oid(Oid relid, AttrNumber attnum) +{ + Relation attrdef_rel; + ScanKeyData scankeys[2]; + SysScanDesc scan; + HeapTuple tuple; + Oid result =3D InvalidOid; + + attrdef_rel =3D heap_open(AttrDefaultRelationId, RowExclusiveLock); + + ScanKeyInit(&scankeys[0], + Anum_pg_attrdef_adrelid, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(relid)); + ScanKeyInit(&scankeys[1], + Anum_pg_attrdef_adnum, + BTEqualStrategyNumber, F_INT2EQ, + Int16GetDatum(attnum)); + + scan =3D systable_beginscan(attrdef_rel, AttrDefaultIndexId, true, + NULL, 2, scankeys); + + if (HeapTupleIsValid(tuple =3D systable_getnext(scan))) + result =3D HeapTupleGetOid(tuple); + + systable_endscan(scan); + heap_close(attrdef_rel, RowExclusiveLock); + + return result; +} + /* * Process an OWNED BY option for CREATE/ALTER SEQUENCE * @@ -1440,7 +1489,7 @@ init_params(ParseState *pstate, List *options, bool i= sInit, * as the sequence. */ static void -process_owned_by(Relation seqrel, List *owned_by) +process_owned_by(Relation seqrel, List *owned_by, DependencyType deptype) { int nnames; Relation tablerel; @@ -1518,6 +1567,20 @@ process_owned_by(Relation seqrel, List *owned_by) depobject.objectId =3D RelationGetRelid(seqrel); depobject.objectSubId =3D 0; recordDependencyOn(&depobject, &refobject, DEPENDENCY_AUTO); + + /* + * For identity columns, also record an internal dependency of the + * sequence on the default (you drop the default, the sequence is + * removed). We still do the auto dependency on the column, because + * that is what TRUNCATE RESTART IDENTITY looks for. + */ + if (deptype =3D=3D DEPENDENCY_INTERNAL) + { + refobject.classId =3D AttrDefaultRelationId; + refobject.objectId =3D get_attrdef_oid(RelationGetRelid(tablerel), attn= um); + refobject.objectSubId =3D 0; + recordDependencyOn(&depobject, &refobject, DEPENDENCY_INTERNAL); + } } =20 /* Done, but hold lock until commit */ @@ -1527,6 +1590,35 @@ process_owned_by(Relation seqrel, List *owned_by) =20 =20 /* + * Return sequence parameters in a list of the form created by the parser. + */ +List * +sequence_options(Oid relid) +{ + SeqTable elm; + Relation seqrel; + Buffer buf; + HeapTupleData seqtuple; + Form_pg_sequence seq; + List *options =3D NIL; + + init_sequence(relid, &elm, &seqrel); + seq =3D read_seq_tuple(elm, seqrel, &buf, &seqtuple); + + options =3D lappend(options, makeDefElem("cache", (Node *) makeInteger(se= q->cache_value), -1)); + options =3D lappend(options, makeDefElem("cycle", (Node *) makeInteger(se= q->is_cycled), -1)); + options =3D lappend(options, makeDefElem("increment", (Node *) makeIntege= r(seq->increment_by), -1)); + options =3D lappend(options, makeDefElem("maxvalue", (Node *) makeInteger= (seq->max_value), -1)); + options =3D lappend(options, makeDefElem("minvalue", (Node *) makeInteger= (seq->min_value), -1)); + options =3D lappend(options, makeDefElem("start", (Node *) makeInteger(se= q->start_value), -1)); + + UnlockReleaseBuffer(buf); + relation_close(seqrel, NoLock); + + return options; +} + +/* * Return sequence parameters, for use by information schema */ Datum diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablec= mds.c index 2137372..b99e59b 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -344,6 +344,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,= LOCKMODE lockmode); static void ATPrepSetStatistics(Relation rel, const char *colName, Node *newValue, LOCKMODE lockmode); static ObjectAddress ATExecSetStatistics(Relation rel, const char *colName, @@ -650,6 +655,9 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid owne= rId, cookedDefaults =3D lappend(cookedDefaults, cooked); descriptor->attrs[attnum - 1]->atthasdef =3D true; } + + if (colDef->identity) + descriptor->attrs[attnum - 1]->attidentity =3D colDef->identity; } =20 /* @@ -1113,7 +1121,7 @@ ExecuteTruncate(TruncateStmt *stmt) foreach(cell, rels) { Relation rel =3D (Relation) lfirst(cell); - List *seqlist =3D getOwnedSequences(RelationGetRelid(rel)); + List *seqlist =3D getOwnedSequences(RelationGetRelid(rel), 0); ListCell *seqcell; =20 foreach(seqcell, seqlist) @@ -1663,6 +1671,7 @@ MergeAttributes(List *schema, List *supers, char relp= ersistence, def->storage =3D attribute->attstorage; def->raw_default =3D NULL; def->cooked_default =3D NULL; + def->identity =3D attribute->attidentity; def->collClause =3D NULL; def->collOid =3D attribute->attcollation; def->constraints =3D NIL; @@ -1851,6 +1860,13 @@ MergeAttributes(List *schema, List *supers, char rel= persistence, get_collation_name(defcollid), get_collation_name(newcollid)))); =20 + if (def->identity !=3D ' ' && newdef->identity !=3D ' ') + ereport(ERROR, + (errcode(ERRCODE_DATATYPE_MISMATCH), + errmsg("column \"%s\" has conflicting identity definitions", + attributeName))); + def->identity =3D newdef->identity; + /* Copy storage parameter */ if (def->storage =3D=3D 0) def->storage =3D newdef->storage; @@ -2941,6 +2957,9 @@ AlterTableGetLockLevel(List *cmds) case AT_DisableRowSecurity: case AT_ForceRowSecurity: case AT_NoForceRowSecurity: + case AT_AddIdentity: + case AT_DropIdentity: + case AT_SetIdentity: cmd_lockmode =3D AccessExclusiveLock; break; =20 @@ -3154,6 +3173,8 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd = *cmd, pass =3D AT_PASS_ADD_COL; break; case AT_ColumnDefault: /* ALTER COLUMN DEFAULT */ + case AT_AddIdentity: + case AT_DropIdentity: =20 /* * We allow defaults on views so that INSERT into a view can have @@ -3166,6 +3187,11 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd= *cmd, /* No command-specific prep needed */ pass =3D cmd->def ? AT_PASS_ADD_CONSTR : AT_PASS_DROP; break; + case AT_SetIdentity: + ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE); + ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode); + pass =3D AT_PASS_COL_ATTRS; + break; case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */ ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE); ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode); @@ -3476,6 +3502,15 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Rela= tion rel, case AT_ColumnDefault: /* ALTER COLUMN DEFAULT */ address =3D ATExecColumnDefault(rel, cmd->name, cmd->def, lockmode); break; + case AT_AddIdentity: + address =3D ATExecAddIdentity(rel, cmd->name, cmd->def, lockmode); + break; + case AT_SetIdentity: + address =3D ATExecSetIdentity(rel, cmd->name, cmd->def, lockmode); + break; + case AT_DropIdentity: + address =3D ATExecDropIdentity(rel, cmd->name, lockmode); + break; case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */ address =3D ATExecDropNotNull(rel, cmd->name, lockmode); break; @@ -4850,6 +4885,7 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab,= Relation rel, attribute.attalign =3D tform->typalign; attribute.attnotnull =3D colDef->is_not_null; attribute.atthasdef =3D false; + attribute.attidentity =3D ' '; attribute.attisdropped =3D false; attribute.attislocal =3D colDef->is_local; attribute.attinhcount =3D colDef->inhcount; @@ -5159,6 +5195,7 @@ ATPrepAddOids(List **wqueue, Relation rel, bool recur= se, AlterTableCmd *cmd, LOC cdef->is_local =3D true; cdef->is_not_null =3D true; cdef->storage =3D 0; + cdef->identity =3D ' '; cdef->location =3D -1; cmd->def =3D (Node *) cdef; } @@ -5370,6 +5407,12 @@ ATExecColumnDefault(Relation rel, const char *colNam= e, errmsg("cannot alter system column \"%s\"", colName))); =20 + if (get_attidentity(RelationGetRelid(rel), attnum) !=3D ' ') + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("column \"%s\" of relation \"%s\" is an identity column", + colName, RelationGetRelationName(rel)))); + /* * 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 @@ -5405,6 +5448,206 @@ ATExecColumnDefault(Relation rel, const char *colNa= me, } =20 /* + * 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; + RawColumnDefault *rawEnt; + ObjectAddress address; + ColumnDef *cdef =3D (ColumnDef *) def; + + Assert(IsA(def, ColumnDef)); + + attrelation =3D heap_open(AttributeRelationId, RowExclusiveLock); + + tuple =3D 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 =3D (Form_pg_attribute) GETSTRUCT(tuple); + attnum =3D attTup->attnum; + + /* Can't alter a system attribute */ + if (attnum <=3D 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot alter system column \"%s\"", + colName))); + + if (attTup->attidentity !=3D ' ') + ereport(ERROR, + (errmsg("column \"%s\" of relation \"%s\" is already an identity colum= n", + colName, RelationGetRelationName(rel)))); + + if (attTup->atthasdef) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("column \"%s\" of relation \"%s\" already has a default value", + colName, RelationGetRelationName(rel)))); + + attTup->attidentity =3D cdef->identity; + simple_heap_update(attrelation, &tuple->t_self, tuple); + CatalogUpdateIndexes(attrelation, tuple); + + InvokeObjectPostAlterHook(RelationRelationId, + RelationGetRelid(rel), + attTup->attnum); + ObjectAddressSubSet(address, RelationRelationId, + RelationGetRelid(rel), attnum); + heap_freetuple(tuple); + + CommandCounterIncrement(); + + rawEnt =3D (RawColumnDefault *) palloc(sizeof(RawColumnDefault)); + rawEnt->attnum =3D attnum; + rawEnt->raw_default =3D cdef->raw_default; + + AddRelationNewConstraints(rel, list_make1(rawEnt), NIL, + false, true, false); + + heap_close(attrelation, RowExclusiveLock); + + return address; +} + +static ObjectAddress +ATExecSetIdentity(Relation rel, const char *colName, Node *def, LOCKMODE l= ockmode) +{ + ListCell *option; + DefElem *generatedEl =3D NULL; + HeapTuple tuple; + Form_pg_attribute attTup; + AttrNumber attnum; + Relation attrelation; + ObjectAddress address; + + foreach(option, (List *) def) + { + DefElem *defel =3D (DefElem *) lfirst(option); + + if (strcmp(defel->defname, "generated") =3D=3D 0) + { + if (generatedEl) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + generatedEl =3D 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 =3D heap_open(AttributeRelationId, RowExclusiveLock); + tuple =3D 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 =3D (Form_pg_attribute) GETSTRUCT(tuple); + attnum =3D attTup->attnum; + + if (attnum <=3D 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot alter system column \"%s\"", + colName))); + + if (attTup->attidentity =3D=3D ' ') + ereport(ERROR, + (errmsg("column \"%s\" of relation \"%s\" is not an identity column", + colName, RelationGetRelationName(rel)))); + + if (generatedEl) + { + attTup->attidentity =3D defGetInt32(generatedEl); + simple_heap_update(attrelation, &tuple->t_self, tuple); + CatalogUpdateIndexes(attrelation, 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, LOCKMODE lockmode) +{ + HeapTuple tuple; + Form_pg_attribute attTup; + AttrNumber attnum; + Relation attrelation; + ObjectAddress address; + + attrelation =3D heap_open(AttributeRelationId, RowExclusiveLock); + tuple =3D 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 =3D (Form_pg_attribute) GETSTRUCT(tuple); + attnum =3D attTup->attnum; + + if (attnum <=3D 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot alter system column \"%s\"", + colName))); + + if (attTup->attidentity =3D=3D ' ') + ereport(ERROR, + (errmsg("column \"%s\" of relation \"%s\" is not an identity column", + colName, RelationGetRelationName(rel)))); + + attTup->attidentity =3D ' '; + simple_heap_update(attrelation, &tuple->t_self, tuple); + CatalogUpdateIndexes(attrelation, tuple); + + InvokeObjectPostAlterHook(RelationRelationId, + RelationGetRelid(rel), + attTup->attnum); + ObjectAddressSubSet(address, RelationRelationId, + RelationGetRelid(rel), attnum); + heap_freetuple(tuple); + + CommandCounterIncrement(); + + RemoveAttrDefault(RelationGetRelid(rel), attnum, DROP_RESTRICT, false, + true); + + heap_close(attrelation, RowExclusiveLock); + + return address; +} + +/* * ALTER TABLE ALTER COLUMN SET STATISTICS */ static void @@ -7913,6 +8156,14 @@ ATPrepAlterColumnType(List **wqueue, errmsg("cannot alter inherited column \"%s\"", colName))); =20 + /* Don't alter identity columns. (XXX This could be done, but needs more + * work to check permitted types, adjust sequence.) */ + if (attTup->attidentity !=3D ' ') + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot alter data type of identity column \"%s\"", + colName))); + /* Look up the target type */ typenameTypeIdAndMod(NULL, typeName, &targettype, &targettypmod); =20 diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 71714bc..73392b0 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -2624,6 +2624,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); @@ -2646,6 +2647,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); @@ -2742,6 +2744,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); @@ -2771,6 +2774,7 @@ _copyInsertStmt(const InsertStmt *from) COPY_NODE_FIELD(onConflictClause); COPY_NODE_FIELD(returningList); COPY_NODE_FIELD(withClause); + COPY_SCALAR_FIELD(override); =20 return newnode; } diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 29a090f..0bee88e 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -932,6 +932,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); @@ -959,6 +960,7 @@ _equalInsertStmt(const InsertStmt *a, const InsertStmt = *b) COMPARE_NODE_FIELD(onConflictClause); COMPARE_NODE_FIELD(returningList); COMPARE_NODE_FIELD(withClause); + COMPARE_SCALAR_FIELD(override); =20 return true; } @@ -2378,6 +2380,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); @@ -2398,6 +2401,7 @@ _equalConstraint(const Constraint *a, const Constrain= t *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); diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c index 20e2dbd..08bd23a 100644 --- a/src/backend/nodes/makefuncs.c +++ b/src/backend/nodes/makefuncs.c @@ -497,6 +497,7 @@ makeColumnDef(const char *colname, Oid typeOid, int32 t= ypmod, Oid collOid) n->storage =3D 0; n->raw_default =3D NULL; n->cooked_default =3D NULL; + n->identity =3D ' '; n->collClause =3D NULL; n->collOid =3D collOid; n->constraints =3D NIL; diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index ae86954..a2b3818 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -2575,6 +2575,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); @@ -2680,6 +2681,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); @@ -3174,6 +3176,13 @@ _outConstraint(StringInfo str, const Constraint *nod= e) WRITE_STRING_FIELD(cooked_expr); break; =20 + 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 917e6c8..163699c 100644 --- a/src/backend/nodes/readfuncs.c +++ b/src/backend/nodes/readfuncs.c @@ -249,6 +249,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 6901e08..fc59260 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -464,6 +464,8 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stm= t) qry->hasModifyingCTE =3D pstate->p_hasModifyingCTE; } =20 + qry->override =3D stmt->override; + isOnConflictUpdate =3D (stmt->onConflictClause && stmt->onConflictClause->action =3D=3D ONCONFLICT_UPDATE); =20 diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 5547fc8..b6a4026 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -278,6 +278,8 @@ static Node *makeRecursiveViewSelect(char *relname, Lis= t *aliases, Node *query); %type alter_table_cmd alter_type_cmd opt_collate_clause replica_identity %type alter_table_cmds alter_type_cmds +%type alter_identity_column_option_list +%type alter_identity_column_option =20 %type opt_drop_behavior =20 @@ -424,7 +426,7 @@ static Node *makeRecursiveViewSelect(char *relname, Lis= t *aliases, Node *query); select_offset_value2 opt_select_fetch_first_value %type row_or_rows first_or_next =20 -%type OptSeqOptList SeqOptList +%type OptSeqOptList SeqOptList OptParenthesizedSeqOptList %type SeqOptElem =20 %type insert_rest @@ -541,6 +543,7 @@ static Node *makeRecursiveViewSelect(char *relname, Lis= t *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 =20 /* * Non-keyword token types. These are hard-wired into the "flex" lexer. @@ -591,7 +594,7 @@ static Node *makeRecursiveViewSelect(char *relname, Lis= t *aliases, Node *query); FALSE_P FAMILY FETCH FILTER FIRST_P FLOAT_P FOLLOWING FOR FORCE FOREIGN FORWARD FREEZE FROM FULL FUNCTION FUNCTIONS =20 - GLOBAL GRANT GRANTED GREATEST GROUP_P GROUPING + GENERATED GLOBAL GRANT GRANTED GREATEST GROUP_P GROUPING =20 HANDLER HAVING HEADER_P HOLD HOUR_P =20 @@ -615,7 +618,7 @@ static Node *makeRecursiveViewSelect(char *relname, Lis= t *aliases, Node *query); NULLS_P NUMERIC =20 OBJECT_P OF OFF OFFSET OIDS 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 OWN= ER =20 PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POLICY POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY @@ -2026,6 +2029,40 @@ alter_table_cmd: n->def =3D (Node *) makeString($6); $$ =3D (Node *)n; } + /* ALTER TABLE ALTER [COLUMN] ADD GENERATED ... AS IDE= NTITY ... */ + | ALTER opt_column ColId ADD_P GENERATED generated_when AS IDENTITY_P O= ptParenthesizedSeqOptList + { + AlterTableCmd *n =3D makeNode(AlterTableCmd); + Constraint *c =3D makeNode(Constraint); + + c->contype =3D CONSTR_IDENTITY; + c->generated_when =3D $6; + c->options =3D $9; + c->location =3D @5; + + n->subtype =3D AT_AddIdentity; + n->name =3D $3; + n->def =3D (Node *) c; + + $$ =3D (Node *)n; + } + /* ALTER TABLE ALTER [COLUMN] SET /R= ESET */ + | ALTER opt_column ColId alter_identity_column_option_list + { + AlterTableCmd *n =3D makeNode(AlterTableCmd); + n->subtype =3D AT_SetIdentity; + n->name =3D $3; + n->def =3D (Node *) $4; + $$ =3D (Node *)n; + } + /* ALTER TABLE ALTER [COLUMN] DROP IDENTITY */ + | ALTER opt_column ColId DROP IDENTITY_P + { + AlterTableCmd *n =3D makeNode(AlterTableCmd); + n->subtype =3D AT_DropIdentity; + n->name =3D $3; + $$ =3D (Node *)n; + } /* ALTER TABLE DROP [COLUMN] IF EXISTS [RESTRICT|CASCA= DE] */ | DROP opt_column IF_P EXISTS ColId opt_drop_behavior { @@ -2463,6 +2500,38 @@ reloption_elem: } ; =20 +alter_identity_column_option_list: + alter_identity_column_option + { $$ =3D list_make1($1); } + | alter_identity_column_option_list alter_identity_column_option + { $$ =3D lappend($1, $2); } + ; + +alter_identity_column_option: + RESTART + { + $$ =3D makeDefElem("restart", NULL, @1); + } + | RESTART opt_with NumericOnly + { + $$ =3D makeDefElem("restart", (Node *)$3, @1); + } + | SET SeqOptElem + { + if (strcmp($2->defname, "restart") =3D=3D 0 || + strcmp($2->defname, "owned_by") =3D=3D 0) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("sequence option \"%s\" not supported here", $2->defname), + parser_errposition(@2))); + $$ =3D $2; + } + | SET GENERATED generated_when + { + $$ =3D makeDefElem("generated", (Node *) makeInteger($3), @1); + } + ; + =20 /*************************************************************************= **** * @@ -2964,6 +3033,7 @@ columnDef: ColId Typename create_generic_options ColQ= ualList n->storage =3D 0; n->raw_default =3D NULL; n->cooked_default =3D NULL; + n->identity =3D ' '; n->collOid =3D InvalidOid; n->fdwoptions =3D $3; SplitColQualList($4, &n->constraints, &n->collClause, @@ -2985,6 +3055,7 @@ columnOptions: ColId WITH OPTIONS ColQualList n->storage =3D 0; n->raw_default =3D NULL; n->cooked_default =3D NULL; + n->identity =3D ' '; n->collOid =3D InvalidOid; SplitColQualList($4, &n->constraints, &n->collClause, yyscanner); @@ -3097,6 +3168,15 @@ ColConstraintElem: n->cooked_expr =3D NULL; $$ =3D (Node *)n; } + | GENERATED generated_when AS IDENTITY_P OptParenthesizedSeqOptList + { + Constraint *n =3D makeNode(Constraint); + n->contype =3D CONSTR_IDENTITY; + n->generated_when =3D $2; + n->options =3D $5; + n->location =3D @1; + $$ =3D (Node *)n; + } | REFERENCES qualified_name opt_column_list key_match key_actions { Constraint *n =3D makeNode(Constraint); @@ -3114,6 +3194,11 @@ ColConstraintElem: } ; =20 +generated_when: + ALWAYS { $$ =3D CONSTR_GENERATED_ALWAYS; } + | BY DEFAULT { $$ =3D CONSTR_GENERATED_DEFAULT; } + ; + /* * ConstraintAttr represents constraint attributes, which we parse as if * they were independent constraint clauses, in order to avoid shift/reduce @@ -3180,6 +3265,7 @@ TableLikeOptionList: TableLikeOption: DEFAULTS { $$ =3D CREATE_TABLE_LIKE_DEFAULTS; } | CONSTRAINTS { $$ =3D CREATE_TABLE_LIKE_CONSTRAINTS; } + | IDENTITY_P { $$ =3D CREATE_TABLE_LIKE_IDENTITY; } | INDEXES { $$ =3D CREATE_TABLE_LIKE_INDEXES; } | STORAGE { $$ =3D CREATE_TABLE_LIKE_STORAGE; } | COMMENTS { $$ =3D CREATE_TABLE_LIKE_COMMENTS; } @@ -3630,6 +3716,10 @@ OptSeqOptList: SeqOptList { $$ =3D $1; } | /*EMPTY*/ { $$ =3D NIL; } ; =20 +OptParenthesizedSeqOptList: '(' SeqOptList ')' { $$ =3D $2; } + | /*EMPTY*/ { $$ =3D NIL; } + ; + SeqOptList: SeqOptElem { $$ =3D list_make1($1); } | SeqOptList SeqOptElem { $$ =3D lappend($1, $2); } ; @@ -3670,6 +3760,11 @@ SeqOptElem: CACHE NumericOnly { $$ =3D makeDefElem("owned_by", (Node *)$3, @1); } + | SEQUENCE NAME_P any_name + { + /* not documented, only used by pg_dump */ + $$ =3D makeDefElem("sequence_name", (Node *)$3, @1); + } | START opt_with NumericOnly { $$ =3D makeDefElem("start", (Node *)$3, @1); @@ -9678,12 +9773,26 @@ insert_rest: $$->cols =3D NIL; $$->selectStmt =3D $1; } + | OVERRIDING override_kind VALUE_P SelectStmt + { + $$ =3D makeNode(InsertStmt); + $$->cols =3D NIL; + $$->override =3D $2; + $$->selectStmt =3D $4; + } | '(' insert_column_list ')' SelectStmt { $$ =3D makeNode(InsertStmt); $$->cols =3D $2; $$->selectStmt =3D $4; } + | '(' insert_column_list ')' OVERRIDING override_kind VALUE_P SelectStmt + { + $$ =3D makeNode(InsertStmt); + $$->cols =3D $2; + $$->override =3D $5; + $$->selectStmt =3D $7; + } | DEFAULT VALUES { $$ =3D makeNode(InsertStmt); @@ -9692,6 +9801,11 @@ insert_rest: } ; =20 +override_kind: + USER { $$ =3D OVERRIDING_USER_VALUE; } + | SYSTEM_P { $$ =3D OVERRIDING_SYSTEM_VALUE; } + ; + insert_column_list: insert_column_item { $$ =3D list_make1($1); } @@ -11088,6 +11202,7 @@ TableFuncElement: ColId Typename opt_collate_clause n->storage =3D 0; n->raw_default =3D NULL; n->cooked_default =3D NULL; + n->identity =3D ' '; n->collClause =3D (CollateClause *) $3; n->collOid =3D InvalidOid; n->constraints =3D NIL; @@ -13825,6 +13940,7 @@ unreserved_keyword: | OPTIONS | ORDINALITY | OVER + | OVERRIDING | OWNED | OWNER | PARALLEL @@ -14083,6 +14199,7 @@ reserved_keyword: | FOR | FOREIGN | FROM + | GENERATED | GRANT | GROUP_P | HAVING diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_= utilcmd.c index 0670bc2..41f0c69 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" @@ -343,6 +344,150 @@ transformCreateStmt(CreateStmt *stmt, const char *que= ryString) return result; } =20 +static void +generateSerialExtraStmts(CreateStmtContext *cxt, ColumnDef *column, List *= seqoptions, DependencyType deptype, + char **snamespace_p, char **sname_p) +{ + ListCell *option; + DefElem *nameEl =3D 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 =3D (DefElem *) lfirst(option); + + if (strcmp(defel->defname, "sequence_name") =3D=3D 0) + { + if (nameEl) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + nameEl =3D defel; + } + } + + if (nameEl) + { + RangeVar *rv =3D makeRangeVarFromNameList((List *) nameEl->arg); + snamespace =3D rv->schemaname; + sname =3D rv->relname; + list_delete_ptr(seqoptions, nameEl); + } + else + { + if (cxt->rel) + snamespaceid =3D RelationGetNamespace(cxt->rel); + else + { + snamespaceid =3D RangeVarGetCreationNamespace(cxt->relation); + RangeVarAdjustRelationPersistence(cxt->relation, snamespaceid); + } + snamespace =3D get_namespace_name(snamespaceid); + sname =3D 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 =3D makeNode(CreateSeqStmt); + seqstmt->sequence =3D makeRangeVar(snamespace, sname, -1); + seqstmt->options =3D seqoptions; + + /* + * 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 =3D cxt->rel->rd_rel->relowner; + else + seqstmt->ownerId =3D InvalidOid; + + cxt->blist =3D 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 =3D makeNode(AlterSeqStmt); + altseqstmt->sequence =3D makeRangeVar(snamespace, sname, -1); + attnamelist =3D list_make3(makeString(snamespace), + makeString(cxt->relation->relname), + makeString(column->colname)); + altseqstmt->options =3D list_make2(makeDefElem("owned_by", + (Node *) attnamelist, -1), + makeDefElem("deptype", + (Node *) makeInteger(deptype), -1)); + + cxt->alist =3D lappend(cxt->alist, altseqstmt); + + *snamespace_p =3D snamespace; + *sname_p =3D sname; +} + +/* + * Create an expression tree representing the function call + * nextval('sequencename'). We cannot reduce the raw tree to cooked + * form until after the sequence is created, but there's no need to do + * so. + */ +static FuncCall * +generateNextvalExpr(char *snamespace, char *sname) +{ + char *qstring; + A_Const *snamenode; + TypeCast *castnode; + FuncCall *funccallnode; + + qstring =3D quote_qualified_identifier(snamespace, sname); + snamenode =3D makeNode(A_Const); + snamenode->val.type =3D T_String; + snamenode->val.val.str =3D qstring; + snamenode->location =3D -1; + castnode =3D makeNode(TypeCast); + castnode->typeName =3D SystemTypeName("regclass"); + castnode->arg =3D (Node *) snamenode; + castnode->location =3D -1; + funccallnode =3D makeFuncCall(SystemFuncName("nextval"), + list_make1(castnode), + -1); + + return funccallnode; +} + /* * transformColumnDefinition - * transform a single ColumnDef within CREATE TABLE @@ -354,7 +499,7 @@ transformColumnDefinition(CreateStmtContext *cxt, Colum= nDef *column) bool is_serial; bool saw_nullable; bool saw_default; - Constraint *constraint; + bool saw_identity; ListCell *clist; =20 cxt->columns =3D lappend(cxt->columns, column); @@ -409,110 +554,21 @@ transformColumnDefinition(CreateStmtContext *cxt, Co= lumnDef *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; - - /* - * 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 =3D RelationGetNamespace(cxt->rel); - else - { - snamespaceid =3D RangeVarGetCreationNamespace(cxt->relation); - RangeVarAdjustRelationPersistence(cxt->relation, snamespaceid); - } - snamespace =3D get_namespace_name(snamespaceid); - sname =3D 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 =3D makeNode(CreateSeqStmt); - seqstmt->sequence =3D makeRangeVar(snamespace, sname, -1); - seqstmt->options =3D NIL; - - /* - * 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 =3D cxt->rel->rd_rel->relowner; - else - seqstmt->ownerId =3D InvalidOid; - - cxt->blist =3D 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 =3D makeNode(AlterSeqStmt); - altseqstmt->sequence =3D makeRangeVar(snamespace, sname, -1); - attnamelist =3D list_make3(makeString(snamespace), - makeString(cxt->relation->relname), - makeString(column->colname)); - altseqstmt->options =3D list_make1(makeDefElem("owned_by", - (Node *) attnamelist, -1)); + Constraint *constraint; =20 - cxt->alist =3D lappend(cxt->alist, altseqstmt); + generateSerialExtraStmts(cxt, column, NIL, DEPENDENCY_AUTO, &snamespace,= &sname); =20 /* * Create appropriate constraints for SERIAL. We do this in full, * rather than shortcutting, so that we will detect any conflicting * constraints the user wrote (like a different DEFAULT). - * - * Create an expression tree representing the function call - * nextval('sequencename'). We cannot reduce the raw tree to cooked - * form until after the sequence is created, but there's no need to do - * so. */ - qstring =3D quote_qualified_identifier(snamespace, sname); - snamenode =3D makeNode(A_Const); - snamenode->val.type =3D T_String; - snamenode->val.val.str =3D qstring; - snamenode->location =3D -1; - castnode =3D makeNode(TypeCast); - castnode->typeName =3D SystemTypeName("regclass"); - castnode->arg =3D (Node *) snamenode; - castnode->location =3D -1; - funccallnode =3D makeFuncCall(SystemFuncName("nextval"), - list_make1(castnode), - -1); constraint =3D makeNode(Constraint); constraint->contype =3D CONSTR_DEFAULT; constraint->location =3D -1; - constraint->raw_expr =3D (Node *) funccallnode; + constraint->raw_expr =3D (Node *) generateNextvalExpr(snamespace, sname); constraint->cooked_expr =3D NULL; column->constraints =3D lappend(column->constraints, constraint); =20 @@ -527,9 +583,12 @@ transformColumnDefinition(CreateStmtContext *cxt, Colu= mnDef *column) =20 saw_nullable =3D false; saw_default =3D false; + saw_identity =3D false; =20 foreach(clist, column->constraints) { + Constraint *constraint; + constraint =3D lfirst(clist); Assert(IsA(constraint, Constraint)); =20 @@ -569,9 +628,43 @@ transformColumnDefinition(CreateStmtContext *cxt, Colu= mnDef *column) constraint->location))); column->raw_default =3D constraint->raw_expr; Assert(constraint->cooked_expr =3D=3D NULL); + column->identity =3D ' '; saw_default =3D true; break; =20 + case CONSTR_IDENTITY: + { + char *snamespace; + char *sname; + Type ctype; + Oid typeOid; + + ctype =3D typenameType(cxt->pstate, column->typeName, NULL); + typeOid =3D HeapTupleGetOid(ctype); + ReleaseSysCache(ctype); + + if (!(typeOid =3D=3D INT2OID || typeOid =3D=3D INT4OID || typeOid =3D= =3D INT8OID)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("identity column type must be smallint, integer, or bigint"= ))); + + if (saw_identity) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("multiple identity specifications for column \"%s\" of tabl= e \"%s\"", + column->colname, cxt->relation->relname), + parser_errposition(cxt->pstate, + constraint->location))); + + generateSerialExtraStmts(cxt, column, constraint->options, DEPENDENCY_= INTERNAL, &snamespace, &sname); + column->raw_default =3D (Node *) generateNextvalExpr(snamespace, sname= ); + Assert(constraint->cooked_expr =3D=3D NULL); + column->identity =3D constraint->generated_when; + saw_identity =3D true; + column->is_not_null =3D TRUE; + break; + } + case CONSTR_CHECK: cxt->ckconstraints =3D lappend(cxt->ckconstraints, constraint); break; @@ -630,6 +723,14 @@ transformColumnDefinition(CreateStmtContext *cxt, Colu= mnDef *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 tab= le \"%s\"", + column->colname, cxt->relation->relname), + parser_errposition(cxt->pstate, + constraint->location))); } =20 /* @@ -834,6 +935,7 @@ transformTableLikeClause(CreateStmtContext *cxt, TableL= ikeClause *table_like_cla def->storage =3D 0; def->raw_default =3D NULL; def->cooked_default =3D NULL; + def->identity =3D ' '; def->collClause =3D NULL; def->collOid =3D attribute->attcollation; def->constraints =3D NIL; @@ -849,7 +951,7 @@ transformTableLikeClause(CreateStmtContext *cxt, TableL= ikeClause *table_like_cla /* * Copy default, if present and the default has been requested */ - if (attribute->atthasdef && + if (attribute->atthasdef && attribute->attidentity =3D=3D ' ' && (table_like_clause->options & CREATE_TABLE_LIKE_DEFAULTS)) { Node *this_default =3D NULL; @@ -877,6 +979,30 @@ transformTableLikeClause(CreateStmtContext *cxt, Table= LikeClause *table_like_cla def->cooked_default =3D this_default; } =20 + /* + * Copy identity if requested + */ + if (attribute->attidentity !=3D ' ' && + (table_like_clause->options & CREATE_TABLE_LIKE_IDENTITY)) + { + List *seqlist; + Oid seq_relid; + List *seq_options; + char *snamespace; + char *sname; + + /* + * find sequence owned by old column; extract sequence parameters; + * build new default and create sequence commands + */ + seqlist =3D getOwnedSequences(RelationGetRelid(relation), attribute->at= tnum); + seq_relid =3D linitial_oid(seqlist); + seq_options =3D sequence_options(seq_relid); + generateSerialExtraStmts(cxt, def, seq_options, DEPENDENCY_INTERNAL, &s= namespace, &sname); + def->raw_default =3D (Node *) generateNextvalExpr(snamespace, sname); + def->identity =3D attribute->attidentity; + } + /* Likewise, copy storage if requested */ if (table_like_clause->options & CREATE_TABLE_LIKE_STORAGE) def->storage =3D attribute->attstorage; @@ -1053,6 +1179,7 @@ transformOfType(CreateStmtContext *cxt, TypeName *ofT= ypename) n->storage =3D 0; n->raw_default =3D NULL; n->cooked_default =3D NULL; + n->identity =3D ' '; n->collClause =3D NULL; n->collOid =3D attr->attcollation; n->constraints =3D NIL; @@ -2594,6 +2721,87 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *s= tmt, break; } =20 + case AT_AddIdentity: + { + Constraint *def =3D (Constraint *) cmd->def; + ColumnDef *newdef =3D makeNode(ColumnDef); + Oid typeOid; + char *snamespace; + char *sname; + + Assert(IsA(def, Constraint)); + + typeOid =3D get_atttype(relid, get_attnum(relid, cmd->name)); + if (!(typeOid =3D=3D INT2OID || typeOid =3D=3D INT4OID || typeOid =3D= =3D INT8OID)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("identity column type must be smallint, integer, or bigint= "))); + + newdef->colname =3D cmd->name; // XXX before below FIXME + generateSerialExtraStmts(&cxt, newdef, def->options, DEPENDENCY_INTER= NAL, &snamespace, &sname); + newdef->raw_default =3D (Node *) generateNextvalExpr(snamespace, snam= e); + newdef->identity =3D def->generated_when; + + cmd->def =3D (Node *) newdef; + + newcmds =3D lappend(newcmds, cmd); + break; + } + + case AT_SetIdentity: + { + /* + * Create an ALTER SEQUENCE statement for the internal + * sequence of the identity column. + */ + ListCell *lc; + List *newseqopts =3D NIL; + List *newdef =3D NIL; + List *seqlist; + AttrNumber attnum; + + /* + * Split options into those handled by ALTER SEQUENCE and + * those for ALTER TABLE proper. + */ + foreach(lc, (List *) cmd->def) + { + DefElem *def =3D (DefElem *) lfirst(lc); + + if (strcmp(def->defname, "generated") =3D=3D 0) + newdef =3D lappend(newdef, def); + else + newseqopts =3D lappend(newseqopts, def); + } + + attnum =3D get_attnum(relid, cmd->name); + + if (attnum) + { + seqlist =3D getOwnedSequences(relid, attnum); + if (seqlist) + { + AlterSeqStmt *seqstmt; + Oid seq_relid; + + seqstmt =3D makeNode(AlterSeqStmt); + seq_relid =3D linitial_oid(seqlist); + seqstmt->sequence =3D makeRangeVar(get_namespace_name(get_rel_names= pace(seq_relid)), + get_rel_name(seq_relid), -1); + seqstmt->options =3D newseqopts; + seqstmt->missing_ok =3D false; + + cxt.alist =3D lappend(cxt.alist, seqstmt); + } + } + /* If column was not found or did not own sequences, we + * just let the ALTER TABLE command error out. */ + + cmd->def =3D (Node *) newdef; + newcmds =3D lappend(newcmds, cmd); + break; + } + default: newcmds =3D lappend(newcmds, cmd); break; diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rew= riteHandler.c index b828e3c..58364e5 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -61,6 +61,7 @@ static Query *rewriteRuleAction(Query *parsetree, static List *adjustJoinTreeList(Query *parsetree, bool removert, int rt_in= dex); static List *rewriteTargetListIU(List *targetList, CmdType commandType, + OverridingKind override, Relation target_relation, int result_rti, List **attrno_list); @@ -696,6 +697,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) @@ -776,6 +778,7 @@ rewriteTargetListIU(List *targetList, for (attrno =3D 1; attrno <=3D numattrs; attrno++) { TargetEntry *new_tle =3D new_tles[attrno - 1]; + bool apply_default; =20 att_tup =3D target_relation->rd_att->attrs[attrno - 1]; =20 @@ -788,8 +791,37 @@ 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 =3D=3D NULL && commandType =3D=3D CMD_INSERT) || - (new_tle && new_tle->expr && IsA(new_tle->expr, SetToDefault))) + apply_default =3D((new_tle =3D=3D NULL && commandType =3D=3D CMD_INSERT)= || + (new_tle && new_tle->expr && IsA(new_tle->expr, SetToDefault))); + + if (commandType =3D=3D CMD_INSERT) + { + if (att_tup->attidentity =3D=3D CONSTR_GENERATED_ALWAYS && !apply_defau= lt) + { + if (override !=3D OVERRIDING_SYSTEM_VALUE) + ereport(ERROR, + (errcode(ERRCODE_GENERATED_ALWAYS), + errmsg("cannot insert into column \"%s\"", NameStr(att_tup->attnam= e)), + errdetail("Column \"%s\" is an identity column defined as GENERATE= D ALWAYS.", + NameStr(att_tup->attname)), + errhint("Use OVERRIDING SYSTEM VALUE to override."))); + } + + if (att_tup->attidentity =3D=3D CONSTR_GENERATED_DEFAULT && override = =3D=3D OVERRIDING_USER_VALUE) + apply_default =3D true; + } + + if (commandType =3D=3D CMD_UPDATE) + { + if (att_tup->attidentity =3D=3D CONSTR_GENERATED_ALWAYS && !apply_defau= lt) + 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; =20 @@ -3214,6 +3246,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events) /* Process the main targetlist ... */ parsetree->targetList =3D rewriteTargetListIU(parsetree->targetList, parsetree->commandType, + parsetree->override, rt_entry_relation, parsetree->resultRelation, &attrnos); @@ -3226,6 +3259,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events) parsetree->targetList =3D rewriteTargetListIU(parsetree->targetList, parsetree->commandType, + parsetree->override, rt_entry_relation, parsetree->resultRelation, NULL); } @@ -3236,6 +3270,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events) parsetree->onConflict->onConflictSet =3D rewriteTargetListIU(parsetree->onConflict->onConflictSet, CMD_UPDATE, + parsetree->override, rt_entry_relation, parsetree->resultRelation, NULL); @@ -3245,7 +3280,9 @@ RewriteQuery(Query *parsetree, List *rewrite_events) { parsetree->targetList =3D 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/rule= utils.c index 8a81d7a..fb3aa78 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -5605,6 +5605,14 @@ get_insert_query_def(Query *query, deparse_context *= context) if (query->targetList) appendStringInfoString(buf, ") "); =20 + if (query->override) + { + if (query->override =3D=3D OVERRIDING_SYSTEM_VALUE) + appendStringInfoString(buf, "OVERRIDING SYSTEM VALUE "); + else if (query->override =3D=3D 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 13ae6ad..2d7cdaa 100644 --- a/src/backend/utils/cache/lsyscache.c +++ b/src/backend/utils/cache/lsyscache.c @@ -836,6 +836,27 @@ get_attnum(Oid relid, const char *attname) return InvalidAttrNumber; } =20 +char +get_attidentity(Oid relid, AttrNumber attnum) +{ + HeapTuple tp; + + tp =3D SearchSysCache2(ATTNUM, + ObjectIdGetDatum(relid), + Int16GetDatum(attnum)); + if (HeapTupleIsValid(tp)) + { + Form_pg_attribute att_tup =3D (Form_pg_attribute) GETSTRUCT(tp); + char result; + + result =3D 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/r= elcache.c index 79e0b1f..45ab961 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -2929,6 +2929,7 @@ RelationBuildLocalRelation(const char *relname, has_not_null =3D false; for (i =3D 0; i < natts; i++) { + rel->rd_att->attrs[i]->attidentity =3D tupDesc->attrs[i]->attidentity; rel->rd_att->attrs[i]->attnotnull =3D tupDesc->attrs[i]->attnotnull; has_not_null |=3D tupDesc->attrs[i]->attnotnull; } diff --git a/src/backend/utils/errcodes.txt b/src/backend/utils/errcodes.txt index e7bdb92..2c5915b 100644 --- a/src/backend/utils/errcodes.txt +++ b/src/backend/utils/errcodes.txt @@ -326,6 +326,7 @@ Section: Class 42 - Syntax Error or Access Rule Violati= on 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 =20 # Note: for ERRCODE purposes, we divide namable objects into these categor= ies: # 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 4da297f..fd90827 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -1840,6 +1840,9 @@ dumpTableData_insert(Archive *fout, void *dcontext) appendPQExpBufferStr(insertStmt, ") "); } =20 + if (tbinfo->needs_override) + appendPQExpBufferStr(insertStmt, "OVERRIDING SYSTEM VALUE "); + appendPQExpBufferStr(insertStmt, "VALUES ("); } } @@ -4848,6 +4851,7 @@ getTables(Archive *fout, int *numTables) int i_toastreloptions; int i_reloftype; int i_relpages; + int i_is_identity_sequence; int i_changed_acl; =20 /* Make sure we are in proper schema */ @@ -4924,6 +4928,7 @@ getTables(Archive *fout, int *numTables) "CASE WHEN 'check_option=3Dlocal' =3D ANY (c.reloptions) THEN 'LOC= AL'::text " "WHEN 'check_option=3Dcascaded' =3D ANY (c.reloptions) THEN 'CASCA= DED'::text ELSE NULL END AS checkoption, " "tc.reloptions AS toast_reloptions, " + "EXISTS (SELECT 1 FROM pg_depend WHERE classid =3D 'pg_class'::reg= class AND objid =3D c.oid AND objsubid =3D 0 AND refclassid =3D 'pg_attrdef= '::regclass AND deptype =3D 'i') AS is_identity_sequence, " "EXISTS (SELECT 1 FROM pg_attribute at LEFT JOIN pg_init_privs pip= ON " "(c.oid =3D pip.objoid " "AND pip.classoid =3D 'pg_class'::regclass " @@ -5374,6 +5379,7 @@ getTables(Archive *fout, int *numTables) i_checkoption =3D PQfnumber(res, "checkoption"); i_toastreloptions =3D PQfnumber(res, "toast_reloptions"); i_reloftype =3D PQfnumber(res, "reloftype"); + i_is_identity_sequence =3D PQfnumber(res, "is_identity_sequence"); i_changed_acl =3D PQfnumber(res, "changed_acl"); =20 if (dopt->lockWaitTimeout) @@ -5474,6 +5480,8 @@ getTables(Archive *fout, int *numTables) =20 tblinfo[i].postponed_def =3D false; /* might get set during sort */ =20 + tblinfo[i].is_identity_sequence =3D (strcmp(PQgetvalue(res, i, i_is_iden= tity_sequence), "t") =3D=3D 0); + /* * Read-lock target tables to make sure they aren't DROPPED or altered * in schema before we get around to dumping them. @@ -6925,6 +6933,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; @@ -6967,7 +6976,34 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int= numTables) =20 resetPQExpBuffer(q); =20 - if (fout->remoteVersion >=3D 90200) + if (fout->remoteVersion >=3D 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 =3D t.oid " + "WHERE a.attrelid =3D '%u'::pg_catalog.oid " + "AND a.attnum > 0::pg_catalog.int2 " + "ORDER BY a.attrelid, a.attnum", + tbinfo->dobj.catId.oid); + } + else if (fout->remoteVersion >=3D 90200) { /* * attfdwoptions is new in 9.2. @@ -7066,6 +7102,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int = numTables) i_typstorage =3D PQfnumber(res, "typstorage"); i_attnotnull =3D PQfnumber(res, "attnotnull"); i_atthasdef =3D PQfnumber(res, "atthasdef"); + i_attidentity =3D PQfnumber(res, "attidentity"); i_attisdropped =3D PQfnumber(res, "attisdropped"); i_attlen =3D PQfnumber(res, "attlen"); i_attalign =3D PQfnumber(res, "attalign"); @@ -7081,6 +7118,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int = numTables) tbinfo->attstattarget =3D (int *) pg_malloc(ntups * sizeof(int)); tbinfo->attstorage =3D (char *) pg_malloc(ntups * sizeof(char)); tbinfo->typstorage =3D (char *) pg_malloc(ntups * sizeof(char)); + tbinfo->attidentity =3D (char *) pg_malloc(ntups * sizeof(bool)); tbinfo->attisdropped =3D (bool *) pg_malloc(ntups * sizeof(bool)); tbinfo->attlen =3D (int *) pg_malloc(ntups * sizeof(int)); tbinfo->attalign =3D (char *) pg_malloc(ntups * sizeof(char)); @@ -7105,6 +7143,8 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int = numTables) tbinfo->attstattarget[j] =3D atoi(PQgetvalue(res, j, i_attstattarget)); tbinfo->attstorage[j] =3D *(PQgetvalue(res, j, i_attstorage)); tbinfo->typstorage[j] =3D *(PQgetvalue(res, j, i_typstorage)); + tbinfo->attidentity[j] =3D *(PQgetvalue(res, j, i_attidentity)); + tbinfo->needs_override =3D tbinfo->needs_override || (tbinfo->attidenti= ty[j] =3D=3D 'a'); tbinfo->attisdropped[j] =3D (PQgetvalue(res, j, i_attisdropped)[0] =3D= =3D 't'); tbinfo->attlen[j] =3D atoi(PQgetvalue(res, j, i_attlen)); tbinfo->attalign[j] =3D *(PQgetvalue(res, j, i_attalign)); @@ -7150,6 +7190,9 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int = numTables) { int adnum; =20 + if (tbinfo->attidentity[j] !=3D ' ') + continue; + adnum =3D atoi(PQgetvalue(res, j, 2)); =20 if (adnum <=3D 0 || adnum > ntups) @@ -15170,10 +15213,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)); + } =20 resetPQExpBuffer(query); =20 @@ -15185,9 +15231,27 @@ dumpSequence(Archive *fout, TableInfo *tbinfo) tbinfo->dobj.catId.oid); } =20 - appendPQExpBuffer(query, - "CREATE SEQUENCE %s\n", - fmtId(tbinfo->dobj.name)); + if (tbinfo->is_identity_sequence) + { + TableInfo *owning_tab =3D findTableByOid(tbinfo->owning_tab); + + 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] =3D=3D 'a') + appendPQExpBuffer(query, "ALWAYS"); + else if (owning_tab->attidentity[tbinfo->owning_col - 1] =3D=3D 'd') + 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)); =20 if (fout->remoteVersion >=3D 80400) appendPQExpBuffer(query, " START WITH %s\n", startv); @@ -15208,7 +15272,10 @@ dumpSequence(Archive *fout, TableInfo *tbinfo) " CACHE %s%s", cache, (cycled ? "\n CYCLE" : "")); =20 - appendPQExpBufferStr(query, ";\n"); + if (tbinfo->is_identity_sequence) + appendPQExpBufferStr(query, "\n);\n"); + else + appendPQExpBufferStr(query, ";\n"); =20 appendPQExpBuffer(labelq, "SEQUENCE %s", fmtId(tbinfo->dobj.name)); =20 @@ -15241,7 +15308,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 =3D findTableByOid(tbinfo->owning_tab); =20 diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h index a60cf95..c4eb2c8 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -283,6 +283,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) */ =20 bool interesting; /* true if need to collect more data */ @@ -300,6 +301,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 */ @@ -310,6 +312,7 @@ typedef struct _tableInfo bool *inhNotNull; /* true if NOT NULL is inherited */ struct _attrDefInfo **attrdefs; /* DEFAULT expressions */ struct _constraintInfo *checkexprs; /* CHECK constraints */ + bool needs_override; /* has GENERATED ALWAYS AS IDENTITY */ =20 /* * Stuff computed only for dumpable tables. diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index 6275a68..e751279 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1547,6 +1547,10 @@ describeOneTableDetails(const char *schemaname, " WHERE c.oid =3D a.attcollation AND t.oid =3D a.atttypid AND a.= attcollation <> t.typcollation) AS attcollation"); else appendPQExpBufferStr(&buf, "\n NULL AS attcollation"); + if (pset.sversion >=3D 100000) + appendPQExpBufferStr(&buf, ", a.attidentity"); + else + appendPQExpBufferStr(&buf, ", NULL AS attidentity"); if (tableinfo.relkind =3D=3D 'i') appendPQExpBufferStr(&buf, ",\n pg_catalog.pg_get_indexdef(a.attrelid, = a.attnum, TRUE) AS indexdef"); else @@ -1709,9 +1713,11 @@ describeOneTableDetails(const char *schemaname, /* Type */ printTableAddCell(&cont, PQgetvalue(res, i, 1), false, false); =20 - /* Modifiers: collate, not null, default */ + /* Modifiers: collate, not null, default, identity */ if (show_modifiers) { + char *identity; + resetPQExpBuffer(&tmpbuf); =20 if (!PQgetisnull(res, i, 5)) @@ -1729,9 +1735,11 @@ describeOneTableDetails(const char *schemaname, appendPQExpBufferStr(&tmpbuf, _("not null")); } =20 + identity =3D PQgetvalue(res, i, 6); + /* handle "default" here */ /* (note: above we cut off the 'default' string at 128) */ - if (strlen(PQgetvalue(res, i, 2)) !=3D 0) + if (strlen(PQgetvalue(res, i, 2)) !=3D 0 && identity[0] =3D=3D ' ') { if (tmpbuf.len > 0) appendPQExpBufferChar(&tmpbuf, ' '); @@ -1740,6 +1748,16 @@ describeOneTableDetails(const char *schemaname, PQgetvalue(res, i, 2)); } =20 + if (strlen(identity) !=3D 0 && (identity[0] =3D=3D 'a' || identity[0] = =3D=3D 'd')) + { + if (tmpbuf.len > 0) + appendPQExpBufferChar(&tmpbuf, ' '); + if (identity[0] =3D=3D 'a') + appendPQExpBuffer(&tmpbuf, "generated always as identity"); + else if (identity[0] =3D=3D 'd') + appendPQExpBuffer(&tmpbuf, "generated by default as identity"); + } + modifiers[i] =3D pg_strdup(tmpbuf.data); printTableAddCell(&cont, modifiers[i], false, false); } @@ -1750,16 +1768,16 @@ describeOneTableDetails(const char *schemaname, =20 /* Expression for index column */ if (tableinfo.relkind =3D=3D 'i') - printTableAddCell(&cont, PQgetvalue(res, i, 6), false, false); + printTableAddCell(&cont, PQgetvalue(res, i, 7), false, false); =20 /* FDW options for foreign table column, only for 9.2 or later */ if (tableinfo.relkind =3D=3D 'f' && pset.sversion >=3D 90200) - printTableAddCell(&cont, PQgetvalue(res, i, 7), false, false); + printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false); =20 /* Storage and Description */ if (verbose) { - int firstvcol =3D 8; + int firstvcol =3D 9; char *storage =3D PQgetvalue(res, i, firstvcol); =20 /* 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 50a45eb..b60b334 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -1744,7 +1744,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_LIST5("TYPE", "SET", "RESET", "ADD", "DROP"); /* ALTER TABLE ALTER [COLUMN] SET */ else if (Matches7("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny= , "SET") || Matches6("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET")) @@ -1760,7 +1760,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") || Matches8("ALTER", "TABLE", MatchAny, "TABLE", MatchAny, "ALTER", Match= Any, "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")) @@ -2621,17 +2621,25 @@ psql_completion(const char *text, int start, int en= d) =20 /* * 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"); =20 /* * 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"); =20 /* Insert an open parenthesis after "VALUES" */ diff --git a/src/include/catalog/catversion.h b/src/include/catalog/catvers= ion.h index cd3048d..089020b 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ =20 /* yyyymmddN */ -#define CATALOG_VERSION_NO 201610201 +#define CATALOG_VERSION_NO 201610312 =20 #endif diff --git a/src/include/catalog/dependency.h b/src/include/catalog/depende= ncy.h index 09b36c5..96b519b 100644 --- a/src/include/catalog/dependency.h +++ b/src/include/catalog/dependency.h @@ -236,7 +236,7 @@ extern bool sequenceIsOwned(Oid seqId, Oid *tableId, in= t32 *colId); =20 extern void markSequenceUnowned(Oid seqId); =20 -extern List *getOwnedSequences(Oid relid); +extern List *getOwnedSequences(Oid relid, AttrNumber attnum); =20 extern Oid get_constraint_index(Oid constraintId); =20 diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_at= tribute.h index 39d8eed..6406b07 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_OI= DS BKI_ROWTYPE_OID(75) BK /* Has DEFAULT value or not */ bool atthasdef; =20 + /* 'a', 'd', or ' ' */ + char attidentity; + /* Is dropped (ie, logically invisible) or not */ bool attisdropped; =20 @@ -188,7 +191,7 @@ typedef FormData_pg_attribute *Form_pg_attribute; * ---------------- */ =20 -#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 =20 =20 /* ---------------- diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h index e57b81c..0c9167d 100644 --- a/src/include/catalog/pg_class.h +++ b/src/include/catalog/pg_class.h @@ -145,7 +145,7 @@ typedef FormData_pg_class *Form_pg_class; */ DATA(insert OID =3D 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 3 1 _null_ _null_ )); DESCR(""); -DATA(insert OID =3D 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 3 1 _null_ _null_ )); +DATA(insert OID =3D 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 3 1 _null_ _null_ )); DESCR(""); DATA(insert OID =3D 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 3 1 _null_ _null_ )); DESCR(""); diff --git a/src/include/commands/sequence.h b/src/include/commands/sequenc= e.h index 392a626..6a89af2 100644 --- a/src/include/commands/sequence.h +++ b/src/include/commands/sequence.h @@ -72,6 +72,7 @@ extern Datum setval_oid(PG_FUNCTION_ARGS); extern Datum setval3_oid(PG_FUNCTION_ARGS); extern Datum lastval(PG_FUNCTION_ARGS); =20 +extern List *sequence_options(Oid relid); extern Datum pg_sequence_parameters(PG_FUNCTION_ARGS); =20 extern ObjectAddress DefineSequence(ParseState *pstate, CreateSeqStmt *stm= t); diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 6de2cab..7fd9174 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -25,6 +25,13 @@ #include "nodes/primnodes.h" #include "nodes/value.h" =20 +typedef enum OverridingKind +{ + OVERRIDING_NOT_SET =3D 0, + OVERRIDING_USER_VALUE, + OVERRIDING_SYSTEM_VALUE +} OverridingKind; + /* Possible sources of a Query */ typedef enum QuerySource { @@ -131,6 +138,8 @@ typedef struct Query =20 List *targetList; /* target list (of TargetEntry) */ =20 + OverridingKind override; /* OVERRIDING clause */ + OnConflictExpr *onConflict; /* ON CONFLICT DO [NOTHING | UPDATE] */ =20 List *returningList; /* return-values list (of TargetEntry) */ @@ -596,6 +605,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 */ @@ -617,9 +627,10 @@ typedef enum TableLikeOption { CREATE_TABLE_LIKE_DEFAULTS =3D 1 << 0, CREATE_TABLE_LIKE_CONSTRAINTS =3D 1 << 1, - CREATE_TABLE_LIKE_INDEXES =3D 1 << 2, - CREATE_TABLE_LIKE_STORAGE =3D 1 << 3, - CREATE_TABLE_LIKE_COMMENTS =3D 1 << 4, + CREATE_TABLE_LIKE_IDENTITY =3D 1 << 2, + CREATE_TABLE_LIKE_INDEXES =3D 1 << 3, + CREATE_TABLE_LIKE_STORAGE =3D 1 << 4, + CREATE_TABLE_LIKE_COMMENTS =3D 1 << 5, CREATE_TABLE_LIKE_ALL =3D PG_INT32_MAX } TableLikeOption; =20 @@ -1226,6 +1237,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; =20 /* ---------------------- @@ -1527,7 +1539,10 @@ typedef enum AlterTableType AT_DisableRowSecurity, /* DISABLE ROW SECURITY */ AT_ForceRowSecurity, /* FORCE ROW SECURITY */ AT_NoForceRowSecurity, /* NO FORCE ROW SECURITY */ - AT_GenericOptions /* OPTIONS (...) */ + AT_GenericOptions, /* OPTIONS (...) */ + AT_AddIdentity, /* ADD IDENTITY */ + AT_SetIdentity, /* SET identity column options */ + AT_DropIdentity /* DROP IDENTITY */ } AlterTableType; =20 typedef struct ReplicaIdentityStmt @@ -1798,6 +1813,7 @@ typedef enum ConstrType /* types of constraints */ * expect it */ CONSTR_NOTNULL, CONSTR_DEFAULT, + CONSTR_IDENTITY, CONSTR_CHECK, CONSTR_PRIMARY, CONSTR_UNIQUE, @@ -1809,6 +1825,9 @@ typedef enum ConstrType /* types of constraints */ CONSTR_ATTR_IMMEDIATE } ConstrType; =20 +#define CONSTR_GENERATED_ALWAYS 'a' +#define CONSTR_GENERATED_DEFAULT 'd' + /* Foreign key action codes */ #define FKCONSTR_ACTION_NOACTION 'a' #define FKCONSTR_ACTION_RESTRICT 'r' @@ -1836,6 +1855,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; =20 /* Fields used for unique constraints (UNIQUE and PRIMARY KEY): */ List *keys; /* String nodes naming referenced column(s) */ diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 17ffef5..c35a77f 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -171,6 +171,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, RESERVED_KEYWORD) PG_KEYWORD("global", GLOBAL, UNRESERVED_KEYWORD) PG_KEYWORD("grant", GRANT, RESERVED_KEYWORD) PG_KEYWORD("granted", GRANTED, UNRESERVED_KEYWORD) @@ -281,6 +282,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 dcb8980..573533b 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/reg= ress/expected/create_table_like.out index 97edde1..163a16c 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=3Dx, xx=3Dtext, y=3Dy (2 rows) =20 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 | Modifiers=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20 +--------+---------+--------------------------------------- + a | integer | not null generated always as identity + b | text |=20 + +INSERT INTO test_like_id_1 (b) VALUES ('b1'); +SELECT * FROM test_like_id_1; + a | b=20=20 +---+---- + 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 | Modifiers=20 +--------+---------+----------- + a | integer | not null + b | text |=20 + +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=20 +---+--- +(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 | Modifiers=20=20=20=20=20=20=20=20=20=20= =20=20=20=20=20 +--------+---------+--------------------------------------- + a | integer | not null generated always as identity + b | text |=20 + +INSERT INTO test_like_id_3 (b) VALUES ('b3'); +SELECT * FROM test_like_id_3; -- identity was copied and applied + a | b=20=20 +---+---- + 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/expe= cted/identity.out new file mode 100644 index 0000000..3640a7a --- /dev/null +++ b/src/test/regress/expected/identity.out @@ -0,0 +1,278 @@ +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 wi= th 7 increment by 5), b text); +ALTER TABLE itest3 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- er= ror +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 tabl= e_name LIKE 'itest_' ORDER BY 1, 2; + table_name | column_name | column_default | is_nullable | is_identity | i= dentity_generation | identity_start | identity_increment | identity_maximu= m | identity_minimum | identity_cycle=20 +------------+-------------+----------------+-------------+-------------+--= -------------------+----------------+--------------------+-----------------= ----+------------------+---------------- + itest1 | a | | NO | YES | B= Y DEFAULT | 1 | 1 | 9223372036854775= 807 | 1 | NO + itest1 | b | | YES | NO | = | | | = | | NO + itest2 | a | | NO | YES | A= LWAYS | 1 | 1 | 9223372036854775= 807 | 1 | NO + itest2 | b | | YES | NO | = | | | = | | NO + itest3 | a | | NO | YES | B= Y DEFAULT | 7 | 5 | 9223372036854775= 807 | 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=20 +--------------- +(0 rows) + +CREATE TABLE itest4 (a int, b text); +ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; +ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- er= ror +ERROR: column "a" of relation "itest4" is already an identity column +ALTER TABLE itest4 ALTER COLUMN b ADD GENERATED ALWAYS AS IDENTITY; -- er= ror +ERROR: identity column type must be smallint, integer, or bigint +-- 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_er= r_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=20 +---+--- + 1 |=20 + 2 |=20 +(2 rows) + +SELECT * FROM itest2; + a | b=20 +---+--- + 1 |=20 + 2 |=20 +(2 rows) + +SELECT * FROM itest3; + a | b=20 +----+--- + 7 |=20 + 12 |=20 +(2 rows) + +SELECT * FROM itest4; + a | b=20 +---+--- + 1 |=20 + 2 |=20 +(2 rows) + +-- OVERRIDING tests +INSERT INTO itest1 VALUES (10, 'xyz'); +INSERT INTO itest1 OVERRIDING USER VALUE VALUES (10, 'xyz'); +SELECT * FROM itest1; + a | b=20=20 +----+----- + 1 |=20 + 2 |=20 + 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=20=20 +----+----- + 1 |=20 + 2 |=20 + 10 | xyz +(3 rows) + +-- UPDATE tests +UPDATE itest1 SET a =3D 101 WHERE a =3D 1; +UPDATE itest1 SET a =3D DEFAULT WHERE a =3D 2; +SELECT * FROM itest1; + a | b=20=20 +-----+----- + 10 | xyz + 3 | xyz + 101 |=20 + 4 |=20 +(4 rows) + +UPDATE itest2 SET a =3D 101 WHERE a =3D 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 =3D DEFAULT WHERE a =3D 2; +SELECT * FROM itest2; + a | b=20=20 +----+----- + 1 |=20 + 10 | xyz + 3 |=20 +(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 +INSERT INTO itest4 DEFAULT VALUES; +SELECT * FROM itest4; + a | b=20 +---+--- + 1 |=20 + 2 |=20 + |=20 +(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=20 +---+--- + 1 |=20 + 2 |=20 +(2 rows) + +SELECT * FROM itestv11; + a | b=20 +---+--- + 1 |=20 + 2 |=20 +(2 rows) + +INSERT INTO itestv10 VALUES (10, 'xyz'); +INSERT INTO itestv10 OVERRIDING USER VALUE VALUES (11, 'xyz'); +SELECT * FROM itestv10; + a | b=20=20 +----+----- + 1 |=20 + 2 |=20 + 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=20=20 +----+----- + 1 |=20 + 2 |=20 + 11 | xyz +(3 rows) + +-- various 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 +-- not allowed for now +ALTER TABLE itest3 ALTER COLUMN a TYPE text; +ERROR: cannot alter data type of identity column "a" +-- ALTER COLUMN +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 B= Y 2 SET START WITH 100 RESTART; +INSERT INTO itest6 DEFAULT VALUES; +INSERT INTO itest6 DEFAULT VALUES; +SELECT * FROM itest6; + a | b=20 +-----+--- + 1 |=20 + 100 |=20 + 102 |=20 +(3 rows) + +SELECT table_name, column_name, is_identity, identity_generation FROM info= rmation_schema.columns WHERE table_name =3D 'itest6'; + table_name | column_name | is_identity | identity_generation=20 +------------+-------------+-------------+--------------------- + itest6 | a | YES | BY DEFAULT + itest6 | b | NO |=20 +(2 rows) + +ALTER TABLE itest6 ALTER COLUMN b SET INCREMENT BY 2; -- fail, not identi= ty +ERROR: column "b" of relation "itest6" is not an identity column +-- inheritance +CREATE TABLE itest7 (a int GENERATED ALWAYS AS IDENTITY); +INSERT INTO itest7 DEFAULT VALUES; +SELECT * FROM itest7; + a=20 +--- + 1 +(1 row) + +-- inherit identity column +CREATE TABLE itest7a (b text) INHERITS (itest7); +INSERT INTO itest7a DEFAULT VALUES; +SELECT * FROM itest7; + a=20 +--- + 1 + 2 +(2 rows) + +SELECT * FROM itest7a; + a | b=20 +---+--- + 2 |=20 +(1 row) + +-- make column identity in child table +CREATE TABLE itest7b (a int); +CREATE TABLE itest7c (a int GENERATED ALWAYS AS IDENTITY) INHERITS (itest7= b); +NOTICE: merging column "a" with inherited definition +INSERT INTO itest7c DEFAULT VALUES; +SELECT * FROM itest7c; + a=20 +--- + 1 +(1 row) + +-- conflicting identity specifications not allowed +CREATE TABLE itest7d (a int GENERATED ALWAYS AS IDENTITY) INHERITS (itest7= ); +NOTICE: merging column "a" with inherited definition +ERROR: column "a" has conflicting identity definitions +SELECT table_name, column_name, is_nullable, is_identity, identity_generat= ion FROM information_schema.columns WHERE table_name LIKE 'itest7%' ORDER B= Y 1, 2; + table_name | column_name | is_nullable | is_identity | identity_generatio= n=20 +------------+-------------+-------------+-------------+-------------------= -- + itest7 | a | NO | YES | ALWAYS + itest7a | a | NO | YES | ALWAYS + itest7a | b | YES | NO |=20 + itest7b | a | YES | NO |=20 + itest7c | a | NO | YES | ALWAYS +(5 rows) + diff --git a/src/test/regress/expected/truncate.out b/src/test/regress/expe= cted/truncate.out index 5c5277e..c76a217 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) =20 +CREATE TABLE truncate_b (id int GENERATED ALWAYS AS IDENTITY (START WITH 4= 4)); +INSERT INTO truncate_b DEFAULT VALUES; +INSERT INTO truncate_b DEFAULT VALUES; +SELECT * FROM truncate_b; + id=20 +---- + 44 + 45 +(2 rows) + +TRUNCATE truncate_b; +INSERT INTO truncate_b DEFAULT VALUES; +INSERT INTO truncate_b DEFAULT VALUES; +SELECT * FROM truncate_b; + id=20 +---- + 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=20 +---- + 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 8641769..9ff4bd4 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -108,6 +108,11 @@ test: select_views portals_p2 foreign_key cluster depe= ndency guc bitmapops combo # ---------- test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without= _oid conversion truncate alter_table sequence polymorphism rowtypes returni= ng largeobject with xml =20 +# ---------- +# Another group of parallel tests +# ---------- +test: identity + # event triggers cannot run concurrently with any test that runs DDL test: event_trigger =20 diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_sch= edule index 835cf35..1f022ae 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -161,6 +161,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 6dded1f..4b8bfc5 100644 --- a/src/test/regress/sql/create_table_like.sql +++ b/src/test/regress/sql/create_table_like.sql @@ -37,6 +37,20 @@ CREATE TABLE inhg (x text, LIKE inhx INCLUDING CONSTRAIN= TS, y text); /* Copies c SELECT * FROM inhg; /* Two records with three columns in order x=3Dx, xx= =3Dtext, y=3Dy */ DROP TABLE inhg; =20 +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/ident= ity.sql new file mode 100644 index 0000000..6390a5c --- /dev/null +++ b/src/test/regress/sql/identity.sql @@ -0,0 +1,156 @@ +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 wi= th 7 increment by 5), b text); +ALTER TABLE itest3 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- er= ror + +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 tabl= e_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; +ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- er= ror +ALTER TABLE itest4 ALTER COLUMN b ADD GENERATED ALWAYS AS IDENTITY; -- er= ror + +-- 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 =3D 101 WHERE a =3D 1; +UPDATE itest1 SET a =3D DEFAULT WHERE a =3D 2; +SELECT * FROM itest1; + +UPDATE itest2 SET a =3D 101 WHERE a =3D 1; +UPDATE itest2 SET a =3D DEFAULT WHERE a =3D 2; +SELECT * FROM itest2; + + +-- DROP IDENTITY tests + +ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY; +ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY; -- error + +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 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; + +-- not allowed for now +ALTER TABLE itest3 ALTER COLUMN a TYPE text; + + +-- ALTER COLUMN + +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 B= Y 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 info= rmation_schema.columns WHERE table_name =3D 'itest6'; + +ALTER TABLE itest6 ALTER COLUMN b SET INCREMENT BY 2; -- fail, not identi= ty + + +-- inheritance + +CREATE TABLE itest7 (a int GENERATED ALWAYS AS IDENTITY); +INSERT INTO itest7 DEFAULT VALUES; +SELECT * FROM itest7; + +-- inherit identity column +CREATE TABLE itest7a (b text) INHERITS (itest7); +INSERT INTO itest7a DEFAULT VALUES; +SELECT * FROM itest7; +SELECT * FROM itest7a; + +-- make column identity in child table +CREATE TABLE itest7b (a int); +CREATE TABLE itest7c (a int GENERATED ALWAYS AS IDENTITY) INHERITS (itest7= b); +INSERT INTO itest7c DEFAULT VALUES; +SELECT * FROM itest7c; + +-- conflicting identity specifications not allowed +CREATE TABLE itest7d (a int GENERATED ALWAYS AS IDENTITY) INHERITS (itest7= ); + +SELECT table_name, column_name, is_nullable, is_identity, identity_generat= ion FROM information_schema.columns WHERE table_name LIKE 'itest7%' ORDER B= Y 1, 2; diff --git a/src/test/regress/sql/truncate.sql b/src/test/regress/sql/trunc= ate.sql index a3d6f53..1aab021 100644 --- a/src/test/regress/sql/truncate.sql +++ b/src/test/regress/sql/truncate.sql @@ -202,6 +202,24 @@ CREATE TABLE truncate_a (id serial, INSERT INTO truncate_a DEFAULT VALUES; SELECT * FROM truncate_a; =20 +CREATE TABLE truncate_b (id int GENERATED ALWAYS AS IDENTITY (START WITH 4= 4)); + +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; --------------04BC72F34EAF6B53CB7A5963 Content-Type: text/plain Content-Disposition: inline Content-Transfer-Encoding: 8bit MIME-Version: 1.0 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers --------------04BC72F34EAF6B53CB7A5963--