Updated patch with some merge conflicts resolved and some minor bug
fixes. Vitaly's earlier reviews were very comprehensive, and I believe
I have fixed all the issues that have been pointed out, so just
double-checking that would be helpful at this point. I still don't have
a solution for managing access to the implicit sequences without
permission checking, but I have an idea, so I might send an update sometime.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>From eee7443a04676a2b22892fb8114d2814e720b8ed Mon Sep 17 00:00:00 2001
From: Peter Eisentraut <pete...@gmx.net>
Date: Sat, 31 Dec 2016 12:00:00 -0500
Subject: [PATCH v3] 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
- slight weirdnesses because serial is some kind of special macro
---
doc/src/sgml/catalogs.sgml | 11 +
doc/src/sgml/information_schema.sgml | 11 +-
doc/src/sgml/ref/alter_table.sgml | 29 ++
doc/src/sgml/ref/create_table.sgml | 58 +++-
doc/src/sgml/ref/insert.sgml | 41 +++
src/backend/access/common/tupdesc.c | 5 +
src/backend/catalog/genbki.pl | 2 +
src/backend/catalog/heap.c | 15 +-
src/backend/catalog/index.c | 1 +
src/backend/catalog/information_schema.sql | 19 +-
src/backend/catalog/pg_depend.c | 17 +-
src/backend/catalog/sql_features.txt | 12 +-
src/backend/commands/sequence.c | 108 ++++++-
src/backend/commands/tablecmds.c | 287 ++++++++++++++++-
src/backend/nodes/copyfuncs.c | 4 +
src/backend/nodes/equalfuncs.c | 4 +
src/backend/nodes/makefuncs.c | 1 +
src/backend/nodes/outfuncs.c | 9 +
src/backend/nodes/readfuncs.c | 1 +
src/backend/parser/analyze.c | 2 +
src/backend/parser/gram.y | 126 +++++++-
src/backend/parser/parse_utilcmd.c | 396 ++++++++++++++++++------
src/backend/rewrite/rewriteHandler.c | 43 ++-
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 | 87 +++++-
src/bin/pg_dump/pg_dump.h | 3 +
src/bin/psql/describe.c | 26 +-
src/bin/psql/tab-complete.c | 18 +-
src/include/catalog/catversion.h | 2 +-
src/include/catalog/dependency.h | 2 +-
src/include/catalog/pg_attribute.h | 20 +-
src/include/catalog/pg_class.h | 2 +-
src/include/commands/sequence.h | 1 +
src/include/nodes/parsenodes.h | 28 +-
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 | 274 ++++++++++++++++
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 | 162 ++++++++++
src/test/regress/sql/truncate.sql | 18 ++
47 files changed, 1800 insertions(+), 176 deletions(-)
create mode 100644 src/test/regress/expected/identity.out
create mode 100644 src/test/regress/sql/identity.sql
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 493050618d..3c18fffc1c 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1105,6 +1105,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.sgml
index c43e325d06..8ece4398ba 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.sgml
index da431f8369..be1c7f33a9 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -46,6 +46,9 @@
ALTER [ COLUMN ] <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> | RESET } [...]
+ 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>RESET</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>
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index 58f8bf6d6a..1d4b6db189 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>
@@ -442,6 +443,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.
@@ -473,7 +480,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
@@ -588,6 +595,35 @@ <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.
+ </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>
@@ -1220,7 +1256,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
@@ -1280,7 +1316,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>
@@ -1664,6 +1700,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.sgml
index 9339826818..4cef7e92b8 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.c
index b56d0e336f..8a8244ec42 100644
--- a/src/backend/access/common/tupdesc.c
+++ b/src/backend/access/common/tupdesc.c
@@ -149,6 +149,7 @@ CreateTupleDescCopy(TupleDesc tupdesc)
memcpy(desc->attrs[i], tupdesc->attrs[i], ATTRIBUTE_FIXED_PART_SIZE);
desc->attrs[i]->attnotnull = false;
desc->attrs[i]->atthasdef = false;
+ desc->attrs[i]->attidentity = ' ';
}
desc->tdtypeid = tupdesc->tdtypeid;
@@ -256,6 +257,7 @@ TupleDescCopyEntry(TupleDesc dst, AttrNumber dstAttno,
/* since we're not copying constraints or defaults, clear these */
dst->attrs[dstAttno - 1]->attnotnull = false;
dst->attrs[dstAttno - 1]->atthasdef = false;
+ dst->attrs[dstAttno - 1]->attidentity = ' ';
}
/*
@@ -400,6 +402,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)
@@ -533,6 +537,7 @@ TupleDescInitEntry(TupleDesc desc,
att->attnotnull = false;
att->atthasdef = false;
+ att->attidentity = ' ';
att->attisdropped = false;
att->attislocal = true;
att->attinhcount = 0;
diff --git a/src/backend/catalog/genbki.pl b/src/backend/catalog/genbki.pl
index 26d165203d..f0d79c324a 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.c
index e5d6aecc3f..c0f66425c1 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, ' ', 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, ' ', 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, ' ', 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, ' ', 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, ' ', 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, ' ', 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, ' ', false, true, 0
};
static const Form_pg_attribute SysAtt[] = {&a1, &a2, &a3, &a4, &a5, &a6, &a7};
@@ -628,6 +628,7 @@ InsertPgAttributeTuple(Relation pg_attribute_rel,
values[Anum_pg_attribute_attalign - 1] = CharGetDatum(new_attribute->attalign);
values[Anum_pg_attribute_attnotnull - 1] = BoolGetDatum(new_attribute->attnotnull);
values[Anum_pg_attribute_atthasdef - 1] = BoolGetDatum(new_attribute->atthasdef);
+ values[Anum_pg_attribute_attidentity - 1] = CharGetDatum(new_attribute->attidentity);
values[Anum_pg_attribute_attisdropped - 1] = BoolGetDatum(new_attribute->attisdropped);
values[Anum_pg_attribute_attislocal - 1] = BoolGetDatum(new_attribute->attislocal);
values[Anum_pg_attribute_attinhcount - 1] = Int32GetDatum(new_attribute->attinhcount);
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 08b0989112..eca570eb49 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 = ' ';
to->attislocal = true;
to->attinhcount = 0;
to->attcollation = collationObjectId[i];
diff --git a/src/backend/catalog/information_schema.sql b/src/backend/catalog/information_schema.sql
index 182d2d0674..8d86cafc6c 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -646,7 +646,7 @@ CREATE VIEW columns AS
CAST(c.relname AS sql_identifier) AS table_name,
CAST(a.attname AS sql_identifier) AS column_name,
CAST(a.attnum AS cardinal_number) AS ordinal_position,
- CAST(pg_get_expr(ad.adbin, ad.adrelid) AS character_data) AS column_default,
+ CAST(CASE WHEN a.atthasdef AND a.attidentity = ' ' THEN pg_get_expr(ad.adbin, ad.adrelid) END AS character_data) AS column_default,
CAST(CASE WHEN a.attnotnull OR (t.typtype = 'd' AND t.typnotnull) THEN 'NO' ELSE 'YES' END
AS yes_or_no)
AS is_nullable,
@@ -727,13 +727,13 @@ CREATE VIEW columns AS
CAST(a.attnum AS sql_identifier) AS dtd_identifier,
CAST('NO' AS yes_or_no) AS is_self_referencing,
- 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(p.start_value AS character_data) AS identity_start,
+ CAST(p.increment AS character_data) AS identity_increment,
+ CAST(p.maximum_value AS character_data) AS identity_maximum,
+ CAST(p.minimum_value AS character_data) AS identity_minimum,
+ CAST(CASE WHEN p.cycle_option THEN 'YES' ELSE 'NO' END AS yes_or_no) AS identity_cycle,
CAST('NEVER' AS character_data) AS is_generated,
CAST(null AS character_data) AS generation_expression,
@@ -750,6 +750,8 @@ CREATE VIEW columns AS
ON (t.typtype = '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_class seq ON (dep.classid = 'pg_class'::regclass AND dep.objid = seq.oid AND dep.deptype = 'i') JOIN LATERAL pg_sequence_parameters(seq.oid) p ON true)
+ ON (dep.refclassid = 'pg_attrdef'::regclass AND dep.refobjid = ad.oid)
WHERE (NOT pg_is_other_temp_schema(nc.oid))
@@ -1543,6 +1545,7 @@ CREATE VIEW sequences AS
FROM pg_namespace nc, pg_class c, pg_sequence s
WHERE c.relnamespace = nc.oid
AND c.relkind = 'S'
+ AND NOT EXISTS (SELECT 1 FROM pg_depend WHERE classid = 'pg_class'::regclass AND objid = c.oid AND deptype = 'i')
AND (NOT pg_is_other_temp_schema(nc.oid))
AND c.oid = s.seqrelid
AND (pg_has_role(c.relowner, 'USAGE')
diff --git a/src/backend/catalog/pg_depend.c b/src/backend/catalog/pg_depend.c
index 7a0713e6cc..0823b882f9 100644
--- a/src/backend/catalog/pg_depend.c
+++ b/src/backend/catalog/pg_depend.c
@@ -19,6 +19,7 @@
#include "access/htup_details.h"
#include "catalog/dependency.h"
#include "catalog/indexing.h"
+#include "catalog/pg_attrdef.h"
#include "catalog/pg_constraint.h"
#include "catalog/pg_depend.h"
#include "catalog/pg_extension.h"
@@ -554,17 +555,20 @@ markSequenceUnowned(Oid seqId)
{
deleteDependencyRecordsForClass(RelationRelationId, seqId,
RelationRelationId, DEPENDENCY_AUTO);
+ deleteDependencyRecordsForClass(RelationRelationId, seqId,
+ AttrDefaultRelationId, DEPENDENCY_INTERNAL);
}
/*
- * 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;
@@ -578,9 +582,14 @@ getOwnedSequences(Oid relid)
Anum_pg_depend_refobjid,
BTEqualStrategyNumber, F_OIDEQ,
ObjectIdGetDatum(relid));
+ if (attnum)
+ ScanKeyInit(&key[2],
+ Anum_pg_depend_refobjsubid,
+ BTEqualStrategyNumber, F_INT4EQ,
+ Int32GetDatum(attnum));
scan = systable_beginscan(depRel, DependReferenceIndexId, true,
- NULL, 2, key);
+ NULL, attnum ? 3 : 2, key);
while (HeapTupleIsValid(tup = systable_getnext(scan)))
{
diff --git a/src/backend/catalog/sql_features.txt b/src/backend/catalog/sql_features.txt
index 8956ba9304..2821b9b702 100644
--- a/src/backend/catalog/sql_features.txt
+++ b/src/backend/catalog/sql_features.txt
@@ -200,7 +200,7 @@ F181 Multiple module support NO
F191 Referential delete actions YES
F200 TRUNCATE TABLE statement YES
F201 CAST function YES
-F202 TRUNCATE TABLE: identity column restart option NO
+F202 TRUNCATE TABLE: identity column restart option YES
F221 Explicit defaults YES
F222 INSERT statement: DEFAULT VALUES clause YES
F231 Privilege tables YES
@@ -241,9 +241,9 @@ F381 Extended schema manipulation 02 ALTER TABLE statement: ADD CONSTRAINT claus
F381 Extended schema manipulation 03 ALTER TABLE statement: DROP CONSTRAINT clause YES
F382 Alter column data type YES
F383 Set column not null clause YES
-F384 Drop identity property clause NO
+F384 Drop identity property clause YES
F385 Drop column generation expression clause NO
-F386 Set identity column generation clause NO
+F386 Set identity column generation clause YES
F391 Long identifiers YES
F392 Unicode escapes in identifiers YES
F393 Unicode escapes in literals YES
@@ -420,11 +420,11 @@ T152 DISTINCT predicate with negation YES
T171 LIKE clause in table definition YES
T172 AS subquery clause in table definition YES
T173 Extended LIKE clause in table definition YES
-T174 Identity columns NO
+T174 Identity columns YES
T175 Generated columns NO
T176 Sequence generator support NO
-T177 Sequence generator support: simple restart option NO
-T178 Identity columns: simple restart option NO
+T177 Sequence generator support: simple restart option YES
+T178 Identity columns: simple restart option YES
T180 System-versioned tables NO
T181 Application-time period tables NO
T191 Referential action RESTRICT YES
diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
index 668d82771a..f5e6a4f6e4 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -25,6 +25,7 @@
#include "catalog/indexing.h"
#include "catalog/namespace.h"
#include "catalog/objectaccess.h"
+#include "catalog/pg_attrdef.h"
#include "catalog/pg_sequence.h"
#include "catalog/pg_type.h"
#include "commands/defrem.h"
@@ -38,6 +39,7 @@
#include "storage/smgr.h"
#include "utils/acl.h"
#include "utils/builtins.h"
+#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
#include "utils/resowner.h"
#include "utils/syscache.h"
@@ -98,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);
/*
@@ -113,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;
@@ -150,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)
@@ -167,6 +171,7 @@ DefineSequence(ParseState *pstate, CreateSeqStmt *seq)
coldef->storage = 0;
coldef->raw_default = NULL;
coldef->cooked_default = NULL;
+ coldef->identity = ' ';
coldef->collClause = NULL;
coldef->collOid = InvalidOid;
coldef->constraints = NIL;
@@ -216,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);
@@ -415,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;
@@ -452,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 */
@@ -495,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);
@@ -1219,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 *start_value = NULL;
DefElem *restart_value = NULL;
@@ -1228,9 +1235,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)
{
@@ -1308,6 +1317,15 @@ 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")));
+ deptype_el = defel;
+ *deptype = intVal(deptype_el->arg);
+ }
else
elog(ERROR, "option \"%s\" not recognized",
defel->defname);
@@ -1486,6 +1504,38 @@ init_params(ParseState *pstate, List *options, bool isInit,
seqform->seqcache = 1;
}
+static Oid
+get_attrdef_oid(Oid relid, AttrNumber attnum)
+{
+ Relation attrdef_rel;
+ ScanKeyData scankeys[2];
+ SysScanDesc scan;
+ HeapTuple tuple;
+ Oid result = InvalidOid;
+
+ attrdef_rel = heap_open(AttrDefaultRelationId, RowExclusiveLock);
+
+ ScanKeyInit(&scankeys[0],
+ Anum_pg_attrdef_adrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(relid));
+ ScanKeyInit(&scankeys[1],
+ Anum_pg_attrdef_adnum,
+ BTEqualStrategyNumber, F_INT2EQ,
+ Int16GetDatum(attnum));
+
+ scan = systable_beginscan(attrdef_rel, AttrDefaultIndexId, true,
+ NULL, 2, scankeys);
+
+ if (HeapTupleIsValid(tuple = systable_getnext(scan)))
+ result = HeapTupleGetOid(tuple);
+
+ systable_endscan(scan);
+ heap_close(attrdef_rel, RowExclusiveLock);
+
+ return result;
+}
+
/*
* Process an OWNED BY option for CREATE/ALTER SEQUENCE
*
@@ -1495,7 +1545,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;
@@ -1531,6 +1581,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),
@@ -1574,6 +1625,20 @@ process_owned_by(Relation seqrel, List *owned_by)
depobject.objectId = RelationGetRelid(seqrel);
depobject.objectSubId = 0;
recordDependencyOn(&depobject, &refobject, DEPENDENCY_AUTO);
+
+ /*
+ * For identity columns, also record an internal dependency of the
+ * sequence on the default (you drop the default, the sequence is
+ * removed). We still do the auto dependency on the column, because
+ * that is what TRUNCATE RESTART IDENTITY looks for.
+ */
+ if (deptype == DEPENDENCY_INTERNAL)
+ {
+ refobject.classId = AttrDefaultRelationId;
+ refobject.objectId = get_attrdef_oid(RelationGetRelid(tablerel), attnum);
+ refobject.objectSubId = 0;
+ recordDependencyOn(&depobject, &refobject, DEPENDENCY_INTERNAL);
+ }
}
/* Done, but hold lock until commit */
@@ -1583,6 +1648,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.c
index 2f605ce83d..bec6e6ffa2 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -358,6 +358,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,
@@ -698,6 +703,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;
}
/*
@@ -1283,7 +1291,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)
@@ -1804,6 +1812,15 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
continue; /* leave newattno entry as zero */
/*
+ * XXX This could be done, but it needs more thought on what it
+ * means.
+ */
+ if (attribute->attidentity != ' ')
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot inherit from table with identity column")));
+
+ /*
* Does it conflict with some previously inherited column?
*/
exist_attno = findAttrByName(attributeName, inhSchema);
@@ -1877,6 +1894,7 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
def->storage = attribute->attstorage;
def->raw_default = NULL;
def->cooked_default = NULL;
+ def->identity = attribute->attidentity;
def->collClause = NULL;
def->collOid = attribute->attcollation;
def->constraints = NIL;
@@ -2072,6 +2090,14 @@ MergeAttributes(List *schema, List *supers, char relpersistence,
get_collation_name(defcollid),
get_collation_name(newcollid))));
+ /*
+ * def->identity is always ' ' because of the prohibition
+ * against inheriting from identity columns enforced earlier.
+ * 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;
@@ -3212,6 +3238,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;
@@ -3430,6 +3459,8 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
pass = AT_PASS_ADD_COL;
break;
case AT_ColumnDefault: /* ALTER COLUMN DEFAULT */
+ case AT_AddIdentity:
+ case AT_DropIdentity:
/*
* We allow defaults on views so that INSERT into a view can have
@@ -3442,6 +3473,11 @@ 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_SetIdentity:
+ ATSimplePermissions(rel, ATT_TABLE | ATT_VIEW | ATT_FOREIGN_TABLE);
+ ATSimpleRecursion(wqueue, rel, cmd, recurse, lockmode);
+ 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);
@@ -3767,6 +3803,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;
@@ -5119,6 +5164,11 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
elog(ERROR, "cache lookup failed for relation %u", myrelid);
relkind = ((Form_pg_class) GETSTRUCT(reltup))->relkind;
+ if (colDef->identity != ' ' && find_inheritance_children(myrelid, NoLock) != NIL)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot 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))
{
@@ -5171,6 +5221,7 @@ ATExecAddColumn(List **wqueue, AlteredTableInfo *tab, Relation rel,
attribute.attalign = tform->typalign;
attribute.attnotnull = colDef->is_not_null;
attribute.atthasdef = false;
+ attribute.attidentity = ' ';
attribute.attisdropped = false;
attribute.attislocal = colDef->is_local;
attribute.attinhcount = colDef->inhcount;
@@ -5480,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 = ' ';
cdef->location = -1;
cmd->def = (Node *) cdef;
}
@@ -5759,6 +5811,12 @@ ATExecColumnDefault(Relation rel, const char *colName,
errmsg("cannot alter system column \"%s\"",
colName)));
+ if (get_attidentity(RelationGetRelid(rel), attnum) != ' ')
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("column \"%s\" of relation \"%s\" is an identity column",
+ colName, RelationGetRelationName(rel))));
+
/*
* 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
@@ -5794,6 +5852,225 @@ 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;
+ RawColumnDefault *rawEnt;
+ ObjectAddress address;
+ ColumnDef *cdef = (ColumnDef *) def;
+
+ Assert(IsA(def, ColumnDef));
+
+ 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)));
+
+ /*
+ * MergeAttributes() disallows inheriting from table with identity column,
+ * so we have to prevent adding one later on.
+ */
+ if (find_inheritance_children(RelationGetRelid(rel), NoLock) != NIL)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot add identity to table that has child tables")));
+
+ /*
+ * Creating a column as identity implies NOT NULL, so adding the identity
+ * to an existing column that is not NOT NULL would create a state that
+ * cannot be reproduced without contortions.
+ */
+ if (!attTup->attnotnull)
+ ereport(ERROR,
+ (errmsg("column \"%s\" of relation \"%s\" must be declared NOT NULL before identity can be added",
+ colName, RelationGetRelationName(rel))));
+
+ if (attTup->attidentity != ' ')
+ ereport(ERROR,
+ (errmsg("column \"%s\" of relation \"%s\" is already an identity column",
+ colName, RelationGetRelationName(rel))));
+
+ if (attTup->atthasdef)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("column \"%s\" of relation \"%s\" already has a default value",
+ colName, RelationGetRelationName(rel))));
+
+ attTup->attidentity = cdef->identity;
+ simple_heap_update(attrelation, &tuple->t_self, tuple);
+ CatalogUpdateIndexes(attrelation, tuple);
+
+ InvokeObjectPostAlterHook(RelationRelationId,
+ RelationGetRelid(rel),
+ attTup->attnum);
+ ObjectAddressSubSet(address, RelationRelationId,
+ RelationGetRelid(rel), attnum);
+ heap_freetuple(tuple);
+
+ CommandCounterIncrement();
+
+ rawEnt = (RawColumnDefault *) palloc(sizeof(RawColumnDefault));
+ rawEnt->attnum = attnum;
+ rawEnt->raw_default = cdef->raw_default;
+
+ AddRelationNewConstraints(rel, list_make1(rawEnt), NIL,
+ false, true, false);
+
+ heap_close(attrelation, RowExclusiveLock);
+
+ return address;
+}
+
+static ObjectAddress
+ATExecSetIdentity(Relation rel, const char *colName, Node *def, LOCKMODE lockmode)
+{
+ ListCell *option;
+ DefElem *generatedEl = NULL;
+ HeapTuple tuple;
+ Form_pg_attribute attTup;
+ AttrNumber attnum;
+ Relation attrelation;
+ ObjectAddress address;
+
+ foreach(option, (List *) def)
+ {
+ DefElem *defel = (DefElem *) lfirst(option);
+
+ if (strcmp(defel->defname, "generated") == 0)
+ {
+ if (generatedEl)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("conflicting or redundant options")));
+ generatedEl = defel;
+ }
+ else
+ elog(ERROR, "option \"%s\" not recognized",
+ defel->defname);
+ }
+
+ /*
+ * Even if there is nothing to change here, we run all the checks. There
+ * will be a subsequent ALTER SEQUENCE that relies on everything being
+ * there.
+ */
+
+ attrelation = heap_open(AttributeRelationId, RowExclusiveLock);
+ tuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), colName);
+ if (!HeapTupleIsValid(tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does not exist",
+ colName, RelationGetRelationName(rel))));
+
+ attTup = (Form_pg_attribute) GETSTRUCT(tuple);
+ attnum = attTup->attnum;
+
+ if (attnum <= 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter system column \"%s\"",
+ colName)));
+
+ if (attTup->attidentity == ' ')
+ ereport(ERROR,
+ (errmsg("column \"%s\" of relation \"%s\" is not an identity column",
+ colName, RelationGetRelationName(rel))));
+
+ if (generatedEl)
+ {
+ attTup->attidentity = defGetInt32(generatedEl);
+ simple_heap_update(attrelation, &tuple->t_self, tuple);
+ CatalogUpdateIndexes(attrelation, tuple);
+
+ InvokeObjectPostAlterHook(RelationRelationId,
+ RelationGetRelid(rel),
+ attTup->attnum);
+ ObjectAddressSubSet(address, RelationRelationId,
+ RelationGetRelid(rel), attnum);
+ }
+
+ heap_freetuple(tuple);
+ heap_close(attrelation, RowExclusiveLock);
+
+ return address;
+}
+
+static ObjectAddress
+ATExecDropIdentity(Relation rel, const char *colName, LOCKMODE lockmode)
+{
+ HeapTuple tuple;
+ Form_pg_attribute attTup;
+ AttrNumber attnum;
+ Relation attrelation;
+ ObjectAddress address;
+
+ attrelation = heap_open(AttributeRelationId, RowExclusiveLock);
+ tuple = SearchSysCacheCopyAttName(RelationGetRelid(rel), colName);
+ if (!HeapTupleIsValid(tuple))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does not exist",
+ colName, RelationGetRelationName(rel))));
+
+ attTup = (Form_pg_attribute) GETSTRUCT(tuple);
+ attnum = attTup->attnum;
+
+ if (attnum <= 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter system column \"%s\"",
+ colName)));
+
+ if (attTup->attidentity == ' ')
+ ereport(ERROR,
+ (errmsg("column \"%s\" of relation \"%s\" is not an identity column",
+ colName, RelationGetRelationName(rel))));
+
+ attTup->attidentity = ' ';
+ simple_heap_update(attrelation, &tuple->t_self, tuple);
+ CatalogUpdateIndexes(attrelation, tuple);
+
+ InvokeObjectPostAlterHook(RelationRelationId,
+ RelationGetRelid(rel),
+ attTup->attnum);
+ ObjectAddressSubSet(address, RelationRelationId,
+ RelationGetRelid(rel), attnum);
+ heap_freetuple(tuple);
+
+ CommandCounterIncrement();
+
+ RemoveAttrDefault(RelationGetRelid(rel), attnum, DROP_RESTRICT, false,
+ true);
+
+ heap_close(attrelation, RowExclusiveLock);
+
+ return address;
+}
+
+/*
* ALTER TABLE ALTER COLUMN SET STATISTICS
*/
static void
@@ -8426,6 +8703,14 @@ ATPrepAlterColumnType(List **wqueue,
errmsg("cannot alter inherited column \"%s\"",
colName)));
+ /* Don't alter identity columns. (XXX This could be done, but needs more
+ * work to check permitted types, adjust sequence.) */
+ if (attTup->attidentity != ' ')
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_TABLE_DEFINITION),
+ errmsg("cannot alter data type of identity column \"%s\"",
+ colName)));
+
/* Don't alter columns used in the partition key */
if (is_partition_attr(rel, attnum, &is_expr))
{
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 6955298577..a9eb4029e1 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2623,6 +2623,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);
@@ -2645,6 +2646,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);
@@ -2753,6 +2755,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);
@@ -2782,6 +2785,7 @@ _copyInsertStmt(const InsertStmt *from)
COPY_NODE_FIELD(onConflictClause);
COPY_NODE_FIELD(returningList);
COPY_NODE_FIELD(withClause);
+ COPY_SCALAR_FIELD(override);
return newnode;
}
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 548a2aa876..6b3ae4d81d 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -932,6 +932,7 @@ _equalQuery(const Query *a, const Query *b)
COMPARE_NODE_FIELD(rtable);
COMPARE_NODE_FIELD(jointree);
COMPARE_NODE_FIELD(targetList);
+ COMPARE_SCALAR_FIELD(override);
COMPARE_NODE_FIELD(onConflict);
COMPARE_NODE_FIELD(returningList);
COMPARE_NODE_FIELD(groupClause);
@@ -959,6 +960,7 @@ _equalInsertStmt(const InsertStmt *a, const InsertStmt *b)
COMPARE_NODE_FIELD(onConflictClause);
COMPARE_NODE_FIELD(returningList);
COMPARE_NODE_FIELD(withClause);
+ COMPARE_SCALAR_FIELD(override);
return true;
}
@@ -2382,6 +2384,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);
@@ -2402,6 +2405,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);
diff --git a/src/backend/nodes/makefuncs.c b/src/backend/nodes/makefuncs.c
index c97532b348..8166bd35ea 100644
--- a/src/backend/nodes/makefuncs.c
+++ b/src/backend/nodes/makefuncs.c
@@ -497,6 +497,7 @@ makeColumnDef(const char *colname, Oid typeOid, int32 typmod, Oid collOid)
n->storage = 0;
n->raw_default = NULL;
n->cooked_default = NULL;
+ n->identity = ' ';
n->collClause = NULL;
n->collOid = collOid;
n->constraints = NIL;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 9fe98739c1..df723b41f3 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2587,6 +2587,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);
@@ -2692,6 +2693,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);
@@ -3188,6 +3190,13 @@ _outConstraint(StringInfo str, const Constraint *node)
WRITE_STRING_FIELD(cooked_expr);
break;
+ case CONSTR_IDENTITY:
+ appendStringInfoString(str, "IDENTITY");
+ WRITE_NODE_FIELD(raw_expr);
+ WRITE_STRING_FIELD(cooked_expr);
+ WRITE_CHAR_FIELD(generated_when);
+ break;
+
case CONSTR_CHECK:
appendStringInfoString(str, "CHECK");
WRITE_BOOL_FIELD(is_no_inherit);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 63f633634c..607168acfc 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -249,6 +249,7 @@ _readQuery(void)
READ_NODE_FIELD(rtable);
READ_NODE_FIELD(jointree);
READ_NODE_FIELD(targetList);
+ READ_ENUM_FIELD(override, OverridingKind);
READ_NODE_FIELD(onConflict);
READ_NODE_FIELD(returningList);
READ_NODE_FIELD(groupClause);
diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c
index a558083f43..70e49fde7b 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -464,6 +464,8 @@ transformInsertStmt(ParseState *pstate, InsertStmt *stmt)
qry->hasModifyingCTE = pstate->p_hasModifyingCTE;
}
+ qry->override = stmt->override;
+
isOnConflictUpdate = (stmt->onConflictClause &&
stmt->onConflictClause->action == ONCONFLICT_UPDATE);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 08cf5b78f5..74856fc043 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -281,6 +281,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
@@ -432,7 +434,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
@@ -548,6 +550,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
@@ -610,7 +613,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
@@ -634,7 +637,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
@@ -705,6 +708,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.
*
@@ -723,7 +727,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 '*' '/' '%'
@@ -2091,6 +2095,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
{
@@ -2528,6 +2566,38 @@ 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, "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 ')'
@@ -3178,6 +3248,7 @@ columnDef: ColId Typename create_generic_options ColQualList
n->storage = 0;
n->raw_default = NULL;
n->cooked_default = NULL;
+ n->identity = ' ';
n->collOid = InvalidOid;
n->fdwoptions = $3;
SplitColQualList($4, &n->constraints, &n->collClause,
@@ -3199,6 +3270,7 @@ columnOptions: ColId WITH OPTIONS ColQualList
n->storage = 0;
n->raw_default = NULL;
n->cooked_default = NULL;
+ n->identity = ' ';
n->collOid = InvalidOid;
SplitColQualList($4, &n->constraints, &n->collClause,
yyscanner);
@@ -3311,6 +3383,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);
@@ -3328,6 +3409,11 @@ ColConstraintElem:
}
;
+generated_when:
+ ALWAYS { $$ = CONSTR_GENERATED_ALWAYS; }
+ | BY DEFAULT { $$ = CONSTR_GENERATED_DEFAULT; }
+ ;
+
/*
* ConstraintAttr represents constraint attributes, which we parse as if
* they were independent constraint clauses, in order to avoid shift/reduce
@@ -3394,6 +3480,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; }
@@ -3903,6 +3990,10 @@ OptSeqOptList: SeqOptList { $$ = $1; }
| /*EMPTY*/ { $$ = NIL; }
;
+OptParenthesizedSeqOptList: '(' SeqOptList ')' { $$ = $2; }
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
+
SeqOptList: SeqOptElem { $$ = list_make1($1); }
| SeqOptList SeqOptElem { $$ = lappend($1, $2); }
;
@@ -3943,6 +4034,11 @@ SeqOptElem: CACHE NumericOnly
{
$$ = 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);
@@ -10070,12 +10166,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);
@@ -10084,6 +10194,11 @@ insert_rest:
}
;
+override_kind:
+ USER { $$ = OVERRIDING_USER_VALUE; }
+ | SYSTEM_P { $$ = OVERRIDING_SYSTEM_VALUE; }
+ ;
+
insert_column_list:
insert_column_item
{ $$ = list_make1($1); }
@@ -11434,6 +11549,7 @@ TableFuncElement: ColId Typename opt_collate_clause
n->storage = 0;
n->raw_default = NULL;
n->cooked_default = NULL;
+ n->identity = ' ';
n->collClause = (CollateClause *) $3;
n->collOid = InvalidOid;
n->constraints = NIL;
@@ -14091,6 +14207,7 @@ unreserved_keyword:
| FORWARD
| FUNCTION
| FUNCTIONS
+ | GENERATED
| GLOBAL
| GRANTED
| HANDLER
@@ -14159,6 +14276,7 @@ unreserved_keyword:
| OPTIONS
| ORDINALITY
| OVER
+ | OVERRIDING
| OWNED
| OWNER
| PARALLEL
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index 4f74208633..f301fa614e 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,150 @@ transformCreateStmt(CreateStmt *stmt, const char *queryString)
return result;
}
+static void
+generateSerialExtraStmts(CreateStmtContext *cxt, ColumnDef *column, 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 = (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((List *) nameEl->arg);
+ snamespace = rv->schemaname;
+ sname = rv->relname;
+ 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 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;
+}
+
+/*
+ * Create an expression tree representing the function call
+ * nextval('sequencename'). We cannot reduce the raw tree to cooked
+ * form until after the sequence is created, but there's no need to do
+ * so.
+ */
+static FuncCall *
+generateNextvalExpr(char *snamespace, char *sname)
+{
+ char *qstring;
+ A_Const *snamenode;
+ TypeCast *castnode;
+ FuncCall *funccallnode;
+
+ qstring = quote_qualified_identifier(snamespace, sname);
+ snamenode = makeNode(A_Const);
+ snamenode->val.type = T_String;
+ snamenode->val.val.str = qstring;
+ snamenode->location = -1;
+ castnode = makeNode(TypeCast);
+ castnode->typeName = SystemTypeName("regclass");
+ castnode->arg = (Node *) snamenode;
+ castnode->location = -1;
+ funccallnode = makeFuncCall(SystemFuncName("nextval"),
+ list_make1(castnode),
+ -1);
+
+ return funccallnode;
+}
+
/*
* transformColumnDefinition -
* transform a single ColumnDef within CREATE TABLE
@@ -367,7 +512,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,110 +567,21 @@ 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 = NIL;
-
- /*
- * If this is ALTER ADD COLUMN, make sure the sequence will be owned
- * by the table's owner. The current user might be someone else
- * (perhaps a superuser, or someone who's only a member of the owning
- * role), but the SEQUENCE OWNED BY mechanisms will bleat unless table
- * and sequence have exactly the same owning role.
- */
- if (cxt->rel)
- seqstmt->ownerId = 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));
+ Constraint *constraint;
- cxt->alist = lappend(cxt->alist, altseqstmt);
+ generateSerialExtraStmts(cxt, column, NIL, DEPENDENCY_AUTO, &snamespace, &sname);
/*
* Create appropriate constraints for SERIAL. We do this in full,
* rather than shortcutting, so that we will detect any conflicting
* constraints the user wrote (like a different DEFAULT).
- *
- * Create an expression tree representing the function call
- * nextval('sequencename'). We cannot reduce the raw tree to cooked
- * form until after the sequence is created, but there's no need to do
- * so.
*/
- qstring = quote_qualified_identifier(snamespace, sname);
- snamenode = makeNode(A_Const);
- snamenode->val.type = T_String;
- snamenode->val.val.str = qstring;
- snamenode->location = -1;
- castnode = makeNode(TypeCast);
- castnode->typeName = SystemTypeName("regclass");
- castnode->arg = (Node *) snamenode;
- castnode->location = -1;
- funccallnode = makeFuncCall(SystemFuncName("nextval"),
- list_make1(castnode),
- -1);
constraint = makeNode(Constraint);
constraint->contype = CONSTR_DEFAULT;
constraint->location = -1;
- constraint->raw_expr = (Node *) funccallnode;
+ constraint->raw_expr = (Node *) generateNextvalExpr(snamespace, sname);
constraint->cooked_expr = NULL;
column->constraints = lappend(column->constraints, constraint);
@@ -540,9 +596,12 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
saw_nullable = false;
saw_default = false;
+ saw_identity = false;
foreach(clist, column->constraints)
{
+ Constraint *constraint;
+
constraint = lfirst(clist);
Assert(IsA(constraint, Constraint));
@@ -582,9 +641,43 @@ transformColumnDefinition(CreateStmtContext *cxt, ColumnDef *column)
constraint->location)));
column->raw_default = constraint->raw_expr;
Assert(constraint->cooked_expr == NULL);
+ column->identity = ' ';
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, constraint->options, DEPENDENCY_INTERNAL, &snamespace, &sname);
+ column->raw_default = (Node *) generateNextvalExpr(snamespace, sname);
+ Assert(constraint->cooked_expr == NULL);
+ column->identity = constraint->generated_when;
+ saw_identity = true;
+ column->is_not_null = TRUE;
+ break;
+ }
+
case CONSTR_CHECK:
cxt->ckconstraints = lappend(cxt->ckconstraints, constraint);
break;
@@ -661,6 +754,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)));
}
/*
@@ -890,6 +991,7 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
def->storage = 0;
def->raw_default = NULL;
def->cooked_default = NULL;
+ def->identity = ' ';
def->collClause = NULL;
def->collOid = attribute->attcollation;
def->constraints = NIL;
@@ -905,7 +1007,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 == ' ' &&
(table_like_clause->options & CREATE_TABLE_LIKE_DEFAULTS))
{
Node *this_default = NULL;
@@ -933,6 +1035,30 @@ transformTableLikeClause(CreateStmtContext *cxt, TableLikeClause *table_like_cla
def->cooked_default = this_default;
}
+ /*
+ * Copy identity if requested
+ */
+ if (attribute->attidentity != ' ' &&
+ (table_like_clause->options & CREATE_TABLE_LIKE_IDENTITY))
+ {
+ List *seqlist;
+ Oid seq_relid;
+ List *seq_options;
+ char *snamespace;
+ char *sname;
+
+ /*
+ * find sequence owned by old column; extract sequence parameters;
+ * build new default and create sequence commands
+ */
+ seqlist = getOwnedSequences(RelationGetRelid(relation), attribute->attnum);
+ seq_relid = linitial_oid(seqlist);
+ seq_options = sequence_options(seq_relid);
+ generateSerialExtraStmts(cxt, def, seq_options, DEPENDENCY_INTERNAL, &snamespace, &sname);
+ def->raw_default = (Node *) generateNextvalExpr(snamespace, sname);
+ def->identity = attribute->attidentity;
+ }
+
/* Likewise, copy storage if requested */
if (table_like_clause->options & CREATE_TABLE_LIKE_STORAGE)
def->storage = attribute->attstorage;
@@ -1109,6 +1235,7 @@ transformOfType(CreateStmtContext *cxt, TypeName *ofTypename)
n->storage = 0;
n->raw_default = NULL;
n->cooked_default = NULL;
+ n->identity = ' ';
n->collClause = NULL;
n->collOid = attr->attcollation;
n->constraints = NIL;
@@ -2653,6 +2780,87 @@ transformAlterTableStmt(Oid relid, AlterTableStmt *stmt,
break;
}
+ case AT_AddIdentity:
+ {
+ Constraint *def = (Constraint *) cmd->def;
+ ColumnDef *newdef = makeNode(ColumnDef);
+ Oid typeOid;
+ char *snamespace;
+ char *sname;
+
+ Assert(IsA(def, Constraint));
+
+ 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; // XXX before below FIXME
+ generateSerialExtraStmts(&cxt, newdef, def->options, DEPENDENCY_INTERNAL, &snamespace, &sname);
+ newdef->raw_default = (Node *) generateNextvalExpr(snamespace, sname);
+ newdef->identity = def->generated_when;
+
+ cmd->def = (Node *) newdef;
+
+ 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, (List *) cmd->def)
+ {
+ DefElem *def = (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 did not own sequences, we
+ * just let the ALTER TABLE command error out. */
+
+ cmd->def = (Node *) newdef;
+ newcmds = lappend(newcmds, cmd);
+ break;
+ }
+
case AT_AttachPartition:
{
PartitionCmd *partcmd = (PartitionCmd *) cmd->def;
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index bf4f098c15..5c9f3005e0 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -61,6 +61,7 @@ static Query *rewriteRuleAction(Query *parsetree,
static List *adjustJoinTreeList(Query *parsetree, bool removert, int rt_index);
static List *rewriteTargetListIU(List *targetList,
CmdType commandType,
+ OverridingKind override,
Relation target_relation,
int result_rti,
List **attrno_list);
@@ -705,6 +706,7 @@ adjustJoinTreeList(Query *parsetree, bool removert, int rt_index)
static List *
rewriteTargetListIU(List *targetList,
CmdType commandType,
+ OverridingKind override,
Relation target_relation,
int result_rti,
List **attrno_list)
@@ -785,6 +787,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];
@@ -797,8 +800,37 @@ rewriteTargetListIU(List *targetList,
* it's an INSERT and there's no tlist entry for the column, or the
* tlist entry is a DEFAULT placeholder node.
*/
- if ((new_tle == 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 == CONSTR_GENERATED_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 == CONSTR_GENERATED_DEFAULT && override == OVERRIDING_USER_VALUE)
+ apply_default = true;
+ }
+
+ if (commandType == CMD_UPDATE)
+ {
+ if (att_tup->attidentity == CONSTR_GENERATED_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;
@@ -3233,6 +3265,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);
@@ -3245,6 +3278,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
parsetree->targetList =
rewriteTargetListIU(parsetree->targetList,
parsetree->commandType,
+ parsetree->override,
rt_entry_relation,
parsetree->resultRelation, NULL);
}
@@ -3255,6 +3289,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
parsetree->onConflict->onConflictSet =
rewriteTargetListIU(parsetree->onConflict->onConflictSet,
CMD_UPDATE,
+ parsetree->override,
rt_entry_relation,
parsetree->resultRelation,
NULL);
@@ -3264,7 +3299,9 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
{
parsetree->targetList =
rewriteTargetListIU(parsetree->targetList,
- parsetree->commandType, rt_entry_relation,
+ parsetree->commandType,
+ parsetree->override,
+ rt_entry_relation,
parsetree->resultRelation, NULL);
rewriteTargetListUD(parsetree, rt_entry, rt_entry_relation);
}
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 4e2ba19d1b..13db529927 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -5790,6 +5790,14 @@ get_insert_query_def(Query *query, deparse_context *context)
if (query->targetList)
appendStringInfoString(buf, ") ");
+ if (query->override)
+ {
+ if (query->override == OVERRIDING_SYSTEM_VALUE)
+ appendStringInfoString(buf, "OVERRIDING SYSTEM VALUE ");
+ else if (query->override == OVERRIDING_USER_VALUE)
+ appendStringInfoString(buf, "OVERRIDING USER VALUE ");
+ }
+
if (select_rte)
{
/* Add the SELECT */
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index 13ae6add03..2d7cdaad8d 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.c
index 2a6835991c..7a0c30442d 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -3259,6 +3259,7 @@ RelationBuildLocalRelation(const char *relname,
has_not_null = false;
for (i = 0; i < natts; i++)
{
+ rel->rd_att->attrs[i]->attidentity = tupDesc->attrs[i]->attidentity;
rel->rd_att->attrs[i]->attnotnull = tupDesc->attrs[i]->attnotnull;
has_not_null |= tupDesc->attrs[i]->attnotnull;
}
diff --git a/src/backend/utils/errcodes.txt b/src/backend/utils/errcodes.txt
index e7bdb925ac..2c5915beb0 100644
--- a/src/backend/utils/errcodes.txt
+++ b/src/backend/utils/errcodes.txt
@@ -326,6 +326,7 @@ Section: Class 42 - Syntax Error or Access Rule Violation
42P21 E ERRCODE_COLLATION_MISMATCH collation_mismatch
42P22 E ERRCODE_INDETERMINATE_COLLATION indeterminate_collation
42809 E ERRCODE_WRONG_OBJECT_TYPE wrong_object_type
+428C9 E ERRCODE_GENERATED_ALWAYS generated_always
# Note: for ERRCODE purposes, we divide namable objects into these categories:
# databases, schemas, prepared statements, cursors, tables, columns,
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index e5545b31d4..6f02a214ca 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1885,6 +1885,9 @@ dumpTableData_insert(Archive *fout, void *dcontext)
appendPQExpBufferStr(insertStmt, ") ");
}
+ if (tbinfo->needs_override)
+ appendPQExpBufferStr(insertStmt, "OVERRIDING SYSTEM VALUE ");
+
appendPQExpBufferStr(insertStmt, "VALUES (");
}
}
@@ -4945,6 +4948,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 */
@@ -5021,6 +5025,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, "
+ "EXISTS (SELECT 1 FROM pg_depend WHERE classid = 'pg_class'::regclass AND objid = c.oid AND objsubid = 0 AND refclassid = 'pg_attrdef'::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 "
@@ -5472,6 +5477,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)
@@ -5572,6 +5578,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.
@@ -7125,6 +7133,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;
@@ -7167,7 +7176,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.
@@ -7266,6 +7302,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");
@@ -7281,6 +7318,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));
@@ -7305,6 +7343,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] == 'a');
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));
@@ -7350,6 +7390,9 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
{
int adnum;
+ if (tbinfo->attidentity[j] != ' ')
+ continue;
+
adnum = atoi(PQgetvalue(res, j, 2));
if (adnum <= 0 || adnum > ntups)
@@ -15502,10 +15545,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);
@@ -15517,9 +15563,27 @@ 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] == 'a')
+ appendPQExpBuffer(query, "ALWAYS");
+ else if (owning_tab->attidentity[tbinfo->owning_col - 1] == 'd')
+ appendPQExpBuffer(query, "BY DEFAULT");
+ appendPQExpBuffer(query, " AS IDENTITY (\n SEQUENCE NAME %s\n",
+ fmtId(tbinfo->dobj.name));
+ }
+ else
+ appendPQExpBuffer(query,
+ "CREATE SEQUENCE %s\n",
+ fmtId(tbinfo->dobj.name));
if (fout->remoteVersion >= 80400)
appendPQExpBuffer(query, " START WITH %s\n", startv);
@@ -15540,7 +15604,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));
@@ -15573,7 +15640,7 @@ dumpSequence(Archive *fout, TableInfo *tbinfo)
* We need not schema-qualify the table reference because both sequence
* and table must be in the same schema.
*/
- if (OidIsValid(tbinfo->owning_tab))
+ if (OidIsValid(tbinfo->owning_tab) && !tbinfo->is_identity_sequence)
{
TableInfo *owning_tab = findTableByOid(tbinfo->owning_tab);
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 395a9f3288..315a2fa7a4 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -284,6 +284,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 */
@@ -302,6 +303,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 */
@@ -313,6 +315,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.c
index a582a37953..2f058bddeb 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -1582,6 +1582,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 == 'i')
appendPQExpBufferStr(&buf, ",\n pg_catalog.pg_get_indexdef(a.attrelid, a.attnum, TRUE) AS indexdef");
else
@@ -1756,12 +1760,24 @@ describeOneTableDetails(const char *schemaname,
/* Collation, Nullable, Default */
if (show_column_details)
{
+ char *identity;
+ char *default_str = "";
+
printTableAddCell(&cont, PQgetvalue(res, i, 5), false, false);
printTableAddCell(&cont, strcmp(PQgetvalue(res, i, 3), "t") == 0 ? "not null" : "", false, false);
- /* (note: above we cut off the 'default' string at 128) */
- printTableAddCell(&cont, PQgetvalue(res, i, 2), false, false);
+ identity = PQgetvalue(res, i, 6);
+
+ if (identity[0] == ' ')
+ /* (note: above we cut off the 'default' string at 128) */
+ default_str = PQgetvalue(res, i, 2);
+ else if (identity[0] == 'a')
+ default_str = "generated always as identity";
+ else if (identity[0] == 'd')
+ default_str = "generated by default as identity";
+
+ printTableAddCell(&cont, default_str, false, false);
}
/* Value: for sequences only */
@@ -1770,16 +1786,16 @@ describeOneTableDetails(const char *schemaname,
/* Expression for index column */
if (tableinfo.relkind == 'i')
- 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 == 'f' && pset.sversion >= 90200)
- printTableAddCell(&cont, PQgetvalue(res, i, 7), false, false);
+ printTableAddCell(&cont, PQgetvalue(res, i, 8), false, false);
/* Storage and Description */
if (verbose)
{
- int firstvcol = 8;
+ int firstvcol = 9;
char *storage = PQgetvalue(res, i, firstvcol);
/* these strings are literal in our syntax, so not translated. */
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 02c8d60c29..4aaa9add9a 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -1807,7 +1807,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"))
@@ -1823,7 +1823,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"))
@@ -2769,17 +2769,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/catversion.h b/src/include/catalog/catversion.h
index 45596abe76..24d9652f9c 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 201612231
+#define CATALOG_VERSION_NO 201612235
#endif
diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h
index e8a302f2fd..44f7e21f71 100644
--- a/src/include/catalog/dependency.h
+++ b/src/include/catalog/dependency.h
@@ -238,7 +238,7 @@ extern bool sequenceIsOwned(Oid seqId, Oid *tableId, int32 *colId);
extern void markSequenceUnowned(Oid seqId);
-extern List *getOwnedSequences(Oid relid);
+extern List *getOwnedSequences(Oid relid, AttrNumber attnum);
extern Oid get_constraint_index(Oid constraintId);
diff --git a/src/include/catalog/pg_attribute.h b/src/include/catalog/pg_attribute.h
index 39d8eedb4e..6406b07599 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;
+ /* 'a', 'd', or ' ' */
+ 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
/* ----------------
diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h
index a61b7a2917..f4a9488483 100644
--- a/src/include/catalog/pg_class.h
+++ b/src/include/catalog/pg_class.h
@@ -149,7 +149,7 @@ typedef FormData_pg_class *Form_pg_class;
*/
DATA(insert OID = 1247 ( pg_type PGNSP 71 0 PGUID 0 0 0 0 0 0 0 f f p r 30 0 t f f f f f f t n f 3 1 _null_ _null_ _null_));
DESCR("");
-DATA(insert OID = 1249 ( pg_attribute PGNSP 75 0 PGUID 0 0 0 0 0 0 0 f f p r 21 0 f f f f f f f t n f 3 1 _null_ _null_ _null_));
+DATA(insert OID = 1249 ( pg_attribute PGNSP 75 0 PGUID 0 0 0 0 0 0 0 f f p r 22 0 f f f f f f f t n f 3 1 _null_ _null_ _null_));
DESCR("");
DATA(insert OID = 1255 ( pg_proc PGNSP 81 0 PGUID 0 0 0 0 0 0 0 f f p r 29 0 t f f f f f f t n f 3 1 _null_ _null_ _null_));
DESCR("");
diff --git a/src/include/commands/sequence.h b/src/include/commands/sequence.h
index 1fd75b216d..b58ab10c0f 100644
--- a/src/include/commands/sequence.h
+++ b/src/include/commands/sequence.h
@@ -58,6 +58,7 @@ extern Datum setval_oid(PG_FUNCTION_ARGS);
extern Datum setval3_oid(PG_FUNCTION_ARGS);
extern Datum lastval(PG_FUNCTION_ARGS);
+extern List *sequence_options(Oid relid);
extern Datum pg_sequence_parameters(PG_FUNCTION_ARGS);
extern Datum pg_sequence_last_value(PG_FUNCTION_ARGS);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 9d8ef775e4..39e32189a8 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -25,6 +25,13 @@
#include "nodes/primnodes.h"
#include "nodes/value.h"
+typedef enum OverridingKind
+{
+ OVERRIDING_NOT_SET = 0,
+ OVERRIDING_USER_VALUE,
+ OVERRIDING_SYSTEM_VALUE
+} OverridingKind;
+
/* Possible sources of a Query */
typedef enum QuerySource
{
@@ -131,6 +138,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) */
@@ -596,6 +605,7 @@ typedef struct ColumnDef
char storage; /* attstorage setting, or 0 for default */
Node *raw_default; /* default value (untransformed parse tree) */
Node *cooked_default; /* default value (transformed expr tree) */
+ char identity; /* attidentity setting */
CollateClause *collClause; /* untransformed COLLATE spec, if any */
Oid collOid; /* collation OID (InvalidOid if not set) */
List *constraints; /* other constraints on column */
@@ -617,9 +627,10 @@ typedef enum TableLikeOption
{
CREATE_TABLE_LIKE_DEFAULTS = 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;
@@ -1328,6 +1339,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;
/* ----------------------
@@ -1631,7 +1643,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
@@ -1904,6 +1919,7 @@ typedef enum ConstrType /* types of constraints */
* expect it */
CONSTR_NOTNULL,
CONSTR_DEFAULT,
+ CONSTR_IDENTITY,
CONSTR_CHECK,
CONSTR_PRIMARY,
CONSTR_UNIQUE,
@@ -1915,6 +1931,9 @@ typedef enum ConstrType /* types of constraints */
CONSTR_ATTR_IMMEDIATE
} ConstrType;
+#define CONSTR_GENERATED_ALWAYS 'a'
+#define CONSTR_GENERATED_DEFAULT 'd'
+
/* Foreign key action codes */
#define FKCONSTR_ACTION_NOACTION 'a'
#define FKCONSTR_ACTION_RESTRICT 'r'
@@ -1942,6 +1961,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/parser/kwlist.h b/src/include/parser/kwlist.h
index 581ff6eedb..d522e89efe 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -173,6 +173,7 @@ PG_KEYWORD("from", FROM, RESERVED_KEYWORD)
PG_KEYWORD("full", FULL, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("function", FUNCTION, UNRESERVED_KEYWORD)
PG_KEYWORD("functions", FUNCTIONS, UNRESERVED_KEYWORD)
+PG_KEYWORD("generated", GENERATED, UNRESERVED_KEYWORD)
PG_KEYWORD("global", GLOBAL, UNRESERVED_KEYWORD)
PG_KEYWORD("grant", GRANT, RESERVED_KEYWORD)
PG_KEYWORD("granted", GRANTED, UNRESERVED_KEYWORD)
@@ -285,6 +286,7 @@ PG_KEYWORD("outer", OUTER_P, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("over", OVER, UNRESERVED_KEYWORD)
PG_KEYWORD("overlaps", OVERLAPS, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("overlay", OVERLAY, COL_NAME_KEYWORD)
+PG_KEYWORD("overriding", OVERRIDING, UNRESERVED_KEYWORD)
PG_KEYWORD("owned", OWNED, UNRESERVED_KEYWORD)
PG_KEYWORD("owner", OWNER, UNRESERVED_KEYWORD)
PG_KEYWORD("parallel", PARALLEL, UNRESERVED_KEYWORD)
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index dcb89807e9..573533b389 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -64,6 +64,7 @@ extern Oid get_opfamily_proc(Oid opfamily, Oid lefttype, Oid righttype,
extern char *get_attname(Oid relid, AttrNumber attnum);
extern char *get_relid_attribute_name(Oid relid, AttrNumber attnum);
extern AttrNumber get_attnum(Oid relid, const char *attname);
+extern char get_attidentity(Oid relid, AttrNumber attnum);
extern Oid get_atttype(Oid relid, AttrNumber attnum);
extern int32 get_atttypmod(Oid relid, AttrNumber attnum);
extern void get_atttypetypmodcoll(Oid relid, AttrNumber attnum,
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index a25b221703..3f405c94ce 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -66,6 +66,53 @@ SELECT * FROM inhg; /* Two records with three columns in order x=x, xx=text, y=y
(2 rows)
DROP TABLE inhg;
+CREATE TABLE test_like_id_1 (a int GENERATED ALWAYS AS IDENTITY, b text);
+\d test_like_id_1
+ Table "public.test_like_id_1"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+------------------------------
+ a | integer | | not null | generated always as identity
+ b | text | | |
+
+INSERT INTO test_like_id_1 (b) VALUES ('b1');
+SELECT * FROM test_like_id_1;
+ a | b
+---+----
+ 1 | b1
+(1 row)
+
+CREATE TABLE test_like_id_2 (LIKE test_like_id_1);
+\d test_like_id_2
+ Table "public.test_like_id_2"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+---------
+ a | integer | | not null |
+ b | text | | |
+
+INSERT INTO test_like_id_2 (b) VALUES ('b2');
+ERROR: null value in column "a" violates not-null constraint
+DETAIL: Failing row contains (null, b2).
+SELECT * FROM test_like_id_2; -- identity was not copied
+ a | b
+---+---
+(0 rows)
+
+CREATE TABLE test_like_id_3 (LIKE test_like_id_1 INCLUDING IDENTITY);
+\d test_like_id_3
+ Table "public.test_like_id_3"
+ Column | Type | Collation | Nullable | Default
+--------+---------+-----------+----------+------------------------------
+ a | integer | | not null | generated always as identity
+ b | text | | |
+
+INSERT INTO test_like_id_3 (b) VALUES ('b3');
+SELECT * FROM test_like_id_3; -- identity was copied and applied
+ a | b
+---+----
+ 1 | b3
+(1 row)
+
+DROP TABLE test_like_id_1, test_like_id_2, test_like_id_3;
CREATE TABLE inhg (x text, LIKE inhx INCLUDING INDEXES, y text); /* copies indexes */
INSERT INTO inhg VALUES (5, 10);
INSERT INTO inhg VALUES (20, 10); -- should fail
diff --git a/src/test/regress/expected/identity.out b/src/test/regress/expected/identity.out
new file mode 100644
index 0000000000..4b09a34e3a
--- /dev/null
+++ b/src/test/regress/expected/identity.out
@@ -0,0 +1,274 @@
+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 | 9223372036854775807 | 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 | 9223372036854775807 | 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 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 tests
+-- fail, not allowed for identity columns
+ALTER TABLE itest1 ALTER COLUMN a SET DEFAULT 1;
+ERROR: column "a" of relation "itest1" is an identity column
+-- fail, not allowed, already has a default
+CREATE TABLE itest5 (a serial, b text);
+ALTER TABLE itest5 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;
+ERROR: column "a" of relation "itest5" already has a default value
+-- not allowed for now
+ALTER TABLE itest3 ALTER COLUMN a TYPE text;
+ERROR: cannot alter data type of identity column "a"
+-- ALTER COLUMN
+CREATE TABLE itest6 (a int GENERATED ALWAYS AS IDENTITY, b text);
+INSERT INTO itest6 DEFAULT VALUES;
+ALTER TABLE itest6 ALTER COLUMN a SET GENERATED BY DEFAULT SET INCREMENT 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
+-- inheritance
+CREATE TABLE itest7 (a int GENERATED ALWAYS AS IDENTITY);
+INSERT INTO itest7 DEFAULT VALUES;
+SELECT * FROM itest7;
+ a
+---
+ 1
+(1 row)
+
+-- inherit identity column not allowed
+CREATE TABLE itest7a (b text) INHERITS (itest7);
+ERROR: cannot inherit from table with identity column
+-- 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)
+
+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
+ itest7b | a | YES | NO |
+ itest7c | a | NO | YES | ALWAYS
+(3 rows)
+
+CREATE TABLE itest7d (a int);
+CREATE TABLE itest7e () INHERITS (itest7d);
+ALTER TABLE itest7d ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- fail
+ERROR: cannot add identity to table that has child tables
+ALTER TABLE itest7d ADD COLUMN b int GENERATED ALWAYS AS IDENTITY; -- fail
+ERROR: cannot add identity column to table that has child tables
diff --git a/src/test/regress/expected/truncate.out b/src/test/regress/expected/truncate.out
index 5c5277e0f1..c76a21788f 100644
--- a/src/test/regress/expected/truncate.out
+++ b/src/test/regress/expected/truncate.out
@@ -393,6 +393,36 @@ SELECT * FROM truncate_a;
2 | 34
(2 rows)
+CREATE TABLE truncate_b (id int GENERATED ALWAYS AS IDENTITY (START WITH 44));
+INSERT INTO truncate_b DEFAULT VALUES;
+INSERT INTO truncate_b DEFAULT VALUES;
+SELECT * FROM truncate_b;
+ id
+----
+ 44
+ 45
+(2 rows)
+
+TRUNCATE truncate_b;
+INSERT INTO truncate_b DEFAULT VALUES;
+INSERT INTO truncate_b DEFAULT VALUES;
+SELECT * FROM truncate_b;
+ id
+----
+ 46
+ 47
+(2 rows)
+
+TRUNCATE truncate_b RESTART IDENTITY;
+INSERT INTO truncate_b DEFAULT VALUES;
+INSERT INTO truncate_b DEFAULT VALUES;
+SELECT * FROM truncate_b;
+ id
+----
+ 44
+ 45
+(2 rows)
+
-- check rollback of a RESTART IDENTITY operation
BEGIN;
TRUNCATE truncate_a RESTART IDENTITY;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 8641769351..9ff4bd4357 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -108,6 +108,11 @@ test: select_views portals_p2 foreign_key cluster dependency guc bitmapops combo
# ----------
test: plancache limit plpgsql copy2 temp domain rangefuncs prepare without_oid conversion truncate alter_table sequence polymorphism rowtypes returning largeobject with xml
+# ----------
+# Another group of parallel tests
+# ----------
+test: identity
+
# event triggers cannot run concurrently with any test that runs DDL
test: event_trigger
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 835cf3556c..1f022aef94 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -161,6 +161,7 @@ test: conversion
test: truncate
test: alter_table
test: sequence
+test: identity
test: polymorphism
test: rowtypes
test: returning
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index 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.sql
new file mode 100644
index 0000000000..9bced8dc4c
--- /dev/null
+++ b/src/test/regress/sql/identity.sql
@@ -0,0 +1,162 @@
+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 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 tests
+
+-- fail, not allowed for identity columns
+ALTER TABLE itest1 ALTER COLUMN a SET DEFAULT 1;
+
+-- fail, not allowed, already has a default
+CREATE TABLE itest5 (a serial, b text);
+ALTER TABLE itest5 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;
+
+-- not allowed for now
+ALTER TABLE itest3 ALTER COLUMN a TYPE text;
+
+
+-- ALTER COLUMN
+
+CREATE TABLE itest6 (a int GENERATED ALWAYS AS IDENTITY, b text);
+INSERT INTO itest6 DEFAULT VALUES;
+
+ALTER TABLE itest6 ALTER COLUMN a SET GENERATED BY DEFAULT SET INCREMENT 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
+
+
+-- inheritance
+
+CREATE TABLE itest7 (a int GENERATED ALWAYS AS IDENTITY);
+INSERT INTO itest7 DEFAULT VALUES;
+SELECT * FROM itest7;
+
+-- inherit identity column not allowed
+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;
+
+SELECT table_name, column_name, is_nullable, is_identity, identity_generation FROM information_schema.columns WHERE table_name LIKE 'itest7%' ORDER BY 1, 2;
+
+CREATE TABLE itest7d (a int);
+CREATE TABLE itest7e () INHERITS (itest7d);
+ALTER TABLE itest7d ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- fail
+ALTER TABLE itest7d ADD COLUMN b int GENERATED ALWAYS AS IDENTITY; -- fail
diff --git a/src/test/regress/sql/truncate.sql b/src/test/regress/sql/truncate.sql
index a3d6f5368f..1aab021dbb 100644
--- a/src/test/regress/sql/truncate.sql
+++ b/src/test/regress/sql/truncate.sql
@@ -202,6 +202,24 @@ CREATE TABLE truncate_a (id serial,
INSERT INTO truncate_a DEFAULT VALUES;
SELECT * FROM truncate_a;
+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.11.0
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
