From: Peter Eisentraut <peter.eisentraut@2ndquadrant.com>
To: Vitaly Burovoy <vitaly.burovoy@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: identity columns
Date: Tue, 21 Mar 2017 12:13:19 -0400
Message-ID: <ed41b243-63b2-f287-e7b0-8b2ac7266d66@2ndquadrant.com> (raw)
In-Reply-To: <CAKOSWNmKbthEdV0m1J=V7r6nDyPk8diKwHW93jYbB+wnXd-c1A@mail.gmail.com>
References: <6adbacbf-73bc-dd1a-2033-63409180fd18@2ndquadrant.com>
<CAKOSWNk3Ug6CV5RBPhF1UCiaR32ZTxdtf-iZ+uv6C3rP6cN8QA@mail.gmail.com>
<CAKOSWNkKUAb1qwubq+MhxbEyT0ewM2NYsXKUskxheg_dUWnWPw@mail.gmail.com>
<4ef801f2-64ce-1a58-277d-d9389ae7770d@2ndquadrant.com>
<e287f723-d095-0d24-483a-3b7e3611c3e9@2ndquadrant.com>
<CAKOSWNmsci7jZzpoqWAH_QcVjQzwOMf04o91Jf5WVTkHbDMy2w@mail.gmail.com>
<1cf308b6-4f5c-05ba-cc74-0a7121603fcf@2ndquadrant.com>
<CAKOSWNmKbthEdV0m1J=V7r6nDyPk8diKwHW93jYbB+wnXd-c1A@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
On 3/20/17 05:43, Vitaly Burovoy wrote:
>>> 1. The fact COPY ignores GENERATED ALWAYS constraint (treats as
>>> GENERATED BY DEFAULT) should be mentioned as well as rules.
> I think it would be better if that behavior is placed on both CREATE
> TABLE and COPY pages.
done
>>> 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?
>
> The column mentioned there does not exist. Therefore the error should
> be about it, not about a type of absent column:
This was already fixed in the previous version.
> Since we extend the spec by "ADD GENERATED...", "SEQUENCE NAME" and
> allow more than one identity column, why can't we extend it by
> allowing "SET GENERATED" for non-identity columns and drop "ADD
> GENERATED..."?
SQL commands generally don't work that way. Either they create or they
alter. There are "OR REPLACE" options when you do both. So I think it
is better to keep these two things separate. Also, while you argue that
we *could* do it the way you propose, I don't really see an argument why
it would be better.
>>> 6. The docs mention a syntax:
>>> ALTER [ COLUMN ] <replaceable
>>> class="PARAMETER">column_name</replaceable> { SET GENERATED { ALWAYS |
>>> BY DEFAULT } | SET <replaceable>sequence_option</replaceable> | 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.
>
> I'm sorry, it still does not work for me (whether you mix it up with "RESTART"):
> test=# ALTER TABLE idnt ALTER COLUMN i RESET;
> ERROR: syntax error at or near ";"
> LINE 1: ALTER TABLE idnt ALTER COLUMN i RESET;
Oh I see, the documentation was wrong. The correct syntax is RESTART
rather than RESET. Fixed the documentation.
>>> 11. The last CF added partitioned tables which are similar to
>>> inherited, but slightly different.
>>
>> fixed
>
> Something is left to be fixed:
> test=# CREATE TABLE measurement (
> test(# i int GENERATED BY DEFAULT AS IDENTITY,
> test(# logdate date not null,
> test(# peaktemp int,
> test(# unitsales int
> test(# ) PARTITION BY RANGE (logdate);
> CREATE TABLE
> test=# CREATE TABLE measurement_y2016m07
> test-# PARTITION OF measurement (
> test(# unitsales DEFAULT 0
> test(# ) FOR VALUES FROM ('2016-07-01') TO ('2016-08-01');
> CREATE TABLE
> test=# ALTER TABLE MEASUREMENT ALTER i RESTART 2;
> ERROR: column "i" of relation "measurement_y2016m07" is not an identity column
fixed
> The patch should be rebased to the current master. It has easy
> conflicts in describe.c in the commit
> 395bfaae8e786eb17fc9dd79e4636f97c9d9b820.
done
> Please, don't include the file catversion.h in it because it is
> changed often and leads to error when applying.
OK
> 16. changing a type does not change an underlying sequence type (its limits):
It does change the type, but changing the type doesn't change the
limits. That is a property of how ALTER SEQUENCE works, which was
separately discussed.
> 17. how to restart a sequence?
> test=# ALTER TABLE itest3 ALTER a SET RESTART 2;
> ERROR: sequence option "restart" not supported here
> LINE 1: ALTER TABLE itest3 ALTER a SET RESTART 2;
>
> Khm. The "RESTART" is one of official "sequence_options" (comparable
> to the "SEQUENCE NAME"), why it is not allowed?
> But there is another DDL which works OK, but not reflected in the docs:
> test=# ALTER TABLE itest3 ALTER a RESTART 2;
> ALTER TABLE
Yes, that is now fixed. See #6 above.
> 18. If there is no sequence owned by a column, all DDLs for a sequence
> behind an identity column do not raise errors but do nothing:
> test=# CREATE TABLE itest3 (a int generated by default as identity
> (start with 7), b text);
> CREATE TABLE
> test=# ALTER SEQUENCE itest3_a_seq OWNED BY NONE;
> ALTER SEQUENCE
I have changed it to prohibit changing OWNED BY of an identity sequence
directly, so this can't happen anymore.
> 19. If anyone occasionally drops OWNED BY for the linked sequence
> there is no ways to restore it "as was":
fixed, see above
> 20. sequence does not follow the table owned by:
fixed
> 21. There are many places where error codes look strange:
> test=# ALTER TABLE itest3 ALTER b SET GENERATED BY DEFAULT; --
> whether it is "internal_error" or user's error?
> ERROR: XX000: column "b" of relation "itest3" is not an identity column
I added error codes to the places that were missing them.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackersAttachments:v5-0001-Identity-columns.patch (application/x-patch, 149.8K) download
From 3f65ff393ff6a8171600eb0ae3b3442bbbbda1e8 Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <peter_e@gmx.net>
Date: Tue, 28 Feb 2017 21:09:02 -0500
Subject: [PATCH v5] 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 | 36 ++-
doc/src/sgml/ref/copy.sgml | 7 +
doc/src/sgml/ref/create_table.sgml | 65 +++-
doc/src/sgml/ref/insert.sgml | 41 +++
src/backend/access/common/tupdesc.c | 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 | 52 ++--
src/backend/catalog/sql_features.txt | 12 +-
src/backend/commands/sequence.c | 109 +++++--
src/backend/commands/tablecmds.c | 284 +++++++++++++++++-
src/backend/executor/execQual.c | 21 ++
src/backend/nodes/copyfuncs.c | 21 ++
src/backend/nodes/equalfuncs.c | 16 +
src/backend/nodes/makefuncs.c | 2 +
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 | 128 +++++++-
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 | 91 +++++-
src/bin/pg_dump/pg_dump.h | 3 +
src/bin/psql/describe.c | 27 +-
src/bin/psql/tab-complete.c | 18 +-
src/include/catalog/dependency.h | 8 +-
src/include/catalog/pg_attribute.h | 25 +-
src/include/catalog/pg_class.h | 2 +-
src/include/commands/sequence.h | 2 +
src/include/nodes/nodes.h | 1 +
src/include/nodes/parsenodes.h | 25 +-
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 | 314 ++++++++++++++++++++
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 | 187 ++++++++++++
src/test/regress/sql/sequence.sql | 2 +-
src/test/regress/sql/truncate.sql | 18 ++
54 files changed, 1996 insertions(+), 197 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.sgmlindex df0435c3f0..394b9e123a 100644--- a/doc/src/sgml/catalogs.sgml+++ b/doc/src/sgml/catalogs.sgml@@ -1120,6 +1120,17 @@ <title><structname>pg_attribute</> Columns</title>
</row>
<row>
+ <entry><structfield>attidentity</structfield></entry>+ <entry><type>char</type></entry>+ <entry></entry>+ <entry>+ If a space character, then not an identity column. Otherwise,+ <literal>a</literal> = generated always, <literal>d</literal> =+ generated by default.+ </entry>+ </row>++ <row>
<entry><structfield>attisdropped</structfield></entry>
<entry><type>bool</type></entry>
<entry></entry>
diff --git a/doc/src/sgml/information_schema.sgml b/doc/src/sgml/information_schema.sgmlindex a3a19ce8ce..02f7927436 100644--- a/doc/src/sgml/information_schema.sgml+++ b/doc/src/sgml/information_schema.sgml@@ -1583,13 +1583,20 @@ <title><literal>columns</literal> Columns</title>
<row>
<entry><literal>is_identity</literal></entry>
<entry><type>yes_or_no</type></entry>
- <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>+ <entry>+ If the column is an identity column, then <literal>YES</literal>,+ else <literal>NO</literal>.+ </entry>
</row>
<row>
<entry><literal>identity_generation</literal></entry>
<entry><type>character_data</type></entry>
- <entry>Applies to a feature not available in <productname>PostgreSQL</></entry>+ <entry>+ If the column is an identity column, then <literal>ALWAYS</literal>+ or <literal>BY DEFAULT</literal>, reflecting the definition of the+ column.+ </entry>
</row>
<row>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgmlindex 077c00373d..458ac9f9e7 100644--- a/doc/src/sgml/ref/alter_table.sgml+++ b/doc/src/sgml/ref/alter_table.sgml@@ -46,6 +46,9 @@
ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET DEFAULT <replaceable class="PARAMETER">expression</replaceable>
ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> DROP DEFAULT
ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> { SET | DROP } NOT NULL
+ ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ]+ ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> { SET GENERATED { ALWAYS | BY DEFAULT } | SET <replaceable>sequence_option</replaceable> | RESTART [ [ WITH ] <replaceable class="parameter">restart</replaceable> ] } [...]+ ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> DROP IDENTITY
ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET STATISTICS <replaceable class="PARAMETER">integer</replaceable>
ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> SET ( <replaceable class="PARAMETER">attribute_option</replaceable> = <replaceable class="PARAMETER">value</replaceable> [, ... ] )
ALTER [ COLUMN ] <replaceable class="PARAMETER">column_name</replaceable> RESET ( <replaceable class="PARAMETER">attribute_option</replaceable> [, ... ] )
@@ -180,6 +183,32 @@ <title>Description</title>
</varlistentry>
<varlistentry>
+ <term><literal>ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY</literal></term>+ <term><literal>SET GENERATED { ALWAYS | BY DEFAULT }</literal></term>+ <term><literal>DROP IDENTITY</literal></term>+ <listitem>+ <para>+ These forms change whether a column is an identity column or change the+ generation attribute of an existing identity column.+ See <xref linkend="sql-createtable"> for details.+ </para>+ </listitem>+ </varlistentry>++ <varlistentry>+ <term><literal>SET <replaceable>sequence_option</replaceable></literal></term>+ <term><literal>RESTART</literal></term>+ <listitem>+ <para>+ These forms alter the sequence that underlies an existing identity+ column. <replaceable>sequence_option</replaceable> is an option+ supported by <xref linkend="sql-altersequence"> such+ as <literal>INCREMENT BY</literal>.+ </para>+ </listitem>+ </varlistentry>++ <varlistentry>
<term><literal>SET STATISTICS</literal></term>
<listitem>
<para>
@@ -1152,8 +1181,11 @@ <title>Notes</title>
</para>
<para>
- The <literal>TRIGGER</>, <literal>CLUSTER</>, <literal>OWNER</>,- and <literal>TABLESPACE</> actions never recurse to descendant tables;+ The actions for identity columns (<literal>ADD+ GENERATED</literal>, <literal>SET</literal> etc., <literal>DROP+ IDENTITY</literal>), as well as the actions+ <literal>TRIGGER</>, <literal>CLUSTER</>, <literal>OWNER</>,+ and <literal>TABLESPACE</> never recurse to descendant tables;
that is, they always act as though <literal>ONLY</> were specified.
Adding a constraint recurses only for <literal>CHECK</> constraints
that are not marked <literal>NO INHERIT</>.
diff --git a/doc/src/sgml/ref/copy.sgml b/doc/src/sgml/ref/copy.sgmlindex 7ff62f2a82..215efcd69d 100644--- a/doc/src/sgml/ref/copy.sgml+++ b/doc/src/sgml/ref/copy.sgml@@ -480,6 +480,13 @@ <title>Notes</title>
</para>
<para>
+ For identity columns, the <command>COPY FROM</command> command will always+ write the column values provided in the input data, like+ the <command>INPUT</command> option <literal>OVERRIDING SYSTEM+ VALUE</literal>.+ </para>++ <para>
<command>COPY</command> input and output is affected by
<varname>DateStyle</varname>. To ensure portability to other
<productname>PostgreSQL</productname> installations that might use
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgmlindex 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 ( <replaceable class="PARAMETER">expression</replaceable> ) [ NO INHERIT ] |
DEFAULT <replaceable>default_expr</replaceable> |
+ GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ] |
UNIQUE <replaceable class="PARAMETER">index_parameters</replaceable> |
PRIMARY KEY <replaceable class="PARAMETER">index_parameters</replaceable> |
REFERENCES <replaceable class="PARAMETER">reftable</replaceable> [ ( <replaceable class="PARAMETER">refcolumn</replaceable> ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
@@ -81,7 +82,7 @@
<phrase>and <replaceable class="PARAMETER">like_option</replaceable> is:</phrase>
-{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | INDEXES | STORAGE | COMMENTS | ALL }+{ INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS | IDENTITY | INDEXES | STORAGE | COMMENTS | ALL }
<phrase>and <replaceable class="PARAMETER">partition_bound_spec</replaceable> is:</phrase>
@@ -412,6 +413,11 @@ <title>Parameters</title>
Column <literal>STORAGE</> settings are also copied from parent tables.
</para>
+ <para>+ 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.+ </para>
</listitem>
</varlistentry>
@@ -481,6 +487,12 @@ <title>Parameters</title>
the original and new tables.
</para>
<para>
+ Any identity specifications of copied column definitions will only be+ copied if <literal>INCLUDING IDENTITY</literal> is specified. A new+ sequence is created for each identity column of the new table, separate+ from the sequences associated with the old table.+ </para>+ <para>
Not-null constraints are always copied to the new table.
<literal>CHECK</literal> constraints will be copied only if
<literal>INCLUDING CONSTRAINTS</literal> is specified.
@@ -512,7 +524,7 @@ <title>Parameters</title>
</para>
<para>
<literal>INCLUDING ALL</literal> is an abbreviated form of
- <literal>INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS</literal>.+ <literal>INCLUDING DEFAULTS INCLUDING IDENTITY INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS</literal>.
</para>
<para>
Note that unlike <literal>INHERITS</literal>, columns and
@@ -627,6 +639,37 @@ <title>Parameters</title>
</varlistentry>
<varlistentry>
+ <term><literal>GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( <replaceable>sequence_options</replaceable> ) ]</literal></term>+ <listitem>+ <para>+ This clause creates the column as an <firstterm>identity+ column</firstterm>. 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.+ </para>++ <para>+ The clauses <literal>ALWAYS</literal> and <literal>BY DEFAULT</literal>+ determine how the sequence value is given precedence over a+ user-specified value in an <command>INSERT</command> statement.+ If <literal>ALWAYS</literal> is specified, a user-specified value is+ only accepted if the <command>INSERT</command> statement+ specifies <literal>OVERRIDING SYSTEM VALUE</literal>. If <literal>BY+ DEFAULT</literal> is specified, then the user-specified value takes+ precedence. See <xref linkend="sql-insert"> for details. (In+ the <command>COPY</command> command, user-specified values are always+ used regardless of this setting.)+ </para>++ <para>+ The optional <replaceable>sequence_options</replaceable> clause can be+ used to override the options of the sequence.+ See <xref linkend="sql-createsequence"> for details.+ </para>+ </listitem>+ </varlistentry>++ <varlistentry>
<term><literal>UNIQUE</> (column constraint)</term>
<term><literal>UNIQUE ( <replaceable class="PARAMETER">column_name</replaceable> [, ... ] )</> (table constraint)</term>
@@ -1259,7 +1302,7 @@ <title>Notes</title>
<para>
Using OIDs in new applications is not recommended: where
- possible, using a <literal>SERIAL</literal> 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 @@ <title>Examples</title>
);
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 <> '')
);
</programlisting>
@@ -1734,6 +1777,20 @@ <title>Zero-column Tables</title>
</refsect2>
<refsect2>
+ <title>Multiple Identity Columns</title>++ <para>+ <productname>PostgreSQL</productname> 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 <command>INSERT</command> command supports only one override clause+ that applies to the entire statement, so having multiple identity columns+ with different behaviors is not well supported.+ </para>+ </refsect2>++ <refsect2>
<title><literal>LIKE</> Clause</title>
<para>
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgmlindex 521216b5d5..95aa77b907 100644--- a/doc/src/sgml/ref/insert.sgml+++ b/doc/src/sgml/ref/insert.sgml@@ -23,6 +23,7 @@
<synopsis>
[ WITH [ RECURSIVE ] <replaceable class="parameter">with_query</replaceable> [, ...] ]
INSERT INTO <replaceable class="PARAMETER">table_name</replaceable> [ AS <replaceable class="parameter">alias</replaceable> ] [ ( <replaceable class="PARAMETER">column_name</replaceable> [, ...] ) ]
+ [ OVERRIDING { SYSTEM | USER} VALUE ]
{ DEFAULT VALUES | VALUES ( { <replaceable class="PARAMETER">expression</replaceable> | DEFAULT } [, ...] ) [, ...] | <replaceable class="PARAMETER">query</replaceable> }
[ ON CONFLICT [ <replaceable class="parameter">conflict_target</replaceable> ] <replaceable class="parameter">conflict_action</replaceable> ]
[ RETURNING * | <replaceable class="parameter">output_expression</replaceable> [ [ AS ] <replaceable class="parameter">output_name</replaceable> ] [, ...] ]
@@ -202,10 +203,43 @@ <title id="sql-inserting-params-title">Inserting</title>
</varlistentry>
<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 defined+ 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 ignored+ and the default sequence-generated values are applied.+ </para>++ <para>+ This clause is useful for example when copying values between tables.+ Writing <literal>INSERT INTO tbl2 OVERRIDING USER VALUE SELECT * FROM+ tbl1</literal> will copy from <literal>tbl1</literal> all columns that+ are not identity columns in <literal>tbl2</literal> but will continue+ 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</title>
</para>
<para>
+ The SQL standard specifies that <literal>OVERRIDING SYSTEM VALUE</literal>+ 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.+ </para>++ <para>
Possible limitations of the <replaceable
class="PARAMETER">query</replaceable> clause are documented under
<xref linkend="sql-select">.
diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.cindex 4e2ebe1ae7..f0ecf48b22 100644--- a/src/backend/access/common/tupdesc.c+++ b/src/backend/access/common/tupdesc.c@@ -150,6 +150,7 @@ CreateTupleDescCopy(TupleDesc tupdesc)
memcpy(desc->attrs[i], tupdesc->attrs[i], ATTRIBUTE_FIXED_PART_SIZE);
desc->attrs[i]->attnotnull = false;
desc->attrs[i]->atthasdef = false;
+ desc->attrs[i]->attidentity = ATTRIBUTE_IDENTITY_NONE;
}
desc->tdtypeid = tupdesc->tdtypeid;
@@ -257,6 +258,7 @@ TupleDescCopyEntry(TupleDesc dst, AttrNumber dstAttno,
/* since we're not copying constraints or defaults, clear these */
dst->attrs[dstAttno - 1]->attnotnull = false;
dst->attrs[dstAttno - 1]->atthasdef = false;
+ dst->attrs[dstAttno - 1]->attidentity = ATTRIBUTE_IDENTITY_NONE;
}
/*
@@ -401,6 +403,8 @@ equalTupleDescs(TupleDesc tupdesc1, TupleDesc tupdesc2)
return false;
if (attr1->atthasdef != attr2->atthasdef)
return false;
+ if (attr1->attidentity != attr2->attidentity)+ return false;
if (attr1->attisdropped != attr2->attisdropped)
return false;
if (attr1->attislocal != attr2->attislocal)
@@ -534,6 +538,7 @@ TupleDescInitEntry(TupleDesc desc,
att->attnotnull = false;
att->atthasdef = false;
+ att->attidentity = ATTRIBUTE_IDENTITY_NONE;
att->attisdropped = false;
att->attislocal = true;
att->attinhcount = 0;
diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.cindex 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 = (NextValueExpr *) node;++ add_object_address(OCLASS_CLASS, nve->seqid, 0,+ context->addrs);+ }
return expression_tree_walker(node, find_expr_references_walker,
(void *) context);
diff --git a/src/backend/catalog/genbki.pl b/src/backend/catalog/genbki.plindex 079516ca2f..93c1274b7f 100644--- a/src/backend/catalog/genbki.pl+++ b/src/backend/catalog/genbki.pl@@ -409,6 +409,7 @@ sub emit_pgattr_row
attcacheoff => '-1',
atttypmod => '-1',
atthasdef => 'f',
+ attidentity => "' '",
attisdropped => 'f',
attislocal => 't',
attinhcount => '0',
@@ -425,6 +426,7 @@ sub bki_insert
my @attnames = @_;
my $oid = $row->{oid} ? "OID = $row->{oid} " : '';
my $bki_values = join ' ', map $row->{$_}, @attnames;
+ $bki_values =~ s/'/"/g;
printf BKI "insert %s( %s)\n", $oid, $bki_values;
}
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.cindex 41c0056556..c0479510c9 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, Oid datum);
static FormData_pg_attribute a1 = {
0, {"ctid"}, TIDOID, 0, sizeof(ItemPointerData),
SelfItemPointerAttributeNumber, 0, -1, -1,
- false, 'p', 's', true, false, false, true, 0+ false, 'p', 's', true, false, ATTRIBUTE_IDENTITY_NONE, false, true, 0
};
static FormData_pg_attribute a2 = {
0, {"oid"}, OIDOID, 0, sizeof(Oid),
ObjectIdAttributeNumber, 0, -1, -1,
- true, 'p', 'i', true, false, false, true, 0+ true, 'p', 'i', true, false, ATTRIBUTE_IDENTITY_NONE, false, true, 0
};
static FormData_pg_attribute a3 = {
0, {"xmin"}, XIDOID, 0, sizeof(TransactionId),
MinTransactionIdAttributeNumber, 0, -1, -1,
- true, 'p', 'i', true, false, false, true, 0+ true, 'p', 'i', true, false, ATTRIBUTE_IDENTITY_NONE, false, true, 0
};
static FormData_pg_attribute a4 = {
0, {"cmin"}, CIDOID, 0, sizeof(CommandId),
MinCommandIdAttributeNumber, 0, -1, -1,
- true, 'p', 'i', true, false, false, true, 0+ true, 'p', 'i', true, false, ATTRIBUTE_IDENTITY_NONE, false, true, 0
};
static FormData_pg_attribute a5 = {
0, {"xmax"}, XIDOID, 0, sizeof(TransactionId),
MaxTransactionIdAttributeNumber, 0, -1, -1,
- true, 'p', 'i', true, false, false, true, 0+ true, 'p', 'i', true, false, ATTRIBUTE_IDENTITY_NONE, false, true, 0
};
static FormData_pg_attribute a6 = {
0, {"cmax"}, CIDOID, 0, sizeof(CommandId),
MaxCommandIdAttributeNumber, 0, -1, -1,
- true, 'p', 'i', true, false, false, true, 0+ true, 'p', 'i', true, false, ATTRIBUTE_IDENTITY_NONE, false, true, 0
};
/*
@@ -184,7 +184,7 @@ static FormData_pg_attribute a6 = {
static FormData_pg_attribute a7 = {
0, {"tableoid"}, OIDOID, 0, sizeof(Oid),
TableOidAttributeNumber, 0, -1, -1,
- true, 'p', 'i', true, false, false, true, 0+ true, 'p', 'i', true, false, ATTRIBUTE_IDENTITY_NONE, false, true, 0
};
static const Form_pg_attribute SysAtt[] = {&a1, &a2, &a3, &a4, &a5, &a6, &a7};
@@ -618,6 +618,7 @@ InsertPgAttributeTuple(Relation pg_attribute_rel,
values[Anum_pg_attribute_attalign - 1] = CharGetDatum(new_attribute->attalign);
values[Anum_pg_attribute_attnotnull - 1] = BoolGetDatum(new_attribute->attnotnull);
values[Anum_pg_attribute_atthasdef - 1] = BoolGetDatum(new_attribute->atthasdef);
+ values[Anum_pg_attribute_attidentity - 1] = CharGetDatum(new_attribute->attidentity);
values[Anum_pg_attribute_attisdropped - 1] = BoolGetDatum(new_attribute->attisdropped);
values[Anum_pg_attribute_attislocal - 1] = BoolGetDatum(new_attribute->attislocal);
values[Anum_pg_attribute_attinhcount - 1] = Int32GetDatum(new_attribute->attinhcount);
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.cindex 8d42a347ea..941303fddd 100644--- a/src/backend/catalog/index.c+++ b/src/backend/catalog/index.c@@ -353,6 +353,7 @@ ConstructTupleDescriptor(Relation heapRelation,
to->attcacheoff = -1;
to->attnotnull = false;
to->atthasdef = false;
+ to->attidentity = ATTRIBUTE_IDENTITY_NONE;
to->attislocal = true;
to->attinhcount = 0;
to->attcollation = collationObjectId[i];
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sqlindex fa2a88fc5c..2185734b48 100644--- a/src/backend/catalog/information_schema.sql+++ b/src/backend/catalog/information_schema.sql@@ -728,13 +728,13 @@ CREATE VIEW columns AS
CAST(a.attnum AS sql_identifier) AS dtd_identifier,
CAST('NO' AS yes_or_no) AS is_self_referencing,
- CAST('NO' AS yes_or_no) AS is_identity,- CAST(null AS character_data) AS identity_generation,- CAST(null AS character_data) AS identity_start,- CAST(null AS character_data) AS identity_increment,- CAST(null AS character_data) AS identity_maximum,- CAST(null AS character_data) AS identity_minimum,- CAST(null AS yes_or_no) AS identity_cycle,+ CAST(CASE WHEN a.attidentity IN ('a', 'd') THEN 'YES' ELSE 'NO' END AS yes_or_no) AS is_identity,+ CAST(CASE a.attidentity WHEN 'a' THEN 'ALWAYS' WHEN 'd' THEN 'BY DEFAULT' END AS character_data) AS identity_generation,+ CAST(seq.seqstart AS character_data) AS identity_start,+ CAST(seq.seqincrement AS character_data) AS identity_increment,+ CAST(seq.seqmax AS character_data) AS identity_maximum,+ CAST(seq.seqmin AS character_data) AS identity_minimum,+ CAST(CASE WHEN seq.seqcycle THEN 'YES' ELSE 'NO' END AS yes_or_no) AS identity_cycle,
CAST('NEVER' AS character_data) AS is_generated,
CAST(null AS character_data) AS generation_expression,
@@ -751,6 +751,8 @@ CREATE VIEW columns AS
ON (t.typtype = 'd' AND t.typbasetype = bt.oid)
LEFT JOIN (pg_collation co JOIN pg_namespace nco ON (co.collnamespace = nco.oid))
ON a.attcollation = co.oid AND (nco.nspname, co.collname) <> ('pg_catalog', 'default')
+ LEFT JOIN (pg_depend dep JOIN pg_sequence seq ON (dep.classid = 'pg_class'::regclass AND dep.objid = seq.seqrelid AND dep.deptype = 'i'))+ ON (dep.refclassid = 'pg_class'::regclass AND dep.refobjid = c.oid AND dep.refobjsubid = a.attnum)
WHERE (NOT pg_is_other_temp_schema(nc.oid))
@@ -1545,6 +1547,7 @@ CREATE VIEW sequences AS
FROM pg_namespace nc, pg_class c, pg_sequence s
WHERE c.relnamespace = nc.oid
AND c.relkind = 'S'
+ AND NOT EXISTS (SELECT 1 FROM pg_depend WHERE classid = 'pg_class'::regclass AND objid = c.oid AND deptype = 'i')
AND (NOT pg_is_other_temp_schema(nc.oid))
AND c.oid = s.seqrelid
AND (pg_has_role(c.relowner, 'USAGE')
diff --git a/src/backend/catalog/pg_depend.c b/src/backend/catalog/pg_depend.cindex d0ee851215..aae879e355 100644--- a/src/backend/catalog/pg_depend.c+++ b/src/backend/catalog/pg_depend.c@@ -488,7 +488,7 @@ getExtensionOfObject(Oid classId, Oid objectId)
/*
* Detect whether a sequence is marked as "owned" by a column
*
- * An ownership marker is an AUTO dependency from the sequence to the+ * An ownership marker is an AUTO or INTERNAL dependency from the sequence to the
* column. If we find one, store the identity of the owning column
* into *tableId and *colId and return TRUE; else return FALSE.
*
@@ -497,7 +497,7 @@ getExtensionOfObject(Oid classId, Oid objectId)
* not happen, though.
*/
bool
-sequenceIsOwned(Oid seqId, Oid *tableId, int32 *colId)+sequenceIsOwned(Oid seqId, char deptype, Oid *tableId, int32 *colId)
{
bool ret = false;
Relation depRel;
@@ -524,7 +524,7 @@ sequenceIsOwned(Oid seqId, Oid *tableId, int32 *colId)
Form_pg_depend depform = (Form_pg_depend) GETSTRUCT(tup);
if (depform->refclassid == RelationRelationId &&
- depform->deptype == DEPENDENCY_AUTO)+ depform->deptype == deptype)
{
*tableId = depform->refobjid;
*colId = depform->refobjsubid;
@@ -541,27 +541,15 @@ sequenceIsOwned(Oid seqId, Oid *tableId, int32 *colId)
}
/*
- * Remove any existing "owned" markers for the specified sequence.- *- * Note: we don't provide a special function to install an "owned"- * marker; just use recordDependencyOn().- */-void-markSequenceUnowned(Oid seqId)-{- deleteDependencyRecordsForClass(RelationRelationId, seqId,- RelationRelationId, DEPENDENCY_AUTO);-}--/*- * Collect a list of OIDs of all sequences owned by the specified relation.+ * Collect a list of OIDs of all sequences owned by the specified relation,+ * and column if specified.
*/
List *
-getOwnedSequences(Oid relid)+getOwnedSequences(Oid relid, AttrNumber attnum)
{
List *result = NIL;
Relation depRel;
- ScanKeyData key[2];+ ScanKeyData key[3];
SysScanDesc scan;
HeapTuple tup;
@@ -575,23 +563,28 @@ getOwnedSequences(Oid relid)
Anum_pg_depend_refobjid,
BTEqualStrategyNumber, F_OIDEQ,
ObjectIdGetDatum(relid));
+ if (attnum)+ ScanKeyInit(&key[2],+ Anum_pg_depend_refobjsubid,+ BTEqualStrategyNumber, F_INT4EQ,+ Int32GetDatum(attnum));
scan = systable_beginscan(depRel, DependReferenceIndexId, true,
- NULL, 2, key);+ NULL, attnum ? 3 : 2, key);
while (HeapTupleIsValid(tup = systable_getnext(scan)))
{
Form_pg_depend deprec = (Form_pg_depend) GETSTRUCT(tup);
/*
- * We assume any auto dependency of a sequence on a column must be+ * We assume any auto or internal dependency of a sequence on a column must be
* what we are looking for. (We need the relkind test because indexes
* can also have auto dependencies on columns.)
*/
if (deprec->classid == RelationRelationId &&
deprec->objsubid == 0 &&
deprec->refobjsubid != 0 &&
- deprec->deptype == DEPENDENCY_AUTO &&+ (deprec->deptype == DEPENDENCY_AUTO || deprec->deptype == DEPENDENCY_INTERNAL) &&
get_rel_relkind(deprec->objid) == RELKIND_SEQUENCE)
{
result = lappend_oid(result, deprec->objid);
@@ -605,6 +598,21 @@ getOwnedSequences(Oid relid)
return result;
}
+/*+ * Get owned sequence, error if not exactly one.+ */+Oid+getOwnedSequence(Oid relid, AttrNumber attnum)+{+ List *seqlist = getOwnedSequences(relid, attnum);++ if (list_length(seqlist) > 1)+ elog(ERROR, "more than one owned sequence found");+ else if (list_length(seqlist) < 1)+ elog(ERROR, "no owned sequence found");+ else+ return linitial_oid(seqlist);+}
/*
* get_constraint_index
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txtindex 8956ba9304..2821b9b702 100644--- a/src/backend/catalog/sql_features.txt+++ b/src/backend/catalog/sql_features.txt@@ -200,7 +200,7 @@ F181 Multiple module support NO
F191 Referential delete actions YES
F200 TRUNCATE TABLE statement YES
F201 CAST function YES
-F202 TRUNCATE TABLE: identity column restart option NO +F202 TRUNCATE TABLE: identity column restart option YES
F221 Explicit defaults YES
F222 INSERT statement: DEFAULT VALUES clause YES
F231 Privilege tables YES
@@ -241,9 +241,9 @@ F381 Extended schema manipulation 02 ALTER TABLE statement: ADD CONSTRAINT claus
F381 Extended schema manipulation 03 ALTER TABLE statement: DROP CONSTRAINT clause YES
F382 Alter column data type YES
F383 Set column not null clause YES
-F384 Drop identity property clause NO +F384 Drop identity property clause YES
F385 Drop column generation expression clause NO
-F386 Set identity column generation clause NO +F386 Set identity column generation clause YES
F391 Long identifiers YES
F392 Unicode escapes in identifiers YES
F393 Unicode escapes in literals YES
@@ -420,11 +420,11 @@ T152 DISTINCT predicate with negation YES
T171 LIKE clause in table definition YES
T172 AS subquery clause in table definition YES
T173 Extended LIKE clause in table definition YES
-T174 Identity columns NO +T174 Identity columns YES
T175 Generated columns NO
T176 Sequence generator support NO
-T177 Sequence generator support: simple restart option NO -T178 Identity columns: simple restart option NO +T177 Sequence generator support: simple restart option YES +T178 Identity columns: simple restart option YES
T180 System-versioned tables NO
T181 Application-time period tables NO
T191 Referential action RESTRICT YES
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.cindex d547db714e..f15141612a 100644--- a/src/backend/commands/sequence.c+++ b/src/backend/commands/sequence.c@@ -93,7 +93,6 @@ static HTAB *seqhashtab = NULL; /* hash table for SeqTable items */
static SeqTableData *last_used_seq = NULL;
static void fill_seq_with_data(Relation rel, HeapTuple tuple);
-static int64 nextval_internal(Oid relid);
static Relation open_share_lock(SeqTable seq);
static void create_seq_hashtable(void);
static void init_sequence(Oid relid, SeqTable *p_elm, Relation *p_rel);
@@ -101,9 +100,10 @@ static Form_pg_sequence_data read_seq_tuple(Relation rel,
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, DependencyType deptype);
/*
@@ -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 = makeNode(CreateStmt);
Oid seqoid;
ObjectAddress address;
@@ -153,7 +154,7 @@ DefineSequence(ParseState *pstate, CreateSeqStmt *seq)
}
/* Check and set all option values */
- init_params(pstate, seq->options, true, &seqform, &seqdataform, &owned_by);+ init_params(pstate, seq->options, true, &seqform, &seqdataform, &owned_by, &deptype);
/*
* Create relation (and fill value[] and null[] for the tuple)
@@ -170,6 +171,7 @@ DefineSequence(ParseState *pstate, CreateSeqStmt *seq)
coldef->storage = 0;
coldef->raw_default = NULL;
coldef->cooked_default = NULL;
+ coldef->identity = ATTRIBUTE_IDENTITY_NONE;
coldef->collClause = NULL;
coldef->collOid = InvalidOid;
coldef->constraints = NIL;
@@ -219,7 +221,7 @@ DefineSequence(ParseState *pstate, CreateSeqStmt *seq)
/* process OWNED BY if given */
if (owned_by)
- process_owned_by(rel, owned_by);+ process_owned_by(rel, owned_by, deptype);
heap_close(rel, NoLock);
@@ -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 = (Form_pg_sequence) GETSTRUCT(tuple);
/* Check and set new values */
- init_params(pstate, stmt->options, false, seqform, &newseqdata, &owned_by);+ init_params(pstate, stmt->options, false, seqform, &newseqdata, &owned_by, &deptype);
/* 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)
/* process OWNED BY if given */
if (owned_by)
- process_owned_by(seqrel, owned_by);+ process_owned_by(seqrel, owned_by, deptype);
InvokeObjectPostAlterHook(RelationRelationId, relid, 0);
@@ -554,7 +557,7 @@ nextval(PG_FUNCTION_ARGS)
*/
relid = RangeVarGetRelid(sequence, NoLock, false);
- PG_RETURN_INT64(nextval_internal(relid));+ PG_RETURN_INT64(nextval_internal(relid, true));
}
Datum
@@ -562,11 +565,11 @@ nextval_oid(PG_FUNCTION_ARGS)
{
Oid relid = PG_GETARG_OID(0);
- PG_RETURN_INT64(nextval_internal(relid));+ PG_RETURN_INT64(nextval_internal(relid, true));
}
-static int64-nextval_internal(Oid relid)+int64+nextval_internal(Oid relid, bool check_permissions)
{
SeqTable elm;
Relation seqrel;
@@ -592,7 +595,8 @@ nextval_internal(Oid relid)
/* open and AccessShareLock sequence */
init_sequence(relid, &elm, &seqrel);
- if (pg_class_aclcheck(elm->relid, GetUserId(),+ if (check_permissions &&+ pg_class_aclcheck(elm->relid, GetUserId(),
ACL_USAGE | ACL_UPDATE) != ACLCHECK_OK)
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
@@ -1221,7 +1225,8 @@ read_seq_tuple(Relation rel, 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)
{
DefElem *as_type = NULL;
DefElem *start_value = NULL;
@@ -1231,9 +1236,11 @@ init_params(ParseState *pstate, List *options, bool isInit,
DefElem *min_value = NULL;
DefElem *cache_value = NULL;
DefElem *is_cycled = NULL;
+ DefElem *deptype_el = NULL;
ListCell *option;
*owned_by = NIL;
+ *deptype = DEPENDENCY_AUTO;
foreach(option, options)
{
@@ -1320,6 +1327,28 @@ init_params(ParseState *pstate, List *options, bool isInit,
parser_errposition(pstate, defel->location)));
*owned_by = defGetQualifiedName(defel);
}
+ else if (strcmp(defel->defname, "deptype") == 0)+ {+ if (deptype_el)+ ereport(ERROR,+ (errcode(ERRCODE_SYNTAX_ERROR),+ errmsg("conflicting or redundant options"),+ parser_errposition(pstate, defel->location)));+ deptype_el = defel;+ *deptype = intVal(deptype_el->arg);+ }+ else if (strcmp(defel->defname, "sequence_name") == 0)+ {+ /*+ * The parser allows this, but it is only for identity columns, in+ * which case it is filtered out in parse_utilcmd.c. We only get+ * here if someone puts it into a CREATE SEQUENCE.+ */+ ereport(ERROR,+ (errcode(ERRCODE_SYNTAX_ERROR),+ errmsg("invalid sequence option SEQUENCE NAME"),+ parser_errposition(pstate, defel->location)));+ }
else
elog(ERROR, "option \"%s\" not recognized",
defel->defname);
@@ -1565,7 +1594,7 @@ init_params(ParseState *pstate, List *options, bool isInit,
* as the sequence.
*/
static void
-process_owned_by(Relation seqrel, List *owned_by)+process_owned_by(Relation seqrel, List *owned_by, 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 == RELKIND_RELATION ||
tablerel->rd_rel->relkind == RELKIND_FOREIGN_TABLE ||
+ tablerel->rd_rel->relkind == RELKIND_VIEW ||
tablerel->rd_rel->relkind == RELKIND_PARTITIONED_TABLE))
ereport(ERROR,
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
@@ -1627,10 +1657,28 @@ process_owned_by(Relation seqrel, List *owned_by)
}
/*
- * OK, we are ready to update pg_depend. First remove any existing AUTO+ * Catch user explicitly running OWNED BY on identity sequence.+ */+ if (deptype == DEPENDENCY_AUTO)+ {+ Oid tableId;+ int32 colId;++ if (sequenceIsOwned(RelationGetRelid(seqrel), DEPENDENCY_INTERNAL, &tableId, &colId))+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("cannot change ownership of identity sequence"),+ errdetail("Sequence \"%s\" is linked to table \"%s\".",+ RelationGetRelationName(seqrel),+ get_rel_name(tableId))));+ }++ /*+ * OK, we are ready to update pg_depend. First remove any existing
* dependencies for the sequence, then optionally add a new one.
*/
- markSequenceUnowned(RelationGetRelid(seqrel));+ deleteDependencyRecordsForClass(RelationRelationId, RelationGetRelid(seqrel),+ RelationRelationId, deptype);
if (tablerel)
{
@@ -1643,7 +1691,7 @@ process_owned_by(Relation seqrel, List *owned_by)
depobject.classId = RelationRelationId;
depobject.objectId = RelationGetRelid(seqrel);
depobject.objectSubId = 0;
- recordDependencyOn(&depobject, &refobject, DEPENDENCY_AUTO);+ recordDependencyOn(&depobject, &refobject, deptype);
}
/* Done, but hold lock until commit */
@@ -1653,6 +1701,33 @@ process_owned_by(Relation seqrel, List *owned_by)
/*
+ * Return sequence parameters in a list of the form created by the parser.+ */+List *+sequence_options(Oid relid)+{+ HeapTuple pgstuple;+ Form_pg_sequence pgsform;+ List *options = NIL;++ pgstuple = SearchSysCache1(SEQRELID, relid);+ if (!HeapTupleIsValid(pgstuple))+ elog(ERROR, "cache lookup failed for sequence %u", relid);+ pgsform = (Form_pg_sequence) GETSTRUCT(pgstuple);++ options = lappend(options, makeDefElem("cache", (Node *) makeInteger(pgsform->seqcache), -1));+ options = lappend(options, makeDefElem("cycle", (Node *) makeInteger(pgsform->seqcycle), -1));+ options = lappend(options, makeDefElem("increment", (Node *) makeInteger(pgsform->seqincrement), -1));+ options = lappend(options, makeDefElem("maxvalue", (Node *) makeInteger(pgsform->seqmax), -1));+ options = lappend(options, makeDefElem("minvalue", (Node *) makeInteger(pgsform->seqmin), -1));+ options = lappend(options, makeDefElem("start", (Node *) makeInteger(pgsform->seqstart), -1));++ ReleaseSysCache(pgstuple);++ return options;+}++/*
* Return sequence parameters (formerly for use by information schema)
*/
Datum
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.cindex 86329e5f9f..52311df655 100644--- a/src/backend/commands/tablecmds.c+++ b/src/backend/commands/tablecmds.c@@ -361,6 +361,11 @@ static ObjectAddress ATExecSetNotNull(AlteredTableInfo *tab, Relation rel,
const char *colName, LOCKMODE lockmode);
static ObjectAddress ATExecColumnDefault(Relation rel, const char *colName,
Node *newDefault, LOCKMODE lockmode);
+static ObjectAddress ATExecAddIdentity(Relation rel, const char *colName,+ Node *def, LOCKMODE lockmode);+static ObjectAddress ATExecSetIdentity(Relation rel, const char *colName,+ Node *def, LOCKMODE lockmode);+static ObjectAddress ATExecDropIdentity(Relation rel, const char *colName, LOCKMODE lockmode);
static void ATPrepSetStatistics(Relation rel, const char *colName,
Node *newValue, LOCKMODE lockmode);
static ObjectAddress ATExecSetStatistics(Relation rel, const char *colName,
@@ -696,6 +701,9 @@ DefineRelation(CreateStmt *stmt, char relkind, Oid ownerId,
cookedDefaults = lappend(cookedDefaults, cooked);
descriptor->attrs[attnum - 1]->atthasdef = true;
}
++ if (colDef->identity)+ descriptor->attrs[attnum - 1]->attidentity = colDef->identity;
}
/*
@@ -1281,7 +1289,7 @@ ExecuteTruncate(TruncateStmt *stmt)
foreach(cell, rels)
{
Relation rel = (Relation) lfirst(cell);
- List *seqlist = getOwnedSequences(RelationGetRelid(rel));+ List *seqlist = getOwnedSequences(RelationGetRelid(rel), 0);
ListCell *seqcell;
foreach(seqcell, seqlist)
@@ -2078,6 +2086,12 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
get_collation_name(defcollid),
get_collation_name(newcollid))));
+ /*+ * Identity is never inherited. The new column can have an+ * identity definition, so we always just take that one.+ */+ def->identity = newdef->identity;+
/* Copy storage parameter */
if (def->storage == 0)
def->storage = newdef->storage;
@@ -3217,6 +3231,9 @@ AlterTableGetLockLevel(List *cmds)
case AT_DisableRowSecurity:
case AT_ForceRowSecurity:
case AT_NoForceRowSecurity:
+ case AT_AddIdentity:+ case AT_DropIdentity:+ case AT_SetIdentity:
cmd_lockmode = AccessExclusiveLock;
break;
@@ -3447,6 +3464,18 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
/* No command-specific prep needed */
pass = cmd->def ? AT_PASS_ADD_CONSTR : AT_PASS_DROP;
break;
+ case AT_AddIdentity:+ ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE);+ pass = AT_PASS_ADD_CONSTR;+ break;+ case AT_DropIdentity:+ ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE);+ pass = AT_PASS_DROP;+ break;+ case AT_SetIdentity:+ ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE);+ pass = AT_PASS_COL_ATTRS;+ break;
case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */
ATSimplePermissions(rel, ATT_TABLE | ATT_FOREIGN_TABLE);
ATPrepDropNotNull(rel, recurse, recursing);
@@ -3772,6 +3801,15 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
case AT_ColumnDefault: /* ALTER COLUMN DEFAULT */
address = ATExecColumnDefault(rel, cmd->name, cmd->def, lockmode);
break;
+ case AT_AddIdentity:+ address = ATExecAddIdentity(rel, cmd->name, cmd->def, lockmode);+ break;+ case AT_SetIdentity:+ address = ATExecSetIdentity(rel, cmd->name, cmd->def, lockmode);+ break;+ case AT_DropIdentity:+ address = ATExecDropIdentity(rel, cmd->name, lockmode);+ break;
case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */
address = ATExecDropNotNull(rel, cmd->name, lockmode);
break;
@@ -5123,6 +5161,17 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
elog(ERROR, "cache lookup failed for relation %u", myrelid);
relkind = ((Form_pg_class) GETSTRUCT(reltup))->relkind;
+ /*+ * Cannot add identity column if table has children, because identity does+ * not inherit. (Adding column and identity separately will work.)+ */+ if (colDef->identity != ATTRIBUTE_IDENTITY_NONE &&+ recurse &&+ find_inheritance_children(myrelid, NoLock) != NIL)+ ereport(ERROR,+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),+ errmsg("cannot recursively add identity column to table that has child tables")));+
/* skip if the name already exists and if_not_exists is true */
if (!check_for_column_name_collision(rel, colDef->colname, if_not_exists))
{
@@ -5175,6 +5224,7 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
attribute.attalign = tform->typalign;
attribute.attnotnull = colDef->is_not_null;
attribute.atthasdef = false;
+ attribute.attidentity = colDef->identity;
attribute.attisdropped = false;
attribute.attislocal = colDef->is_local;
attribute.attinhcount = colDef->inhcount;
@@ -5481,6 +5531,7 @@ ATPrepAddOids(List **wqueue, Relation rel, bool recurse, AlterTableCmd *cmd, LOC
cdef->is_local = true;
cdef->is_not_null = true;
cdef->storage = 0;
+ cdef->identity = ATTRIBUTE_IDENTITY_NONE;
cdef->location = -1;
cmd->def = (Node *) cdef;
}
@@ -5542,6 +5593,12 @@ ATExecDropNotNull(Relation rel, const char *colName, LOCKMODE lockmode)
errmsg("cannot alter system column \"%s\"",
colName)));
+ if (get_attidentity(RelationGetRelid(rel), attnum) != ATTRIBUTE_IDENTITY_NONE)+ 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
*
@@ -5758,6 +5815,13 @@ ATExecColumnDefault(Relation rel, const char *colName,
errmsg("cannot alter system column \"%s\"",
colName)));
+ if (get_attidentity(RelationGetRelid(rel), attnum) != ATTRIBUTE_IDENTITY_NONE)+ ereport(ERROR,+ (errcode(ERRCODE_SYNTAX_ERROR),+ errmsg("column \"%s\" of relation \"%s\" is an identity column",+ colName, RelationGetRelationName(rel)),+ newDefault ? 0 : errhint("Use ALTER TABLE ... ALTER COLUMN ... DROP IDENTITY instead.")));+
/*
* Remove any old default for the column. We use RESTRICT here for
* safety, but at present we do not expect anything to depend on the
@@ -5793,6 +5857,212 @@ ATExecColumnDefault(Relation rel, const char *colName,
}
/*
+ * ALTER TABLE ALTER COLUMN ADD IDENTITY+ *+ * Return the address of the affected column.+ */+static ObjectAddress+ATExecAddIdentity(Relation rel, const char *colName,+ Node *def, LOCKMODE lockmode)+{+ Relation attrelation;+ HeapTuple tuple;+ Form_pg_attribute attTup;+ AttrNumber attnum;+ ObjectAddress address;+ ColumnDef *cdef = castNode(ColumnDef, def);++ attrelation = heap_open(AttributeRelationId, RowExclusiveLock);++ tuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), colName);+ if (!HeapTupleIsValid(tuple))+ ereport(ERROR,+ (errcode(ERRCODE_UNDEFINED_COLUMN),+ errmsg("column \"%s\" of relation \"%s\" does not exist",+ colName, RelationGetRelationName(rel))));+ attTup = (Form_pg_attribute) GETSTRUCT(tuple);+ attnum = attTup->attnum;++ /* Can't alter a system attribute */+ if (attnum <= 0)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("cannot alter system column \"%s\"",+ colName)));++ /*+ * Creating a column as identity implies NOT NULL, so adding the identity+ * to an existing column that is not NOT NULL would create a state that+ * cannot be reproduced without contortions.+ */+ if (!attTup->attnotnull)+ ereport(ERROR,+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),+ errmsg("column \"%s\" of relation \"%s\" must be declared NOT NULL before identity can be added",+ colName, RelationGetRelationName(rel))));++ if (attTup->attidentity != ATTRIBUTE_IDENTITY_NONE)+ ereport(ERROR,+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),+ errmsg("column \"%s\" of relation \"%s\" is already an identity column",+ colName, RelationGetRelationName(rel))));++ if (attTup->atthasdef)+ ereport(ERROR,+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),+ errmsg("column \"%s\" of relation \"%s\" already has a default value",+ colName, RelationGetRelationName(rel))));++ attTup->attidentity = cdef->identity;+ CatalogTupleUpdate(attrelation, &tuple->t_self, tuple);++ InvokeObjectPostAlterHook(RelationRelationId,+ RelationGetRelid(rel),+ attTup->attnum);+ ObjectAddressSubSet(address, RelationRelationId,+ RelationGetRelid(rel), attnum);+ heap_freetuple(tuple);++ heap_close(attrelation, RowExclusiveLock);++ return address;+}++static ObjectAddress+ATExecSetIdentity(Relation rel, const char *colName, Node *def, LOCKMODE lockmode)+{+ ListCell *option;+ DefElem *generatedEl = NULL;+ HeapTuple tuple;+ Form_pg_attribute attTup;+ AttrNumber attnum;+ Relation attrelation;+ ObjectAddress address;++ foreach(option, castNode(List, def))+ {+ DefElem *defel = castNode(DefElem, lfirst(option));++ if (strcmp(defel->defname, "generated") == 0)+ {+ if (generatedEl)+ ereport(ERROR,+ (errcode(ERRCODE_SYNTAX_ERROR),+ errmsg("conflicting or redundant options")));+ generatedEl = defel;+ }+ else+ elog(ERROR, "option \"%s\" not recognized",+ defel->defname);+ }++ /*+ * Even if there is nothing to change here, we run all the checks. There+ * will be a subsequent ALTER SEQUENCE that relies on everything being+ * there.+ */++ attrelation = heap_open(AttributeRelationId, RowExclusiveLock);+ tuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), colName);+ if (!HeapTupleIsValid(tuple))+ ereport(ERROR,+ (errcode(ERRCODE_UNDEFINED_COLUMN),+ errmsg("column \"%s\" of relation \"%s\" does not exist",+ colName, RelationGetRelationName(rel))));++ attTup = (Form_pg_attribute) GETSTRUCT(tuple);+ attnum = attTup->attnum;++ if (attnum <= 0)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("cannot alter system column \"%s\"",+ colName)));++ if (attTup->attidentity == ATTRIBUTE_IDENTITY_NONE)+ ereport(ERROR,+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),+ errmsg("column \"%s\" of relation \"%s\" is not an identity column",+ colName, RelationGetRelationName(rel))));++ if (generatedEl)+ {+ attTup->attidentity = defGetInt32(generatedEl);+ CatalogTupleUpdate(attrelation, &tuple->t_self, tuple);++ InvokeObjectPostAlterHook(RelationRelationId,+ RelationGetRelid(rel),+ attTup->attnum);+ ObjectAddressSubSet(address, RelationRelationId,+ RelationGetRelid(rel), attnum);+ }++ heap_freetuple(tuple);+ heap_close(attrelation, RowExclusiveLock);++ return address;+}++static ObjectAddress+ATExecDropIdentity(Relation rel, const char *colName, LOCKMODE lockmode)+{+ HeapTuple tuple;+ Form_pg_attribute attTup;+ AttrNumber attnum;+ Relation attrelation;+ ObjectAddress address;+ Oid seqid;+ ObjectAddress seqaddress;++ attrelation = heap_open(AttributeRelationId, RowExclusiveLock);+ tuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), colName);+ if (!HeapTupleIsValid(tuple))+ ereport(ERROR,+ (errcode(ERRCODE_UNDEFINED_COLUMN),+ errmsg("column \"%s\" of relation \"%s\" does not exist",+ colName, RelationGetRelationName(rel))));++ attTup = (Form_pg_attribute) GETSTRUCT(tuple);+ attnum = attTup->attnum;++ if (attnum <= 0)+ ereport(ERROR,+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),+ errmsg("cannot alter system column \"%s\"",+ colName)));++ if (attTup->attidentity == ATTRIBUTE_IDENTITY_NONE)+ ereport(ERROR,+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),+ errmsg("column \"%s\" of relation \"%s\" is not an identity column",+ colName, RelationGetRelationName(rel))));++ attTup->attidentity = ATTRIBUTE_IDENTITY_NONE;+ CatalogTupleUpdate(attrelation, &tuple->t_self, tuple);++ InvokeObjectPostAlterHook(RelationRelationId,+ RelationGetRelid(rel),+ attTup->attnum);+ ObjectAddressSubSet(address, RelationRelationId,+ RelationGetRelid(rel), attnum);+ heap_freetuple(tuple);++ heap_close(attrelation, RowExclusiveLock);++ /* drop the internal sequence */+ seqid = getOwnedSequence(RelationGetRelid(rel), attnum);+ deleteDependencyRecordsForClass(RelationRelationId, seqid,+ RelationRelationId, DEPENDENCY_INTERNAL);+ CommandCounterIncrement();+ seqaddress.classId = RelationRelationId;+ seqaddress.objectId = seqid;+ seqaddress.objectSubId = 0;+ performDeletion(&seqaddress, DROP_RESTRICT, PERFORM_DELETION_INTERNAL);++ return address;+}++/*
* ALTER TABLE ALTER COLUMN SET STATISTICS
*/
static void
@@ -9540,7 +9810,8 @@ ATExecChangeOwner(Oid relationOid, Oid newOwnerId, bool recursing, LOCKMODE lock
Oid tableId;
int32 colId;
- if (sequenceIsOwned(relationOid, &tableId, &colId))+ if (sequenceIsOwned(relationOid, DEPENDENCY_AUTO, &tableId, &colId) ||+ sequenceIsOwned(relationOid, DEPENDENCY_INTERNAL, &tableId, &colId))
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot change owner of sequence \"%s\"",
@@ -9811,7 +10082,7 @@ change_owner_recurse_to_sequences(Oid relationOid, Oid newOwnerId, LOCKMODE lock
if (depForm->refobjsubid == 0 ||
depForm->classid != RelationRelationId ||
depForm->objsubid != 0 ||
- depForm->deptype != DEPENDENCY_AUTO)+ !(depForm->deptype == DEPENDENCY_AUTO || depForm->deptype == DEPENDENCY_INTERNAL))
continue;
/* Use relation_open just in case it's an index */
@@ -12116,7 +12387,8 @@ AlterTableNamespace(AlterObjectSchemaStmt *stmt, Oid *oldschema)
Oid tableId;
int32 colId;
- if (sequenceIsOwned(relid, &tableId, &colId))+ if (sequenceIsOwned(relid, DEPENDENCY_AUTO, &tableId, &colId) ||+ sequenceIsOwned(relid, DEPENDENCY_INTERNAL, &tableId, &colId))
ereport(ERROR,
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg("cannot move an owned sequence into another schema"),
@@ -12299,7 +12571,7 @@ AlterIndexNamespaces(Relation classRel, Relation rel,
}
/*
- * Move all SERIAL-column sequences of the specified relation to another+ * Move all identity and SERIAL-column sequences of the specified relation to another
* namespace.
*
* Note: we assume adequate permission checking was done by the caller,
@@ -12343,7 +12615,7 @@ AlterSeqNamespaces(Relation classRel, Relation rel,
if (depForm->refobjsubid == 0 ||
depForm->classid != RelationRelationId ||
depForm->objsubid != 0 ||
- depForm->deptype != DEPENDENCY_AUTO)+ !(depForm->deptype == DEPENDENCY_AUTO || depForm->deptype == DEPENDENCY_INTERNAL))
continue;
/* Use relation_open just in case it's an index */
diff --git a/src/backend/executor/execQual.c b/src/backend/executor/execQual.cindex 90bef6f01f..34dde8957a 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(ArrayCoerceExprState *astate,
bool *isNull);
static Datum ExecEvalCurrentOfExpr(ExprState *exprstate, ExprContext *econtext,
bool *isNull);
+static Datum ExecEvalNextValueExpr(ExprState *exprstate, ExprContext *econtext,+ bool *isNull);
static Datum ExecEvalGroupingFuncExpr(GroupingFuncExprState *gstate,
ExprContext *econtext,
bool *isNull);
@@ -4212,6 +4215,20 @@ ExecEvalCurrentOfExpr(ExprState *exprstate, ExprContext *econtext,
/*
+ * ExecEvalNextValueExpr+ */+static Datum+ExecEvalNextValueExpr(ExprState *exprstate, ExprContext *econtext,+ bool *isNull)+{+ NextValueExpr *nve = (NextValueExpr *) exprstate->expr;++ *isNull = false;+ return Int64GetDatum(nextval_internal(nve->seqid, false));+}+++/*
* ExecEvalExprSwitchContext
*
* Same as ExecEvalExpr, but get into the right allocation context explicitly.
@@ -4953,6 +4970,10 @@ ExecInitExpr(Expr *node, PlanState *parent)
state = makeNode(ExprState);
state->evalfunc = ExecEvalCurrentOfExpr;
break;
+ case T_NextValueExpr:+ state = (ExprState *) makeNode(ExprState);+ state->evalfunc = (ExprStateEvalFunc) ExecEvalNextValueExpr;+ break;
case T_TargetEntry:
{
TargetEntry *tle = (TargetEntry *) node;
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.cindex c799e3112c..6789e8791a 100644--- a/src/backend/nodes/copyfuncs.c+++ b/src/backend/nodes/copyfuncs.c@@ -1978,6 +1978,20 @@ _copyCurrentOfExpr(const CurrentOfExpr *from)
return newnode;
}
+ /*+ * _copyNextValueExpr+ */+static NextValueExpr *+_copyNextValueExpr(const NextValueExpr *from)+{+ NextValueExpr *newnode = makeNode(NextValueExpr);++ COPY_SCALAR_FIELD(seqid);+ COPY_SCALAR_FIELD(typeId);++ return newnode;+}+
/*
* _copyInferenceElem
*/
@@ -2750,6 +2764,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);
@@ -2772,6 +2787,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);
@@ -2880,6 +2896,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);
@@ -2923,6 +2940,7 @@ _copyInsertStmt(const InsertStmt *from)
COPY_NODE_FIELD(onConflictClause);
COPY_NODE_FIELD(returningList);
COPY_NODE_FIELD(withClause);
+ COPY_SCALAR_FIELD(override);
return newnode;
}
@@ -4857,6 +4875,9 @@ copyObject(const void *from)
case T_CurrentOfExpr:
retval = _copyCurrentOfExpr(from);
break;
+ case T_NextValueExpr:+ retval = _copyNextValueExpr(from);+ break;
case T_InferenceElem:
retval = _copyInferenceElem(from);
break;
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.cindex b230f65e03..76efac2dc3 100644--- a/src/backend/nodes/equalfuncs.c+++ b/src/backend/nodes/equalfuncs.c@@ -734,6 +734,15 @@ _equalCurrentOfExpr(const CurrentOfExpr *a, const CurrentOfExpr *b)
}
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);
@@ -954,6 +963,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);
@@ -993,6 +1003,7 @@ _equalInsertStmt(const InsertStmt *a, const InsertStmt *b)
COMPARE_NODE_FIELD(onConflictClause);
COMPARE_NODE_FIELD(returningList);
COMPARE_NODE_FIELD(withClause);
+ COMPARE_SCALAR_FIELD(override);
return true;
}
@@ -2498,6 +2509,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);
@@ -2518,6 +2530,7 @@ _equalConstraint(const Constraint *a, const Constraint *b)
COMPARE_SCALAR_FIELD(is_no_inherit);
COMPARE_NODE_FIELD(raw_expr);
COMPARE_STRING_FIELD(cooked_expr);
+ COMPARE_SCALAR_FIELD(generated_when);
COMPARE_NODE_FIELD(keys);
COMPARE_NODE_FIELD(exclusions);
COMPARE_NODE_FIELD(options);
@@ -3067,6 +3080,9 @@ equal(const void *a, const void *b)
case T_CurrentOfExpr:
retval = _equalCurrentOfExpr(a, b);
break;
+ case T_NextValueExpr:+ retval = _equalNextValueExpr(a, b);+ break;
case T_InferenceElem:
retval = _equalInferenceElem(a, b);
break;
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.cindex e88d82f3b0..5a8775309f 100644--- a/src/backend/nodes/makefuncs.c+++ b/src/backend/nodes/makefuncs.c@@ -15,6 +15,7 @@
*/
#include "postgres.h"
+#include "catalog/pg_attribute.h"
#include "catalog/pg_class.h"
#include "catalog/pg_type.h"
#include "fmgr.h"
@@ -497,6 +498,7 @@ makeColumnDef(const char *colname, Oid typeOid, int32 typmod, Oid collOid)
n->storage = 0;
n->raw_default = NULL;
n->cooked_default = NULL;
+ n->identity = ATTRIBUTE_IDENTITY_NONE;
n->collClause = NULL;
n->collOid = collOid;
n->constraints = NIL;
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.cindex 6e52eb7231..31714fd466 100644--- a/src/backend/nodes/nodeFuncs.c+++ b/src/backend/nodes/nodeFuncs.c@@ -246,6 +246,9 @@ exprType(const Node *expr)
case T_CurrentOfExpr:
type = BOOLOID;
break;
+ case T_NextValueExpr:+ type = ((const NextValueExpr *) expr)->typeId;+ break;
case T_InferenceElem:
{
const InferenceElem *n = (const InferenceElem *) expr;
@@ -919,6 +922,9 @@ exprCollation(const Node *expr)
case T_CurrentOfExpr:
coll = InvalidOid; /* result is always boolean */
break;
+ case T_NextValueExpr:+ coll = InvalidOid; /* result is always an integer type */+ break;
case T_InferenceElem:
coll = exprCollation((Node *) ((const InferenceElem *) expr)->expr);
break;
@@ -1122,6 +1128,9 @@ exprSetCollation(Node *expr, Oid collation)
case T_CurrentOfExpr:
Assert(!OidIsValid(collation)); /* result is always boolean */
break;
+ case T_NextValueExpr:+ Assert(!OidIsValid(collation)); /* result is always an integer type */+ break;
default:
elog(ERROR, "unrecognized node type: %d", (int) nodeTag(expr));
break;
@@ -1881,6 +1890,7 @@ expression_tree_walker(Node *node,
case T_CaseTestExpr:
case T_SetToDefault:
case T_CurrentOfExpr:
+ case T_NextValueExpr:
case T_SQLValueFunction:
case T_RangeTblRef:
case T_SortGroupClause:
@@ -2475,6 +2485,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.cindex 7418fbeded..1e7cf3252f 100644--- a/src/backend/nodes/outfuncs.c+++ b/src/backend/nodes/outfuncs.c@@ -2686,6 +2686,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);
@@ -2791,6 +2792,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);
@@ -3321,6 +3323,13 @@ _outConstraint(StringInfo str, const Constraint *node)
WRITE_STRING_FIELD(cooked_expr);
break;
+ case CONSTR_IDENTITY:+ appendStringInfoString(str, "IDENTITY");+ WRITE_NODE_FIELD(raw_expr);+ WRITE_STRING_FIELD(cooked_expr);+ WRITE_CHAR_FIELD(generated_when);+ break;+
case CONSTR_CHECK:
appendStringInfoString(str, "CHECK");
WRITE_BOOL_FIELD(is_no_inherit);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.cindex d3bbc02f24..f8595b7f32 100644--- a/src/backend/nodes/readfuncs.c+++ b/src/backend/nodes/readfuncs.c@@ -247,6 +247,7 @@ _readQuery(void)
READ_NODE_FIELD(rtable);
READ_NODE_FIELD(jointree);
READ_NODE_FIELD(targetList);
+ READ_ENUM_FIELD(override, OverridingKind);
READ_NODE_FIELD(onConflict);
READ_NODE_FIELD(returningList);
READ_NODE_FIELD(groupClause);
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.cindex 3571e50aea..e2bcca5061 100644--- a/src/backend/parser/analyze.c+++ b/src/backend/parser/analyze.c@@ -484,6 +484,8 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
qry->hasModifyingCTE = pstate->p_hasModifyingCTE;
}
+ qry->override = stmt->override;+
isOnConflictUpdate = (stmt->onConflictClause &&
stmt->onConflictClause->action == ONCONFLICT_UPDATE);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.yindex d0d45a557b..855f0d5e49 100644--- a/src/backend/parser/gram.y+++ b/src/backend/parser/gram.y@@ -51,6 +51,7 @@
#include "catalog/index.h"
#include "catalog/namespace.h"
#include "catalog/pg_am.h"
+#include "catalog/pg_attribute.h"
#include "catalog/pg_trigger.h"
#include "commands/defrem.h"
#include "commands/trigger.h"
@@ -292,6 +293,8 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <node> alter_table_cmd alter_type_cmd opt_collate_clause
replica_identity partition_cmd
%type <list> alter_table_cmds alter_type_cmds
+%type <list> alter_identity_column_option_list+%type <defelt> alter_identity_column_option
%type <dbehavior> opt_drop_behavior
@@ -449,7 +452,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
select_offset_value2 opt_select_fetch_first_value
%type <ival> row_or_rows first_or_next
-%type <list> OptSeqOptList SeqOptList+%type <list> OptSeqOptList SeqOptList OptParenthesizedSeqOptList
%type <defelt> SeqOptElem
%type <istmt> insert_rest
@@ -569,6 +572,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
opt_frame_clause frame_extent frame_bound
%type <str> opt_existing_window_name
%type <boolean> opt_if_not_exists
+%type <ival> generated_when override_kind
%type <partspec> PartitionSpec OptPartitionSpec
%type <str> part_strategy
%type <partelem> part_elem
@@ -631,7 +635,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
FALSE_P FAMILY FETCH FILTER FIRST_P FLOAT_P FOLLOWING FOR
FORCE FOREIGN FORWARD FREEZE FROM FULL FUNCTION FUNCTIONS
- GLOBAL GRANT GRANTED GREATEST GROUP_P GROUPING+ GENERATED GLOBAL GRANT GRANTED GREATEST GROUP_P GROUPING
HANDLER HAVING HEADER_P HOLD HOUR_P
@@ -655,7 +659,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
NULLS_P NUMERIC
OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OPTIONS OR
- ORDER ORDINALITY OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER+ ORDER ORDINALITY OUT_P OUTER_P OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POLICY
POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
@@ -726,6 +730,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
* same as if they weren't keywords). We need to do this for PARTITION,
* RANGE, ROWS to support opt_existing_window_name; and for RANGE, ROWS
* so that they can follow a_expr without creating postfix-operator problems;
+ * for GENERATED so that it can follow b_expr;
* and for NULL so that it can follow b_expr in ColQualList without creating
* postfix-operator problems.
*
@@ -744,7 +749,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
* blame any funny behavior of UNBOUNDED on the SQL standard, though.
*/
%nonassoc UNBOUNDED /* ideally should have same precedence as IDENT */
-%nonassoc IDENT NULL_P PARTITION RANGE ROWS PRECEDING FOLLOWING CUBE ROLLUP+%nonassoc IDENT GENERATED NULL_P PARTITION RANGE ROWS PRECEDING FOLLOWING CUBE ROLLUP
%left Op OPERATOR /* multi-character ops and user-defined operators */
%left '+' '-'
%left '*' '/' '%'
@@ -2126,6 +2131,40 @@ alter_table_cmd:
n->def = (Node *) makeString($6);
$$ = (Node *)n;
}
+ /* ALTER TABLE <name> ALTER [COLUMN] <colname> ADD GENERATED ... AS IDENTITY ... */+ | ALTER opt_column ColId ADD_P GENERATED generated_when AS IDENTITY_P OptParenthesizedSeqOptList+ {+ AlterTableCmd *n = makeNode(AlterTableCmd);+ Constraint *c = makeNode(Constraint);++ c->contype = CONSTR_IDENTITY;+ c->generated_when = $6;+ c->options = $9;+ c->location = @5;++ n->subtype = AT_AddIdentity;+ n->name = $3;+ n->def = (Node *) c;++ $$ = (Node *)n;+ }+ /* ALTER TABLE <name> ALTER [COLUMN] <colname> SET <sequence options>/RESET */+ | ALTER opt_column ColId alter_identity_column_option_list+ {+ AlterTableCmd *n = makeNode(AlterTableCmd);+ n->subtype = AT_SetIdentity;+ n->name = $3;+ n->def = (Node *) $4;+ $$ = (Node *)n;+ }+ /* ALTER TABLE <name> ALTER [COLUMN] <colname> DROP IDENTITY */+ | ALTER opt_column ColId DROP IDENTITY_P+ {+ AlterTableCmd *n = makeNode(AlterTableCmd);+ n->subtype = AT_DropIdentity;+ n->name = $3;+ $$ = (Node *)n;+ }
/* ALTER TABLE <name> DROP [COLUMN] IF EXISTS <colname> [RESTRICT|CASCADE] */
| DROP opt_column IF_P EXISTS ColId opt_drop_behavior
{
@@ -2563,6 +2602,39 @@ reloption_elem:
}
;
+alter_identity_column_option_list:+ alter_identity_column_option+ { $$ = list_make1($1); }+ | alter_identity_column_option_list alter_identity_column_option+ { $$ = lappend($1, $2); }+ ;++alter_identity_column_option:+ RESTART+ {+ $$ = makeDefElem("restart", NULL, @1);+ }+ | RESTART opt_with NumericOnly+ {+ $$ = makeDefElem("restart", (Node *)$3, @1);+ }+ | SET SeqOptElem+ {+ if (strcmp($2->defname, "as") == 0 ||+ strcmp($2->defname, "restart") == 0 ||+ strcmp($2->defname, "owned_by") == 0)+ ereport(ERROR,+ (errcode(ERRCODE_SYNTAX_ERROR),+ errmsg("sequence option \"%s\" not supported here", $2->defname),+ parser_errposition(@2)));+ $$ = $2;+ }+ | SET GENERATED generated_when+ {+ $$ = makeDefElem("generated", (Node *) makeInteger($3), @1);+ }+ ;+
ForValues:
/* a LIST partition */
FOR VALUES IN_P '(' partbound_datum_list ')'
@@ -3213,6 +3285,7 @@ columnDef: ColId Typename create_generic_options ColQualList
n->storage = 0;
n->raw_default = NULL;
n->cooked_default = NULL;
+ n->identity = ATTRIBUTE_IDENTITY_NONE;
n->collOid = InvalidOid;
n->fdwoptions = $3;
SplitColQualList($4, &n->constraints, &n->collClause,
@@ -3234,6 +3307,7 @@ columnOptions: ColId WITH OPTIONS ColQualList
n->storage = 0;
n->raw_default = NULL;
n->cooked_default = NULL;
+ n->identity = ATTRIBUTE_IDENTITY_NONE;
n->collOid = InvalidOid;
SplitColQualList($4, &n->constraints, &n->collClause,
yyscanner);
@@ -3345,6 +3419,15 @@ ColConstraintElem:
n->cooked_expr = NULL;
$$ = (Node *)n;
}
+ | GENERATED generated_when AS IDENTITY_P OptParenthesizedSeqOptList+ {+ Constraint *n = makeNode(Constraint);+ n->contype = CONSTR_IDENTITY;+ n->generated_when = $2;+ n->options = $5;+ n->location = @1;+ $$ = (Node *)n;+ }
| REFERENCES qualified_name opt_column_list key_match key_actions
{
Constraint *n = makeNode(Constraint);
@@ -3362,6 +3445,11 @@ ColConstraintElem:
}
;
+generated_when:+ ALWAYS { $$ = ATTRIBUTE_IDENTITY_ALWAYS; }+ | BY DEFAULT { $$ = ATTRIBUTE_IDENTITY_BY_DEFAULT; }+ ;+
/*
* ConstraintAttr represents constraint attributes, which we parse as if
* they were independent constraint clauses, in order to avoid shift/reduce
@@ -3428,6 +3516,7 @@ TableLikeOptionList:
TableLikeOption:
DEFAULTS { $$ = CREATE_TABLE_LIKE_DEFAULTS; }
| CONSTRAINTS { $$ = CREATE_TABLE_LIKE_CONSTRAINTS; }
+ | IDENTITY_P { $$ = CREATE_TABLE_LIKE_IDENTITY; }
| INDEXES { $$ = CREATE_TABLE_LIKE_INDEXES; }
| STORAGE { $$ = CREATE_TABLE_LIKE_STORAGE; }
| COMMENTS { $$ = CREATE_TABLE_LIKE_COMMENTS; }
@@ -3936,6 +4025,10 @@ OptSeqOptList: SeqOptList { $$ = $1; }
| /*EMPTY*/ { $$ = NIL; }
;
+OptParenthesizedSeqOptList: '(' SeqOptList ')' { $$ = $2; }+ | /*EMPTY*/ { $$ = NIL; }+ ;+
SeqOptList: SeqOptElem { $$ = list_make1($1); }
| SeqOptList SeqOptElem { $$ = lappend($1, $2); }
;
@@ -3980,6 +4073,11 @@ SeqOptElem: AS SimpleTypename
{
$$ = makeDefElem("owned_by", (Node *)$3, @1);
}
+ | SEQUENCE NAME_P any_name+ {+ /* not documented, only used by pg_dump */+ $$ = makeDefElem("sequence_name", (Node *)$3, @1);+ }
| START opt_with NumericOnly
{
$$ = makeDefElem("start", (Node *)$3, @1);
@@ -10309,12 +10407,26 @@ insert_rest:
$$->cols = NIL;
$$->selectStmt = $1;
}
+ | OVERRIDING override_kind VALUE_P SelectStmt+ {+ $$ = makeNode(InsertStmt);+ $$->cols = NIL;+ $$->override = $2;+ $$->selectStmt = $4;+ }
| '(' insert_column_list ')' SelectStmt
{
$$ = makeNode(InsertStmt);
$$->cols = $2;
$$->selectStmt = $4;
}
+ | '(' insert_column_list ')' OVERRIDING override_kind VALUE_P SelectStmt+ {+ $$ = makeNode(InsertStmt);+ $$->cols = $2;+ $$->override = $5;+ $$->selectStmt = $7;+ }
| DEFAULT VALUES
{
$$ = makeNode(InsertStmt);
@@ -10323,6 +10435,11 @@ insert_rest:
}
;
+override_kind:+ USER { $$ = OVERRIDING_USER_VALUE; }+ | SYSTEM_P { $$ = OVERRIDING_SYSTEM_VALUE; }+ ;+
insert_column_list:
insert_column_item
{ $$ = list_make1($1); }
@@ -11686,6 +11803,7 @@ TableFuncElement: ColId Typename opt_collate_clause
n->storage = 0;
n->raw_default = NULL;
n->cooked_default = NULL;
+ n->identity = ATTRIBUTE_IDENTITY_NONE;
n->collClause = (CollateClause *) $3;
n->collOid = InvalidOid;
n->constraints = NIL;
@@ -14494,6 +14612,7 @@ unreserved_keyword:
| FORWARD
| FUNCTION
| FUNCTIONS
+ | GENERATED
| GLOBAL
| GRANTED
| HANDLER
@@ -14562,6 +14681,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.cindex 673276a9d3..58578fa0da 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 *queryString)
return result;
}
+static void+generateSerialExtraStmts(CreateStmtContext *cxt, ColumnDef *column,+ Oid seqtypid, List *seqoptions, DependencyType deptype,+ char **snamespace_p, char **sname_p)+{+ ListCell *option;+ DefElem *nameEl = NULL;+ Oid snamespaceid;+ char *snamespace;+ char *sname;+ CreateSeqStmt *seqstmt;+ AlterSeqStmt *altseqstmt;+ List *attnamelist;++ /*+ * Determine namespace and name to use for the sequence.+ *+ * First, check if a sequence name was passed in as an option. This is+ * used by pg_dump. Else, generate a name.+ *+ * Although we use ChooseRelationName, it's not guaranteed that the+ * selected sequence name won't conflict; given sufficiently long+ * field names, two different serial columns in the same table could+ * be assigned the same sequence name, and we'd not notice since we+ * aren't creating the sequence quite yet. In practice this seems+ * quite unlikely to be a problem, especially since few people would+ * need two serial columns in one table.+ */++ foreach(option, seqoptions)+ {+ DefElem *defel = castNode(DefElem, lfirst(option));++ if (strcmp(defel->defname, "sequence_name") == 0)+ {+ if (nameEl)+ ereport(ERROR,+ (errcode(ERRCODE_SYNTAX_ERROR),+ errmsg("conflicting or redundant options")));+ nameEl = defel;+ }+ }++ if (nameEl)+ {+ RangeVar *rv = makeRangeVarFromNameList(castNode(List, nameEl->arg));+ snamespace = rv->schemaname;+ sname = rv->relname;+ seqoptions = list_delete_ptr(seqoptions, nameEl);+ }+ else+ {+ if (cxt->rel)+ snamespaceid = RelationGetNamespace(cxt->rel);+ else+ {+ snamespaceid = RangeVarGetCreationNamespace(cxt->relation);+ RangeVarAdjustRelationPersistence(cxt->relation, snamespaceid);+ }+ snamespace = get_namespace_name(snamespaceid);+ sname = ChooseRelationName(cxt->relation->relname,+ column->colname,+ "seq",+ snamespaceid);+ }++ ereport(DEBUG1,+ (errmsg("%s will create implicit sequence \"%s\" for serial column \"%s.%s\"",+ cxt->stmtType, sname,+ cxt->relation->relname, column->colname)));++ /*+ * Build a CREATE SEQUENCE command to create the sequence object, and+ * add it to the list of things to be done before this CREATE/ALTER+ * TABLE.+ */+ seqstmt = makeNode(CreateSeqStmt);+ seqstmt->sequence = makeRangeVar(snamespace, sname, -1);+ seqstmt->options = seqoptions;+ /*+ * If a sequence data type was specified, add it to the options. Prepend+ * to the list rather than append; in case a user supplied their own AS+ * clause, the "redundant options" error will point to their occurrence,+ * not our synthetic one.+ */+ if (seqtypid)+ seqstmt->options = lcons(makeDefElem("as", (Node *) makeTypeNameFromOid(seqtypid, -1), -1),+ seqstmt->options);++ /*+ * If this is ALTER ADD COLUMN, make sure the sequence will be owned+ * by the table's owner. The current user might be someone else+ * (perhaps a superuser, or someone who's only a member of the owning+ * role), but the SEQUENCE OWNED BY mechanisms will bleat unless table+ * and sequence have exactly the same owning role.+ */+ if (cxt->rel)+ seqstmt->ownerId = cxt->rel->rd_rel->relowner;+ else+ seqstmt->ownerId = InvalidOid;++ cxt->blist = lappend(cxt->blist, seqstmt);++ /*+ * Build an ALTER SEQUENCE ... OWNED BY command to mark the sequence+ * as owned by this column, and add it to the list of things to be+ * done after this CREATE/ALTER TABLE.+ */+ altseqstmt = makeNode(AlterSeqStmt);+ altseqstmt->sequence = makeRangeVar(snamespace, sname, -1);+ attnamelist = list_make3(makeString(snamespace),+ makeString(cxt->relation->relname),+ makeString(column->colname));+ altseqstmt->options = list_make2(makeDefElem("owned_by",+ (Node *) attnamelist, -1),+ makeDefElem("deptype",+ (Node *) makeInteger(deptype), -1));++ cxt->alist = lappend(cxt->alist, altseqstmt);++ *snamespace_p = snamespace;+ *sname_p = sname;+}+
/*
* transformColumnDefinition -
* transform a single ColumnDef within CREATE TABLE
@@ -367,7 +492,7 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
bool is_serial;
bool saw_nullable;
bool saw_default;
- Constraint *constraint;+ bool saw_identity;
ListCell *clist;
cxt->columns = lappend(cxt->columns, column);
@@ -422,83 +547,17 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
/* Special actions for SERIAL pseudo-types */
if (is_serial)
{
- Oid snamespaceid;
char *snamespace;
char *sname;
char *qstring;
A_Const *snamenode;
TypeCast *castnode;
FuncCall *funccallnode;
- CreateSeqStmt *seqstmt;- AlterSeqStmt *altseqstmt;- List *attnamelist;-- /*- * Determine namespace and name to use for the sequence.- *- * Although we use ChooseRelationName, it's not guaranteed that the- * selected sequence name won't conflict; given sufficiently long- * field names, two different serial columns in the same table could- * be assigned the same sequence name, and we'd not notice since we- * aren't creating the sequence quite yet. In practice this seems- * quite unlikely to be a problem, especially since few people would- * need two serial columns in one table.- */- if (cxt->rel)- snamespaceid = RelationGetNamespace(cxt->rel);- else- {- snamespaceid = RangeVarGetCreationNamespace(cxt->relation);- RangeVarAdjustRelationPersistence(cxt->relation, snamespaceid);- }- snamespace = get_namespace_name(snamespaceid);- sname = ChooseRelationName(cxt->relation->relname,- column->colname,- "seq",- snamespaceid);-- ereport(DEBUG1,- (errmsg("%s will create implicit sequence \"%s\" for serial column \"%s.%s\"",- cxt->stmtType, sname,- cxt->relation->relname, column->colname)));-- /*- * Build a CREATE SEQUENCE command to create the sequence object, and- * add it to the list of things to be done before this CREATE/ALTER- * TABLE.- */- seqstmt = makeNode(CreateSeqStmt);- seqstmt->sequence = makeRangeVar(snamespace, sname, -1);- seqstmt->options = list_make1(makeDefElem("as", (Node *) makeTypeNameFromOid(column->typeName->typeOid, -1), -1));+ Constraint *constraint;- /*- * If this is ALTER ADD COLUMN, make sure the sequence will be owned- * by the table's owner. The current user might be someone else- * (perhaps a superuser, or someone who's only a member of the owning- * role), but the SEQUENCE OWNED BY mechanisms will bleat unless table- * and sequence have exactly the same owning role.- */- if (cxt->rel)- seqstmt->ownerId = cxt->rel->rd_rel->relowner;- else- seqstmt->ownerId = InvalidOid;-- cxt->blist = lappend(cxt->blist, seqstmt);-- /*- * Build an ALTER SEQUENCE ... OWNED BY command to mark the sequence- * as owned by this column, and add it to the list of things to be- * done after this CREATE/ALTER TABLE.- */- altseqstmt = makeNode(AlterSeqStmt);- altseqstmt->sequence = makeRangeVar(snamespace, sname, -1);- attnamelist = list_make3(makeString(snamespace),- makeString(cxt->relation->relname),- makeString(column->colname));- altseqstmt->options = list_make1(makeDefElem("owned_by",- (Node *) attnamelist, -1));-- cxt->alist = lappend(cxt->alist, altseqstmt);+ generateSerialExtraStmts(cxt, column,+ column->typeName->typeOid, NIL, DEPENDENCY_AUTO,+ &snamespace, &sname);
/*
* Create appropriate constraints for SERIAL. We do this in full,
@@ -540,10 +599,11 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
saw_nullable = false;
saw_default = false;
+ saw_identity = false;
foreach(clist, column->constraints)
{
- constraint = castNode(Constraint, lfirst(clist));+ Constraint *constraint = castNode(Constraint, lfirst(clist));
switch (constraint->contype)
{
@@ -581,9 +641,44 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
constraint->location)));
column->raw_default = constraint->raw_expr;
Assert(constraint->cooked_expr == NULL);
+ column->identity = ATTRIBUTE_IDENTITY_NONE;
saw_default = true;
break;
+ case CONSTR_IDENTITY:+ {+ char *snamespace;+ char *sname;+ Type ctype;+ Oid typeOid;++ ctype = typenameType(cxt->pstate, column->typeName, NULL);+ typeOid = HeapTupleGetOid(ctype);+ ReleaseSysCache(ctype);++ if (!(typeOid == INT2OID || typeOid == INT4OID || typeOid == 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 table \"%s\"",+ column->colname, cxt->relation->relname),+ parser_errposition(cxt->pstate,+ constraint->location)));++ generateSerialExtraStmts(cxt, column,+ typeOid, constraint->options, DEPENDENCY_INTERNAL,+ &snamespace, &sname);++ column->identity = constraint->generated_when;+ saw_identity = true;+ column->is_not_null = TRUE;+ break;+ }+
case CONSTR_CHECK:
cxt->ckconstraints = lappend(cxt->ckconstraints, constraint);
break;
@@ -660,6 +755,14 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
constraint->contype);
break;
}
++ if (saw_default && saw_identity)+ ereport(ERROR,+ (errcode(ERRCODE_SYNTAX_ERROR),+ errmsg("both default and identity specified for column \"%s\" of table \"%s\"",+ column->colname, cxt->relation->relname),+ parser_errposition(cxt->pstate,+ constraint->location)));
}
/*
@@ -889,6 +992,7 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
def->storage = 0;
def->raw_default = NULL;
def->cooked_default = NULL;
+ def->identity = ATTRIBUTE_IDENTITY_NONE;
def->collClause = NULL;
def->collOid = attribute->attcollation;
def->constraints = NIL;
@@ -904,7 +1008,7 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
/*
* Copy default, if present and the default has been requested
*/
- if (attribute->atthasdef &&+ if (attribute->atthasdef && attribute->attidentity == ATTRIBUTE_IDENTITY_NONE &&
(table_like_clause->options & CREATE_TABLE_LIKE_DEFAULTS))
{
Node *this_default = NULL;
@@ -932,6 +1036,29 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
def->cooked_default = this_default;
}
+ /*+ * Copy identity if requested+ */+ if (attribute->attidentity != ATTRIBUTE_IDENTITY_NONE &&+ (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 = getOwnedSequence(RelationGetRelid(relation), attribute->attnum);+ seq_options = sequence_options(seq_relid);+ generateSerialExtraStmts(cxt, def,+ InvalidOid, seq_options, DEPENDENCY_INTERNAL,+ &snamespace, &sname);+ def->identity = attribute->attidentity;+ }+
/* Likewise, copy storage if requested */
if (table_like_clause->options & CREATE_TABLE_LIKE_STORAGE)
def->storage = attribute->attstorage;
@@ -1106,6 +1233,7 @@ transformOfType(CreateStmtContext *cxt, TypeName *ofTypename)
n->storage = 0;
n->raw_default = NULL;
n->cooked_default = NULL;
+ n->identity = ATTRIBUTE_IDENTITY_NONE;
n->collClause = NULL;
n->collOid = attr->attcollation;
n->constraints = NIL;
@@ -2628,6 +2756,7 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt,
case AT_AlterColumnType:
{
ColumnDef *def = (ColumnDef *) cmd->def;
+ AttrNumber attnum;
/*
* For ALTER COLUMN TYPE, transform the USING clause if
@@ -2640,6 +2769,110 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt,
EXPR_KIND_ALTER_COL_TRANSFORM);
}
+ /*+ * For identity column, create ALTER SEQUENCE command to+ * change the data type of the sequence.+ */+ attnum = get_attnum(relid, cmd->name);+ /* if attribute not found, something will error about it later */+ if (attnum != InvalidAttrNumber && get_attidentity(relid, attnum) != ATTRIBUTE_IDENTITY_NONE)+ {+ Oid seq_relid = getOwnedSequence(relid, attnum);+ Oid typeOid = typenameTypeId(pstate, def->typeName);+ AlterSeqStmt *altseqstmt = makeNode(AlterSeqStmt);++ if (!(typeOid == INT2OID || typeOid == INT4OID || typeOid == INT8OID))+ ereport(ERROR,+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),+ errmsg("identity column type must be smallint, integer, or bigint")));++ altseqstmt->sequence = makeRangeVar(get_namespace_name(get_rel_namespace(seq_relid)),+ get_rel_name(seq_relid),+ -1);+ altseqstmt->options = list_make1(makeDefElem("as", (Node *) makeTypeNameFromOid(typeOid, -1), -1));+ cxt.blist = lappend(cxt.blist, altseqstmt);+ }++ newcmds = lappend(newcmds, cmd);+ break;+ }++ case AT_AddIdentity:+ {+ Constraint *def = castNode(Constraint, cmd->def);+ ColumnDef *newdef = makeNode(ColumnDef);+ Oid typeOid;+ char *snamespace;+ char *sname;++ typeOid = get_atttype(relid, get_attnum(relid, cmd->name));+ if (!(typeOid == INT2OID || typeOid == INT4OID || typeOid == INT8OID))+ ereport(ERROR,+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),+ errmsg("identity column type must be smallint, integer, or bigint")));++ newdef->colname = cmd->name;+ newdef->identity = def->generated_when;+ cmd->def = (Node *) newdef;++ generateSerialExtraStmts(&cxt, newdef,+ typeOid, def->options, DEPENDENCY_INTERNAL,+ &snamespace, &sname);++ newcmds = lappend(newcmds, cmd);+ break;+ }++ case AT_SetIdentity:+ {+ /*+ * Create an ALTER SEQUENCE statement for the internal+ * sequence of the identity column.+ */+ ListCell *lc;+ List *newseqopts = NIL;+ List *newdef = NIL;+ List *seqlist;+ AttrNumber attnum;++ /*+ * Split options into those handled by ALTER SEQUENCE and+ * those for ALTER TABLE proper.+ */+ foreach(lc, castNode(List, cmd->def))+ {+ DefElem *def = castNode(DefElem, lfirst(lc));++ if (strcmp(def->defname, "generated") == 0)+ newdef = lappend(newdef, def);+ else+ newseqopts = lappend(newseqopts, def);+ }++ attnum = get_attnum(relid, cmd->name);++ if (attnum)+ {+ seqlist = getOwnedSequences(relid, attnum);+ if (seqlist)+ {+ AlterSeqStmt *seqstmt;+ Oid seq_relid;++ seqstmt = makeNode(AlterSeqStmt);+ seq_relid = linitial_oid(seqlist);+ seqstmt->sequence = makeRangeVar(get_namespace_name(get_rel_namespace(seq_relid)),+ get_rel_name(seq_relid), -1);+ seqstmt->options = newseqopts;+ seqstmt->missing_ok = false;++ cxt.alist = lappend(cxt.alist, seqstmt);+ }+ }+ /* If column was not found or was not an identity column, we+ * just let the ALTER TABLE command error out later. */++ cmd->def = (Node *) newdef;
newcmds = lappend(newcmds, cmd);
break;
}
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.cindex 354e5d0462..3781326ea0 100644--- a/src/backend/rewrite/rewriteHandler.c+++ b/src/backend/rewrite/rewriteHandler.c@@ -21,6 +21,7 @@
#include "postgres.h"
#include "access/sysattr.h"
+#include "catalog/dependency.h"
#include "catalog/pg_type.h"
#include "commands/trigger.h"
#include "foreign/fdwapi.h"
@@ -61,6 +62,7 @@ static Query *rewriteRuleAction(Query *parsetree,
static List *adjustJoinTreeList(Query *parsetree, bool removert, int rt_index);
static List *rewriteTargetListIU(List *targetList,
CmdType commandType,
+ OverridingKind override,
Relation target_relation,
int result_rti,
List **attrno_list);
@@ -709,6 +711,7 @@ adjustJoinTreeList(Query *parsetree, bool removert, int rt_index)
static List *
rewriteTargetListIU(List *targetList,
CmdType commandType,
+ OverridingKind override,
Relation target_relation,
int result_rti,
List **attrno_list)
@@ -789,6 +792,7 @@ rewriteTargetListIU(List *targetList,
for (attrno = 1; attrno <= numattrs; attrno++)
{
TargetEntry *new_tle = new_tles[attrno - 1];
+ bool apply_default;
att_tup = target_relation->rd_att->attrs[attrno - 1];
@@ -801,12 +805,51 @@ rewriteTargetListIU(List *targetList,
* it's an INSERT and there's no tlist entry for the column, or the
* tlist entry is a DEFAULT placeholder node.
*/
- if ((new_tle == NULL && commandType == CMD_INSERT) ||- (new_tle && new_tle->expr && IsA(new_tle->expr, SetToDefault)))+ apply_default = ((new_tle == NULL && commandType == CMD_INSERT) ||+ (new_tle && new_tle->expr && IsA(new_tle->expr, SetToDefault)));++ if (commandType == CMD_INSERT)+ {+ if (att_tup->attidentity == ATTRIBUTE_IDENTITY_ALWAYS && !apply_default)+ {+ if (override != OVERRIDING_SYSTEM_VALUE)+ ereport(ERROR,+ (errcode(ERRCODE_GENERATED_ALWAYS),+ errmsg("cannot insert into column \"%s\"", NameStr(att_tup->attname)),+ errdetail("Column \"%s\" is an identity column defined as GENERATED ALWAYS.",+ NameStr(att_tup->attname)),+ errhint("Use OVERRIDING SYSTEM VALUE to override.")));+ }++ if (att_tup->attidentity == ATTRIBUTE_IDENTITY_BY_DEFAULT && override == OVERRIDING_USER_VALUE)+ apply_default = true;+ }++ if (commandType == CMD_UPDATE)+ {+ if (att_tup->attidentity == ATTRIBUTE_IDENTITY_ALWAYS && !apply_default)+ ereport(ERROR,+ (errcode(ERRCODE_GENERATED_ALWAYS),+ errmsg("column \"%s\" can only be updated to DEFAULT", NameStr(att_tup->attname)),+ errdetail("Column \"%s\" is an identity column defined as GENERATED ALWAYS.",+ NameStr(att_tup->attname))));+ }++ if (apply_default)
{
Node *new_expr;
- new_expr = build_column_default(target_relation, attrno);+ if (att_tup->attidentity != ' ')+ {+ NextValueExpr *nve = makeNode(NextValueExpr);++ nve->seqid = getOwnedSequence(RelationGetRelid(target_relation), attrno);+ nve->typeId = att_tup->atttypid;++ new_expr = (Node *) nve;+ }+ else+ new_expr = build_column_default(target_relation, attrno);
/*
* If there is no default (ie, default is effectively NULL), we
@@ -3232,6 +3275,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
/* Process the main targetlist ... */
parsetree->targetList = rewriteTargetListIU(parsetree->targetList,
parsetree->commandType,
+ parsetree->override,
rt_entry_relation,
parsetree->resultRelation,
&attrnos);
@@ -3244,6 +3288,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
parsetree->targetList =
rewriteTargetListIU(parsetree->targetList,
parsetree->commandType,
+ parsetree->override,
rt_entry_relation,
parsetree->resultRelation, NULL);
}
@@ -3254,6 +3299,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
parsetree->onConflict->onConflictSet =
rewriteTargetListIU(parsetree->onConflict->onConflictSet,
CMD_UPDATE,
+ parsetree->override,
rt_entry_relation,
parsetree->resultRelation,
NULL);
@@ -3263,7 +3309,9 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
{
parsetree->targetList =
rewriteTargetListIU(parsetree->targetList,
- parsetree->commandType, rt_entry_relation,+ parsetree->commandType,+ parsetree->override,+ rt_entry_relation,
parsetree->resultRelation, NULL);
rewriteTargetListUD(parsetree, rt_entry, rt_entry_relation);
}
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.cindex 5c823250bc..83838a4339 100644--- a/src/backend/utils/adt/ruleutils.c+++ b/src/backend/utils/adt/ruleutils.c@@ -5813,6 +5813,14 @@ get_insert_query_def(Query *query, deparse_context *context)
if (query->targetList)
appendStringInfoString(buf, ") ");
+ if (query->override)+ {+ if (query->override == OVERRIDING_SYSTEM_VALUE)+ appendStringInfoString(buf, "OVERRIDING SYSTEM VALUE ");+ else if (query->override == OVERRIDING_USER_VALUE)+ appendStringInfoString(buf, "OVERRIDING USER VALUE ");+ }+
if (select_rte)
{
/* Add the SELECT */
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.cindex b891f388e5..831a7f99a2 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;
}
+char+get_attidentity(Oid relid, AttrNumber attnum)+{+ HeapTuple tp;++ tp = SearchSysCache2(ATTNUM,+ ObjectIdGetDatum(relid),+ Int16GetDatum(attnum));+ if (HeapTupleIsValid(tp))+ {+ Form_pg_attribute att_tup = (Form_pg_attribute) GETSTRUCT(tp);+ char result;++ result = att_tup->attidentity;+ ReleaseSysCache(tp);+ return result;+ }+ else+ return 0;+}+
/*
* get_atttype
*
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.cindex ce55fc5277..778d80e3a2 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 = false;
for (i = 0; i < natts; i++)
{
+ rel->rd_att->attrs[i]->attidentity = tupDesc->attrs[i]->attidentity;
rel->rd_att->attrs[i]->attnotnull = tupDesc->attrs[i]->attnotnull;
has_not_null |= tupDesc->attrs[i]->attnotnull;
}
diff --git a/src/backend/utils/errcodes.txt b/src/backend/utils/errcodes.txtindex b6e0e987a8..4f35471762 100644--- a/src/backend/utils/errcodes.txt+++ b/src/backend/utils/errcodes.txt@@ -327,6 +327,7 @@ Section: Class 42 - Syntax Error or Access Rule Violation
42P21 E ERRCODE_COLLATION_MISMATCH collation_mismatch
42P22 E ERRCODE_INDETERMINATE_COLLATION indeterminate_collation
42809 E ERRCODE_WRONG_OBJECT_TYPE wrong_object_type
+428C9 E ERRCODE_GENERATED_ALWAYS generated_always
# Note: for ERRCODE purposes, we divide namable objects into these categories:
# databases, schemas, prepared statements, cursors, tables, columns,
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.cindex e67171dccb..df7d0d8887 100644--- a/src/bin/pg_dump/pg_dump.c+++ b/src/bin/pg_dump/pg_dump.c@@ -43,6 +43,7 @@
#include "access/sysattr.h"
#include "access/transam.h"
#include "catalog/pg_am.h"
+#include "catalog/pg_attribute.h"
#include "catalog/pg_cast.h"
#include "catalog/pg_class.h"
#include "catalog/pg_default_acl.h"
@@ -1900,6 +1901,9 @@ dumpTableData_insert(Archive *fout, void *dcontext)
appendPQExpBufferStr(insertStmt, ") ");
}
+ if (tbinfo->needs_override)+ appendPQExpBufferStr(insertStmt, "OVERRIDING SYSTEM VALUE ");+
appendPQExpBufferStr(insertStmt, "VALUES (");
}
}
@@ -5422,6 +5426,7 @@ getTables(Archive *fout, int *numTables)
int i_toastreloptions;
int i_reloftype;
int i_relpages;
+ int i_is_identity_sequence;
int i_changed_acl;
/* Make sure we are in proper schema */
@@ -5499,6 +5504,7 @@ getTables(Archive *fout, int *numTables)
"CASE WHEN 'check_option=local' = ANY (c.reloptions) THEN 'LOCAL'::text "
"WHEN 'check_option=cascaded' = ANY (c.reloptions) THEN 'CASCADED'::text ELSE NULL END AS checkoption, "
"tc.reloptions AS toast_reloptions, "
+ "c.relkind = '%c' AND EXISTS (SELECT 1 FROM pg_depend WHERE classid = 'pg_class'::regclass AND objid = c.oid AND objsubid = 0 AND refclassid = 'pg_class'::regclass AND deptype = 'i') AS is_identity_sequence, "
"EXISTS (SELECT 1 FROM pg_attribute at LEFT JOIN pg_init_privs pip ON "
"(c.oid = pip.objoid "
"AND pip.classoid = 'pg_class'::regclass "
@@ -5515,7 +5521,7 @@ getTables(Archive *fout, int *numTables)
"(c.relkind = '%c' AND "
"d.classid = c.tableoid AND d.objid = c.oid AND "
"d.objsubid = 0 AND "
- "d.refclassid = c.tableoid AND d.deptype = 'a') "+ "d.refclassid = c.tableoid AND d.deptype IN ('a', 'i')) "
"LEFT JOIN pg_class tc ON (c.reltoastrelid = tc.oid) "
"LEFT JOIN pg_init_privs pip ON "
"(c.oid = pip.objoid "
@@ -5528,6 +5534,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,
@@ -5950,6 +5957,7 @@ getTables(Archive *fout, int *numTables)
i_checkoption = PQfnumber(res, "checkoption");
i_toastreloptions = PQfnumber(res, "toast_reloptions");
i_reloftype = PQfnumber(res, "reloftype");
+ i_is_identity_sequence = PQfnumber(res, "is_identity_sequence");
i_changed_acl = PQfnumber(res, "changed_acl");
if (dopt->lockWaitTimeout)
@@ -6050,6 +6058,8 @@ getTables(Archive *fout, int *numTables)
tblinfo[i].dummy_view = false; /* might get set during sort */
tblinfo[i].postponed_def = false; /* might get set during sort */
+ tblinfo[i].is_identity_sequence = (strcmp(PQgetvalue(res, i, i_is_identity_sequence), "t") == 0);+
/*
* Read-lock target tables to make sure they aren't DROPPED or altered
* in schema before we get around to dumping them.
@@ -7613,6 +7623,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;
@@ -7655,7 +7666,34 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
resetPQExpBuffer(q);
- if (fout->remoteVersion >= 90200)+ if (fout->remoteVersion >= 100000)+ {+ /*+ * attidentity is new in version 10.+ */+ appendPQExpBuffer(q, "SELECT a.attnum, a.attname, a.atttypmod, "+ "a.attstattarget, a.attstorage, t.typstorage, "+ "a.attnotnull, a.atthasdef, a.attisdropped, "+ "a.attlen, a.attalign, a.attislocal, "+ "pg_catalog.format_type(t.oid,a.atttypmod) AS atttypname, "+ "array_to_string(a.attoptions, ', ') AS attoptions, "+ "CASE WHEN a.attcollation <> t.typcollation "+ "THEN a.attcollation ELSE 0 END AS attcollation, "+ "a.attidentity, "+ "pg_catalog.array_to_string(ARRAY("+ "SELECT pg_catalog.quote_ident(option_name) || "+ "' ' || pg_catalog.quote_literal(option_value) "+ "FROM pg_catalog.pg_options_to_table(attfdwoptions) "+ "ORDER BY option_name"+ "), E',\n ') AS attfdwoptions "+ "FROM pg_catalog.pg_attribute a LEFT JOIN pg_catalog.pg_type t "+ "ON a.atttypid = t.oid "+ "WHERE a.attrelid = '%u'::pg_catalog.oid "+ "AND a.attnum > 0::pg_catalog.int2 "+ "ORDER BY a.attrelid, a.attnum",+ tbinfo->dobj.catId.oid);+ }+ else if (fout->remoteVersion >= 90200)
{
/*
* attfdwoptions is new in 9.2.
@@ -7754,6 +7792,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
i_typstorage = PQfnumber(res, "typstorage");
i_attnotnull = PQfnumber(res, "attnotnull");
i_atthasdef = PQfnumber(res, "atthasdef");
+ i_attidentity = PQfnumber(res, "attidentity");
i_attisdropped = PQfnumber(res, "attisdropped");
i_attlen = PQfnumber(res, "attlen");
i_attalign = PQfnumber(res, "attalign");
@@ -7769,6 +7808,7 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
tbinfo->attstattarget = (int *) pg_malloc(ntups * sizeof(int));
tbinfo->attstorage = (char *) pg_malloc(ntups * sizeof(char));
tbinfo->typstorage = (char *) pg_malloc(ntups * sizeof(char));
+ tbinfo->attidentity = (char *) pg_malloc(ntups * sizeof(bool));
tbinfo->attisdropped = (bool *) pg_malloc(ntups * sizeof(bool));
tbinfo->attlen = (int *) pg_malloc(ntups * sizeof(int));
tbinfo->attalign = (char *) pg_malloc(ntups * sizeof(char));
@@ -7793,6 +7833,8 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
tbinfo->attstattarget[j] = atoi(PQgetvalue(res, j, i_attstattarget));
tbinfo->attstorage[j] = *(PQgetvalue(res, j, i_attstorage));
tbinfo->typstorage[j] = *(PQgetvalue(res, j, i_typstorage));
+ tbinfo->attidentity[j] = *(PQgetvalue(res, j, i_attidentity));+ tbinfo->needs_override = tbinfo->needs_override || (tbinfo->attidentity[j] == ATTRIBUTE_IDENTITY_ALWAYS);
tbinfo->attisdropped[j] = (PQgetvalue(res, j, i_attisdropped)[0] == 't');
tbinfo->attlen[j] = atoi(PQgetvalue(res, j, i_attlen));
tbinfo->attalign[j] = *(PQgetvalue(res, j, i_attalign));
@@ -16069,10 +16111,13 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
/*
* DROP must be fully qualified in case same name appears in pg_catalog
*/
- appendPQExpBuffer(delqry, "DROP SEQUENCE %s.",- fmtId(tbinfo->dobj.namespace->dobj.name));- appendPQExpBuffer(delqry, "%s;\n",- fmtId(tbinfo->dobj.name));+ if (!tbinfo->is_identity_sequence)+ {+ appendPQExpBuffer(delqry, "DROP SEQUENCE %s.",+ fmtId(tbinfo->dobj.namespace->dobj.name));+ appendPQExpBuffer(delqry, "%s;\n",+ fmtId(tbinfo->dobj.name));+ }
resetPQExpBuffer(query);
@@ -16084,11 +16129,30 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
tbinfo->dobj.catId.oid);
}
- appendPQExpBuffer(query,- "CREATE SEQUENCE %s\n",- fmtId(tbinfo->dobj.name));+ if (tbinfo->is_identity_sequence)+ {+ TableInfo *owning_tab = findTableByOid(tbinfo->owning_tab);++ appendPQExpBuffer(query,+ "ALTER TABLE %s ",+ fmtId(owning_tab->dobj.name));+ appendPQExpBuffer(query,+ "ALTER COLUMN %s ADD GENERATED ",+ fmtId(owning_tab->attnames[tbinfo->owning_col - 1]));+ if (owning_tab->attidentity[tbinfo->owning_col - 1] == ATTRIBUTE_IDENTITY_ALWAYS)+ appendPQExpBuffer(query, "ALWAYS");+ else if (owning_tab->attidentity[tbinfo->owning_col - 1] == ATTRIBUTE_IDENTITY_BY_DEFAULT)+ appendPQExpBuffer(query, "BY DEFAULT");+ appendPQExpBuffer(query, " AS IDENTITY (\n SEQUENCE NAME %s\n",+ fmtId(tbinfo->dobj.name));+ }+ else+ appendPQExpBuffer(query,+ "CREATE SEQUENCE %s\n",+ fmtId(tbinfo->dobj.name));- if (strcmp(seqtype, "bigint") != 0)+ if (strcmp(seqtype, "bigint") != 0 &&+ !tbinfo->is_identity_sequence)
appendPQExpBuffer(query, " AS %s\n", seqtype);
if (fout->remoteVersion >= 80400)
@@ -16110,7 +16174,10 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
" CACHE %s%s",
cache, (cycled ? "\n CYCLE" : ""));
- appendPQExpBufferStr(query, ";\n");+ if (tbinfo->is_identity_sequence)+ appendPQExpBufferStr(query, "\n);\n");+ else+ appendPQExpBufferStr(query, ";\n");
appendPQExpBuffer(labelq, "SEQUENCE %s", fmtId(tbinfo->dobj.name));
@@ -16143,7 +16210,7 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
* We need not schema-qualify the table reference because both sequence
* and table must be in the same schema.
*/
- if (OidIsValid(tbinfo->owning_tab))+ if (OidIsValid(tbinfo->owning_tab) && !tbinfo->is_identity_sequence)
{
TableInfo *owning_tab = findTableByOid(tbinfo->owning_tab);
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.hindex 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) */
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 */
/*
* Stuff computed only for dumpable tables.
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.cindex 61a3e2a848..085954732d 100644--- a/src/bin/psql/describe.c+++ b/src/bin/psql/describe.c@@ -14,6 +14,7 @@
#include <ctype.h>
+#include "catalog/pg_attribute.h"
#include "catalog/pg_class.h"
#include "catalog/pg_default_acl.h"
#include "fe_utils/string_utils.h"
@@ -1590,6 +1591,10 @@ describeOneTableDetails(const char *schemaname,
" WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation");
else
appendPQExpBufferStr(&buf, "\n NULL AS attcollation");
+ if (pset.sversion >= 100000)+ appendPQExpBufferStr(&buf, ", a.attidentity");+ else+ appendPQExpBufferStr(&buf, ", NULL AS attidentity");
if (tableinfo.relkind == RELKIND_INDEX)
appendPQExpBufferStr(&buf, ",\n pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef");
else
@@ -1776,12 +1781,24 @@ describeOneTableDetails(const char *schemaname,
/* Collation, Nullable, Default */
if (show_column_details)
{
+ char *identity;+ char *default_str = "";+
printTableAddCell(&cont, PQgetvalue(res, i, 5), false, false);
printTableAddCell(&cont, strcmp(PQgetvalue(res, i, 3), "t") == 0 ? "not null" : "", false, false);
- /* (note: above we cut off the 'default' string at 128) */- printTableAddCell(&cont, PQgetvalue(res, i, 2), false, false);+ identity = PQgetvalue(res, i, 6);++ if (identity[0] == ATTRIBUTE_IDENTITY_NONE)+ /* (note: above we cut off the 'default' string at 128) */+ default_str = PQgetvalue(res, i, 2);+ else if (identity[0] == ATTRIBUTE_IDENTITY_ALWAYS)+ default_str = "generated always as identity";+ else if (identity[0] == ATTRIBUTE_IDENTITY_BY_DEFAULT)+ default_str = "generated by default as identity";++ printTableAddCell(&cont, default_str, false, false);
}
/* Value: for sequences only */
@@ -1790,16 +1807,16 @@ describeOneTableDetails(const char *schemaname,
/* Expression for index column */
if (tableinfo.relkind == RELKIND_INDEX)
- printTableAddCell(&cont, PQgetvalue(res, i, 6), false, false);+ printTableAddCell(&cont, PQgetvalue(res, i, 7), false, false);
/* FDW options for foreign table column, only for 9.2 or later */
if (tableinfo.relkind == RELKIND_FOREIGN_TABLE && pset.sversion >= 90200)
- printTableAddCell(&cont, PQgetvalue(res, i, 7), false, false);+ printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
/* Storage and Description */
if (verbose)
{
- int firstvcol = 8;+ int firstvcol = 9;
char *storage = PQgetvalue(res, i, firstvcol);
/* these strings are literal in our syntax, so not translated. */
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.cindex f7494065de..6ad9dc9df8 100644--- a/src/bin/psql/tab-complete.c+++ b/src/bin/psql/tab-complete.c@@ -1895,7 +1895,7 @@ psql_completion(const char *text, int start, int end)
/* ALTER TABLE ALTER [COLUMN] <foo> */
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] <foo> SET */
else if (Matches7("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "SET") ||
Matches6("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "SET"))
@@ -1911,7 +1911,7 @@ psql_completion(const char *text, int start, int end)
/* ALTER TABLE ALTER [COLUMN] <foo> DROP */
else if (Matches7("ALTER", "TABLE", MatchAny, "ALTER", "COLUMN", MatchAny, "DROP") ||
Matches6("ALTER", "TABLE", MatchAny, "ALTER", MatchAny, "DROP"))
- COMPLETE_WITH_LIST2("DEFAULT", "NOT NULL");+ COMPLETE_WITH_LIST3("DEFAULT", "IDENTITY", "NOT NULL");
else if (Matches4("ALTER", "TABLE", MatchAny, "CLUSTER"))
COMPLETE_WITH_CONST("ON");
else if (Matches5("ALTER", "TABLE", MatchAny, "CLUSTER", "ON"))
@@ -2920,17 +2920,25 @@ psql_completion(const char *text, int start, int end)
/*
* Complete INSERT INTO <table> with "(" or "VALUES" or "SELECT" or
- * "TABLE" or "DEFAULT VALUES"+ * "TABLE" or "DEFAULT VALUES" or "OVERRIDING"
*/
else if (TailMatches3("INSERT", "INTO", MatchAny))
- COMPLETE_WITH_LIST5("(", "DEFAULT VALUES", "SELECT", "TABLE", "VALUES");+ COMPLETE_WITH_LIST6("(", "DEFAULT VALUES", "SELECT", "TABLE", "VALUES", "OVERRIDING");
/*
* Complete INSERT INTO <table> (attribs) with "VALUES" or "SELECT" or
- * "TABLE"+ * "TABLE" or "OVERRIDING"
*/
else if (TailMatches4("INSERT", "INTO", MatchAny, MatchAny) &&
ends_with(prev_wd, ')'))
+ COMPLETE_WITH_LIST4("SELECT", "TABLE", "VALUES", "OVERRIDING");++ /* Complete OVERRIDING */+ else if (TailMatches1("OVERRIDING"))+ COMPLETE_WITH_LIST2("SYSTEM VALUE", "USER VALUE");++ /* Complete after OVERRIDING clause */+ else if (TailMatches3("OVERRIDING", MatchAny, "VALUE"))
COMPLETE_WITH_LIST3("SELECT", "TABLE", "VALUES");
/* Insert an open parenthesis after "VALUES" */
diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.hindex 10759c7c58..c5044b4af9 100644--- a/src/include/catalog/dependency.h+++ b/src/include/catalog/dependency.h@@ -237,11 +237,9 @@ extern long changeDependencyFor(Oid classId, Oid objectId,
extern Oid getExtensionOfObject(Oid classId, Oid objectId);
-extern bool sequenceIsOwned(Oid seqId, Oid *tableId, int32 *colId);--extern void markSequenceUnowned(Oid seqId);--extern List *getOwnedSequences(Oid relid);+extern bool sequenceIsOwned(Oid seqId, char deptype, Oid *tableId, int32 *colId);+extern List *getOwnedSequences(Oid relid, AttrNumber attnum);+extern Oid getOwnedSequence(Oid relid, AttrNumber attnum);
extern Oid get_constraint_index(Oid constraintId);
diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_attribute.hindex e861f4c589..0deb5c86c8 100644--- a/src/include/catalog/pg_attribute.h+++ b/src/include/catalog/pg_attribute.h@@ -133,6 +133,9 @@ CATALOG(pg_attribute,1249) BKI_BOOTSTRAP BKI_WITHOUT_OIDS BKI_ROWTYPE_OID(75) BK
/* Has DEFAULT value or not */
bool atthasdef;
+ /* One of the ATTRIBUTE_IDENTITY_* constants below */+ char attidentity;+
/* Is dropped (ie, logically invisible) or not */
bool attisdropped;
@@ -188,7 +191,7 @@ typedef FormData_pg_attribute *Form_pg_attribute;
* ----------------
*/
-#define Natts_pg_attribute 21+#define Natts_pg_attribute 22
#define Anum_pg_attribute_attrelid 1
#define Anum_pg_attribute_attname 2
#define Anum_pg_attribute_atttypid 3
@@ -203,13 +206,14 @@ typedef FormData_pg_attribute *Form_pg_attribute;
#define Anum_pg_attribute_attalign 12
#define Anum_pg_attribute_attnotnull 13
#define Anum_pg_attribute_atthasdef 14
-#define Anum_pg_attribute_attisdropped 15-#define Anum_pg_attribute_attislocal 16-#define Anum_pg_attribute_attinhcount 17-#define Anum_pg_attribute_attcollation 18-#define Anum_pg_attribute_attacl 19-#define Anum_pg_attribute_attoptions 20-#define Anum_pg_attribute_attfdwoptions 21+#define Anum_pg_attribute_attidentity 15+#define Anum_pg_attribute_attisdropped 16+#define Anum_pg_attribute_attislocal 17+#define Anum_pg_attribute_attinhcount 18+#define Anum_pg_attribute_attcollation 19+#define Anum_pg_attribute_attacl 20+#define Anum_pg_attribute_attoptions 21+#define Anum_pg_attribute_attfdwoptions 22
/* ----------------
@@ -220,4 +224,9 @@ typedef FormData_pg_attribute *Form_pg_attribute;
* ----------------
*/
++#define ATTRIBUTE_IDENTITY_NONE ' '+#define ATTRIBUTE_IDENTITY_ALWAYS 'a'+#define ATTRIBUTE_IDENTITY_BY_DEFAULT 'd'+
#endif /* PG_ATTRIBUTE_H */
diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.hindex d1d493ee05..5a288830e8 100644--- a/src/include/catalog/pg_class.h+++ b/src/include/catalog/pg_class.h@@ -149,7 +149,7 @@ typedef FormData_pg_class *Form_pg_class;
*/
DATA(insert OID = 1247 ( pg_type PGNSP 71 0 PGUID 0 0 0 0 0 0 0 f f p r 30 0 t f f f f f f t n f 3 1 _null_ _null_ _null_));
DESCR("");
-DATA(insert OID = 1249 ( pg_attribute PGNSP 75 0 PGUID 0 0 0 0 0 0 0 f f p r 21 0 f f f f f f f t n f 3 1 _null_ _null_ _null_));+DATA(insert OID = 1249 ( pg_attribute PGNSP 75 0 PGUID 0 0 0 0 0 0 0 f f p r 22 0 f f f f f f f t n f 3 1 _null_ _null_ _null_));
DESCR("");
DATA(insert OID = 1255 ( pg_proc PGNSP 81 0 PGUID 0 0 0 0 0 0 0 f f p r 29 0 t f f f f f f t n f 3 1 _null_ _null_ _null_));
DESCR("");
diff --git a/src/include/commands/sequence.h b/src/include/commands/sequence.hindex 49a77c42fc..304586e48e 100644--- a/src/include/commands/sequence.h+++ b/src/include/commands/sequence.h@@ -51,7 +51,9 @@ typedef struct xl_seq_rec
/* SEQUENCE TUPLE DATA FOLLOWS AT THE END */
} xl_seq_rec;
+extern int64 nextval_internal(Oid relid, bool check_permissions);
extern Datum nextval(PG_FUNCTION_ARGS);
+extern List *sequence_options(Oid relid);
extern ObjectAddress DefineSequence(ParseState *pstate, CreateSeqStmt *stmt);
extern ObjectAddress AlterSequence(ParseState *pstate, AlterSeqStmt *stmt);
diff --git a/src/include/nodes/nodes.h b/src/include/nodes/nodes.hindex 2bc7a5df11..1da74e148e 100644--- a/src/include/nodes/nodes.h+++ b/src/include/nodes/nodes.h@@ -188,6 +188,7 @@ typedef enum NodeTag
T_FromExpr,
T_OnConflictExpr,
T_IntoClause,
+ T_NextValueExpr,
/*
* TAGS FOR EXPRESSION STATE NODES (execnodes.h)
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.hindex a15df229a4..2d5a5074c0 100644--- a/src/include/nodes/parsenodes.h+++ b/src/include/nodes/parsenodes.h@@ -27,6 +27,13 @@
#include "nodes/primnodes.h"
#include "nodes/value.h"
+typedef enum OverridingKind+{+ OVERRIDING_NOT_SET = 0,+ OVERRIDING_USER_VALUE,+ OVERRIDING_SYSTEM_VALUE+} OverridingKind;+
/* Possible sources of a Query */
typedef enum QuerySource
{
@@ -130,6 +137,8 @@ typedef struct Query
List *targetList; /* target list (of TargetEntry) */
+ OverridingKind override; /* OVERRIDING clause */+
OnConflictExpr *onConflict; /* ON CONFLICT DO [NOTHING | UPDATE] */
List *returningList; /* return-values list (of TargetEntry) */
@@ -637,6 +646,7 @@ typedef struct ColumnDef
char storage; /* attstorage setting, or 0 for default */
Node *raw_default; /* default value (untransformed parse tree) */
Node *cooked_default; /* default value (transformed expr tree) */
+ char identity; /* attidentity setting */
CollateClause *collClause; /* untransformed COLLATE spec, if any */
Oid collOid; /* collation OID (InvalidOid if not set) */
List *constraints; /* other constraints on column */
@@ -658,9 +668,10 @@ typedef enum TableLikeOption
{
CREATE_TABLE_LIKE_DEFAULTS = 1 << 0,
CREATE_TABLE_LIKE_CONSTRAINTS = 1 << 1,
- CREATE_TABLE_LIKE_INDEXES = 1 << 2,- CREATE_TABLE_LIKE_STORAGE = 1 << 3,- CREATE_TABLE_LIKE_COMMENTS = 1 << 4,+ CREATE_TABLE_LIKE_IDENTITY = 1 << 2,+ CREATE_TABLE_LIKE_INDEXES = 1 << 3,+ CREATE_TABLE_LIKE_STORAGE = 1 << 4,+ CREATE_TABLE_LIKE_COMMENTS = 1 << 5,
CREATE_TABLE_LIKE_ALL = PG_INT32_MAX
} TableLikeOption;
@@ -1399,6 +1410,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;
/* ----------------------
@@ -1708,7 +1720,10 @@ typedef enum AlterTableType
AT_NoForceRowSecurity, /* NO FORCE ROW SECURITY */
AT_GenericOptions, /* OPTIONS (...) */
AT_AttachPartition, /* ATTACH PARTITION */
- AT_DetachPartition /* DETACH PARTITION */+ AT_DetachPartition, /* DETACH PARTITION */+ AT_AddIdentity, /* ADD IDENTITY */+ AT_SetIdentity, /* SET identity column options */+ AT_DropIdentity /* DROP IDENTITY */
} AlterTableType;
typedef struct ReplicaIdentityStmt
@@ -1984,6 +1999,7 @@ typedef enum ConstrType /* types of constraints */
* expect it */
CONSTR_NOTNULL,
CONSTR_DEFAULT,
+ CONSTR_IDENTITY,
CONSTR_CHECK,
CONSTR_PRIMARY,
CONSTR_UNIQUE,
@@ -2022,6 +2038,7 @@ typedef struct Constraint
bool is_no_inherit; /* is constraint non-inheritable? */
Node *raw_expr; /* expr, as untransformed parse tree */
char *cooked_expr; /* expr, as nodeToString representation */
+ char generated_when;
/* Fields used for unique constraints (UNIQUE and PRIMARY KEY): */
List *keys; /* String nodes naming referenced column(s) */
diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.hindex d57b4fab3d..b87fe84545 100644--- a/src/include/nodes/primnodes.h+++ b/src/include/nodes/primnodes.h@@ -1292,6 +1292,20 @@ typedef struct InferenceElem
Oid inferopclass; /* OID of att opclass, or InvalidOid */
} InferenceElem;
+/*+ * NextValueExpr - get next value from sequence+ *+ * This has the same effect as calling the nextval() function, but it does not+ * check permissions on the sequence. This is used for identity columns,+ * where the sequence is an implicit dependency without its own permissions.+ */+typedef struct NextValueExpr+{+ Expr xpr;+ Oid seqid;+ Oid typeId;+} NextValueExpr;+
/*--------------------
* TargetEntry -
* a target entry (used in query target lists)
diff --git a/src/include/parser/kwlist.h b/src/include/parser/kwlist.hindex 28c4dab258..086420871b 100644--- a/src/include/parser/kwlist.h+++ b/src/include/parser/kwlist.h@@ -174,6 +174,7 @@ PG_KEYWORD("from", FROM, RESERVED_KEYWORD)
PG_KEYWORD("full", FULL, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("function", FUNCTION, UNRESERVED_KEYWORD)
PG_KEYWORD("functions", FUNCTIONS, UNRESERVED_KEYWORD)
+PG_KEYWORD("generated", GENERATED, UNRESERVED_KEYWORD)
PG_KEYWORD("global", GLOBAL, UNRESERVED_KEYWORD)
PG_KEYWORD("grant", GRANT, RESERVED_KEYWORD)
PG_KEYWORD("granted", GRANTED, UNRESERVED_KEYWORD)
@@ -286,6 +287,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.hindex b6d1fca2fa..88629d99aa 100644--- a/src/include/utils/lsyscache.h+++ b/src/include/utils/lsyscache.h@@ -64,6 +64,7 @@ extern Oid get_opfamily_proc(Oid opfamily, Oid lefttype, Oid righttype,
extern char *get_attname(Oid relid, AttrNumber attnum);
extern char *get_relid_attribute_name(Oid relid, AttrNumber attnum);
extern AttrNumber get_attnum(Oid relid, const char *attname);
+extern char get_attidentity(Oid relid, AttrNumber attnum);
extern Oid get_atttype(Oid relid, AttrNumber attnum);
extern int32 get_atttypmod(Oid relid, AttrNumber attnum);
extern void get_atttypetypmodcoll(Oid relid, AttrNumber attnum,
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.outindex a25b221703..3f405c94ce 100644--- a/src/test/regress/expected/create_table_like.out+++ b/src/test/regress/expected/create_table_like.out@@ -66,6 +66,53 @@ SELECT * FROM inhg; /* Two records with three columns in order x=x, xx=text, y=y
(2 rows)
DROP TABLE inhg;
+CREATE TABLE test_like_id_1 (a int GENERATED ALWAYS AS IDENTITY, b text);+\d test_like_id_1+ Table "public.test_like_id_1"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------+ a | integer | | not null | generated always as identity+ b | text | | | ++INSERT INTO test_like_id_1 (b) VALUES ('b1');+SELECT * FROM test_like_id_1;+ a | b +---+----+ 1 | b1+(1 row)++CREATE TABLE test_like_id_2 (LIKE test_like_id_1);+\d test_like_id_2+ Table "public.test_like_id_2"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+---------+ a | integer | | not null | + b | text | | | ++INSERT INTO test_like_id_2 (b) VALUES ('b2');+ERROR: null value in column "a" violates not-null constraint+DETAIL: Failing row contains (null, b2).+SELECT * FROM test_like_id_2; -- identity was not copied+ a | b +---+---+(0 rows)++CREATE TABLE test_like_id_3 (LIKE test_like_id_1 INCLUDING IDENTITY);+\d test_like_id_3+ Table "public.test_like_id_3"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+------------------------------+ a | integer | | not null | generated always as identity+ b | text | | | ++INSERT INTO test_like_id_3 (b) VALUES ('b3');+SELECT * FROM test_like_id_3; -- identity was copied and applied+ a | b +---+----+ 1 | b3+(1 row)++DROP TABLE test_like_id_1, test_like_id_2, test_like_id_3;
CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */
INSERT INTO inhg VALUES (5, 10);
INSERT INTO inhg VALUES (20, 10); -- should fail
diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.outnew file mode 100644index 0000000000..7df46cf71f--- /dev/null+++ b/src/test/regress/expected/identity.out@@ -0,0 +1,314 @@+CREATE TABLE itest1 (a int generated by default as identity, b text);+CREATE TABLE itest2 (a bigint generated always as identity, b text);+CREATE TABLE itest3 (a smallint generated by default as identity (start with 7 increment by 5), b text);+ALTER TABLE itest3 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error+ERROR: column "a" of relation "itest3" is already an identity column+SELECT table_name, column_name, column_default, is_nullable, is_identity, identity_generation, identity_start, identity_increment, identity_maximum, identity_minimum, identity_cycle FROM information_schema.columns WHERE table_name LIKE 'itest_' ORDER BY 1, 2;+ table_name | column_name | column_default | is_nullable | is_identity | identity_generation | identity_start | identity_increment | identity_maximum | identity_minimum | identity_cycle +------------+-------------+----------------+-------------+-------------+---------------------+----------------+--------------------+---------------------+------------------+----------------+ itest1 | a | | NO | YES | BY DEFAULT | 1 | 1 | 2147483647 | 1 | NO+ itest1 | b | | YES | NO | | | | | | NO+ itest2 | a | | NO | YES | ALWAYS | 1 | 1 | 9223372036854775807 | 1 | NO+ itest2 | b | | YES | NO | | | | | | NO+ itest3 | a | | NO | YES | BY DEFAULT | 7 | 5 | 32767 | 1 | NO+ itest3 | b | | YES | NO | | | | | | NO+(6 rows)++-- internal sequences should not be shown here+SELECT sequence_name FROM information_schema.sequences WHERE sequence_name LIKE 'itest%';+ sequence_name +---------------+(0 rows)++CREATE TABLE itest4 (a int, b text);+ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error, requires NOT NULL+ERROR: column "a" of relation "itest4" must be declared NOT NULL before identity can be added+ALTER TABLE itest4 ALTER COLUMN a SET NOT NULL;+ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- ok+ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL; -- error, disallowed+ERROR: column "a" of relation "itest4" is an identity column+ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error, already set+ERROR: column "a" of relation "itest4" is already an identity column+ALTER TABLE itest4 ALTER COLUMN b ADD GENERATED ALWAYS AS IDENTITY; -- error, wrong data type+ERROR: identity column type must be smallint, integer, or bigint+-- for later+ALTER TABLE itest4 ALTER COLUMN b SET DEFAULT '';+-- invalid column type+CREATE TABLE itest_err_1 (a text generated by default as identity);+ERROR: identity column type must be smallint, integer, or bigint+-- duplicate identity+CREATE TABLE itest_err_2 (a int generated always as identity generated by default as identity);+ERROR: multiple identity specifications for column "a" of table "itest_err_2"+LINE 1: ...E itest_err_2 (a int generated always as identity generated ...+ ^+-- cannot have default and identity+CREATE TABLE itest_err_3 (a int default 5 generated by default as identity);+ERROR: both default and identity specified for column "a" of table "itest_err_3"+LINE 1: CREATE TABLE itest_err_3 (a int default 5 generated by defau...+ ^+-- cannot combine serial and identity+CREATE TABLE itest_err_4 (a serial generated by default as identity);+ERROR: both default and identity specified for column "a" of table "itest_err_4"+INSERT INTO itest1 DEFAULT VALUES;+INSERT INTO itest1 DEFAULT VALUES;+INSERT INTO itest2 DEFAULT VALUES;+INSERT INTO itest2 DEFAULT VALUES;+INSERT INTO itest3 DEFAULT VALUES;+INSERT INTO itest3 DEFAULT VALUES;+INSERT INTO itest4 DEFAULT VALUES;+INSERT INTO itest4 DEFAULT VALUES;+SELECT * FROM itest1;+ a | b +---+---+ 1 | + 2 | +(2 rows)++SELECT * FROM itest2;+ a | b +---+---+ 1 | + 2 | +(2 rows)++SELECT * FROM itest3;+ a | b +----+---+ 7 | + 12 | +(2 rows)++SELECT * FROM itest4;+ a | b +---+---+ 1 | + 2 | +(2 rows)++-- OVERRIDING tests+INSERT INTO itest1 VALUES (10, 'xyz');+INSERT INTO itest1 OVERRIDING USER VALUE VALUES (10, 'xyz');+SELECT * FROM itest1;+ a | b +----+-----+ 1 | + 2 | + 10 | xyz+ 3 | xyz+(4 rows)++INSERT INTO itest2 VALUES (10, 'xyz');+ERROR: cannot insert into column "a"+DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS.+HINT: Use OVERRIDING SYSTEM VALUE to override.+INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (10, 'xyz');+SELECT * FROM itest2;+ a | b +----+-----+ 1 | + 2 | + 10 | xyz+(3 rows)++-- UPDATE tests+UPDATE itest1 SET a = 101 WHERE a = 1;+UPDATE itest1 SET a = DEFAULT WHERE a = 2;+SELECT * FROM itest1;+ a | b +-----+-----+ 10 | xyz+ 3 | xyz+ 101 | + 4 | +(4 rows)++UPDATE itest2 SET a = 101 WHERE a = 1;+ERROR: column "a" can only be updated to DEFAULT+DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS.+UPDATE itest2 SET a = DEFAULT WHERE a = 2;+SELECT * FROM itest2;+ a | b +----+-----+ 1 | + 10 | xyz+ 3 | +(3 rows)++-- DROP IDENTITY tests+ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY;+ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY; -- error+ERROR: column "a" of relation "itest4" is not an identity column+INSERT INTO itest4 DEFAULT VALUES; -- fails because NOT NULL is not dropped+ERROR: null value in column "a" violates not-null constraint+DETAIL: Failing row contains (null, ).+ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL;+INSERT INTO itest4 DEFAULT VALUES;+SELECT * FROM itest4;+ a | b +---+---+ 1 | + 2 | + | +(3 rows)++-- check that sequence is removed+SELECT sequence_name FROM itest4_a_seq;+ERROR: relation "itest4_a_seq" does not exist+LINE 1: SELECT sequence_name FROM itest4_a_seq;+ ^+-- test views+CREATE TABLE itest10 (a int generated by default as identity, b text);+CREATE TABLE itest11 (a int generated always as identity, b text);+CREATE VIEW itestv10 AS SELECT * FROM itest10;+CREATE VIEW itestv11 AS SELECT * FROM itest11;+INSERT INTO itestv10 DEFAULT VALUES;+INSERT INTO itestv10 DEFAULT VALUES;+INSERT INTO itestv11 DEFAULT VALUES;+INSERT INTO itestv11 DEFAULT VALUES;+SELECT * FROM itestv10;+ a | b +---+---+ 1 | + 2 | +(2 rows)++SELECT * FROM itestv11;+ a | b +---+---+ 1 | + 2 | +(2 rows)++INSERT INTO itestv10 VALUES (10, 'xyz');+INSERT INTO itestv10 OVERRIDING USER VALUE VALUES (11, 'xyz');+SELECT * FROM itestv10;+ a | b +----+-----+ 1 | + 2 | + 10 | xyz+ 3 | xyz+(4 rows)++INSERT INTO itestv11 VALUES (10, 'xyz');+ERROR: cannot insert into column "a"+DETAIL: Column "a" is an identity column defined as GENERATED ALWAYS.+HINT: Use OVERRIDING SYSTEM VALUE to override.+INSERT INTO itestv11 OVERRIDING SYSTEM VALUE VALUES (11, 'xyz');+SELECT * FROM itestv11;+ a | b +----+-----+ 1 | + 2 | + 11 | xyz+(3 rows)++-- various ALTER COLUMN tests+-- fail, not allowed for identity columns+ALTER TABLE itest1 ALTER COLUMN a SET DEFAULT 1;+ERROR: column "a" of relation "itest1" is an identity column+-- fail, not allowed, already has a default+CREATE TABLE itest5 (a serial, b text);+ALTER TABLE itest5 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;+ERROR: column "a" of relation "itest5" already has a default value+ALTER TABLE itest3 ALTER COLUMN a TYPE int;+SELECT seqtypid::regtype FROM pg_sequence WHERE seqrelid = 'itest3_a_seq'::regclass;+ seqtypid +----------+ integer+(1 row)++\d itest3+ Table "public.itest3"+ Column | Type | Collation | Nullable | Default +--------+---------+-----------+----------+----------------------------------+ a | integer | | not null | generated by default as identity+ b | text | | | ++ALTER TABLE itest3 ALTER COLUMN a TYPE text; -- error+ERROR: identity column type must be smallint, integer, or bigint+-- ALTER COLUMN ... SET+CREATE TABLE itest6 (a int GENERATED ALWAYS AS IDENTITY, b text);+INSERT INTO itest6 DEFAULT VALUES;+ALTER TABLE itest6 ALTER COLUMN a SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 100 RESTART;+INSERT INTO itest6 DEFAULT VALUES;+INSERT INTO itest6 DEFAULT VALUES;+SELECT * FROM itest6;+ a | b +-----+---+ 1 | + 100 | + 102 | +(3 rows)++SELECT table_name, column_name, is_identity, identity_generation FROM information_schema.columns WHERE table_name = 'itest6';+ table_name | column_name | is_identity | identity_generation +------------+-------------+-------------+---------------------+ itest6 | a | YES | BY DEFAULT+ itest6 | b | NO | +(2 rows)++ALTER TABLE itest6 ALTER COLUMN b SET INCREMENT BY 2; -- fail, not identity+ERROR: column "b" of relation "itest6" is not an identity column+-- prohibited direct modification of sequence+ALTER SEQUENCE itest6_a_seq OWNED BY NONE;+ERROR: cannot change ownership of identity sequence+DETAIL: Sequence "itest6_a_seq" is linked to table "itest6".+-- inheritance+CREATE TABLE itest7 (a int GENERATED ALWAYS AS IDENTITY);+INSERT INTO itest7 DEFAULT VALUES;+SELECT * FROM itest7;+ a +---+ 1+(1 row)++-- identity property is not inherited+CREATE TABLE itest7a (b text) INHERITS (itest7);+-- make column identity in child table+CREATE TABLE itest7b (a int);+CREATE TABLE itest7c (a int GENERATED ALWAYS AS IDENTITY) INHERITS (itest7b);+NOTICE: merging column "a" with inherited definition+INSERT INTO itest7c DEFAULT VALUES;+SELECT * FROM itest7c;+ a +---+ 1+(1 row)++CREATE TABLE itest7d (a int not null);+CREATE TABLE itest7e () INHERITS (itest7d);+ALTER TABLE itest7d ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;+ALTER TABLE itest7d ADD COLUMN b int GENERATED ALWAYS AS IDENTITY; -- error+ERROR: cannot recursively add identity column to table that has child tables+SELECT table_name, column_name, is_nullable, is_identity, identity_generation FROM information_schema.columns WHERE table_name LIKE 'itest7%' ORDER BY 1, 2;+ table_name | column_name | is_nullable | is_identity | identity_generation +------------+-------------+-------------+-------------+---------------------+ itest7 | a | NO | YES | ALWAYS+ itest7a | a | NO | NO | + itest7a | b | YES | NO | + itest7b | a | YES | NO | + itest7c | a | NO | YES | ALWAYS+ itest7d | a | NO | YES | ALWAYS+ itest7e | a | NO | NO | +(7 rows)++-- These ALTER TABLE variants will not recurse.+ALTER TABLE itest7 ALTER COLUMN a SET GENERATED BY DEFAULT;+ALTER TABLE itest7 ALTER COLUMN a RESTART;+ALTER TABLE itest7 ALTER COLUMN a DROP IDENTITY;+-- privileges+CREATE USER regress_user1;+CREATE TABLE itest8 (a int GENERATED ALWAYS AS IDENTITY, b text);+GRANT SELECT, INSERT ON itest8 TO regress_user1;+SET ROLE regress_user1;+INSERT INTO itest8 DEFAULT VALUES;+SELECT * FROM itest8;+ a | b +---+---+ 1 | +(1 row)++RESET ROLE;+DROP TABLE itest8;+DROP USER regress_user1;diff --git a/src/test/regress/expected/sequence.out b/src/test/regress/expected/sequence.outindex 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 table-ERROR: referenced relation "pg_tables" is not a table or foreign table+CREATE SEQUENCE sequence_testx OWNED BY pg_class_oid_index.oid; -- not a table+ERROR: referenced relation "pg_class_oid_index" is not a table or foreign table
CREATE SEQUENCE sequence_testx OWNED BY pg_class.relname; -- not same schema
ERROR: sequence must be in same schema as table it is linked to
CREATE TABLE sequence_test_table (a int);
diff --git a/src/test/regress/expected/truncate.out b/src/test/regress/expected/truncate.outindex 81612d8c88..b652562f5b 100644--- a/src/test/regress/expected/truncate.out+++ b/src/test/regress/expected/truncate.out@@ -393,6 +393,36 @@ SELECT * FROM truncate_a;
2 | 34
(2 rows)
+CREATE TABLE truncate_b (id int GENERATED ALWAYS AS IDENTITY (START WITH 44));+INSERT INTO truncate_b DEFAULT VALUES;+INSERT INTO truncate_b DEFAULT VALUES;+SELECT * FROM truncate_b;+ id +----+ 44+ 45+(2 rows)++TRUNCATE truncate_b;+INSERT INTO truncate_b DEFAULT VALUES;+INSERT INTO truncate_b DEFAULT VALUES;+SELECT * FROM truncate_b;+ id +----+ 46+ 47+(2 rows)++TRUNCATE truncate_b RESTART IDENTITY;+INSERT INTO truncate_b DEFAULT VALUES;+INSERT INTO truncate_b DEFAULT VALUES;+SELECT * FROM truncate_b;+ id +----+ 44+ 45+(2 rows)+
-- check rollback of a RESTART IDENTITY operation
BEGIN;
TRUNCATE truncate_a RESTART IDENTITY;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_scheduleindex 38743d98c3..bfc17705ea 100644--- a/src/test/regress/parallel_schedule+++ b/src/test/regress/parallel_schedule@@ -111,6 +111,11 @@ test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combo
# ----------
test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml
+# ----------+# Another group of parallel tests+# ----------+test: identity+
# event triggers cannot run concurrently with any test that runs DDL
test: event_trigger
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_scheduleindex d9f64c2873..f451e3a422 100644--- a/src/test/regress/serial_schedule+++ b/src/test/regress/serial_schedule@@ -169,6 +169,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.sqlindex 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 CONSTRAINTS, y text); /* Copies c
SELECT * FROM inhg; /* Two records with three columns in order x=x, xx=text, y=y */
DROP TABLE inhg;
+CREATE TABLE test_like_id_1 (a int GENERATED ALWAYS AS IDENTITY, b text);+\d test_like_id_1+INSERT INTO test_like_id_1 (b) VALUES ('b1');+SELECT * FROM test_like_id_1;+CREATE TABLE test_like_id_2 (LIKE test_like_id_1);+\d test_like_id_2+INSERT INTO test_like_id_2 (b) VALUES ('b2');+SELECT * FROM test_like_id_2; -- identity was not copied+CREATE TABLE test_like_id_3 (LIKE test_like_id_1 INCLUDING IDENTITY);+\d test_like_id_3+INSERT INTO test_like_id_3 (b) VALUES ('b3');+SELECT * FROM test_like_id_3; -- identity was copied and applied+DROP TABLE test_like_id_1, test_like_id_2, test_like_id_3;+
CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */
INSERT INTO inhg VALUES (5, 10);
INSERT INTO inhg VALUES (20, 10); -- should fail
diff --git a/src/test/regress/sql/identity.sql b/src/test/regress/sql/identity.sqlnew file mode 100644index 0000000000..efcd2aa1cc--- /dev/null+++ b/src/test/regress/sql/identity.sql@@ -0,0 +1,187 @@+CREATE TABLE itest1 (a int generated by default as identity, b text);+CREATE TABLE itest2 (a bigint generated always as identity, b text);+CREATE TABLE itest3 (a smallint generated by default as identity (start with 7 increment by 5), b text);+ALTER TABLE itest3 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error++SELECT table_name, column_name, column_default, is_nullable, is_identity, identity_generation, identity_start, identity_increment, identity_maximum, identity_minimum, identity_cycle FROM information_schema.columns WHERE table_name LIKE 'itest_' ORDER BY 1, 2;++-- internal sequences should not be shown here+SELECT sequence_name FROM information_schema.sequences WHERE sequence_name LIKE 'itest%';++CREATE TABLE itest4 (a int, b text);+ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error, requires NOT NULL+ALTER TABLE itest4 ALTER COLUMN a SET NOT NULL;+ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- ok+ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL; -- error, disallowed+ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error, already set+ALTER TABLE itest4 ALTER COLUMN b ADD GENERATED ALWAYS AS IDENTITY; -- error, wrong data type++-- for later+ALTER TABLE itest4 ALTER COLUMN b SET DEFAULT '';++-- invalid column type+CREATE TABLE itest_err_1 (a text generated by default as identity);++-- duplicate identity+CREATE TABLE itest_err_2 (a int generated always as identity generated by default as identity);++-- cannot have default and identity+CREATE TABLE itest_err_3 (a int default 5 generated by default as identity);++-- cannot combine serial and identity+CREATE TABLE itest_err_4 (a serial generated by default as identity);++INSERT INTO itest1 DEFAULT VALUES;+INSERT INTO itest1 DEFAULT VALUES;+INSERT INTO itest2 DEFAULT VALUES;+INSERT INTO itest2 DEFAULT VALUES;+INSERT INTO itest3 DEFAULT VALUES;+INSERT INTO itest3 DEFAULT VALUES;+INSERT INTO itest4 DEFAULT VALUES;+INSERT INTO itest4 DEFAULT VALUES;++SELECT * FROM itest1;+SELECT * FROM itest2;+SELECT * FROM itest3;+SELECT * FROM itest4;+++-- OVERRIDING tests++INSERT INTO itest1 VALUES (10, 'xyz');+INSERT INTO itest1 OVERRIDING USER VALUE VALUES (10, 'xyz');++SELECT * FROM itest1;++INSERT INTO itest2 VALUES (10, 'xyz');+INSERT INTO itest2 OVERRIDING SYSTEM VALUE VALUES (10, 'xyz');++SELECT * FROM itest2;+++-- UPDATE tests++UPDATE itest1 SET a = 101 WHERE a = 1;+UPDATE itest1 SET a = DEFAULT WHERE a = 2;+SELECT * FROM itest1;++UPDATE itest2 SET a = 101 WHERE a = 1;+UPDATE itest2 SET a = DEFAULT WHERE a = 2;+SELECT * FROM itest2;+++-- DROP IDENTITY tests++ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY;+ALTER TABLE itest4 ALTER COLUMN a DROP IDENTITY; -- error++INSERT INTO itest4 DEFAULT VALUES; -- fails because NOT NULL is not dropped+ALTER TABLE itest4 ALTER COLUMN a DROP NOT NULL;+INSERT INTO itest4 DEFAULT VALUES;+SELECT * FROM itest4;++-- check that sequence is removed+SELECT sequence_name FROM itest4_a_seq;+++-- test views++CREATE TABLE itest10 (a int generated by default as identity, b text);+CREATE TABLE itest11 (a int generated always as identity, b text);++CREATE VIEW itestv10 AS SELECT * FROM itest10;+CREATE VIEW itestv11 AS SELECT * FROM itest11;++INSERT INTO itestv10 DEFAULT VALUES;+INSERT INTO itestv10 DEFAULT VALUES;++INSERT INTO itestv11 DEFAULT VALUES;+INSERT INTO itestv11 DEFAULT VALUES;++SELECT * FROM itestv10;+SELECT * FROM itestv11;++INSERT INTO itestv10 VALUES (10, 'xyz');+INSERT INTO itestv10 OVERRIDING USER VALUE VALUES (11, 'xyz');++SELECT * FROM itestv10;++INSERT INTO itestv11 VALUES (10, 'xyz');+INSERT INTO itestv11 OVERRIDING SYSTEM VALUE VALUES (11, 'xyz');++SELECT * FROM itestv11;+++-- various ALTER COLUMN tests++-- fail, not allowed for identity columns+ALTER TABLE itest1 ALTER COLUMN a SET DEFAULT 1;++-- fail, not allowed, already has a default+CREATE TABLE itest5 (a serial, b text);+ALTER TABLE itest5 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;++ALTER TABLE itest3 ALTER COLUMN a TYPE int;+SELECT seqtypid::regtype FROM pg_sequence WHERE seqrelid = 'itest3_a_seq'::regclass;+\d itest3++ALTER TABLE itest3 ALTER COLUMN a TYPE text; -- error+++-- ALTER COLUMN ... SET++CREATE TABLE itest6 (a int GENERATED ALWAYS AS IDENTITY, b text);+INSERT INTO itest6 DEFAULT VALUES;++ALTER TABLE itest6 ALTER COLUMN a SET GENERATED BY DEFAULT SET INCREMENT BY 2 SET START WITH 100 RESTART;+INSERT INTO itest6 DEFAULT VALUES;+INSERT INTO itest6 DEFAULT VALUES;+SELECT * FROM itest6;++SELECT table_name, column_name, is_identity, identity_generation FROM information_schema.columns WHERE table_name = 'itest6';++ALTER TABLE itest6 ALTER COLUMN b SET INCREMENT BY 2; -- fail, not identity+++-- prohibited direct modification of sequence++ALTER SEQUENCE itest6_a_seq OWNED BY NONE;+++-- inheritance++CREATE TABLE itest7 (a int GENERATED ALWAYS AS IDENTITY);+INSERT INTO itest7 DEFAULT VALUES;+SELECT * FROM itest7;++-- identity property is not inherited+CREATE TABLE itest7a (b text) INHERITS (itest7);++-- make column identity in child table+CREATE TABLE itest7b (a int);+CREATE TABLE itest7c (a int GENERATED ALWAYS AS IDENTITY) INHERITS (itest7b);+INSERT INTO itest7c DEFAULT VALUES;+SELECT * FROM itest7c;++CREATE TABLE itest7d (a int not null);+CREATE TABLE itest7e () INHERITS (itest7d);+ALTER TABLE itest7d ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;+ALTER TABLE itest7d ADD COLUMN b int GENERATED ALWAYS AS IDENTITY; -- error++SELECT table_name, column_name, is_nullable, is_identity, identity_generation FROM information_schema.columns WHERE table_name LIKE 'itest7%' ORDER BY 1, 2;++-- These ALTER TABLE variants will not recurse.+ALTER TABLE itest7 ALTER COLUMN a SET GENERATED BY DEFAULT;+ALTER TABLE itest7 ALTER COLUMN a RESTART;+ALTER TABLE itest7 ALTER COLUMN a DROP IDENTITY;++-- privileges+CREATE USER regress_user1;+CREATE TABLE itest8 (a int GENERATED ALWAYS AS IDENTITY, b text);+GRANT SELECT, INSERT ON itest8 TO regress_user1;+SET ROLE regress_user1;+INSERT INTO itest8 DEFAULT VALUES;+SELECT * FROM itest8;+RESET ROLE;+DROP TABLE itest8;+DROP USER regress_user1;diff --git a/src/test/regress/sql/sequence.sql b/src/test/regress/sql/sequence.sqlindex 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;
-- OWNED BY errors
CREATE SEQUENCE sequence_testx OWNED BY nobody; -- nonsense word
-CREATE SEQUENCE sequence_testx OWNED BY pg_tables.tablename; -- not a table+CREATE SEQUENCE sequence_testx OWNED BY pg_class_oid_index.oid; -- not a table
CREATE SEQUENCE sequence_testx OWNED BY pg_class.relname; -- not same schema
CREATE TABLE sequence_test_table (a int);
CREATE SEQUENCE sequence_testx OWNED BY sequence_test_table.b; -- wrong column
diff --git a/src/test/regress/sql/truncate.sql b/src/test/regress/sql/truncate.sqlindex d61eea1a42..9d3d8de54a 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;
+CREATE TABLE truncate_b (id int GENERATED ALWAYS AS IDENTITY (START WITH 44));++INSERT INTO truncate_b DEFAULT VALUES;+INSERT INTO truncate_b DEFAULT VALUES;+SELECT * FROM truncate_b;++TRUNCATE truncate_b;++INSERT INTO truncate_b DEFAULT VALUES;+INSERT INTO truncate_b DEFAULT VALUES;+SELECT * FROM truncate_b;++TRUNCATE truncate_b RESTART IDENTITY;++INSERT INTO truncate_b DEFAULT VALUES;+INSERT INTO truncate_b DEFAULT VALUES;+SELECT * FROM truncate_b;+
-- check rollback of a RESTART IDENTITY operation
BEGIN;
TRUNCATE truncate_a RESTART IDENTITY;
--
2.12.0
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: peter.eisentraut@2ndquadrant.com, vitaly.burovoy@gmail.com
Subject: Re: identity columns
In-Reply-To: <ed41b243-63b2-f287-e7b0-8b2ac7266d66@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox