Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.84_2) (envelope-from ) id 1citvk-0006Gv-6E for pgsql-hackers@arkaria.postgresql.org; Wed, 01 Mar 2017 02:23:52 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.84_2) (envelope-from ) id 1citvj-0003UZ-Jf for pgsql-hackers@arkaria.postgresql.org; Wed, 01 Mar 2017 02:23:51 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1citvg-0003On-Bv for pgsql-hackers@postgresql.org; Wed, 01 Mar 2017 02:23:48 +0000 Received: from out4-smtp.messagingengine.com ([66.111.4.28]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.84_2) (envelope-from ) id 1citvT-00012g-2a for pgsql-hackers@postgresql.org; Wed, 01 Mar 2017 02:23:47 +0000 Received: from compute7.internal (compute7.nyi.internal [10.202.2.47]) by mailout.nyi.internal (Postfix) with ESMTP id 0E62E2067C; Tue, 28 Feb 2017 21:23:33 -0500 (EST) Received: from frontend1 ([10.202.2.160]) by compute7.internal (MEProxy); Tue, 28 Feb 2017 21:23:33 -0500 DKIM-Signature: v=1; a=rsa-sha1; c=relaxed/relaxed; d= messagingengine.com; h=cc:content-type:date:from:in-reply-to :message-id:mime-version:references:subject:to:x-me-sender :x-me-sender:x-sasl-enc:x-sasl-enc; s=smtpout; bh=E1VAQP3r4Yyg9O WsjxyNalN2axE=; b=VVOf40HU3QXgLtyyhSMkX6kq5QkP2E7VfSctFXtqwpBnAd XtfiSM2hrIKLXLtAxbO1TOb3NfUAOk6MnlbF8p6qhgDakQQWvUtvpJ7E628PSPcF JIu3Oj+f7gd33fVXrR9DW+ouWnz7we6AB6ApYlPKLa9ArwoVz8ybn8t58eY6o= X-ME-Sender: X-Sasl-enc: V64Ff3GsiInwYs1JmF+GcphGflXuK9yc8k92S41yxSWR 1488335012 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 70A367E354; Tue, 28 Feb 2017 21:23:32 -0500 (EST) Subject: Re: identity columns To: Vitaly Burovoy References: <6adbacbf-73bc-dd1a-2033-63409180fd18@2ndquadrant.com> <4ef801f2-64ce-1a58-277d-d9389ae7770d@2ndquadrant.com> Cc: pgsql-hackers From: Peter Eisentraut Organization: 2ndQuadrant Message-ID: <1cf308b6-4f5c-05ba-cc74-0a7121603fcf@2ndquadrant.com> Date: Tue, 28 Feb 2017 21:23:31 -0500 User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.12; rv:45.0) Gecko/20100101 Thunderbird/45.7.0 MIME-Version: 1.0 In-Reply-To: Content-Type: multipart/mixed; boundary="------------D63BFAABA96C254008719BF2" 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. --------------D63BFAABA96C254008719BF2 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: 8bit New patch that fixes everything. ;-) Besides hopefully addressing all your issues, this version also no longer requires separate privileges on the internal sequence, which was the last outstanding functional issue that I am aware of. This version also no longer stores a default entry in pg_attrdef. Instead, the rewriter makes up the default expression on the fly. This makes some things much simpler. On 1/4/17 19:34, Vitaly Burovoy wrote: > 1. The fact COPY ignores GENERATED ALWAYS constraint (treats as > GENERATED BY DEFAULT) should be mentioned as well as rules. fixed (documentation added) What do you mean for rules? > 2. Usually error message for identical columns (with LIKE clause) > looks like this: > test=# CREATE TABLE def(i serial, n1 int NOT NULL, n2 int); > CREATE TABLE > test=# CREATE TABLE test(i serial, LIKE def INCLUDING ALL); > ERROR: column "i" specified more than once > > but for generated columns it is disorienting enough: > test=# CREATE TABLE idnt(i int GENERATED BY DEFAULT AS IDENTITY); > CREATE TABLE > test=# CREATE TABLE test(i int GENERATED BY DEFAULT AS IDENTITY, LIKE > idnt INCLUDING ALL); > ERROR: relation "test_i_seq" already exists Yeah, this is kind of hard to fix though, because the sequence names are decided during parse analysis, when we don't see that the same sequence name is also used by another new column. We could maybe patch around it in an ugly way, but it doesn't seem worth it for this marginal case. > 3. Strange error (not about absence of a column; but see pp.5 and 8): > test=# ALTER TABLE idnt ALTER COLUMN o ADD GENERATED ALWAYS AS IDENTITY; > ERROR: identity column type must be smallint, integer, or bigint What's wrong with that? > 4. Altering type leads to an error: > test=# ALTER TABLE idnt ALTER COLUMN i TYPE bigint; > ERROR: cannot alter data type of identity column "i" > > it is understandable for non-integers. But why do you forbid changing > to the supported types? fixed (is now allowed) > 5. Attempt to make a column be identity fails: > test=# ALTER TABLE idnt ALTER COLUMN n1 SET GENERATED BY DEFAULT; > ERROR: column "n1" of relation "idnt" is not an identity column > > As I understand from the Spec, chapter 11.20 General Rule 2)b) says > about the final state of a column without mentioning of the initial > state. > Therefore even if the column has the initial state "not generated", > after the command it changes the state to either "GENERATED ALWAYS" or > "GENERATED BY DEFAULT". 11.12 states that "If or is specified, then C shall be an identity column." So this clause is only to alter an existing identity column, not make a new one. > 6. The docs mention a syntax: > ALTER [ COLUMN ] class="PARAMETER">column_name { SET GENERATED { ALWAYS | > BY DEFAULT } | SET sequence_option | RESET > } [...] > > but the command fails: > test=# ALTER TABLE idnt ALTER COLUMN i RESET; > ERROR: syntax error at or near ";" > LINE 1: ALTER TABLE idnt ALTER COLUMN i RESET; This works for me. Check again please. > 7. (the same line of the doc) > usually ellipsis appears in inside parenthesis with clauses can be > repeated, in other words it should be written as: > ALTER column_name ( { SET GENERATED { ALWAYS | > BY DEFAULT } | } [...] ) But there are no parentheses in that syntax. I think the syntax synopsis as written is correct. > 8. To avoid unnecessary syntax "ALTER COLUMN ... ADD GENERATED ..." > and use existing syntax "ALTER COLUMN ... SET GENERATED ..." you can > just make the "SET" word be optional as a PG's extension. I.e. instead > of: > test=# ALTER TABLE idnt ALTER i SET GENERATED ALWAYS SET INCREMENT BY 4; > > you can write: > test=# ALTER TABLE idnt ALTER i SET GENERATED ALWAYS INCREMENT BY 4; > test=# ALTER TABLE idnt ALTER n1 SET GENERATED ALWAYS INCREMENT BY 4; > -- which sets identity constraint - see p.5 > > which is very similar to your extended syntax: > test=# ALTER TABLE idnt ALTER n1 ADD GENERATED ALWAYS AS IDENTITY > (INCREMENT BY 4); See under 5 that that is not correct. > 9. The command fails: > test=# ALTER TABLE idnt ALTER n2 ADD GENERATED ALWAYS AS IDENTITY; > ERROR: column "n2" of relation "idnt" must be declared NOT NULL > before identity can be added > > whereas the Spec does not contains a requirement for a column to be a > NOT NULLABLE. > You can implicitly set a column as NOT NULL (as the "serial" macros > does), but not require it later. The spec requires that an identity column is NOT NULL. > Moreover you can get a NULLABLE identity column by: > test=# ALTER TABLE idnt ALTER COLUMN i DROP NOT NULL; > ALTER TABLE > test=# \d idnt > Table "public.idnt" > Column | Type | Collation | Nullable | Default > --------+---------+-----------+----------+------------------------------ > i | integer | | | generated always as identity > n1 | integer | | not null | > n2 | integer | | | Fixed by disallowing that command (similar to dropping NOT NULL from a primary key, for example). > 10. Inherited tables are not allowed at all fixed > 11. The last CF added partitioned tables which are similar to > inherited, but slightly different. fixed > 12. You've introduced a new parameter for a sequence declaration > ("SEQUENCE NAME") which is not mentioned in the docs and not > supported: > a2=# CREATE SEQUENCE s SEQUENCE NAME s; > ERROR: option "sequence_name" not recognized > > I think the error should look as: > a2=# CREATE SEQUENCE s SEQUENCE__ NAME s; > ERROR: syntax error at or near "SEQUENCE__" > LINE 1: CREATE SEQUENCE s SEQUENCE__ NAME s; > ^ Fixed by improving message. > 13. Also strange error message: > test=# CREATE SCHEMA sch; > test=# ALTER TABLE idnt ALTER COLUMN n1 ADD GENERATED BY DEFAULT AS > IDENTITY (SEQUENCE NAME sch.s START 1); > ERROR: relation "sch.idnt" does not exist > > But if a table sch.idnt exists, it leads to a silent inconsistency: > test=# CREATE TABLE sch.idnt(n1 int); > CREATE TABLE > test=# ALTER TABLE idnt ALTER COLUMN n1 ADD GENERATED BY DEFAULT AS > IDENTITY (SEQUENCE NAME sch.s START 1); > ALTER TABLE > test=# DROP TABLE sch.idnt; > ERROR: could not find tuple for attrdef 0 I can't reproduce this. Can you give me a complete command sequence from scratch? > 14. Wrong hint message: > test=# DROP SEQUENCE idnt_i_seq; > ERROR: cannot drop sequence idnt_i_seq because default for table idnt > column i requires it > HINT: You can drop default for table idnt column i instead. > > but according to DDL there is no "default" which can be dropped: > test=# ALTER TABLE idnt ALTER COLUMN i DROP DEFAULT; > ERROR: column "i" of relation "idnt" is an identity column fixed (added errhint) > 15. And finally. The command: > test=# CREATE TABLE t(i int GENERATED BY DEFAULT AS IDENTITY (SEQUENCE NAME s)); > > leads to a core dump. > It happens when no sequence parameter (like "START") is set. fixed -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services --------------D63BFAABA96C254008719BF2 Content-Type: text/x-patch; name="v4-0001-Identity-columns.patch" Content-Transfer-Encoding: quoted-printable Content-Disposition: attachment; filename="v4-0001-Identity-columns.patch" =46rom 7a49363d0e5793544f75f4b57bf6049ba84b6c67 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Tue, 28 Feb 2017 21:09:02 -0500 Subject: [PATCH v4] Identity columns This is the SQL standard-conforming variant of PostgreSQL's serial columns. It also fixes a few usability issues that serial columns have: - CREATE TABLE / LIKE copies default but refers to same sequence - cannot add/drop serialness with ALTER TABLE - dropping default does not drop sequence - need to grant separate privileges to sequence - slight weirdnesses because serial is some kind of special macro --- doc/src/sgml/catalogs.sgml | 11 + doc/src/sgml/information_schema.sgml | 11 +- doc/src/sgml/ref/alter_table.sgml | 29 ++ doc/src/sgml/ref/create_table.sgml | 65 +++- doc/src/sgml/ref/insert.sgml | 41 +++ src/backend/access/common/tupdesc.c | 5 + src/backend/catalog/dependency.c | 7 + src/backend/catalog/genbki.pl | 2 + src/backend/catalog/heap.c | 15 +- src/backend/catalog/index.c | 1 + src/backend/catalog/information_schema.sql | 17 +- src/backend/catalog/pg_depend.c | 35 ++- src/backend/catalog/sql_features.txt | 12 +- src/backend/commands/sequence.c | 87 +++++- src/backend/commands/tablecmds.c | 263 +++++++++++++++- src/backend/executor/execQual.c | 21 ++ src/backend/nodes/copyfuncs.c | 21 ++ src/backend/nodes/equalfuncs.c | 16 + src/backend/nodes/makefuncs.c | 1 + src/backend/nodes/nodeFuncs.c | 11 + src/backend/nodes/outfuncs.c | 9 + src/backend/nodes/readfuncs.c | 1 + src/backend/parser/analyze.c | 2 + src/backend/parser/gram.y | 127 +++++++- src/backend/parser/parse_utilcmd.c | 379 +++++++++++++++++++-= ---- src/backend/rewrite/rewriteHandler.c | 56 +++- src/backend/utils/adt/ruleutils.c | 8 + src/backend/utils/cache/lsyscache.c | 21 ++ src/backend/utils/cache/relcache.c | 1 + src/backend/utils/errcodes.txt | 1 + src/bin/pg_dump/pg_dump.c | 90 +++++- src/bin/pg_dump/pg_dump.h | 3 + src/bin/psql/describe.c | 26 +- src/bin/psql/tab-complete.c | 18 +- src/include/catalog/catversion.h | 2 +- src/include/catalog/dependency.h | 3 +- src/include/catalog/pg_attribute.h | 20 +- src/include/catalog/pg_class.h | 2 +- src/include/commands/sequence.h | 2 + src/include/nodes/nodes.h | 1 + src/include/nodes/parsenodes.h | 28 +- src/include/nodes/primnodes.h | 14 + src/include/parser/kwlist.h | 2 + src/include/utils/lsyscache.h | 1 + src/test/regress/expected/create_table_like.out | 47 +++ src/test/regress/expected/identity.out | 306 +++++++++++++++++++ src/test/regress/expected/sequence.out | 4 +- src/test/regress/expected/truncate.out | 30 ++ src/test/regress/parallel_schedule | 5 + src/test/regress/serial_schedule | 1 + src/test/regress/sql/create_table_like.sql | 14 + src/test/regress/sql/identity.sql | 177 +++++++++++ src/test/regress/sql/sequence.sql | 2 +- src/test/regress/sql/truncate.sql | 18 ++ 54 files changed, 1923 insertions(+), 169 deletions(-) create mode 100644 src/test/regress/expected/identity.out create mode 100644 src/test/regress/sql/identity.sql diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 96cb9185c2..0df8c3da79 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -1120,6 +1120,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 a3a19ce8ce..02f7927436 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 da431f8369..be1c7f33a9 100644 --- a/doc/src/sgml/ref/alter_table.sgml +++ b/doc/src/sgml/ref/alter_table.sgml @@ -46,6 +46,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 [, ... ] ) @@ -180,6 +183,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 bb081ff86f..d808ad4d64 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -62,6 +62,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 ] @@ -81,7 +82,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 and partition_bound_spec is: =20 @@ -412,6 +413,11 @@ Parameters Column STORAGE settings are also copied from parent tabl= es. =20 + + If a column in the parent table is an identity column, that property= is + not inherited. A column in the child table can be declared identity + column if desired. + =20 @@ -481,6 +487,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. @@ -512,7 +524,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 @@ -627,6 +639,37 @@ 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. (In + the COPY command, user-specified values are always + used regardless of this setting.) + + + + The optional sequence_options clause can = be + used to override the options of the sequence. + See for details. + + + + + UNIQUE (column constraint) UNIQUE ( column_name [, ... ] ) (table constraint) =20 @@ -1259,7 +1302,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 @@ -1319,7 +1362,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 <> '') ); @@ -1734,6 +1777,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 521216b5d5..95aa77b907 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> @@ -711,6 +745,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 4e2ebe1ae7..8ef24fe978 100644 --- a/src/backend/access/common/tupdesc.c +++ b/src/backend/access/common/tupdesc.c @@ -150,6 +150,7 @@ CreateTupleDescCopy(TupleDesc tupdesc) memcpy(desc->attrs[i], tupdesc->attrs[i], ATTRIBUTE_FIXED_PART_SIZE); desc->attrs[i]->attnotnull =3D false; desc->attrs[i]->atthasdef =3D false; + desc->attrs[i]->attidentity =3D ' '; } =20 desc->tdtypeid =3D tupdesc->tdtypeid; @@ -257,6 +258,7 @@ TupleDescCopyEntry(TupleDesc dst, AttrNumber dstAttno, /* since we're not copying constraints or defaults, clear these */ dst->attrs[dstAttno - 1]->attnotnull =3D false; dst->attrs[dstAttno - 1]->atthasdef =3D false; + dst->attrs[dstAttno - 1]->attidentity =3D ' '; } =20 /* @@ -401,6 +403,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) @@ -534,6 +538,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/dependency.c b/src/backend/catalog/depende= ncy.c index fc088b2165..76e3c43ba6 100644 --- a/src/backend/catalog/dependency.c +++ b/src/backend/catalog/dependency.c @@ -1923,6 +1923,13 @@ find_expr_references_walker(Node *node, context->addrs); /* fall through to examine arguments */ } + else if (IsA(node, NextValueExpr)) + { + NextValueExpr *nve =3D (NextValueExpr *) node; + + add_object_address(OCLASS_CLASS, nve->seqid, 0, + context->addrs); + } =20 return expression_tree_walker(node, find_expr_references_walker, (void *) context); diff --git a/src/backend/catalog/genbki.pl b/src/backend/catalog/genbki.pl index 079516ca2f..93c1274b7f 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 41c0056556..77efa1eeeb 100644 --- a/src/backend/catalog/heap.c +++ b/src/backend/catalog/heap.c @@ -142,37 +142,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 /* @@ -184,7 +184,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}; @@ -618,6 +618,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 f8d92145e8..e5a194b4b5 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 51795cd6de..5bf434e976 100644 --- a/src/backend/catalog/information_schema.sql +++ b/src/backend/catalog/information_schema.sql @@ -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(seq.seqstart AS character_data) AS identity_start, + CAST(seq.seqincrement AS character_data) AS identity_increment, + CAST(seq.seqmax AS character_data) AS identity_maximum, + CAST(seq.seqmin AS character_data) AS identity_minimum, + CAST(CASE WHEN seq.seqcycle THEN 'YES' ELSE 'NO' END AS yes_or_= no) AS identity_cycle, =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_sequence seq ON (dep.classid =3D= 'pg_class'::regclass AND dep.objid =3D seq.seqrelid AND dep.deptype =3D 'i= ')) + ON (dep.refclassid =3D 'pg_class'::regclass AND dep.refobjid = =3D c.oid AND dep.refobjsubid =3D a.attnum) =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, pg_sequence s 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 c.oid =3D s.seqrelid AND (pg_has_role(c.relowner, 'USAGE') diff --git a/src/backend/catalog/pg_depend.c b/src/backend/catalog/pg_depen= d.c index d0ee851215..f4c7d5a80f 100644 --- a/src/backend/catalog/pg_depend.c +++ b/src/backend/catalog/pg_depend.c @@ -551,17 +551,20 @@ markSequenceUnowned(Oid seqId) { deleteDependencyRecordsForClass(RelationRelationId, seqId, RelationRelationId, DEPENDENCY_AUTO); + deleteDependencyRecordsForClass(RelationRelationId, seqId, + RelationRelationId, 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 @@ -575,23 +578,28 @@ getOwnedSequences(Oid relid) Anum_pg_depend_refobjid, BTEqualStrategyNumber, F_OIDEQ, ObjectIdGetDatum(relid)); + if (attnum) + ScanKeyInit(&key[2], + Anum_pg_depend_refobjsubid, + BTEqualStrategyNumber, F_INT4EQ, + Int32GetDatum(attnum)); =20 scan =3D systable_beginscan(depRel, DependReferenceIndexId, true, - NULL, 2, key); + NULL, attnum ? 3 : 2, key); =20 while (HeapTupleIsValid(tup =3D systable_getnext(scan))) { Form_pg_depend deprec =3D (Form_pg_depend) GETSTRUCT(tup); =20 /* - * We assume any auto dependency of a sequence on a column must be + * We assume any auto or internal dependency of a sequence on a column m= ust be * what we are looking for. (We need the relkind test because indexes * can also have auto dependencies on columns.) */ if (deprec->classid =3D=3D RelationRelationId && deprec->objsubid =3D=3D 0 && deprec->refobjsubid !=3D 0 && - deprec->deptype =3D=3D DEPENDENCY_AUTO && + (deprec->deptype =3D=3D DEPENDENCY_AUTO || deprec->deptype =3D=3D DEPEN= DENCY_INTERNAL) && get_rel_relkind(deprec->objid) =3D=3D RELKIND_SEQUENCE) { result =3D lappend_oid(result, deprec->objid); @@ -605,6 +613,21 @@ getOwnedSequences(Oid relid) return result; } =20 +/* + * Get owned sequence, error if not exactly one. + */ +Oid +getOwnedSequence(Oid relid, AttrNumber attnum) +{ + List *seqlist =3D getOwnedSequences(relid, attnum); + + if (list_length(seqlist) > 1) + elog(ERROR, "more than one owned sequence found"); + else if (list_length(seqlist) < 1) + elog(ERROR, "no owned sequence found"); + else + return linitial_oid(seqlist); +} =20 /* * get_constraint_index diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql= _features.txt index 8956ba9304..2821b9b702 100644 --- a/src/backend/catalog/sql_features.txt +++ b/src/backend/catalog/sql_features.txt @@ -200,7 +200,7 @@ F181 Multiple module support NO F191 Referential delete actions YES=09 F200 TRUNCATE TABLE statement YES=09 F201 CAST function YES=09 -F202 TRUNCATE TABLE: identity column restart option NO=09 +F202 TRUNCATE TABLE: identity column restart option YES=09 F221 Explicit defaults YES=09 F222 INSERT statement: DEFAULT VALUES clause YES=09 F231 Privilege tables YES=09 @@ -241,9 +241,9 @@ F381 Extended schema manipulation 02 ALTER TABLE statem= ent: ADD CONSTRAINT claus F381 Extended schema manipulation 03 ALTER TABLE statement: DROP CONSTRAIN= T clause YES=09 F382 Alter column data type YES=09 F383 Set column not null clause YES=09 -F384 Drop identity property clause NO=09 +F384 Drop identity property clause YES=09 F385 Drop column generation expression clause NO=09 -F386 Set identity column generation clause NO=09 +F386 Set identity column generation clause YES=09 F391 Long identifiers YES=09 F392 Unicode escapes in identifiers YES=09 F393 Unicode escapes in literals YES=09 @@ -420,11 +420,11 @@ T152 DISTINCT predicate with negation YES T171 LIKE clause in table definition YES=09 T172 AS subquery clause in table definition YES=09 T173 Extended LIKE clause in table definition YES=09 -T174 Identity columns NO=09 +T174 Identity columns YES=09 T175 Generated columns NO=09 T176 Sequence generator support NO=09 -T177 Sequence generator support: simple restart option NO=09 -T178 Identity columns: simple restart option NO=09 +T177 Sequence generator support: simple restart option YES=09 +T178 Identity columns: simple restart option YES=09 T180 System-versioned tables NO=09 T181 Application-time period tables NO=09 T191 Referential action RESTRICT YES=09 diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequenc= e.c index e0df642254..52e1890346 100644 --- a/src/backend/commands/sequence.c +++ b/src/backend/commands/sequence.c @@ -93,7 +93,6 @@ static HTAB *seqhashtab =3D NULL; /* hash table for SeqTa= ble items */ static SeqTableData *last_used_seq =3D NULL; =20 static void fill_seq_with_data(Relation rel, HeapTuple tuple); -static int64 nextval_internal(Oid relid); static Relation open_share_lock(SeqTable seq); static void create_seq_hashtable(void); static void init_sequence(Oid relid, SeqTable *p_elm, Relation *p_rel); @@ -101,9 +100,10 @@ static Form_pg_sequence_data read_seq_tuple(Relation r= el, Buffer *buf, HeapTuple seqdatatuple); static void init_params(ParseState *pstate, List *options, bool isInit, Form_pg_sequence seqform, - Form_pg_sequence_data seqdataform, List **owned_by); + Form_pg_sequence_data seqdataform, 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 /* @@ -116,6 +116,7 @@ DefineSequence(ParseState *pstate, CreateSeqStmt *seq) FormData_pg_sequence seqform; FormData_pg_sequence_data seqdataform; List *owned_by; + DependencyType deptype; CreateStmt *stmt =3D makeNode(CreateStmt); Oid seqoid; ObjectAddress address; @@ -153,7 +154,7 @@ DefineSequence(ParseState *pstate, CreateSeqStmt *seq) } =20 /* Check and set all option values */ - init_params(pstate, seq->options, true, &seqform, &seqdataform, &owned_by= ); + init_params(pstate, seq->options, true, &seqform, &seqdataform, &owned_by= , &deptype); =20 /* * Create relation (and fill value[] and null[] for the tuple) @@ -170,6 +171,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; @@ -219,7 +221,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 @@ -418,6 +420,7 @@ AlterSequence(ParseState *pstate, AlterSeqStmt *stmt) Form_pg_sequence_data seqdata; FormData_pg_sequence_data newseqdata; List *owned_by; + DependencyType deptype; ObjectAddress address; Relation rel; HeapTuple tuple; @@ -455,7 +458,7 @@ AlterSequence(ParseState *pstate, AlterSeqStmt *stmt) seqform =3D (Form_pg_sequence) GETSTRUCT(tuple); =20 /* Check and set new values */ - init_params(pstate, stmt->options, false, seqform, &newseqdata, &owned_by= ); + init_params(pstate, stmt->options, false, seqform, &newseqdata, &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 */ @@ -498,7 +501,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 @@ -554,7 +557,7 @@ nextval(PG_FUNCTION_ARGS) */ relid =3D RangeVarGetRelid(sequence, NoLock, false); =20 - PG_RETURN_INT64(nextval_internal(relid)); + PG_RETURN_INT64(nextval_internal(relid, true)); } =20 Datum @@ -562,11 +565,11 @@ nextval_oid(PG_FUNCTION_ARGS) { Oid relid =3D PG_GETARG_OID(0); =20 - PG_RETURN_INT64(nextval_internal(relid)); + PG_RETURN_INT64(nextval_internal(relid, true)); } =20 -static int64 -nextval_internal(Oid relid) +int64 +nextval_internal(Oid relid, bool check_permissions) { SeqTable elm; Relation seqrel; @@ -592,7 +595,8 @@ nextval_internal(Oid relid) /* open and AccessShareLock sequence */ init_sequence(relid, &elm, &seqrel); =20 - if (pg_class_aclcheck(elm->relid, GetUserId(), + if (check_permissions && + pg_class_aclcheck(elm->relid, GetUserId(), ACL_USAGE | ACL_UPDATE) !=3D ACLCHECK_OK) ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), @@ -1221,7 +1225,8 @@ read_seq_tuple(Relation rel, Buffer *buf, HeapTuple s= eqdatatuple) static void init_params(ParseState *pstate, List *options, bool isInit, Form_pg_sequence seqform, - Form_pg_sequence_data seqdataform, List **owned_by) + Form_pg_sequence_data seqdataform, List **owned_by, + DependencyType *deptype) { DefElem *as_type =3D NULL; DefElem *start_value =3D NULL; @@ -1231,9 +1236,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) { @@ -1320,6 +1327,28 @@ 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"), + parser_errposition(pstate, defel->location))); + deptype_el =3D defel; + *deptype =3D intVal(deptype_el->arg); + } + else if (strcmp(defel->defname, "sequence_name") =3D=3D 0) + { + /* + * The parser allows this, but it is only for identity columns, in + * which case it is filtered out in parse_utilcmd.c. We only get + * here if someone puts it into a CREATE SEQUENCE. + */ + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("invalid sequence option SEQUENCE NAME"), + parser_errposition(pstate, defel->location))); + } else elog(ERROR, "option \"%s\" not recognized", defel->defname); @@ -1565,7 +1594,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; @@ -1601,6 +1630,7 @@ process_owned_by(Relation seqrel, List *owned_by) /* Must be a regular or foreign table */ if (!(tablerel->rd_rel->relkind =3D=3D RELKIND_RELATION || tablerel->rd_rel->relkind =3D=3D RELKIND_FOREIGN_TABLE || + tablerel->rd_rel->relkind =3D=3D RELKIND_VIEW || tablerel->rd_rel->relkind =3D=3D RELKIND_PARTITIONED_TABLE)) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), @@ -1643,7 +1673,7 @@ process_owned_by(Relation seqrel, List *owned_by) depobject.classId =3D RelationRelationId; depobject.objectId =3D RelationGetRelid(seqrel); depobject.objectSubId =3D 0; - recordDependencyOn(&depobject, &refobject, DEPENDENCY_AUTO); + recordDependencyOn(&depobject, &refobject, deptype); } =20 /* Done, but hold lock until commit */ @@ -1653,6 +1683,33 @@ 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) +{ + HeapTuple pgstuple; + Form_pg_sequence pgsform; + List *options =3D NIL; + + pgstuple =3D SearchSysCache1(SEQRELID, relid); + if (!HeapTupleIsValid(pgstuple)) + elog(ERROR, "cache lookup failed for sequence %u", relid); + pgsform =3D (Form_pg_sequence) GETSTRUCT(pgstuple); + + options =3D lappend(options, makeDefElem("cache", (Node *) makeInteger(pg= sform->seqcache), -1)); + options =3D lappend(options, makeDefElem("cycle", (Node *) makeInteger(pg= sform->seqcycle), -1)); + options =3D lappend(options, makeDefElem("increment", (Node *) makeIntege= r(pgsform->seqincrement), -1)); + options =3D lappend(options, makeDefElem("maxvalue", (Node *) makeInteger= (pgsform->seqmax), -1)); + options =3D lappend(options, makeDefElem("minvalue", (Node *) makeInteger= (pgsform->seqmin), -1)); + options =3D lappend(options, makeDefElem("start", (Node *) makeInteger(pg= sform->seqstart), -1)); + + ReleaseSysCache(pgstuple); + + return options; +} + +/* * Return sequence parameters (formerly for use by information schema) */ Datum diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablec= mds.c index 3cea220421..1ac6331c70 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -359,6 +359,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, @@ -694,6 +699,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 /* @@ -1279,7 +1287,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) @@ -2068,6 +2076,12 @@ MergeAttributes(List *schema, List *supers, char rel= persistence, get_collation_name(defcollid), get_collation_name(newcollid)))); =20 + /* + * Identity is never inherited. The new column can have an + * identity definition, so we always just take that one. + */ + def->identity =3D newdef->identity; + /* Copy storage parameter */ if (def->storage =3D=3D 0) def->storage =3D newdef->storage; @@ -3197,6 +3211,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 @@ -3415,6 +3432,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 @@ -3427,6 +3446,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); ATPrepDropNotNull(rel, recurse, recursing); @@ -3752,6 +3776,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; @@ -5102,6 +5135,17 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab= , Relation rel, elog(ERROR, "cache lookup failed for relation %u", myrelid); relkind =3D ((Form_pg_class) GETSTRUCT(reltup))->relkind; =20 + /* + * Cannot add identity column if table has children, because identity does + * not inherit. (Adding column and identity separately will work.) + */ + if (colDef->identity !=3D ' ' && + recurse && + find_inheritance_children(myrelid, NoLock) !=3D NIL) + ereport(ERROR, + (errcode(ERRCODE_INVALID_TABLE_DEFINITION), + errmsg("cannot recursively add identity column to table that has chil= d tables"))); + /* skip if the name already exists and if_not_exists is true */ if (!check_for_column_name_collision(rel, colDef->colname, if_not_exists)) { @@ -5154,6 +5198,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 colDef->identity; attribute.attisdropped =3D false; attribute.attislocal =3D colDef->is_local; attribute.attinhcount =3D colDef->inhcount; @@ -5460,6 +5505,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; } @@ -5521,6 +5567,12 @@ ATExecDropNotNull(Relation rel, const char *colName,= LOCKMODE lockmode) 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)))); + /* * Check that the attribute is not in a primary key * @@ -5737,6 +5789,13 @@ 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)), + newDefault ? 0 : errhint("Use ALTER TABLE ... ALTER COLUMN ... DROP I= DENTITY instead."))); + /* * Remove any old default for the column. We use RESTRICT here for * safety, but at present we do not expect anything to depend on the @@ -5772,6 +5831,208 @@ 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; + ObjectAddress address; + ColumnDef *cdef =3D castNode(ColumnDef, def); + + 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))); + + /* + * Creating a column as identity implies NOT NULL, so adding the identity + * to an existing column that is not NOT NULL would create a state that + * cannot be reproduced without contortions. + */ + if (!attTup->attnotnull) + ereport(ERROR, + (errmsg("column \"%s\" of relation \"%s\" must be declared NOT NULL be= fore identity can be added", + colName, RelationGetRelationName(rel)))); + + 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; + CatalogTupleUpdate(attrelation, &tuple->t_self, tuple); + + InvokeObjectPostAlterHook(RelationRelationId, + RelationGetRelid(rel), + attTup->attnum); + ObjectAddressSubSet(address, RelationRelationId, + RelationGetRelid(rel), attnum); + heap_freetuple(tuple); + + heap_close(attrelation, RowExclusiveLock); + + return address; +} + +static ObjectAddress +ATExecSetIdentity(Relation rel, const char *colName, Node *def, LOCKMODE l= ockmode) +{ + ListCell *option; + DefElem *generatedEl =3D NULL; + HeapTuple tuple; + Form_pg_attribute attTup; + AttrNumber attnum; + Relation attrelation; + ObjectAddress address; + + foreach(option, castNode(List, def)) + { + DefElem *defel =3D castNode(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); + CatalogTupleUpdate(attrelation, &tuple->t_self, tuple); + + InvokeObjectPostAlterHook(RelationRelationId, + RelationGetRelid(rel), + attTup->attnum); + ObjectAddressSubSet(address, RelationRelationId, + RelationGetRelid(rel), attnum); + } + + heap_freetuple(tuple); + heap_close(attrelation, RowExclusiveLock); + + return address; +} + +static ObjectAddress +ATExecDropIdentity(Relation rel, const char *colName, LOCKMODE lockmode) +{ + HeapTuple tuple; + Form_pg_attribute attTup; + AttrNumber attnum; + Relation attrelation; + ObjectAddress address; + Oid seqid; + ObjectAddress seqaddress; + + 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 ' '; + CatalogTupleUpdate(attrelation, &tuple->t_self, tuple); + + InvokeObjectPostAlterHook(RelationRelationId, + RelationGetRelid(rel), + attTup->attnum); + ObjectAddressSubSet(address, RelationRelationId, + RelationGetRelid(rel), attnum); + heap_freetuple(tuple); + + heap_close(attrelation, RowExclusiveLock); + + /* drop the internal sequence */ + seqid =3D getOwnedSequence(RelationGetRelid(rel), attnum); + deleteDependencyRecordsForClass(RelationRelationId, seqid, + RelationRelationId, DEPENDENCY_INTERNAL); + CommandCounterIncrement(); + seqaddress.classId =3D RelationRelationId; + seqaddress.objectId =3D seqid; + seqaddress.objectSubId =3D 0; + performDeletion(&seqaddress, DROP_RESTRICT, PERFORM_DELETION_INTERNAL); + + return address; +} + +/* * ALTER TABLE ALTER COLUMN SET STATISTICS */ static void diff --git a/src/backend/executor/execQual.c b/src/backend/executor/execQua= l.c index 4566219ca8..685ea739f3 100644 --- a/src/backend/executor/execQual.c +++ b/src/backend/executor/execQual.c @@ -41,6 +41,7 @@ #include "access/tupconvert.h" #include "catalog/objectaccess.h" #include "catalog/pg_type.h" +#include "commands/sequence.h" #include "executor/execdebug.h" #include "executor/nodeSubplan.h" #include "funcapi.h" @@ -182,6 +183,8 @@ static Datum ExecEvalArrayCoerceExpr(ArrayCoerceExprSta= te *astate, bool *isNull); static Datum ExecEvalCurrentOfExpr(ExprState *exprstate, ExprContext *econ= text, bool *isNull); +static Datum ExecEvalNextValueExpr(ExprState *exprstate, ExprContext *econ= text, + bool *isNull); static Datum ExecEvalGroupingFuncExpr(GroupingFuncExprState *gstate, ExprContext *econtext, bool *isNull); @@ -4211,6 +4214,20 @@ ExecEvalCurrentOfExpr(ExprState *exprstate, ExprCont= ext *econtext, =20 =20 /* + * ExecEvalNextValueExpr + */ +static Datum +ExecEvalNextValueExpr(ExprState *exprstate, ExprContext *econtext, + bool *isNull) +{ + NextValueExpr *nve =3D (NextValueExpr *) exprstate->expr; + + *isNull =3D false; + return Int64GetDatum(nextval_internal(nve->seqid, false)); +} + + +/* * ExecEvalExprSwitchContext * * Same as ExecEvalExpr, but get into the right allocation context explici= tly. @@ -4952,6 +4969,10 @@ ExecInitExpr(Expr *node, PlanState *parent) state =3D makeNode(ExprState); state->evalfunc =3D ExecEvalCurrentOfExpr; break; + case T_NextValueExpr: + state =3D (ExprState *) makeNode(ExprState); + state->evalfunc =3D (ExprStateEvalFunc) ExecEvalNextValueExpr; + break; case T_TargetEntry: { TargetEntry *tle =3D (TargetEntry *) node; diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index 05d8538717..6fc98ef1c4 100644 --- a/src/backend/nodes/copyfuncs.c +++ b/src/backend/nodes/copyfuncs.c @@ -1905,6 +1905,20 @@ _copyCurrentOfExpr(const CurrentOfExpr *from) return newnode; } =20 + /* + * _copyNextValueExpr + */ +static NextValueExpr * +_copyNextValueExpr(const NextValueExpr *from) +{ + NextValueExpr *newnode =3D makeNode(NextValueExpr); + + COPY_SCALAR_FIELD(seqid); + COPY_SCALAR_FIELD(typeId); + + return newnode; +} + /* * _copyInferenceElem */ @@ -2644,6 +2658,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); @@ -2666,6 +2681,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); @@ -2774,6 +2790,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); @@ -2817,6 +2834,7 @@ _copyInsertStmt(const InsertStmt *from) COPY_NODE_FIELD(onConflictClause); COPY_NODE_FIELD(returningList); COPY_NODE_FIELD(withClause); + COPY_SCALAR_FIELD(override); =20 return newnode; } @@ -4749,6 +4767,9 @@ copyObject(const void *from) case T_CurrentOfExpr: retval =3D _copyCurrentOfExpr(from); break; + case T_NextValueExpr: + retval =3D _copyNextValueExpr(from); + break; case T_InferenceElem: retval =3D _copyInferenceElem(from); break; diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index d595cd7481..3203cf1320 100644 --- a/src/backend/nodes/equalfuncs.c +++ b/src/backend/nodes/equalfuncs.c @@ -714,6 +714,15 @@ _equalCurrentOfExpr(const CurrentOfExpr *a, const Curr= entOfExpr *b) } =20 static bool +_equalNextValueExpr(const NextValueExpr *a, const NextValueExpr *b) +{ + COMPARE_SCALAR_FIELD(seqid); + COMPARE_SCALAR_FIELD(typeId); + + return true; +} + +static bool _equalInferenceElem(const InferenceElem *a, const InferenceElem *b) { COMPARE_NODE_FIELD(expr); @@ -934,6 +943,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); @@ -973,6 +983,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; } @@ -2455,6 +2466,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); @@ -2475,6 +2487,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); @@ -3020,6 +3033,9 @@ equal(const void *a, const void *b) case T_CurrentOfExpr: retval =3D _equalCurrentOfExpr(a, b); break; + case T_NextValueExpr: + retval =3D _equalNextValueExpr(a, b); + break; case T_InferenceElem: retval =3D _equalInferenceElem(a, b); break; diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c index 7586cce56a..22ab685a17 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/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c index 71b24a079c..b60e58698f 100644 --- a/src/backend/nodes/nodeFuncs.c +++ b/src/backend/nodes/nodeFuncs.c @@ -246,6 +246,9 @@ exprType(const Node *expr) case T_CurrentOfExpr: type =3D BOOLOID; break; + case T_NextValueExpr: + type =3D ((const NextValueExpr *) expr)->typeId; + break; case T_InferenceElem: { const InferenceElem *n =3D (const InferenceElem *) expr; @@ -919,6 +922,9 @@ exprCollation(const Node *expr) case T_CurrentOfExpr: coll =3D InvalidOid; /* result is always boolean */ break; + case T_NextValueExpr: + coll =3D InvalidOid; /* result is always an integer type */ + break; case T_InferenceElem: coll =3D exprCollation((Node *) ((const InferenceElem *) expr)->expr); break; @@ -1122,6 +1128,9 @@ exprSetCollation(Node *expr, Oid collation) case T_CurrentOfExpr: Assert(!OidIsValid(collation)); /* result is always boolean */ break; + case T_NextValueExpr: + Assert(!OidIsValid(collation)); /* result is always an integer type */ + break; default: elog(ERROR, "unrecognized node type: %d", (int) nodeTag(expr)); break; @@ -1878,6 +1887,7 @@ expression_tree_walker(Node *node, case T_CaseTestExpr: case T_SetToDefault: case T_CurrentOfExpr: + case T_NextValueExpr: case T_SQLValueFunction: case T_RangeTblRef: case T_SortGroupClause: @@ -2452,6 +2462,7 @@ expression_tree_mutator(Node *node, case T_CaseTestExpr: case T_SetToDefault: case T_CurrentOfExpr: + case T_NextValueExpr: case T_SQLValueFunction: case T_RangeTblRef: case T_SortGroupClause: diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index b3802b4428..5041c887b2 100644 --- a/src/backend/nodes/outfuncs.c +++ b/src/backend/nodes/outfuncs.c @@ -2614,6 +2614,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); @@ -2719,6 +2720,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); @@ -3218,6 +3220,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 d2f69fe70b..034c736a3f 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 796b5c9a5f..31d3f384bc 100644 --- a/src/backend/parser/analyze.c +++ b/src/backend/parser/analyze.c @@ -484,6 +484,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 e833b2eba5..ad4d7f0827 100644 --- a/src/backend/parser/gram.y +++ b/src/backend/parser/gram.y @@ -292,6 +292,8 @@ static Node *makeRecursiveViewSelect(char *relname, Lis= t *aliases, Node *query); %type alter_table_cmd alter_type_cmd opt_collate_clause replica_identity partition_cmd %type alter_table_cmds alter_type_cmds +%type alter_identity_column_option_list +%type alter_identity_column_option =20 %type opt_drop_behavior =20 @@ -447,7 +449,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 @@ -563,6 +565,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 %type PartitionSpec OptPartitionSpec %type part_strategy %type part_elem @@ -625,7 +628,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 @@ -649,7 +652,7 @@ static Node *makeRecursiveViewSelect(char *relname, Lis= t *aliases, Node *query); NULLS_P NUMERIC =20 OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OPTIONS OR - ORDER ORDINALITY OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER + ORDER ORDINALITY OUT_P OUTER_P OVER OVERLAPS OVERLAY OVERRIDING OWNED OWN= ER =20 PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POLICY POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY @@ -720,6 +723,7 @@ static Node *makeRecursiveViewSelect(char *relname, Lis= t *aliases, Node *query); * same as if they weren't keywords). We need to do this for PARTITION, * RANGE, ROWS to support opt_existing_window_name; and for RANGE, ROWS * so that they can follow a_expr without creating postfix-operator proble= ms; + * for GENERATED so that it can follow b_expr; * and for NULL so that it can follow b_expr in ColQualList without creati= ng * postfix-operator problems. * @@ -738,7 +742,7 @@ static Node *makeRecursiveViewSelect(char *relname, Lis= t *aliases, Node *query); * blame any funny behavior of UNBOUNDED on the SQL standard, though. */ %nonassoc UNBOUNDED /* ideally should have same precedence as IDENT */ -%nonassoc IDENT NULL_P PARTITION RANGE ROWS PRECEDING FOLLOWING CUBE ROLLUP +%nonassoc IDENT GENERATED NULL_P PARTITION RANGE ROWS PRECEDING FOLLOWING = CUBE ROLLUP %left Op OPERATOR /* multi-character ops and user-defined operators */ %left '+' '-' %left '*' '/' '%' @@ -2125,6 +2129,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 { @@ -2562,6 +2600,39 @@ 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, "as") =3D=3D 0 || + 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); + } + ; + ForValues: /* a LIST partition */ FOR VALUES IN_P '(' partbound_datum_list ')' @@ -3212,6 +3283,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, @@ -3233,6 +3305,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); @@ -3344,6 +3417,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); @@ -3361,6 +3443,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 @@ -3427,6 +3514,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; } @@ -3935,6 +4023,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); } ; @@ -3979,6 +4071,11 @@ SeqOptElem: AS SimpleTypename { $$ =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); @@ -10350,12 +10447,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); @@ -10364,6 +10475,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); } @@ -11714,6 +11830,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; @@ -14374,6 +14491,7 @@ unreserved_keyword: | FORWARD | FUNCTION | FUNCTIONS + | GENERATED | GLOBAL | GRANTED | HANDLER @@ -14442,6 +14560,7 @@ unreserved_keyword: | OPTIONS | ORDINALITY | OVER + | OVERRIDING | OWNED | OWNER | PARALLEL diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_= utilcmd.c index ff2bab6551..9e9b00ee5c 100644 --- a/src/backend/parser/parse_utilcmd.c +++ b/src/backend/parser/parse_utilcmd.c @@ -42,6 +42,7 @@ #include "catalog/pg_type.h" #include "commands/comment.h" #include "commands/defrem.h" +#include "commands/sequence.h" #include "commands/tablecmds.h" #include "commands/tablespace.h" #include "miscadmin.h" @@ -356,6 +357,130 @@ transformCreateStmt(CreateStmt *stmt, const char *que= ryString) return result; } =20 +static void +generateSerialExtraStmts(CreateStmtContext *cxt, ColumnDef *column, + Oid seqtypid, 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 castNode(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(castNode(List, nameEl->arg)); + snamespace =3D rv->schemaname; + sname =3D rv->relname; + seqoptions =3D 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 a sequence data type was specified, add it to the options. Prepend + * to the list rather than append; in case a user supplied their own AS + * clause, the "redundant options" error will point to their occurrence, + * not our synthetic one. + */ + if (seqtypid) + seqstmt->options =3D lcons(makeDefElem("as", (Node *) makeTypeNameFromOi= d(seqtypid, -1), -1), + seqstmt->options); + + /* + * If this is ALTER ADD COLUMN, make sure the sequence will be owned + * by the table's owner. The current user might be someone else + * (perhaps a superuser, or someone who's only a member of the owning + * role), but the SEQUENCE OWNED BY mechanisms will bleat unless table + * and sequence have exactly the same owning role. + */ + if (cxt->rel) + seqstmt->ownerId =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; +} + /* * transformColumnDefinition - * transform a single ColumnDef within CREATE TABLE @@ -367,7 +492,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); @@ -422,83 +547,17 @@ transformColumnDefinition(CreateStmtContext *cxt, Col= umnDef *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 list_make1(makeDefElem("as", (Node *) makeTypeNameF= romOid(column->typeName->typeOid, -1), -1)); + Constraint *constraint; =20 - /* - * 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)); - - cxt->alist =3D lappend(cxt->alist, altseqstmt); + generateSerialExtraStmts(cxt, column, + column->typeName->typeOid, NIL, DEPENDENCY_AUTO, + &snamespace, &sname); =20 /* * Create appropriate constraints for SERIAL. We do this in full, @@ -540,10 +599,11 @@ transformColumnDefinition(CreateStmtContext *cxt, Col= umnDef *column) =20 saw_nullable =3D false; saw_default =3D false; + saw_identity =3D false; =20 foreach(clist, column->constraints) { - constraint =3D castNode(Constraint, lfirst(clist)); + Constraint *constraint =3D castNode(Constraint, lfirst(clist)); =20 switch (constraint->contype) { @@ -581,9 +641,44 @@ 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, + typeOid, constraint->options, DEPENDENCY_INTERNAL, + &snamespace, &sname); + + 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; @@ -660,6 +755,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 /* @@ -889,6 +992,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; @@ -904,7 +1008,7 @@ transformTableLikeClause(CreateStmtContext *cxt, Table= LikeClause *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; @@ -932,6 +1036,29 @@ transformTableLikeClause(CreateStmtContext *cxt, Tabl= eLikeClause *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)) + { + Oid seq_relid; + List *seq_options; + char *snamespace; + char *sname; + + /* + * find sequence owned by old column; extract sequence parameters; + * build new create sequence command + */ + seq_relid =3D getOwnedSequence(RelationGetRelid(relation), attribute->a= ttnum); + seq_options =3D sequence_options(seq_relid); + generateSerialExtraStmts(cxt, def, + InvalidOid, seq_options, DEPENDENCY_INTERNAL, + &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; @@ -1108,6 +1235,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; @@ -2630,6 +2758,7 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *st= mt, case AT_AlterColumnType: { ColumnDef *def =3D (ColumnDef *) cmd->def; + AttrNumber attnum; =20 /* * For ALTER COLUMN TYPE, transform the USING clause if @@ -2642,6 +2771,110 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *= stmt, EXPR_KIND_ALTER_COL_TRANSFORM); } =20 + /* + * For identity column, create ALTER SEQUENCE command to + * change the data type of the sequence. + */ + attnum =3D get_attnum(relid, cmd->name); + /* if attribute not found, something will error about it later */ + if (attnum !=3D InvalidAttrNumber && get_attidentity(relid, attnum) != =3D ' ') + { + Oid seq_relid =3D getOwnedSequence(relid, attnum); + Oid typeOid =3D typenameTypeId(pstate, def->typeName); + AlterSeqStmt *altseqstmt =3D makeNode(AlterSeqStmt); + + 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 bigin= t"))); + + altseqstmt->sequence =3D makeRangeVar(get_namespace_name(get_rel_nam= espace(seq_relid)), + get_rel_name(seq_relid), + -1); + altseqstmt->options =3D list_make1(makeDefElem("as", (Node *) makeTy= peNameFromOid(typeOid, -1), -1)); + cxt.blist =3D lappend(cxt.blist, altseqstmt); + } + + newcmds =3D lappend(newcmds, cmd); + break; + } + + case AT_AddIdentity: + { + Constraint *def =3D castNode(Constraint, cmd->def); + ColumnDef *newdef =3D makeNode(ColumnDef); + Oid typeOid; + char *snamespace; + char *sname; + + 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; + newdef->identity =3D def->generated_when; + cmd->def =3D (Node *) newdef; + + generateSerialExtraStmts(&cxt, newdef, + typeOid, def->options, DEPENDENCY_INTERNAL, + &snamespace, &sname); + + 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, castNode(List, cmd->def)) + { + DefElem *def =3D castNode(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; } diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rew= riteHandler.c index 20bbb37484..be3bc32c61 100644 --- a/src/backend/rewrite/rewriteHandler.c +++ b/src/backend/rewrite/rewriteHandler.c @@ -21,6 +21,7 @@ #include "postgres.h" =20 #include "access/sysattr.h" +#include "catalog/dependency.h" #include "catalog/pg_type.h" #include "commands/trigger.h" #include "foreign/fdwapi.h" @@ -61,6 +62,7 @@ static Query *rewriteRuleAction(Query *parsetree, static List *adjustJoinTreeList(Query *parsetree, bool removert, int rt_in= dex); static List *rewriteTargetListIU(List *targetList, CmdType commandType, + OverridingKind override, Relation target_relation, int result_rti, List **attrno_list); @@ -705,6 +707,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) @@ -785,6 +788,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 @@ -797,12 +801,51 @@ rewriteTargetListIU(List *targetList, * it's an INSERT and there's no tlist entry for the column, or the * tlist entry is a DEFAULT placeholder node. */ - if ((new_tle =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 - new_expr =3D build_column_default(target_relation, attrno); + if (att_tup->attidentity !=3D ' ') + { + NextValueExpr *nve =3D makeNode(NextValueExpr); + + nve->seqid =3D getOwnedSequence(RelationGetRelid(target_relation), att= rno); + nve->typeId =3D att_tup->atttypid; + + new_expr =3D (Node *) nve; + } + else + new_expr =3D build_column_default(target_relation, attrno); =20 /* * If there is no default (ie, default is effectively NULL), we @@ -3228,6 +3271,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); @@ -3240,6 +3284,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events) parsetree->targetList =3D rewriteTargetListIU(parsetree->targetList, parsetree->commandType, + parsetree->override, rt_entry_relation, parsetree->resultRelation, NULL); } @@ -3250,6 +3295,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); @@ -3259,7 +3305,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 b27b77de21..97cbe11332 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -5789,6 +5789,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 1b04c098d0..187daf2998 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 9001e202b0..df51d9ac6c 100644 --- a/src/backend/utils/cache/relcache.c +++ b/src/backend/utils/cache/relcache.c @@ -3267,6 +3267,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 46aadd76f7..7d09f9f809 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 7273ec8fe2..a70922b07a 100644 --- a/src/bin/pg_dump/pg_dump.c +++ b/src/bin/pg_dump/pg_dump.c @@ -1891,6 +1891,9 @@ dumpTableData_insert(Archive *fout, void *dcontext) appendPQExpBufferStr(insertStmt, ") "); } =20 + if (tbinfo->needs_override) + appendPQExpBufferStr(insertStmt, "OVERRIDING SYSTEM VALUE "); + appendPQExpBufferStr(insertStmt, "VALUES ("); } } @@ -5376,6 +5379,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 */ @@ -5452,6 +5456,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, " + "c.relkind =3D '%c' AND EXISTS (SELECT 1 FROM pg_depend WHERE clas= sid =3D 'pg_class'::regclass AND objid =3D c.oid AND objsubid =3D 0 AND ref= classid =3D 'pg_class'::regclass AND deptype =3D 'i') AS is_identity_sequen= ce, " "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 " @@ -5468,7 +5473,7 @@ getTables(Archive *fout, int *numTables) "(c.relkind =3D '%c' AND " "d.classid =3D c.tableoid AND d.objid =3D c.oid AND " "d.objsubid =3D 0 AND " - "d.refclassid =3D c.tableoid AND d.deptype =3D 'a') " + "d.refclassid =3D c.tableoid AND d.deptype IN ('a', 'i')) " "LEFT JOIN pg_class tc ON (c.reltoastrelid =3D tc.oid) " "LEFT JOIN pg_init_privs pip ON " "(c.oid =3D pip.objoid " @@ -5481,6 +5486,7 @@ getTables(Archive *fout, int *numTables) initacl_subquery->data, initracl_subquery->data, username_subquery, + RELKIND_SEQUENCE, attacl_subquery->data, attracl_subquery->data, attinitacl_subquery->data, @@ -5903,6 +5909,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) @@ -6003,6 +6010,8 @@ getTables(Archive *fout, int *numTables) tblinfo[i].dummy_view =3D false; /* might get set during sort */ 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. @@ -7565,6 +7574,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; @@ -7607,7 +7617,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. @@ -7706,6 +7743,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"); @@ -7721,6 +7759,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)); @@ -7745,6 +7784,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)); @@ -16019,10 +16060,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 @@ -16034,11 +16078,30 @@ 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 (strcmp(seqtype, "bigint") !=3D 0) + if (strcmp(seqtype, "bigint") !=3D 0 && + !tbinfo->is_identity_sequence) appendPQExpBuffer(query, " AS %s\n", seqtype); =20 if (fout->remoteVersion >=3D 80400) @@ -16060,7 +16123,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 @@ -16093,7 +16159,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 a466527ec6..f5ea2f7a6f 100644 --- a/src/bin/pg_dump/pg_dump.h +++ b/src/bin/pg_dump/pg_dump.h @@ -287,6 +287,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 */ @@ -305,6 +306,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 */ @@ -316,6 +318,7 @@ typedef struct _tableInfo struct _attrDefInfo **attrdefs; /* DEFAULT expressions */ struct _constraintInfo *checkexprs; /* CHECK constraints */ char *partkeydef; /* partition key definition */ + bool needs_override; /* has GENERATED ALWAYS AS IDENTITY */ =20 /* * Stuff computed only for dumpable tables. diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index e2e4cbcc08..920cb98371 100644 --- a/src/bin/psql/describe.c +++ b/src/bin/psql/describe.c @@ -1582,6 +1582,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 @@ -1756,12 +1760,24 @@ describeOneTableDetails(const char *schemaname, /* Collation, Nullable, Default */ if (show_column_details) { + char *identity; + char *default_str =3D ""; + printTableAddCell(&cont, PQgetvalue(res, i, 5), false, false); =20 printTableAddCell(&cont, strcmp(PQgetvalue(res, i, 3), "t") =3D=3D 0 ? = "not null" : "", false, false); =20 - /* (note: above we cut off the 'default' string at 128) */ - printTableAddCell(&cont, PQgetvalue(res, i, 2), false, false); + identity =3D PQgetvalue(res, i, 6); + + if (identity[0] =3D=3D ' ') + /* (note: above we cut off the 'default' string at 128) */ + default_str =3D PQgetvalue(res, i, 2); + else if (identity[0] =3D=3D 'a') + default_str =3D "generated always as identity"; + else if (identity[0] =3D=3D 'd') + default_str =3D "generated by default as identity"; + + printTableAddCell(&cont, default_str, false, false); } =20 /* Value: for sequences only */ @@ -1770,16 +1786,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 ddad71a10f..ac91cef3d2 100644 --- a/src/bin/psql/tab-complete.c +++ b/src/bin/psql/tab-complete.c @@ -1855,7 +1855,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")) @@ -1871,7 +1871,7 @@ psql_completion(const char *text, int start, int end) /* ALTER TABLE ALTER [COLUMN] DROP */ else if (Matches7("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny= , "DROP") || Matches6("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "DROP")) - COMPLETE_WITH_LIST2("DEFAULT", "NOT NULL"); + COMPLETE_WITH_LIST3("DEFAULT", "IDENTITY", "NOT NULL"); else if (Matches4("ALTER", "TABLE", MatchAny, "CLUSTER")) COMPLETE_WITH_CONST("ON"); else if (Matches5("ALTER", "TABLE", MatchAny, "CLUSTER", "ON")) @@ -2870,17 +2870,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 9e8c1c2a02..97e4e32987 100644 --- a/src/include/catalog/catversion.h +++ b/src/include/catalog/catversion.h @@ -53,6 +53,6 @@ */ =20 /* yyyymmddN */ -#define CATALOG_VERSION_NO 201702231 +#define CATALOG_VERSION_NO 201702285 =20 #endif diff --git a/src/include/catalog/dependency.h b/src/include/catalog/depende= ncy.h index 10759c7c58..ae040ca530 100644 --- a/src/include/catalog/dependency.h +++ b/src/include/catalog/dependency.h @@ -241,7 +241,8 @@ 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); +extern Oid getOwnedSequence(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 e861f4c589..1e28a626bc 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 3f96611f74..1734978d61 100644 --- a/src/include/catalog/pg_class.h +++ b/src/include/catalog/pg_class.h @@ -149,7 +149,7 @@ typedef FormData_pg_class *Form_pg_class; */ DATA(insert OID =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 f 3 1 _null_ _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 f 3 1 _null_ _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 f 3 1 _null_ _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 f 3 1 _null_ _null_ _null_)); DESCR(""); diff --git a/src/include/commands/sequence.h b/src/include/commands/sequenc= e.h index 49a77c42fc..304586e48e 100644 --- a/src/include/commands/sequence.h +++ b/src/include/commands/sequence.h @@ -51,7 +51,9 @@ typedef struct xl_seq_rec /* SEQUENCE TUPLE DATA FOLLOWS AT THE END */ } xl_seq_rec; =20 +extern int64 nextval_internal(Oid relid, bool check_permissions); extern Datum nextval(PG_FUNCTION_ARGS); +extern List *sequence_options(Oid relid); =20 extern ObjectAddress DefineSequence(ParseState *pstate, CreateSeqStmt *stm= t); extern ObjectAddress AlterSequence(ParseState *pstate, AlterSeqStmt *stmt); diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.h index 28aca928a8..feedd0f540 100644 --- a/src/include/nodes/nodes.h +++ b/src/include/nodes/nodes.h @@ -183,6 +183,7 @@ typedef enum NodeTag T_FromExpr, T_OnConflictExpr, T_IntoClause, + T_NextValueExpr, =20 /* * TAGS FOR EXPRESSION STATE NODES (execnodes.h) diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 5afc3ebea0..280342104a 100644 --- a/src/include/nodes/parsenodes.h +++ b/src/include/nodes/parsenodes.h @@ -27,6 +27,13 @@ #include "nodes/primnodes.h" #include "nodes/value.h" =20 +typedef enum OverridingKind +{ + OVERRIDING_NOT_SET =3D 0, + OVERRIDING_USER_VALUE, + OVERRIDING_SYSTEM_VALUE +} OverridingKind; + /* Possible sources of a Query */ typedef enum QuerySource { @@ -130,6 +137,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) */ @@ -604,6 +613,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 */ @@ -625,9 +635,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 @@ -1360,6 +1371,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 /* ---------------------- @@ -1669,7 +1681,10 @@ typedef enum AlterTableType AT_NoForceRowSecurity, /* NO FORCE ROW SECURITY */ AT_GenericOptions, /* OPTIONS (...) */ AT_AttachPartition, /* ATTACH PARTITION */ - AT_DetachPartition /* DETACH PARTITION */ + AT_DetachPartition, /* DETACH PARTITION */ + AT_AddIdentity, /* ADD IDENTITY */ + AT_SetIdentity, /* SET identity column options */ + AT_DropIdentity /* DROP IDENTITY */ } AlterTableType; =20 typedef struct ReplicaIdentityStmt @@ -1942,6 +1957,7 @@ typedef enum ConstrType /* types of constraints */ * expect it */ CONSTR_NOTNULL, CONSTR_DEFAULT, + CONSTR_IDENTITY, CONSTR_CHECK, CONSTR_PRIMARY, CONSTR_UNIQUE, @@ -1953,6 +1969,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' @@ -1980,6 +1999,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/nodes/primnodes.h b/src/include/nodes/primnodes.h index 235bc75096..ff50bc87a7 100644 --- a/src/include/nodes/primnodes.h +++ b/src/include/nodes/primnodes.h @@ -1270,6 +1270,20 @@ typedef struct InferenceElem Oid inferopclass; /* OID of att opclass, or InvalidOid */ } InferenceElem; =20 +/* + * NextValueExpr - get next value from sequence + * + * This has the same effect as calling the nextval() function, but it does= not + * check permissions on the sequence. This is used for identity columns, + * where the sequence is an implicit dependency without its own permission= s. + */ +typedef struct NextValueExpr +{ + Expr xpr; + Oid seqid; + Oid typeId; +} NextValueExpr; + /*-------------------- * TargetEntry - * a target entry (used in query target lists) diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.h index 985d6505ec..e1a0a53e16 100644 --- a/src/include/parser/kwlist.h +++ b/src/include/parser/kwlist.h @@ -173,6 +173,7 @@ PG_KEYWORD("from", FROM, RESERVED_KEYWORD) PG_KEYWORD("full", FULL, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("function", FUNCTION, UNRESERVED_KEYWORD) PG_KEYWORD("functions", FUNCTIONS, UNRESERVED_KEYWORD) +PG_KEYWORD("generated", GENERATED, UNRESERVED_KEYWORD) PG_KEYWORD("global", GLOBAL, UNRESERVED_KEYWORD) PG_KEYWORD("grant", GRANT, RESERVED_KEYWORD) PG_KEYWORD("granted", GRANTED, UNRESERVED_KEYWORD) @@ -285,6 +286,7 @@ PG_KEYWORD("outer", OUTER_P, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("over", OVER, UNRESERVED_KEYWORD) PG_KEYWORD("overlaps", OVERLAPS, TYPE_FUNC_NAME_KEYWORD) PG_KEYWORD("overlay", OVERLAY, COL_NAME_KEYWORD) +PG_KEYWORD("overriding", OVERRIDING, UNRESERVED_KEYWORD) PG_KEYWORD("owned", OWNED, UNRESERVED_KEYWORD) PG_KEYWORD("owner", OWNER, UNRESERVED_KEYWORD) PG_KEYWORD("parallel", PARALLEL, UNRESERVED_KEYWORD) diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h index b6d1fca2fa..88629d99aa 100644 --- a/src/include/utils/lsyscache.h +++ b/src/include/utils/lsyscache.h @@ -64,6 +64,7 @@ extern Oid get_opfamily_proc(Oid opfamily, Oid lefttype, = Oid righttype, extern char *get_attname(Oid relid, AttrNumber attnum); extern char *get_relid_attribute_name(Oid relid, AttrNumber attnum); extern AttrNumber get_attnum(Oid relid, const char *attname); +extern char get_attidentity(Oid relid, AttrNumber attnum); extern Oid get_atttype(Oid relid, AttrNumber attnum); extern int32 get_atttypmod(Oid relid, AttrNumber attnum); extern void get_atttypetypmodcoll(Oid relid, AttrNumber attnum, diff --git a/src/test/regress/expected/create_table_like.out b/src/test/reg= ress/expected/create_table_like.out index a25b221703..3f405c94ce 100644 --- a/src/test/regress/expected/create_table_like.out +++ b/src/test/regress/expected/create_table_like.out @@ -66,6 +66,53 @@ SELECT * FROM inhg; /* Two records with three columns in= order x=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 | Collation | Nullable | Default=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 | Collation | Nullable | Default=20 +--------+---------+-----------+----------+--------- + a | integer | | not null |=20 + 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 | Collation | Nullable | Default=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 0000000000..bf6158b315 --- /dev/null +++ b/src/test/regress/expected/identity.out @@ -0,0 +1,306 @@ +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 | 2147483647 = | 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 | 32767 = | 1 | NO + itest3 | b | | YES | NO | = | | | = | | NO +(6 rows) + +-- internal sequences should not be shown here +SELECT sequence_name FROM information_schema.sequences WHERE sequence_name= LIKE 'itest%'; + sequence_name=20 +--------------- +(0 rows) + +CREATE TABLE itest4 (a int, b text); +ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- er= ror, requires NOT NULL +ERROR: column "a" of relation "itest4" must be declared NOT NULL before i= dentity can be added +ALTER TABLE itest4 ALTER COLUMN a SET NOT NULL; +ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- ok +ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL; -- error, disallowed +ERROR: column "a" of relation "itest4" is an identity column +ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- er= ror, already set +ERROR: column "a" of relation "itest4" is already an identity column +ALTER TABLE itest4 ALTER COLUMN b ADD GENERATED ALWAYS AS IDENTITY; -- er= ror, wrong data type +ERROR: identity column type must be smallint, integer, or bigint +-- for later +ALTER TABLE itest4 ALTER COLUMN b SET DEFAULT ''; +-- invalid column type +CREATE TABLE itest_err_1 (a text generated by default as identity); +ERROR: identity column type must be smallint, integer, or bigint +-- duplicate identity +CREATE TABLE itest_err_2 (a int generated always as identity generated by = default as identity); +ERROR: multiple identity specifications for column "a" of table "itest_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; -- fails because NOT NULL is not dropp= ed +ERROR: null value in column "a" violates not-null constraint +DETAIL: Failing row contains (null, ). +ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL; +INSERT INTO itest4 DEFAULT VALUES; +SELECT * FROM itest4; + a | b=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 ALTER COLUMN tests +-- fail, not allowed for identity columns +ALTER TABLE itest1 ALTER COLUMN a SET DEFAULT 1; +ERROR: column "a" of relation "itest1" is an identity column +-- fail, not allowed, already has a default +CREATE TABLE itest5 (a serial, b text); +ALTER TABLE itest5 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; +ERROR: column "a" of relation "itest5" already has a default value +ALTER TABLE itest3 ALTER COLUMN a TYPE int; +SELECT seqtypid::regtype FROM pg_sequence WHERE seqrelid =3D 'itest3_a_seq= '::regclass; + seqtypid=20 +---------- + integer +(1 row) + +\d itest3 + Table "public.itest3" + Column | Type | Collation | Nullable | Default=20=20=20=20= =20=20=20=20=20=20=20=20=20=20 +--------+---------+-----------+----------+--------------------------------= -- + a | integer | | not null | generated by default as identity + b | text | | |=20 + +ALTER TABLE itest3 ALTER COLUMN a TYPE text; -- error +ERROR: identity column type must be smallint, integer, or bigint +-- ALTER COLUMN ... SET +CREATE TABLE itest6 (a int GENERATED ALWAYS AS IDENTITY, b text); +INSERT INTO itest6 DEFAULT VALUES; +ALTER TABLE itest6 ALTER COLUMN a SET GENERATED BY DEFAULT SET INCREMENT 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) + +-- identity property is not inherited +CREATE TABLE itest7a (b text) INHERITS (itest7); +-- make column identity in child table +CREATE TABLE itest7b (a int); +CREATE TABLE itest7c (a int GENERATED ALWAYS AS IDENTITY) INHERITS (itest7= b); +NOTICE: merging column "a" with inherited definition +INSERT INTO itest7c DEFAULT VALUES; +SELECT * FROM itest7c; + a=20 +--- + 1 +(1 row) + +CREATE TABLE itest7d (a int not null); +CREATE TABLE itest7e () INHERITS (itest7d); +ALTER TABLE itest7d ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; +ALTER TABLE itest7d ADD COLUMN b int GENERATED ALWAYS AS IDENTITY; -- err= or +ERROR: cannot recursively add identity column to table that has child tab= les +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 | NO |=20 + itest7a | b | YES | NO |=20 + itest7b | a | YES | NO |=20 + itest7c | a | NO | YES | ALWAYS + itest7d | a | NO | YES | ALWAYS + itest7e | a | NO | YES | ALWAYS +(7 rows) + +-- privileges +CREATE USER regress_user1; +CREATE TABLE itest8 (a int GENERATED ALWAYS AS IDENTITY, b text); +GRANT SELECT, INSERT ON itest8 TO regress_user1; +SET ROLE regress_user1; +INSERT INTO itest8 DEFAULT VALUES; +SELECT * FROM itest8; + a | b=20 +---+--- + 1 |=20 +(1 row) + +RESET ROLE; +DROP TABLE itest8; +DROP USER regress_user1; diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expe= cted/sequence.out index f339489151..469c082ad1 100644 --- a/src/test/regress/expected/sequence.out +++ b/src/test/regress/expected/sequence.out @@ -20,8 +20,8 @@ ERROR: CACHE (0) must be greater than zero CREATE SEQUENCE sequence_testx OWNED BY nobody; -- nonsense word ERROR: invalid OWNED BY option HINT: Specify OWNED BY table.column or OWNED BY NONE. -CREATE SEQUENCE sequence_testx OWNED BY pg_tables.tablename; -- not a tab= le -ERROR: referenced relation "pg_tables" is not a table or foreign table +CREATE SEQUENCE sequence_testx OWNED BY pg_class_oid_index.oid; -- not a = table +ERROR: referenced relation "pg_class_oid_index" is not a table or foreign= table CREATE SEQUENCE sequence_testx OWNED BY pg_class.relname; -- not same sch= ema ERROR: sequence must be in same schema as table it is linked to CREATE TABLE sequence_test_table (a int); diff --git a/src/test/regress/expected/truncate.out b/src/test/regress/expe= cted/truncate.out index 5c5277e0f1..c76a21788f 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 edeb2d6bc7..74d1805708 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -111,6 +111,11 @@ test: select_views portals_p2 foreign_key cluster 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 27a46d76d5..342e03f6e9 100644 --- a/src/test/regress/serial_schedule +++ b/src/test/regress/serial_schedule @@ -164,6 +164,7 @@ test: conversion test: truncate test: alter_table test: sequence +test: identity test: polymorphism test: rowtypes test: returning diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/= sql/create_table_like.sql index 900ca804cb..557040bbe7 100644 --- a/src/test/regress/sql/create_table_like.sql +++ b/src/test/regress/sql/create_table_like.sql @@ -37,6 +37,20 @@ 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 0000000000..775e8ef285 --- /dev/null +++ b/src/test/regress/sql/identity.sql @@ -0,0 +1,177 @@ +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; -- er= ror, requires NOT NULL +ALTER TABLE itest4 ALTER COLUMN a SET NOT NULL; +ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- ok +ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL; -- error, disallowed +ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- er= ror, already set +ALTER TABLE itest4 ALTER COLUMN b ADD GENERATED ALWAYS AS IDENTITY; -- er= ror, wrong data type + +-- for later +ALTER TABLE itest4 ALTER COLUMN b SET DEFAULT ''; + +-- invalid column type +CREATE TABLE itest_err_1 (a text generated by default as identity); + +-- duplicate identity +CREATE TABLE itest_err_2 (a int generated always as identity generated by = default as identity); + +-- cannot have default and identity +CREATE TABLE itest_err_3 (a int default 5 generated by default as identity= ); + +-- cannot combine serial and identity +CREATE TABLE itest_err_4 (a serial generated by default as identity); + +INSERT INTO itest1 DEFAULT VALUES; +INSERT INTO itest1 DEFAULT VALUES; +INSERT INTO itest2 DEFAULT VALUES; +INSERT INTO itest2 DEFAULT VALUES; +INSERT INTO itest3 DEFAULT VALUES; +INSERT INTO itest3 DEFAULT VALUES; +INSERT INTO itest4 DEFAULT VALUES; +INSERT INTO itest4 DEFAULT VALUES; + +SELECT * FROM itest1; +SELECT * FROM itest2; +SELECT * FROM itest3; +SELECT * FROM itest4; + + +-- OVERRIDING tests + +INSERT INTO itest1 VALUES (10, 'xyz'); +INSERT INTO itest1 OVERRIDING USER VALUE VALUES (10, 'xyz'); + +SELECT * FROM itest1; + +INSERT INTO itest2 VALUES (10, 'xyz'); +INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (10, 'xyz'); + +SELECT * FROM itest2; + + +-- UPDATE tests + +UPDATE itest1 SET a =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; -- fails because NOT NULL is not dropp= ed +ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL; +INSERT INTO itest4 DEFAULT VALUES; +SELECT * FROM itest4; + +-- check that sequence is removed +SELECT sequence_name FROM itest4_a_seq; + + +-- test views + +CREATE TABLE itest10 (a int generated by default as identity, b text); +CREATE TABLE itest11 (a int generated always as identity, b text); + +CREATE VIEW itestv10 AS SELECT * FROM itest10; +CREATE VIEW itestv11 AS SELECT * FROM itest11; + +INSERT INTO itestv10 DEFAULT VALUES; +INSERT INTO itestv10 DEFAULT VALUES; + +INSERT INTO itestv11 DEFAULT VALUES; +INSERT INTO itestv11 DEFAULT VALUES; + +SELECT * FROM itestv10; +SELECT * FROM itestv11; + +INSERT INTO itestv10 VALUES (10, 'xyz'); +INSERT INTO itestv10 OVERRIDING USER VALUE VALUES (11, 'xyz'); + +SELECT * FROM itestv10; + +INSERT INTO itestv11 VALUES (10, 'xyz'); +INSERT INTO itestv11 OVERRIDING SYSTEM VALUE VALUES (11, 'xyz'); + +SELECT * FROM itestv11; + + +-- various ALTER COLUMN tests + +-- fail, not allowed for identity columns +ALTER TABLE itest1 ALTER COLUMN a SET DEFAULT 1; + +-- fail, not allowed, already has a default +CREATE TABLE itest5 (a serial, b text); +ALTER TABLE itest5 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; + +ALTER TABLE itest3 ALTER COLUMN a TYPE int; +SELECT seqtypid::regtype FROM pg_sequence WHERE seqrelid =3D 'itest3_a_seq= '::regclass; +\d itest3 + +ALTER TABLE itest3 ALTER COLUMN a TYPE text; -- error + + +-- ALTER COLUMN ... SET + +CREATE TABLE itest6 (a int GENERATED ALWAYS AS IDENTITY, b text); +INSERT INTO itest6 DEFAULT VALUES; + +ALTER TABLE itest6 ALTER COLUMN a SET GENERATED BY DEFAULT SET INCREMENT 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; + +-- identity property is not inherited +CREATE TABLE itest7a (b text) INHERITS (itest7); + +-- make column identity in child table +CREATE TABLE itest7b (a int); +CREATE TABLE itest7c (a int GENERATED ALWAYS AS IDENTITY) INHERITS (itest7= b); +INSERT INTO itest7c DEFAULT VALUES; +SELECT * FROM itest7c; + +CREATE TABLE itest7d (a int not null); +CREATE TABLE itest7e () INHERITS (itest7d); +ALTER TABLE itest7d ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; +ALTER TABLE itest7d ADD COLUMN b int GENERATED ALWAYS AS IDENTITY; -- err= or + +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; + +-- privileges +CREATE USER regress_user1; +CREATE TABLE itest8 (a int GENERATED ALWAYS AS IDENTITY, b text); +GRANT SELECT, INSERT ON itest8 TO regress_user1; +SET ROLE regress_user1; +INSERT INTO itest8 DEFAULT VALUES; +SELECT * FROM itest8; +RESET ROLE; +DROP TABLE itest8; +DROP USER regress_user1; diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/seque= nce.sql index 0fbd255967..18fa6ae5e9 100644 --- a/src/test/regress/sql/sequence.sql +++ b/src/test/regress/sql/sequence.sql @@ -13,7 +13,7 @@ CREATE SEQUENCE sequence_testx CACHE 0; =20 -- OWNED BY errors CREATE SEQUENCE sequence_testx OWNED BY nobody; -- nonsense word -CREATE SEQUENCE sequence_testx OWNED BY pg_tables.tablename; -- not a tab= le +CREATE SEQUENCE sequence_testx OWNED BY pg_class_oid_index.oid; -- not a = table CREATE SEQUENCE sequence_testx OWNED BY pg_class.relname; -- not same sch= ema CREATE TABLE sequence_test_table (a int); CREATE SEQUENCE sequence_testx OWNED BY sequence_test_table.b; -- wrong c= olumn diff --git a/src/test/regress/sql/truncate.sql b/src/test/regress/sql/trunc= ate.sql index a3d6f5368f..1aab021dbb 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; --=20 2.12.0 --------------D63BFAABA96C254008719BF2 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 --------------D63BFAABA96C254008719BF2--