Here is another attempt to implement identity columns. This is a
standard-conforming variant of PostgreSQL's serial columns. It also
fixes a few usability issues that serial columns have:
- need to set permissions on sequence in addition to table (*)
- 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
(*) Not actually implemented yet, because I wanted to make use of the
NEXT VALUE FOR stuff I had previously posted, but I have more work to do
there.
There have been previous attempts at this between 2003 and 2007. The
latest effort failed because it tried to implement identity columns and
generated columns in one go, but then discovered that they have wildly
different semantics. But AFAICT, there weren't any fundamental issues
with the identity parts of the patch.
Here some interesting threads of old:
https://www.postgresql.org/message-id/flat/xzp1xw2x5jo.fsf%40dwp.des.no#xzp1xw2x5jo....@dwp.des.no
https://www.postgresql.org/message-id/flat/1146360362.839.104.camel%40home#1146360362.839.104.camel@home
https://www.postgresql.org/message-id/23436.1149629297%40sss.pgh.pa.us
https://www.postgresql.org/message-id/flat/448C9B7A.6010000%40dunaweb.hu#448c9b7a.6010...@dunaweb.hu
https://www.postgresql.org/message-id/flat/45DACD1E.2000207%40dunaweb.hu#45dacd1e.2000...@dunaweb.hu
https://www.postgresql.org/message-id/flat/18812.1178572575%40sss.pgh.pa.us
Some comments on the implementation, and where it differs from previous
patches:
- The new attidentity column stores whether a column is an identity
column and when it is generated (always/by default). I kept this
independent from atthasdef mainly for he benefit of existing (client)
code querying those catalogs, but I kept confusing myself by this, so
I'm not sure about that choice. Basically we need to store four
distinct states (nothing, default, identity always, identity by default)
somehow.
- The way attidentity is initialized in some places is a bit hackish. I
haven't focused on that so much without having a clear resolution to the
previous question.
- One previous patch managed to make GENERATED an unreserved key word.
I have it reserved right now, but perhaps with a bit more trying it can
be unreserved.
- I did not implement the restriction of one identity column per table.
That didn't seem necessary.
- I did not implement an override clause on COPY. If COPY supplies a
value, it is always used.
- pg_dump is as always a mess. Some of that is because of the way
pg_upgrade works: It only gives out one OID at a time, so you need to
create the table and sequences in separate entries.
--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 4e09e06..0879d12 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1095,6 +1095,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 c43e325..8ece439 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 6f51cbc..969ce45 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -42,6 +42,8 @@
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> 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> [, ... ] )
@@ -170,6 +172,15 @@ <title>Description</title>
</varlistentry>
<varlistentry>
+ <term><literal>ADD GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY</literal>/<literal>DROP IDENTITY</literal></term>
+ <listitem>
+ <para>
+ These forms change whether a column is an identity column.
+ </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 bf2ad64..408fdca 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -49,6 +49,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 ]
@@ -68,7 +69,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><replaceable class="PARAMETER">index_parameters</replaceable> in <literal>UNIQUE</literal>, <literal>PRIMARY KEY</literal>, and <literal>EXCLUDE</literal> constraints are:</phrase>
@@ -338,6 +339,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.
@@ -369,7 +376,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
@@ -484,6 +491,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. This is similar to a default value TODO.
+ </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>
@@ -1116,7 +1152,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
@@ -1176,7 +1212,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>
diff --git a/doc/src/sgml/ref/insert.sgml b/doc/src/sgml/ref/insert.sgml
index 06f4160..78107c6 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,6 +203,37 @@ <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 continue the
+ sequence counters for any identity columns.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>DEFAULT VALUES</literal></term>
<listitem>
<para>
diff --git a/src/backend/access/common/tupdesc.c b/src/backend/access/common/tupdesc.c
index b56d0e3..8a8244e 100644
--- a/src/backend/access/common/tupdesc.c
+++ b/src/backend/access/common/tupdesc.c
@@ -149,6 +149,7 @@ CreateTupleDescCopy(TupleDesc tupdesc)
memcpy(desc->attrs[i], tupdesc->attrs[i], ATTRIBUTE_FIXED_PART_SIZE);
desc->attrs[i]->attnotnull = 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 26d1652..f0d79c3 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 e997b57..9654164 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -138,37 +138,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
};
/*
@@ -180,7 +180,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};
@@ -624,6 +624,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 b0b43cf..b2ec40c 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 00550eb..88a4577 100644
--- a/src/backend/catalog/information_schema.sql
+++ b/src/backend/catalog/information_schema.sql
@@ -727,8 +727,8 @@ 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(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(null AS character_data) AS identity_start,
CAST(null AS character_data) AS identity_increment,
CAST(null AS character_data) AS identity_maximum,
diff --git a/src/backend/catalog/pg_depend.c b/src/backend/catalog/pg_depend.c
index 7a0713e..0823b88 100644
--- a/src/backend/catalog/pg_depend.c
+++ b/src/backend/catalog/pg_depend.c
@@ -19,6 +19,7 @@
#include "access/htup_details.h"
#include "catalog/dependency.h"
#include "catalog/indexing.h"
+#include "catalog/pg_attrdef.h"
#include "catalog/pg_constraint.h"
#include "catalog/pg_depend.h"
#include "catalog/pg_extension.h"
@@ -554,17 +555,20 @@ markSequenceUnowned(Oid seqId)
{
deleteDependencyRecordsForClass(RelationRelationId, seqId,
RelationRelationId, DEPENDENCY_AUTO);
+ deleteDependencyRecordsForClass(RelationRelationId, seqId,
+ AttrDefaultRelationId, DEPENDENCY_INTERNAL);
}
/*
- * 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/commands/sequence.c b/src/backend/commands/sequence.c
index c98f981..810ae3b 100644
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -22,8 +22,10 @@
#include "access/xloginsert.h"
#include "access/xlogutils.h"
#include "catalog/dependency.h"
+#include "catalog/indexing.h"
#include "catalog/namespace.h"
#include "catalog/objectaccess.h"
+#include "catalog/pg_attrdef.h"
#include "catalog/pg_type.h"
#include "commands/defrem.h"
#include "commands/sequence.h"
@@ -36,6 +38,7 @@
#include "storage/smgr.h"
#include "utils/acl.h"
#include "utils/builtins.h"
+#include "utils/fmgroids.h"
#include "utils/lsyscache.h"
#include "utils/resowner.h"
#include "utils/syscache.h"
@@ -95,9 +98,9 @@ static void init_sequence(Oid relid, SeqTable *p_elm, Relation *p_rel);
static Form_pg_sequence read_seq_tuple(SeqTable elm, Relation rel,
Buffer *buf, HeapTuple seqtuple);
static void init_params(List *options, bool isInit,
- Form_pg_sequence new, List **owned_by);
+ Form_pg_sequence new, List **owned_by, DependencyType *deptype);
static void do_setval(Oid relid, int64 next, bool iscalled);
-static void process_owned_by(Relation seqrel, List *owned_by);
+static void process_owned_by(Relation seqrel, List *owned_by, DependencyType deptype);
/*
@@ -109,6 +112,7 @@ DefineSequence(CreateSeqStmt *seq)
{
FormData_pg_sequence new;
List *owned_by;
+ DependencyType deptype;
CreateStmt *stmt = makeNode(CreateStmt);
Oid seqoid;
ObjectAddress address;
@@ -145,7 +149,7 @@ DefineSequence(CreateSeqStmt *seq)
}
/* Check and set all option values */
- init_params(seq->options, true, &new, &owned_by);
+ init_params(seq->options, true, &new, &owned_by, &deptype);
/*
* Create relation (and fill value[] and null[] for the tuple)
@@ -247,7 +251,7 @@ DefineSequence(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);
@@ -414,6 +418,7 @@ AlterSequence(AlterSeqStmt *stmt)
Form_pg_sequence seq;
FormData_pg_sequence new;
List *owned_by;
+ DependencyType deptype;
ObjectAddress address;
/* Open and lock sequence. */
@@ -440,7 +445,7 @@ AlterSequence(AlterSeqStmt *stmt)
memcpy(&new, seq, sizeof(FormData_pg_sequence));
/* Check and set new values */
- init_params(stmt->options, false, &new, &owned_by);
+ init_params(stmt->options, false, &new, &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 */
@@ -483,7 +488,7 @@ AlterSequence(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);
@@ -1164,7 +1169,7 @@ read_seq_tuple(SeqTable elm, Relation rel, Buffer *buf, HeapTuple seqtuple)
*/
static void
init_params(List *options, bool isInit,
- Form_pg_sequence new, List **owned_by)
+ Form_pg_sequence new, List **owned_by, DependencyType *deptype)
{
DefElem *start_value = NULL;
DefElem *restart_value = NULL;
@@ -1173,9 +1178,11 @@ init_params(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)
{
@@ -1245,6 +1252,15 @@ init_params(List *options, bool isInit,
errmsg("conflicting or redundant options")));
*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);
@@ -1423,6 +1439,38 @@ init_params(List *options, bool isInit,
new->cache_value = 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
*
@@ -1432,7 +1480,7 @@ init_params(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;
@@ -1510,6 +1558,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 */
@@ -1519,6 +1581,35 @@ 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)
+{
+ SeqTable elm;
+ Relation seqrel;
+ Buffer buf;
+ HeapTupleData seqtuple;
+ Form_pg_sequence seq;
+ List *options = NIL;
+
+ init_sequence(relid, &elm, &seqrel);
+ seq = read_seq_tuple(elm, seqrel, &buf, &seqtuple);
+
+ options = lappend(options, makeDefElem("cache", (Node *) makeInteger(seq->cache_value)));
+ options = lappend(options, makeDefElem("cycle", (Node *) makeInteger(seq->is_cycled)));
+ options = lappend(options, makeDefElem("increment", (Node *) makeInteger(seq->increment_by)));
+ options = lappend(options, makeDefElem("maxvalue", (Node *) makeInteger(seq->max_value)));
+ options = lappend(options, makeDefElem("minvalue", (Node *) makeInteger(seq->min_value)));
+ options = lappend(options, makeDefElem("start", (Node *) makeInteger(seq->start_value)));
+
+ UnlockReleaseBuffer(buf);
+ relation_close(seqrel, NoLock);
+
+ return options;
+}
+
+/*
* Return sequence parameters, for use by information schema
*/
Datum
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 86e9814..0c8e78a 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -344,6 +344,9 @@ 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 ATExecDropIdentity(Relation rel, const char *colName, LOCKMODE lockmode);
static void ATPrepSetStatistics(Relation rel, const char *colName,
Node *newValue, LOCKMODE lockmode);
static ObjectAddress ATExecSetStatistics(Relation rel, const char *colName,
@@ -650,6 +653,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;
}
/*
@@ -1113,7 +1119,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)
@@ -2941,6 +2947,8 @@ AlterTableGetLockLevel(List *cmds)
case AT_DisableRowSecurity:
case AT_ForceRowSecurity:
case AT_NoForceRowSecurity:
+ case AT_AddIdentity:
+ case AT_DropIdentity:
cmd_lockmode = AccessExclusiveLock;
break;
@@ -3154,6 +3162,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
@@ -3476,6 +3486,12 @@ 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_DropIdentity:
+ address = ATExecDropIdentity(rel, cmd->name, lockmode);
+ break;
case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */
address = ATExecDropNotNull(rel, cmd->name, lockmode);
break;
@@ -5405,6 +5421,125 @@ 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)));
+
+ if (attTup->attidentity != ' ')
+ ereport(ERROR,
+ (errmsg("column \"%s\" of relation \"%s\" is already an identity column",
+ 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
+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
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 1877fb4..bc0a1a2 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -2624,6 +2624,7 @@ _copyColumnDef(const ColumnDef *from)
COPY_SCALAR_FIELD(storage);
COPY_NODE_FIELD(raw_default);
COPY_NODE_FIELD(cooked_default);
+ COPY_SCALAR_FIELD(identity);
COPY_NODE_FIELD(collClause);
COPY_SCALAR_FIELD(collOid);
COPY_NODE_FIELD(constraints);
@@ -2646,6 +2647,7 @@ _copyConstraint(const Constraint *from)
COPY_SCALAR_FIELD(is_no_inherit);
COPY_NODE_FIELD(raw_expr);
COPY_STRING_FIELD(cooked_expr);
+ COPY_SCALAR_FIELD(generated_when);
COPY_NODE_FIELD(keys);
COPY_NODE_FIELD(exclusions);
COPY_NODE_FIELD(options);
@@ -2740,6 +2742,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);
@@ -2769,6 +2772,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 448e1a9..8719bdd 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -931,6 +931,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);
@@ -958,6 +959,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;
}
@@ -2376,6 +2378,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);
@@ -2396,6 +2399,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 d72a85e..e32b258 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 = 0;
n->collClause = NULL;
n->collOid = collOid;
n->constraints = NIL;
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 29b7712..a82a2ff 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2588,6 +2588,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);
@@ -3186,6 +3188,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 6f9a81e..c12d254 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -248,6 +248,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 eac86cc..26f9722 100644
--- a/src/backend/parser/analyze.c
+++ b/src/backend/parser/analyze.c
@@ -463,6 +463,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 cb5cfc4..5fcdf78 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -424,7 +424,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
@@ -541,6 +541,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
/*
* Non-keyword token types. These are hard-wired into the "flex" lexer.
@@ -591,7 +592,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
@@ -615,7 +616,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
NULLS_P NUMERIC
OBJECT_P OF OFF OFFSET OIDS ON ONLY OPERATOR OPTION OPTIONS OR
- ORDER ORDINALITY OUT_P OUTER_P OVER OVERLAPS OVERLAY OWNED OWNER
+ ORDER ORDINALITY OUT_P OUTER_P OVER OVERLAPS OVERLAY OVERRIDING OWNED OWNER
PARALLEL PARSER PARTIAL PARTITION PASSING PASSWORD PLACING PLANS POLICY
POSITION PRECEDING PRECISION PRESERVE PREPARE PREPARED PRIMARY
@@ -2026,6 +2027,31 @@ 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> 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
{
@@ -3097,6 +3123,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);
@@ -3114,6 +3149,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
@@ -3180,6 +3220,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; }
@@ -3630,6 +3671,10 @@ OptSeqOptList: SeqOptList { $$ = $1; }
| /*EMPTY*/ { $$ = NIL; }
;
+OptParenthesizedSeqOptList: '(' SeqOptList ')' { $$ = $2; }
+ | /*EMPTY*/ { $$ = NIL; }
+ ;
+
SeqOptList: SeqOptElem { $$ = list_make1($1); }
| SeqOptList SeqOptElem { $$ = lappend($1, $2); }
;
@@ -9655,12 +9700,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);
@@ -9669,6 +9728,11 @@ insert_rest:
}
;
+override_kind:
+ USER { $$ = OVERRIDING_USER_VALUE; }
+ | SYSTEM_P { $$ = OVERRIDING_SYSTEM_VALUE; }
+ ;
+
insert_column_list:
insert_column_item
{ $$ = list_make1($1); }
@@ -13802,6 +13866,7 @@ unreserved_keyword:
| OPTIONS
| ORDINALITY
| OVER
+ | OVERRIDING
| OWNED
| OWNER
| PARALLEL
@@ -14060,6 +14125,7 @@ reserved_keyword:
| FOR
| FOREIGN
| FROM
+ | GENERATED
| GRANT
| GROUP_P
| HAVING
diff --git a/src/backend/parser/parse_utilcmd.c b/src/backend/parser/parse_utilcmd.c
index e98fad0..34de410 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"
@@ -342,6 +343,120 @@ 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)
+{
+ Oid snamespaceid;
+ char *snamespace;
+ char *sname;
+ 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 = 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),
+ makeDefElem("deptype",
+ (Node *) makeInteger(deptype)));
+
+ 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
@@ -353,7 +468,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);
@@ -408,110 +523,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)));
+ Constraint *constraint;
- /*
- * 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));
-
- 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);
@@ -526,9 +552,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));
@@ -568,9 +597,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;
@@ -629,6 +692,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)));
}
/*
@@ -876,6 +947,29 @@ 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);
+ }
+
/* Likewise, copy storage if requested */
if (table_like_clause->options & CREATE_TABLE_LIKE_STORAGE)
def->storage = attribute->attstorage;
@@ -2605,6 +2699,33 @@ 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;
+ }
+
default:
newcmds = lappend(newcmds, cmd);
break;
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index a22a11e..a9652f4 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);
@@ -696,6 +697,7 @@ adjustJoinTreeList(Query *parsetree, bool removert, int rt_index)
static List *
rewriteTargetListIU(List *targetList,
CmdType commandType,
+ OverridingKind override,
Relation target_relation,
int result_rti,
List **attrno_list)
@@ -776,6 +778,7 @@ rewriteTargetListIU(List *targetList,
for (attrno = 1; attrno <= numattrs; attrno++)
{
TargetEntry *new_tle = new_tles[attrno - 1];
+ bool apply_default;
att_tup = target_relation->rd_att->attrs[attrno - 1];
@@ -788,8 +791,37 @@ rewriteTargetListIU(List *targetList,
* it's an INSERT and there's no tlist entry for the column, or the
* tlist entry is a DEFAULT placeholder node.
*/
- if ((new_tle == 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;
@@ -3214,6 +3246,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);
@@ -3226,6 +3259,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
parsetree->targetList =
rewriteTargetListIU(parsetree->targetList,
parsetree->commandType,
+ parsetree->override,
rt_entry_relation,
parsetree->resultRelation, NULL);
}
@@ -3236,6 +3270,7 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
parsetree->onConflict->onConflictSet =
rewriteTargetListIU(parsetree->onConflict->onConflictSet,
CMD_UPDATE,
+ parsetree->override,
rt_entry_relation,
parsetree->resultRelation,
NULL);
@@ -3245,7 +3280,9 @@ RewriteQuery(Query *parsetree, List *rewrite_events)
{
parsetree->targetList =
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 8a81d7a..fb3aa78 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -5605,6 +5605,14 @@ get_insert_query_def(Query *query, deparse_context *context)
if (query->targetList)
appendStringInfoString(buf, ") ");
+ 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/relcache.c b/src/backend/utils/cache/relcache.c
index 8d2ad01..78c31d1 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -2937,6 +2937,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 be924d5..fbda6c4 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 a5c2d09..1c3ff12 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -1883,6 +1883,9 @@ dumpTableData_insert(Archive *fout, void *dcontext)
appendPQExpBufferStr(insertStmt, ") ");
}
+ if (tbinfo->needs_override)
+ appendPQExpBufferStr(insertStmt, "OVERRIDING SYSTEM VALUE ");
+
appendPQExpBufferStr(insertStmt, "VALUES (");
}
}
@@ -5194,6 +5197,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 */
@@ -5270,6 +5274,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 "
@@ -5867,6 +5872,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 && fout->remoteVersion >= 70300)
@@ -5968,6 +5974,8 @@ getTables(Archive *fout, int *numTables)
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.
@@ -7651,6 +7659,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;
@@ -7696,7 +7705,34 @@ getTableAttrs(Archive *fout, TableInfo *tblinfo, int numTables)
resetPQExpBuffer(q);
- if (fout->remoteVersion >= 90200)
+ if (fout->remoteVersion >= 100000)
+ {
+ /*
+ * attidenity 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.
@@ -7838,6 +7874,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");
@@ -7853,6 +7890,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));
@@ -7877,6 +7915,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));
@@ -7952,6 +7992,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)
@@ -16404,10 +16447,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);
@@ -16419,9 +16465,26 @@ 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");
+ }
+ else
+ appendPQExpBuffer(query,
+ "CREATE SEQUENCE %s\n",
+ fmtId(tbinfo->dobj.name));
if (fout->remoteVersion >= 80400)
appendPQExpBuffer(query, " START WITH %s\n", startv);
@@ -16442,7 +16505,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));
@@ -16475,7 +16541,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 2bfa2d9..f23d178 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -283,6 +283,7 @@ typedef struct _tableInfo
/* these two are set only if table is a sequence owned by a column: */
Oid owning_tab; /* OID of table owning sequence */
int owning_col; /* attr # of column owning sequence */
+ bool is_identity_sequence;
int relpages; /* table's size in pages (from pg_class) */
bool interesting; /* true if need to collect more data */
@@ -300,6 +301,7 @@ typedef struct _tableInfo
char *attstorage; /* attribute storage scheme */
char *typstorage; /* type storage scheme */
bool *attisdropped; /* true if attr is dropped; don't dump it */
+ char *attidentity;
int *attlen; /* attribute length, used by binary_upgrade */
char *attalign; /* attribute align, used by binary_upgrade */
bool *attislocal; /* true if attr has local definition */
@@ -310,6 +312,7 @@ typedef struct _tableInfo
bool *inhNotNull; /* true if NOT NULL is inherited */
struct _attrDefInfo **attrdefs; /* DEFAULT expressions */
struct _constraintInfo *checkexprs; /* CHECK constraints */
+ bool needs_override; /* has GENERATED ALWAYS AS IDENTITY */
/*
* Stuff computed only for dumpable tables.
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 6275a68..7dc97eb 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -140,7 +140,7 @@ describeAccessMethods(const char *pattern, bool verbose)
printQueryOpt myopt = pset.popt;
static const bool translate_columns[] = {false, true, false, false};
- if (pset.sversion < 90600)
+ if (pset.sversion < 100000)
{
char sverbuf[32];
@@ -1547,6 +1547,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 >= 90600)
+ 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
@@ -1709,9 +1713,11 @@ describeOneTableDetails(const char *schemaname,
/* Type */
printTableAddCell(&cont, PQgetvalue(res, i, 1), false, false);
- /* Modifiers: collate, not null, default */
+ /* Modifiers: collate, not null, default, identity */
if (show_modifiers)
{
+ char *identity;
+
resetPQExpBuffer(&tmpbuf);
if (!PQgetisnull(res, i, 5))
@@ -1740,6 +1746,17 @@ describeOneTableDetails(const char *schemaname,
PQgetvalue(res, i, 2));
}
+ identity = PQgetvalue(res, i, 6);
+ if (strlen(identity) != 0 && (identity[0] == 'a' || identity[0] == 'd'))
+ {
+ if (tmpbuf.len > 0)
+ appendPQExpBufferChar(&tmpbuf, ' ');
+ if (identity[0] == 'a')
+ appendPQExpBuffer(&tmpbuf, " generated always as identity");
+ else if (identity[0] == 'd')
+ appendPQExpBuffer(&tmpbuf, " generated by default as identity");
+ }
+
modifiers[i] = pg_strdup(tmpbuf.data);
printTableAddCell(&cont, modifiers[i], false, false);
}
@@ -1750,16 +1767,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/include/catalog/catversion.h b/src/include/catalog/catversion.h
index c04edad..e881764 100644
--- a/src/include/catalog/catversion.h
+++ b/src/include/catalog/catversion.h
@@ -53,6 +53,6 @@
*/
/* yyyymmddN */
-#define CATALOG_VERSION_NO 201608231
+#define CATALOG_VERSION_NO 201608262
#endif
diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h
index 09b36c5..96b519b 100644
--- a/src/include/catalog/dependency.h
+++ b/src/include/catalog/dependency.h
@@ -236,7 +236,7 @@ extern bool sequenceIsOwned(Oid seqId, Oid *tableId, 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 39d8eed..6406b07 100644
--- a/src/include/catalog/pg_attribute.h
+++ b/src/include/catalog/pg_attribute.h
@@ -133,6 +133,9 @@ CATALOG(pg_attribute,1249) BKI_BOOTSTRAP BKI_WITHOUT_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 e57b81c..0c9167d 100644
--- a/src/include/catalog/pg_class.h
+++ b/src/include/catalog/pg_class.h
@@ -145,7 +145,7 @@ typedef FormData_pg_class *Form_pg_class;
*/
DATA(insert OID = 1247 ( pg_type PGNSP 71 0 PGUID 0 0 0 0 0 0 0 f f p r 30 0 t f f f f f f t n 3 1 _null_ _null_ ));
DESCR("");
-DATA(insert OID = 1249 ( pg_attribute PGNSP 75 0 PGUID 0 0 0 0 0 0 0 f f p r 21 0 f f f f f f f t n 3 1 _null_ _null_ ));
+DATA(insert OID = 1249 ( pg_attribute PGNSP 75 0 PGUID 0 0 0 0 0 0 0 f f p r 22 0 f f f f f f f t n 3 1 _null_ _null_ ));
DESCR("");
DATA(insert OID = 1255 ( pg_proc PGNSP 81 0 PGUID 0 0 0 0 0 0 0 f f p r 29 0 t f f f f f f t n 3 1 _null_ _null_ ));
DESCR("");
diff --git a/src/include/commands/sequence.h b/src/include/commands/sequence.h
index 6af60d8..a85982d 100644
--- a/src/include/commands/sequence.h
+++ b/src/include/commands/sequence.h
@@ -71,6 +71,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 ObjectAddress DefineSequence(CreateSeqStmt *stmt);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 1481fff..4af485f 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
{
@@ -130,6 +137,8 @@ typedef struct Query
List *targetList; /* target list (of TargetEntry) */
+ OverridingKind override; /* OVERRIDING clause */
+
OnConflictExpr *onConflict; /* ON CONFLICT DO [NOTHING | UPDATE] */
List *returningList; /* return-values list (of TargetEntry) */
@@ -595,6 +604,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 */
@@ -616,9 +626,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;
@@ -1224,6 +1235,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;
/* ----------------------
@@ -1525,7 +1537,9 @@ typedef enum AlterTableType
AT_DisableRowSecurity, /* DISABLE ROW SECURITY */
AT_ForceRowSecurity, /* FORCE ROW SECURITY */
AT_NoForceRowSecurity, /* NO FORCE ROW SECURITY */
- AT_GenericOptions /* OPTIONS (...) */
+ AT_GenericOptions, /* OPTIONS (...) */
+ AT_AddIdentity,
+ AT_DropIdentity
} AlterTableType;
typedef struct ReplicaIdentityStmt
@@ -1796,6 +1810,7 @@ typedef enum ConstrType /* types of constraints */
* expect it */
CONSTR_NOTNULL,
CONSTR_DEFAULT,
+ CONSTR_IDENTITY,
CONSTR_CHECK,
CONSTR_PRIMARY,
CONSTR_UNIQUE,
@@ -1807,6 +1822,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'
@@ -1834,6 +1852,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 17ffef5..c35a77f 100644
--- a/src/include/parser/kwlist.h
+++ b/src/include/parser/kwlist.h
@@ -171,6 +171,7 @@ PG_KEYWORD("from", FROM, RESERVED_KEYWORD)
PG_KEYWORD("full", FULL, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("function", FUNCTION, UNRESERVED_KEYWORD)
PG_KEYWORD("functions", FUNCTIONS, UNRESERVED_KEYWORD)
+PG_KEYWORD("generated", GENERATED, RESERVED_KEYWORD)
PG_KEYWORD("global", GLOBAL, UNRESERVED_KEYWORD)
PG_KEYWORD("grant", GRANT, RESERVED_KEYWORD)
PG_KEYWORD("granted", GRANTED, UNRESERVED_KEYWORD)
@@ -281,6 +282,7 @@ PG_KEYWORD("outer", OUTER_P, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("over", OVER, UNRESERVED_KEYWORD)
PG_KEYWORD("overlaps", OVERLAPS, TYPE_FUNC_NAME_KEYWORD)
PG_KEYWORD("overlay", OVERLAY, COL_NAME_KEYWORD)
+PG_KEYWORD("overriding", OVERRIDING, UNRESERVED_KEYWORD)
PG_KEYWORD("owned", OWNED, UNRESERVED_KEYWORD)
PG_KEYWORD("owner", OWNER, UNRESERVED_KEYWORD)
PG_KEYWORD("parallel", PARALLEL, UNRESERVED_KEYWORD)
diff --git a/src/test/regress/expected/create_table_like.out b/src/test/regress/expected/create_table_like.out
index 97edde1..869df7a 100644
--- a/src/test/regress/expected/create_table_like.out
+++ b/src/test/regress/expected/create_table_like.out
@@ -66,6 +66,32 @@ 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);
+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);
+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);
+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 0000000..1aed7c2
--- /dev/null
+++ b/src/test/regress/expected/identity.out
@@ -0,0 +1,177 @@
+CREATE TABLE itest1 (a int generated by default as identity, b text);
+CREATE TABLE itest2 (a bigint generated always as identity, b text);
+CREATE TABLE itest3 (a smallint generated by default as identity (start 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
+CREATE TABLE itest4 (a int, b text);
+ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;
+ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error
+ERROR: column "a" of relation "itest4" is already an identity column
+ALTER TABLE itest4 ALTER COLUMN b ADD GENERATED ALWAYS AS IDENTITY; -- error
+ERROR: identity column type must be smallint, integer, or bigint
+-- invalid column type
+CREATE TABLE itest_err_1 (a text generated by default as identity);
+ERROR: identity column type must be smallint, integer, or bigint
+-- duplicate identity
+CREATE TABLE itest_err_2 (a int generated always as identity generated by default as identity);
+ERROR: multiple identity specifications for column "a" of table "itest_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;
+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)
+
diff --git a/src/test/regress/expected/truncate.out b/src/test/regress/expected/truncate.out
index 5c5277e..c76a217 100644
--- a/src/test/regress/expected/truncate.out
+++ b/src/test/regress/expected/truncate.out
@@ -393,6 +393,36 @@ SELECT * FROM truncate_a;
2 | 34
(2 rows)
+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 1cb5dfc..d36e2b0 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 8958d8c..be5a76d 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -160,6 +160,7 @@ test: conversion
test: truncate
test: alter_table
test: sequence
+test: identity
test: polymorphism
test: rowtypes
test: returning
diff --git a/src/test/regress/sql/create_table_like.sql b/src/test/regress/sql/create_table_like.sql
index 6dded1f..0cb994f 100644
--- a/src/test/regress/sql/create_table_like.sql
+++ b/src/test/regress/sql/create_table_like.sql
@@ -37,6 +37,17 @@ 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);
+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);
+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);
+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 0000000..3fdd504
--- /dev/null
+++ b/src/test/regress/sql/identity.sql
@@ -0,0 +1,99 @@
+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
+
+CREATE TABLE itest4 (a int, b text);
+ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY;
+ALTER TABLE itest4 ALTER COLUMN a ADD GENERATED ALWAYS AS IDENTITY; -- error
+ALTER TABLE itest4 ALTER COLUMN b ADD GENERATED ALWAYS AS IDENTITY; -- error
+
+-- 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;
+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;
diff --git a/src/test/regress/sql/truncate.sql b/src/test/regress/sql/truncate.sql
index a3d6f53..1aab021 100644
--- a/src/test/regress/sql/truncate.sql
+++ b/src/test/regress/sql/truncate.sql
@@ -202,6 +202,24 @@ CREATE TABLE truncate_a (id serial,
INSERT INTO truncate_a DEFAULT VALUES;
SELECT * FROM truncate_a;
+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;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
