diff -dcrpN pgsql.orig/doc/src/sgml/catalogs.sgml pgsql/doc/src/sgml/catalogs.sgml
*** pgsql.orig/doc/src/sgml/catalogs.sgml 2007-04-09 11:35:39.000000000 +0200
--- pgsql/doc/src/sgml/catalogs.sgml 2007-04-13 20:24:48.000000000 +0200
***************
*** 744,749 ****
--- 744,767 ----
+ adidentity
+ bool
+
+
+ This column is an IDENTITY column
+
+
+
+
+ adgenerated
+ bool
+
+
+ This column is defined as GENERATED ALWAYS
+
+
+
+
adbin
text
diff -dcrpN pgsql.orig/doc/src/sgml/datatype.sgml pgsql/doc/src/sgml/datatype.sgml
*** pgsql.orig/doc/src/sgml/datatype.sgml 2007-04-09 11:35:40.000000000 +0200
--- pgsql/doc/src/sgml/datatype.sgml 2007-04-13 20:24:48.000000000 +0200
*************** ALTER SEQUENCE
+ Identity Columns
+
+
+ identity
+
+
+
+ sequence
+ and identity column
+
+
+
+ Identity columns are similar to the
+ pseudo data types serial and
+ bigserial but also have different properties.
+
+
+
+ Identity is not a type, but a type
+ modifier. As such, an identity column isn't
+ limited to numeric types. Every type that has an implicit
+ cast from int8 can be used as an
+ identity column.
+
+
+ CREATE TABLE tablename (
+ colname coltype
+ GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
+ );
+
+
+ In the above example coltype can be e.g. float,
+ decimal or text.
+
+
+
+ Sequence generation options can also be specified at table creation:
+
+
+ CREATE TABLE tablename (
+ colname coltype
+ GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [( sequence_options )]
+ );
+
+
+ The optional sequence_options
+ are the same sequence options that can be used to
+ CREATE or ALTER a sequence.
+
+
+
+ For an identity column, either
+ GENERATED ALWAYS or
+ GENERATED BY DEFAULT can be specified.
+ The behaviour for GENERATED BY DEFAULT
+ is similar to the serial pseudo-type, e.g.
+ the sequence next value generation can be suppressed
+ at INSERT by providing an explicit value.
+ With GENERATED ALWAYS, the sequence
+ generation cannot be suppressed, the explicitly provided
+ value is ignored.
+
+
+
+ There can be only one identity column
+ in a table at any time.
+
+
+
+ The identity column in PostgreSQL conforms
+ to the SQL:2003 standard.
+
+
+
+
+ Generated Columns
+
+
+ generated
+
+
+
+ Generated is not a type, but a type
+ modifier. Generated columns are columns
+ with enforced DEFAULT values.
+ This kind of columns can be used to keep a "static" value
+ in the table whose value may depend on other columns of
+ the same row. The expression is not limited to constants
+ as with the DEFAULT clause, instead it may use the same
+ range of expressions as the CHECK clause.
+
+
+ CREATE TABLE tablename (
+ colname coltype
+ GENERATED ALWAYS AS ( expression )
+ );
+
+
+
+
+ The value of the generated column is
+ computed after all other (regular) columns are assigned
+ with their values but before the identity
+ column's next serial value is computed. A generated
+ column cannot reference other generated columns.
+
+
+
+ The generated column in PostgreSQL conforms
+ to the SQL:2003 standard.
+
+
diff -dcrpN pgsql.orig/doc/src/sgml/ref/alter_table.sgml pgsql/doc/src/sgml/ref/alter_table.sgml
*** pgsql.orig/doc/src/sgml/ref/alter_table.sgml 2007-03-23 12:16:52.000000000 +0100
--- pgsql/doc/src/sgml/ref/alter_table.sgml 2007-04-15 00:27:40.000000000 +0200
*************** where act
*** 36,41 ****
--- 36,44 ----
ALTER [ COLUMN ] column TYPE type [ USING expression ]
ALTER [ COLUMN ] column SET DEFAULT expression
ALTER [ COLUMN ] column DROP DEFAULT
+ ALTER [ COLUMN ] column SET IDENTITY GENERATED { ALWAYS | BY DEFAULT }
+ ALTER [ COLUMN ] column DROP IDENTITY
+ ALTER [ COLUMN ] column SET GENERATED ALWAYS AS ( expression )
ALTER [ COLUMN ] column { SET | DROP } NOT NULL
ALTER [ COLUMN ] column SET STATISTICS integer
ALTER [ COLUMN ] column SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
*************** where act
*** 119,124 ****
--- 122,163 ----
Defaults can also be created for views, in which case they are
inserted into INSERT statements on the view before
the view's ON INSERT rule is applied.
+ DROP DEFAULT is valid for IDENTITY
+ and GENERATED columns, in which case these
+ columns lose their IDENTITY/GENERATED
+ state together with their DEFAULT expression.
+
+
+
+
+
+ SET IDENTITY GENERATED { ALWAYS | BY DEFAULT }
+
+
+ This form "upgrades" a column that has an associated
+ sequence to an IDENTITY column. See also
+ ALTER SEQUENCE ... OWNED BY.
+ The command also restores the column's DEFAULT
+ expression, i.e. NEXTVAL('tablename_colname_seq')
+
+
+
+
+
+ SET GENERATED ALWAYS AS ( expression )
+
+
+ This form modifies a column to be a GENERATED column.
+
+
+
+
+
+ SET/DROP IDENTITY
+
+
+ This form downgrades an IDENTITY to a regular column.
+ The column still keeps the OWNED sequence and its DEFAULT.
*************** where act
*** 385,390 ****
--- 424,436 ----
(or an index, sequence, or view) or the name of an individual column in
a table. There is no effect on the stored data.
+
+
+ If the renamed column is SERIAL or it's an
+ identity column, the underlying sequence is
+ also renamed so it always tries to keep the
+ tablename_columnname_seq form.
+
diff -dcrpN pgsql.orig/doc/src/sgml/ref/copy.sgml pgsql/doc/src/sgml/ref/copy.sgml
*** pgsql.orig/doc/src/sgml/ref/copy.sgml 2007-02-09 07:23:11.000000000 +0100
--- pgsql/doc/src/sgml/ref/copy.sgml 2007-04-13 20:24:48.000000000 +0200
*************** PostgreSQL documentation
*** 22,27 ****
--- 22,28 ----
COPY tablename [ ( column [, ...] ) ]
+ [ OVERRIDING { SYSTEM | USER } VALUE ]
FROM { 'filename' | STDIN }
[ [ WITH ]
[ BINARY ]
*************** COPY { ta
*** 116,121 ****
--- 117,134 ----
+ OVERRIDING { SYSTEM | USER } VALUE
+
+
+ Specifies that values override enforced default values
+ for GENERATED ALWAYS columns. Only effective for
+ COPY tablename FROM
+ Only allowed for the owner of the table.
+
+
+
+
+
filename
diff -dcrpN pgsql.orig/doc/src/sgml/ref/create_table.sgml pgsql/doc/src/sgml/ref/create_table.sgml
*** pgsql.orig/doc/src/sgml/ref/create_table.sgml 2007-02-09 07:23:12.000000000 +0100
--- pgsql/doc/src/sgml/ref/create_table.sgml 2007-04-13 20:31:43.000000000 +0200
*************** PostgreSQL documentation
*** 21,27 ****
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( [
! { column_name data_type [ DEFAULT default_expr ] [ column_constraint [ ... ] ]
| table_constraint
| LIKE parent_table [ { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS } ] ... }
[, ... ]
--- 21,29 ----
CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } ] TABLE table_name ( [
! { column_name data_type
! | [ DEFAULT default_expr | GENERATED ALWAYS AS (default_expr ) | GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] ]
! | [ column_constraint [ ... ] ]
| table_constraint
| LIKE parent_table [ { INCLUDING | EXCLUDING } { DEFAULTS | CONSTRAINTS } ] ... }
[, ... ]
*************** and table
*** 186,191 ****
--- 188,234 ----
+ GENERATED ALWAYS AS (
+ default_expr )
+
+
+ This creates a GENERATED column. It's similar
+ to the DEFAULT clause, with the following
+ exceptions. In an INSERT operation the default value
+ will be enforced even when an expression is specified.
+ This can be overridden by specifying OVERRIDING SYSTEM VALUE.
+ In an UPDATE statement, only
+ SET colname = DEFAULT
+ is allowed for such columns.
+
+
+
+
+
+ GENERATED { ALWAYS BY DEFAULT } AS IDENTITY [ (
+ sequence_options ) ]
+
+
+ This clause creates an identity column, whose default value
+ will be generated by an automatically created sequence.
+ For the SERIAL pseudo-type this is implicit.
+ However, any type that has implicit cast from bigint
+ can be specified for the column.
+
+
+
+ With GENERATED ALWAYS AS IDENTITY, the default value
+ will always be enforced in an INSERT operation.
+ OVERRIDING SYSTEM VALUE works the same just as with GENERATED columns.
+ With this feature, one can approximate the autoincrementer type
+ that other DBMSs provide, e.g.:
+ INSERT INTO table (id, ... ) VALUES (0, ...);
+ will insert a new column with the next sequence value instead of 0.
+
+
+
+
+
INHERITS ( parent_table [, ... ] )
diff -dcrpN pgsql.orig/doc/src/sgml/ref/insert.sgml pgsql/doc/src/sgml/ref/insert.sgml
*** pgsql.orig/doc/src/sgml/ref/insert.sgml 2007-02-09 07:23:12.000000000 +0100
--- pgsql/doc/src/sgml/ref/insert.sgml 2007-04-13 20:33:24.000000000 +0200
*************** PostgreSQL documentation
*** 21,26 ****
--- 21,27 ----
INSERT INTO table [ ( column [, ...] ) ]
+ [ OVERRIDING { SYSTEM | USER } VALUE ]
{ DEFAULT VALUES | VALUES ( { expression | DEFAULT } [, ...] ) [, ...] | query }
[ RETURNING * | output_expression [ AS output_name ] [, ...] ]
*************** INSERT INTO attnum),
! RelationGetRelationName(rel),
! get_attname(RelationGetRelid(rel), colDef->attnum))));
! }
! }
! if (numolddefval > 0)
! {
! AttrDefault *defval = oldconstr->defval;
! int ndef = numolddefval;
! while (--ndef >= 0)
! {
! if (defval[ndef].adgenerated &&
! !defval[ndef].adidentity)
! {
! Form_pg_attribute atp_ref = rel->rd_att->attrs[defval[ndef].adnum - 1];
! var = scanRTEForColumn(pstate,
! rte,
! NameStr(atp_ref->attname),
! -1);
! if (contain_var_reference(expr, varno, ((Var *)var)->varattno, sublevels_up))
! ereport(ERROR,
! (errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
! errmsg("GENERATED column \"%s\" of table \"%s\" cannot be referenced by GENERATED column \"%s\"",
! NameStr(atp_ref->attname),
! RelationGetRelationName(rel),
! get_attname(RelationGetRelid(rel), colDef->attnum))));
! }
! }
! }
! }
!
! StoreAttrDefault(rel, colDef->attnum, nodeToString(expr), colDef->is_identity, colDef->is_generated);
cooked = (CookedConstraint *) palloc(sizeof(CookedConstraint));
cooked->contype = CONSTR_DEFAULT;
cooked->name = NULL;
cooked->attnum = colDef->attnum;
cooked->expr = expr;
+ cooked->is_identity = colDef->is_identity;
+ cooked->is_generated = colDef->is_generated;
cookedConstraints = lappend(cookedConstraints, cooked);
}
*************** AddRelationRawConstraints(Relation rel,
*** 1722,1727 ****
--- 1795,1802 ----
cooked->name = ccname;
cooked->attnum = 0;
cooked->expr = expr;
+ cooked->is_identity = false;
+ cooked->is_generated = false;
cookedConstraints = lappend(cookedConstraints, cooked);
}
*************** cookDefault(ParseState *pstate,
*** 1799,1805 ****
Node *raw_default,
Oid atttypid,
int32 atttypmod,
! char *attname)
{
Node *expr;
--- 1874,1881 ----
Node *raw_default,
Oid atttypid,
int32 atttypmod,
! char *attname,
! bool is_generated)
{
Node *expr;
*************** cookDefault(ParseState *pstate,
*** 1813,1819 ****
/*
* Make sure default expr does not refer to any vars.
*/
! if (contain_var_clause(expr))
ereport(ERROR,
(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
errmsg("cannot use column references in default expression")));
--- 1889,1895 ----
/*
* Make sure default expr does not refer to any vars.
*/
! if (!is_generated && contain_var_clause(expr))
ereport(ERROR,
(errcode(ERRCODE_INVALID_COLUMN_REFERENCE),
errmsg("cannot use column references in default expression")));
diff -dcrpN pgsql.orig/src/backend/commands/copy.c pgsql/src/backend/commands/copy.c
*** pgsql.orig/src/backend/commands/copy.c 2007-03-30 09:33:54.000000000 +0200
--- pgsql/src/backend/commands/copy.c 2007-04-14 22:53:52.000000000 +0200
*************** typedef struct CopyStateData
*** 103,108 ****
--- 103,109 ----
QueryDesc *queryDesc; /* executable query to copy from */
List *attnumlist; /* integer list of attnums to copy */
char *filename; /* filename, or NULL for STDIN/STDOUT */
+ bool overriding; /* OVERRIDING SYSTEM VALUE */
bool binary; /* binary format? */
bool oids; /* include OIDs? */
bool csv_mode; /* Comma Separated Value format? */
*************** DoCopy(const CopyStmt *stmt, const char
*** 1124,1129 ****
--- 1125,1131 ----
cstate->copy_dest = COPY_FILE; /* default */
cstate->filename = stmt->filename;
+ cstate->overriding = stmt->overriding; /* OVERRIDING SYSTEM VALUE */
if (is_from)
CopyFrom(cstate); /* copy from file to database */
*************** CopyFrom(CopyState cstate)
*** 1626,1631 ****
--- 1628,1634 ----
Oid in_func_oid;
Datum *values;
char *nulls;
+ char *replace;
int nfields;
char **field_strings;
bool done = false;
*************** CopyFrom(CopyState cstate)
*** 1635,1640 ****
--- 1638,1645 ----
TupleTableSlot *slot;
bool file_has_oids;
int *defmap;
+ bool *idmap;
+ bool *genmap;
ExprState **defexprs; /* array of default att expressions */
ExprContext *econtext; /* used for ExecEvalExpr for default atts */
MemoryContext oldcontext = CurrentMemoryContext;
*************** CopyFrom(CopyState cstate)
*** 1642,1647 ****
--- 1647,1653 ----
CommandId mycid = GetCurrentCommandId();
bool use_wal = true; /* by default, use WAL logging */
bool use_fsm = true; /* by default, use FSM for free space */
+ bool hasgenerated;
Assert(cstate->rel);
*************** CopyFrom(CopyState cstate)
*** 1765,1770 ****
--- 1771,1777 ----
slot = MakeSingleTupleTableSlot(tupDesc);
econtext = GetPerTupleExprContext(estate);
+ econtext->ecxt_scantuple = slot;
/*
* Pick up the required catalog information for each attribute in the
*************** CopyFrom(CopyState cstate)
*** 1775,1784 ****
--- 1782,1797 ----
in_functions = (FmgrInfo *) palloc(num_phys_attrs * sizeof(FmgrInfo));
typioparams = (Oid *) palloc(num_phys_attrs * sizeof(Oid));
defmap = (int *) palloc(num_phys_attrs * sizeof(int));
+ idmap = (bool *) palloc(num_phys_attrs * sizeof(bool));
+ genmap = (bool *) palloc(num_phys_attrs * sizeof(bool));
defexprs = (ExprState **) palloc(num_phys_attrs * sizeof(ExprState *));
+ hasgenerated = false;
for (attnum = 1; attnum <= num_phys_attrs; attnum++)
{
+ bool adidentity;
+ bool adgenerated;
+
/* We don't need info for dropped attributes */
if (attr[attnum - 1]->attisdropped)
continue;
*************** CopyFrom(CopyState cstate)
*** 1793,1799 ****
fmgr_info(in_func_oid, &in_functions[attnum - 1]);
/* Get default info if needed */
! if (!list_member_int(cstate->attnumlist, attnum))
{
/* attribute is NOT to be copied from input */
/* use default value if one exists */
--- 1806,1813 ----
fmgr_info(in_func_oid, &in_functions[attnum - 1]);
/* Get default info if needed */
! column_default_properties(cstate->rel, attnum, &adidentity, &adgenerated);
! if (!list_member_int(cstate->attnumlist, attnum) || adidentity || adgenerated)
{
/* attribute is NOT to be copied from input */
/* use default value if one exists */
*************** CopyFrom(CopyState cstate)
*** 1804,1809 ****
--- 1818,1826 ----
defexprs[num_defaults] = ExecPrepareExpr((Expr *) defexpr,
estate);
defmap[num_defaults] = attnum - 1;
+ idmap[num_defaults] = adidentity;
+ genmap[num_defaults] = adgenerated;
+ hasgenerated |= adgenerated && !adidentity;
num_defaults++;
}
}
*************** CopyFrom(CopyState cstate)
*** 1870,1875 ****
--- 1887,1893 ----
values = (Datum *) palloc(num_phys_attrs * sizeof(Datum));
nulls = (char *) palloc(num_phys_attrs * sizeof(char));
+ replace = (char *) palloc(num_phys_attrs * sizeof(char));
/* create workspace for CopyReadAttributes results */
nfields = file_has_oids ? (attr_count + 1) : attr_count;
*************** CopyFrom(CopyState cstate)
*** 2060,2069 ****
/*
* Now compute and insert any defaults available for the columns not
* provided by the input data. Anything not processed here or above
! * will remain NULL.
*/
for (i = 0; i < num_defaults; i++)
{
values[defmap[i]] = ExecEvalExpr(defexprs[i], econtext,
&isnull, NULL);
if (!isnull)
--- 2078,2100 ----
/*
* Now compute and insert any defaults available for the columns not
* provided by the input data. Anything not processed here or above
! * will remain NULL. Skip GENERATED fields until the next phase.
! * Skip SERIAL/GENERATED BY DEFAULT AS IDENTITY if value was given.
! * Skip the GENERATED ALWAYS AS IDENTITY field if the stream contains
! * non-NULL value AND we got OVERRIDING SYSTEM VALUE, i.e. accept
! * the provided value.
*/
for (i = 0; i < num_defaults; i++)
{
+ if (genmap[i] && !idmap[i])
+ continue;
+ if (idmap[i] && nulls[defmap[i]] == ' ')
+ {
+ if (!genmap[i])
+ continue;
+ if (genmap[i] && cstate->overriding)
+ continue;
+ }
values[defmap[i]] = ExecEvalExpr(defexprs[i], econtext,
&isnull, NULL);
if (!isnull)
*************** CopyFrom(CopyState cstate)
*** 2073,2078 ****
--- 2104,2135 ----
/* And now we can form the input tuple. */
tuple = heap_formtuple(tupDesc, values, nulls);
+ /*
+ * Generate DEFAULTs for GENERATED columns after all regular columns.
+ */
+ if (hasgenerated)
+ {
+ ExecStoreTuple(tuple, slot, InvalidBuffer, false);
+ MemSet(replace, ' ', num_phys_attrs * sizeof(char));
+ for (i = 0; i < num_defaults; i++)
+ {
+ if (genmap[i] && !idmap[i] &&
+ (!cstate->overriding || nulls[defmap[i]] == 'n'))
+ {
+ values[defmap[i]] = ExecEvalExpr(defexprs[i], econtext,
+ &isnull, NULL);
+ if (!isnull)
+ nulls[defmap[i]] = ' ';
+ replace[defmap[i]] = 'r';
+ }
+ }
+ tuple = heap_modifytuple(tuple,
+ tupDesc,
+ values,
+ nulls,
+ replace);
+ }
+
if (cstate->oids && file_has_oids)
HeapTupleSetOid(tuple, loaded_oid);
*************** CopyFrom(CopyState cstate)
*** 2138,2148 ****
--- 2195,2208 ----
pfree(values);
pfree(nulls);
+ pfree(replace);
pfree(field_strings);
pfree(in_functions);
pfree(typioparams);
pfree(defmap);
+ pfree(idmap);
+ pfree(genmap);
pfree(defexprs);
ExecDropSingleTupleTableSlot(slot);
diff -dcrpN pgsql.orig/src/backend/commands/sequence.c pgsql/src/backend/commands/sequence.c
*** pgsql.orig/src/backend/commands/sequence.c 2007-02-09 07:23:21.000000000 +0100
--- pgsql/src/backend/commands/sequence.c 2007-04-13 18:28:52.000000000 +0200
*************** DefineSequence(CreateSeqStmt *seq)
*** 129,134 ****
--- 129,136 ----
coldef->raw_default = NULL;
coldef->cooked_default = NULL;
coldef->constraints = NIL;
+ coldef->is_identity = false;
+ coldef->is_generated = false;
null[i - 1] = ' ';
diff -dcrpN pgsql.orig/src/backend/commands/tablecmds.c pgsql/src/backend/commands/tablecmds.c
*** pgsql.orig/src/backend/commands/tablecmds.c 2007-04-09 11:35:46.000000000 +0200
--- pgsql/src/backend/commands/tablecmds.c 2007-04-14 22:48:11.000000000 +0200
***************
*** 34,39 ****
--- 34,40 ----
#include "catalog/toasting.h"
#include "commands/cluster.h"
#include "commands/defrem.h"
+ #include "commands/sequence.h"
#include "commands/tablecmds.h"
#include "commands/tablespace.h"
#include "commands/trigger.h"
*************** static void StoreCatalogInheritance1(Oid
*** 174,179 ****
--- 175,182 ----
int16 seqNumber, Relation inhRelation);
static int findAttrByName(const char *attributeName, List *schema);
static void setRelhassubclassInRelation(Oid relationId, bool relhassubclass);
+ static void renameseqfor(Oid tableOid, const char *newrelname,
+ const char *oldattname, const char *newattname);
static void AlterIndexNamespaces(Relation classRel, Relation rel,
Oid oldNspOid, Oid newNspOid);
static void AlterSeqNamespaces(Relation classRel, Relation rel,
*************** static void ATExecDropNotNull(Relation r
*** 217,223 ****
static void ATExecSetNotNull(AlteredTableInfo *tab, Relation rel,
const char *colName);
static void ATExecColumnDefault(Relation rel, const char *colName,
! Node *newDefault);
static void ATPrepSetStatistics(Relation rel, const char *colName,
Node *flagValue);
static void ATExecSetStatistics(Relation rel, const char *colName,
--- 220,227 ----
static void ATExecSetNotNull(AlteredTableInfo *tab, Relation rel,
const char *colName);
static void ATExecColumnDefault(Relation rel, const char *colName,
! Node *newDefault,
! bool adidentity, bool adgenerated);
static void ATPrepSetStatistics(Relation rel, const char *colName,
Node *flagValue);
static void ATExecSetStatistics(Relation rel, const char *colName,
*************** static void ATExecEnableDisableRule(Rela
*** 259,264 ****
--- 263,270 ----
char fires_when);
static void ATExecAddInherit(Relation rel, RangeVar *parent);
static void ATExecDropInherit(Relation rel, RangeVar *parent);
+ static void ATExecAlterSeq(Relation rel, char *column, List *seq_opts);
+ static void ATExecIdentity(Relation rel, char *column, bool is_identity, bool is_generated);
static void copy_relation_data(Relation rel, SMgrRelation dst);
*************** DefineRelation(CreateStmt *stmt, char re
*** 287,292 ****
--- 293,299 ----
Datum reloptions;
ListCell *listptr;
AttrNumber attnum;
+ bool saw_identity;
/*
* Truncate relname to appropriate length (probably a waste of time, as
*************** DefineRelation(CreateStmt *stmt, char re
*** 366,371 ****
--- 373,393 ----
stmt->relation->istemp,
&inheritOids, &old_constraints, &parentOidCount);
+ saw_identity = false;
+ foreach(listptr, schema)
+ {
+ ColumnDef *colDef = lfirst(listptr);
+
+ if (colDef->is_identity)
+ {
+ if (saw_identity)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("multiple IDENTITY columns are not allowed")));
+ saw_identity = true;
+ }
+ }
+
/*
* Create a relation descriptor from the relation schema and create the
* relation. Note that in this stage only inherited (pre-cooked) defaults
*************** DefineRelation(CreateStmt *stmt, char re
*** 480,485 ****
--- 502,509 ----
rawEnt = (RawColumnDefault *) palloc(sizeof(RawColumnDefault));
rawEnt->attnum = attnum;
rawEnt->raw_default = colDef->raw_default;
+ rawEnt->is_identity = colDef->is_identity;
+ rawEnt->is_generated = colDef->is_generated;
rawDefaults = lappend(rawDefaults, rawEnt);
}
}
*************** MergeAttributes(List *schema, List *supe
*** 931,936 ****
--- 955,962 ----
def->raw_default = NULL;
def->cooked_default = NULL;
def->constraints = NIL;
+ def->is_identity = false;
+ def->is_generated = false;
inhSchema = lappend(inhSchema, def);
newattno[parent_attno - 1] = ++child_attno;
}
*************** MergeAttributes(List *schema, List *supe
*** 941,946 ****
--- 967,974 ----
if (attribute->atthasdef)
{
char *this_default = NULL;
+ bool this_identity = false;
+ bool this_generated = false;
AttrDefault *attrdef;
int i;
*************** MergeAttributes(List *schema, List *supe
*** 952,957 ****
--- 980,987 ----
if (attrdef[i].adnum == parent_attno)
{
this_default = attrdef[i].adbin;
+ this_identity = attrdef[i].adidentity;
+ this_generated = attrdef[i].adgenerated;
break;
}
}
*************** MergeAttributes(List *schema, List *supe
*** 969,979 ****
--- 999,1015 ----
*/
Assert(def->raw_default == NULL);
if (def->cooked_default == NULL)
+ {
def->cooked_default = pstrdup(this_default);
+ def->is_identity = this_identity;
+ def->is_generated = this_generated;
+ }
else if (strcmp(def->cooked_default, this_default) != 0)
{
def->cooked_default = bogus_marker;
have_bogus_defaults = true;
+ def->is_identity = false;
+ def->is_generated = false;
}
}
}
*************** MergeAttributes(List *schema, List *supe
*** 1065,1070 ****
--- 1101,1108 ----
{
def->raw_default = newdef->raw_default;
def->cooked_default = newdef->cooked_default;
+ def->is_identity = newdef->is_identity;
+ def->is_generated = newdef->is_generated;
}
}
else
*************** setRelhassubclassInRelation(Oid relation
*** 1409,1414 ****
--- 1447,1482 ----
}
+ static void renameseqfor(Oid tableOid, const char *newrelname, const char *oldattname, const char *newattname)
+ {
+ int attnum;
+ Oid sequenceId;
+
+ if (!newrelname)
+ newrelname = get_rel_name(tableOid);
+ attnum = get_attnum(tableOid, oldattname);
+ if (attnum == InvalidAttrNumber)
+ return;
+
+ sequenceId = get_relid_att_serial_sequence(tableOid, attnum);
+
+ if (OidIsValid(sequenceId))
+ {
+ char *sname;
+
+ sname = ChooseRelationName(newrelname,
+ newattname,
+ "seq",
+ get_rel_namespace(sequenceId));
+ renamerel(sequenceId, sname);
+ ereport(NOTICE,
+ (errmsg("supporting sequence for column \"%s\" renamed to \"%s\"",
+ newattname, sname)));
+ pfree(sname);
+ }
+ }
+
+
/*
* renameatt - changes the name of a attribute in a relation
*
*************** renameatt(Oid myrelid,
*** 1611,1616 ****
--- 1679,1690 ----
heap_close(attrelation, RowExclusiveLock);
relation_close(targetrelation, NoLock); /* close rel but keep lock */
+
+ /*
+ * Also rename the underlying sequence
+ * if this column is SERIAL or GENERATED AS IDENTITY.
+ */
+ renameseqfor(myrelid, NULL, oldattname, newattname);
}
/*
*************** renamerel(Oid myrelid, const char *newre
*** 1632,1637 ****
--- 1706,1712 ----
char *oldrelname;
char relkind;
bool relhastriggers;
+ int attnum;
/*
* Grab an exclusive lock on the target table or index, which we will NOT
*************** renamerel(Oid myrelid, const char *newre
*** 1679,1684 ****
--- 1754,1768 ----
/* keep the system catalog indexes current */
CatalogUpdateIndexes(relrelation, reltup);
+ for (attnum = 1; attnum <= targetrelation->rd_att->natts; attnum++)
+ {
+ char *attname;
+ if (targetrelation->rd_att->attrs[attnum - 1]->attisdropped)
+ continue;
+ attname = NameStr(targetrelation->rd_att->attrs[attnum - 1]->attname);
+ renameseqfor(myrelid, newrelname, attname, attname);
+ }
+
heap_freetuple(reltup);
heap_close(relrelation, RowExclusiveLock);
*************** ATPrepCmd(List **wqueue, Relation rel, A
*** 1971,1976 ****
--- 2055,2063 ----
case AT_DisableRule:
case AT_AddInherit: /* INHERIT / NO INHERIT */
case AT_DropInherit:
+ case AT_SetSeqOpts:
+ case AT_SetIdentity:
+ case AT_DropIdentity:
ATSimplePermissions(rel, false);
/* These commands never recurse */
/* No command-specific prep needed */
*************** ATExecCmd(AlteredTableInfo *tab, Relatio
*** 2068,2074 ****
ATExecAddColumn(tab, rel, (ColumnDef *) cmd->def);
break;
case AT_ColumnDefault: /* ALTER COLUMN DEFAULT */
! ATExecColumnDefault(rel, cmd->name, cmd->def);
break;
case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */
ATExecDropNotNull(rel, cmd->name);
--- 2155,2161 ----
ATExecAddColumn(tab, rel, (ColumnDef *) cmd->def);
break;
case AT_ColumnDefault: /* ALTER COLUMN DEFAULT */
! ATExecColumnDefault(rel, cmd->name, cmd->def, cmd->is_identity, cmd->is_generated);
break;
case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */
ATExecDropNotNull(rel, cmd->name);
*************** ATExecCmd(AlteredTableInfo *tab, Relatio
*** 2193,2198 ****
--- 2280,2294 ----
case AT_DropInherit:
ATExecDropInherit(rel, (RangeVar *) cmd->def);
break;
+ case AT_SetSeqOpts:
+ ATExecAlterSeq(rel, cmd->name, (List *)cmd->def);
+ break;
+ case AT_SetIdentity:
+ ATExecIdentity(rel, cmd->name, true, cmd->is_generated);
+ break;
+ case AT_DropIdentity:
+ ATExecIdentity(rel, cmd->name, false, false);
+ break;
default: /* oops */
elog(ERROR, "unrecognized alter table type: %d",
(int) cmd->subtype);
*************** ATExecAddColumn(AlteredTableInfo *tab, R
*** 2952,2957 ****
--- 3048,3066 ----
Form_pg_type tform;
Expr *defval;
+ if (colDef->is_identity && rel->rd_att->constr)
+ {
+ int ndef = rel->rd_att->constr->num_defval;
+ AttrDefault *defval = rel->rd_att->constr->defval;
+ while (--ndef >= 0)
+ {
+ if (defval[ndef].adidentity)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("multiple IDENTITY columns are not allowed")));
+ }
+ }
+
attrdesc = heap_open(AttributeRelationId, RowExclusiveLock);
/*
*************** ATExecAddColumn(AlteredTableInfo *tab, R
*** 3096,3101 ****
--- 3205,3212 ----
rawEnt = (RawColumnDefault *) palloc(sizeof(RawColumnDefault));
rawEnt->attnum = attribute->attnum;
rawEnt->raw_default = copyObject(colDef->raw_default);
+ rawEnt->is_identity = colDef->is_identity;
+ rawEnt->is_generated = colDef->is_generated;
/*
* This function is intended for CREATE TABLE, so it processes a
*************** ATExecSetNotNull(AlteredTableInfo *tab,
*** 3332,3338 ****
*/
static void
ATExecColumnDefault(Relation rel, const char *colName,
! Node *newDefault)
{
AttrNumber attnum;
--- 3443,3450 ----
*/
static void
ATExecColumnDefault(Relation rel, const char *colName,
! Node *newDefault,
! bool adidentity, bool adgenerated)
{
AttrNumber attnum;
*************** ATExecColumnDefault(Relation rel, const
*** 3368,3373 ****
--- 3480,3487 ----
rawEnt = (RawColumnDefault *) palloc(sizeof(RawColumnDefault));
rawEnt->attnum = attnum;
rawEnt->raw_default = newDefault;
+ rawEnt->is_identity = adidentity;
+ rawEnt->is_generated = adgenerated;
/*
* This function is intended for CREATE TABLE, so it processes a
*************** ATExecAlterColumnType(AlteredTableInfo *
*** 4837,4842 ****
--- 4951,4958 ----
ScanKeyData key[3];
SysScanDesc scan;
HeapTuple depTup;
+ bool adidentity;
+ bool adgenerated;
attrelation = heap_open(AttributeRelationId, RowExclusiveLock);
*************** ATExecAlterColumnType(AlteredTableInfo *
*** 4879,4884 ****
--- 4995,5001 ----
if (attTup->atthasdef)
{
defaultexpr = build_column_default(rel, attnum);
+ column_default_properties(rel, attnum, &adidentity, &adgenerated);
Assert(defaultexpr);
defaultexpr = strip_implicit_coercions(defaultexpr);
defaultexpr = coerce_to_target_type(NULL, /* no UNKNOWN params */
*************** ATExecAlterColumnType(AlteredTableInfo *
*** 4893,4899 ****
--- 5010,5020 ----
colName, TypeNameToString(typename))));
}
else
+ {
defaultexpr = NULL;
+ adidentity = false;
+ adgenerated = false;
+ }
/*
* Find everything that depends on the column (constraints, indexes, etc),
*************** ATExecAlterColumnType(AlteredTableInfo *
*** 5142,5148 ****
*/
RemoveAttrDefault(RelationGetRelid(rel), attnum, DROP_RESTRICT, true);
! StoreAttrDefault(rel, attnum, nodeToString(defaultexpr));
}
/* Cleanup */
--- 5263,5269 ----
*/
RemoveAttrDefault(RelationGetRelid(rel), attnum, DROP_RESTRICT, true);
! StoreAttrDefault(rel, attnum, nodeToString(defaultexpr), adidentity, adgenerated);
}
/* Cleanup */
*************** ATExecAddInherit(Relation child_rel, Ran
*** 6031,6036 ****
--- 6152,6365 ----
}
/*
+ * ALTER TABLE RESTART WITH / SET sequence_options
+ */
+ static void
+ ATExecAlterSeq(Relation rel, char *column, List *seq_opts)
+ {
+ Oid tableOid;
+ AttrNumber attnum;
+ Oid sequenceId;
+
+ tableOid = RelationGetRelid(rel);
+ attnum = get_attnum(tableOid, column);
+
+ if (attnum == InvalidAttrNumber)
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does not exist",
+ column, RelationGetRelationName(rel))));
+
+ sequenceId = get_relid_att_serial_sequence(tableOid, attnum);
+ if (OidIsValid(sequenceId))
+ {
+ HeapTuple seqtup;
+
+ seqtup = SearchSysCache(RELOID,
+ ObjectIdGetDatum(sequenceId),
+ 0, 0, 0);
+ if (HeapTupleIsValid(seqtup))
+ {
+ Form_pg_class seq = (Form_pg_class) GETSTRUCT(seqtup);
+ HeapTuple nsptup;
+ RangeVar *rv = NULL;
+
+ nsptup = SearchSysCache(NAMESPACEOID,
+ ObjectIdGetDatum(seq->relnamespace),
+ 0, 0, 0);
+ if (HeapTupleIsValid(nsptup))
+ {
+ Form_pg_namespace nsp = (Form_pg_namespace) GETSTRUCT(nsptup);
+ rv = makeRangeVar(NameStr(nsp->nspname), NameStr(seq->relname));
+ ReleaseSysCache(nsptup);
+ }
+
+ ReleaseSysCache(seqtup);
+
+ if (rv != NULL)
+ {
+ AlterSeqStmt *newcmd;
+
+ newcmd = makeNode(AlterSeqStmt);
+ newcmd->sequence = rv;
+ newcmd->options = seq_opts;
+ AlterSequence(newcmd);
+ pfree(newcmd);
+ pfree(rv);
+ }
+ }
+ } else
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("column \"%s\" of relation \"%s\" is not an IDENTITY or SERIAL column",
+ column, RelationGetRelationName(rel))));
+ }
+
+
+ static void
+ ATExecIdentity(Relation rel, char *column, bool is_identity, bool is_generated)
+ {
+ Oid tableOid;
+ Relation tableRel;
+ AttrNumber idattnum;
+ Oid sequenceId;
+ TupleDesc tupleDesc;
+ Relation attrelation;
+ HeapTuple atttup;
+ Form_pg_attribute attform;
+ int attnum;
+ bool adidentity;
+
+ tableOid = RelationGetRelid(rel);
+ tableRel = heap_open(tableOid, AccessExclusiveLock);
+
+ attrelation = heap_open(AttributeRelationId, RowExclusiveLock);
+
+ atttup = SearchSysCacheCopyAttName(tableOid, column);
+ if (!HeapTupleIsValid(atttup))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does not exist",
+ column, RelationGetRelationName(rel))));
+
+ attform = (Form_pg_attribute) GETSTRUCT(atttup);
+
+ idattnum = attform->attnum;
+ if (idattnum <= 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot modify system column \"%s\"",
+ column)));
+
+ if (is_identity)
+ {
+ tupleDesc = RelationGetDescr(rel);
+
+ for (attnum = 1; attnum <= tupleDesc->natts; attnum++)
+ {
+ if (tupleDesc->attrs[attnum - 1]->attisdropped)
+ continue;
+ column_default_properties(rel, attnum, &adidentity, NULL);
+ if (idattnum != attnum && adidentity)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("multiple IDENTITY columns are not allowed")));
+ }
+ } else
+ is_generated = false;
+
+ sequenceId = get_relid_att_serial_sequence(tableOid, idattnum);
+
+ if (!OidIsValid(sequenceId))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("column \"%s\" of relation \"%s\" does not have an OWNED sequence",
+ column, RelationGetRelationName(rel))));
+
+ column_default_properties(rel, idattnum, &adidentity, NULL);
+
+ if (is_identity && adidentity)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("column \"%s\" of relation \"%s\" is an IDENTITY column",
+ column, RelationGetRelationName(rel))));
+
+ if (!is_identity && !adidentity)
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("column \"%s\" of relation \"%s\" is a non-IDENTITY serial column",
+ column, RelationGetRelationName(rel))));
+
+ /* Remove the old DEFAULT and add a NEW one */
+ if (is_identity)
+ {
+ /*
+ * It's not enough to simply set the IDENTITY flag, be sure
+ * that the column will have the correct DEFAULT expression, too.
+ * Someone may have done a DROP DEFAULT on the column but kept
+ * the OWNED sequence.
+ */
+ char *qstring;
+ Oid snamespaceid;
+ char *snamespace;
+ char *sname;
+ RawColumnDefault *rawEnt;
+ A_Const *snamenode;
+ FuncCall *funccallnode;
+
+ snamespaceid = get_rel_namespace(sequenceId);
+ snamespace = get_namespace_name(snamespaceid);
+ sname = get_rel_name(sequenceId);
+
+ qstring = quote_qualified_identifier(snamespace, sname);
+ snamenode = makeNode(A_Const);
+ snamenode->val.type = T_String;
+ snamenode->val.val.str = qstring;
+ snamenode->typename = SystemTypeName("regclass");
+
+ funccallnode = makeNode(FuncCall);
+ funccallnode->funcname = SystemFuncName("nextval");
+ funccallnode->args = list_make1(snamenode);
+ funccallnode->agg_star = false;
+ funccallnode->agg_distinct = false;
+ funccallnode->location = -1;
+
+ rawEnt = (RawColumnDefault *) palloc(sizeof(RawColumnDefault));
+ rawEnt->attnum = idattnum;
+ rawEnt->raw_default = (Node *) funccallnode;
+ rawEnt->is_identity = true;
+ rawEnt->is_generated = is_generated;
+
+ RemoveAttrDefault(RelationGetRelid(rel), idattnum, DROP_RESTRICT, true);
+ AddRelationRawConstraints(rel, list_make1(rawEnt), NIL);
+ }
+ else
+ {
+ /*
+ * Remove the IDENTITY property from the DEFAULT.
+ * we checked that the column was an IDENTITY column,
+ * assume it still has the nextval() expression
+ * as its DEFAULT expression.
+ */
+ Node *defaultexpr = build_column_default(rel, idattnum);
+ RemoveAttrDefault(RelationGetRelid(rel), idattnum, DROP_RESTRICT, true);
+ StoreAttrDefault(rel, idattnum, nodeToString(defaultexpr), false, false);
+ }
+
+ /* keep system catalog indexes current */
+ CatalogUpdateIndexes(attrelation, atttup);
+
+ heap_freetuple(atttup);
+
+ heap_close(attrelation, RowExclusiveLock);
+
+ CommandCounterIncrement();
+
+ heap_close(rel, NoLock);
+ }
+
+
+ /*
* Obtain the source-text form of the constraint expression for a check
* constraint, given its pg_constraint tuple
*/
diff -dcrpN pgsql.orig/src/backend/commands/typecmds.c pgsql/src/backend/commands/typecmds.c
*** pgsql.orig/src/backend/commands/typecmds.c 2007-04-04 10:11:12.000000000 +0200
--- pgsql/src/backend/commands/typecmds.c 2007-04-13 13:13:30.000000000 +0200
*************** DefineDomain(CreateDomainStmt *stmt)
*** 730,736 ****
defaultExpr = cookDefault(pstate, constr->raw_expr,
basetypeoid,
basetypeMod,
! domainName);
/*
* Expression must be stored as a nodeToString result, but we
--- 730,737 ----
defaultExpr = cookDefault(pstate, constr->raw_expr,
basetypeoid,
basetypeMod,
! domainName,
! false);
/*
* Expression must be stored as a nodeToString result, but we
*************** AlterDomainDefault(List *names, Node *de
*** 1391,1397 ****
defaultExpr = cookDefault(pstate, defaultRaw,
typTup->typbasetype,
typTup->typtypmod,
! NameStr(typTup->typname));
/*
* Expression must be stored as a nodeToString result, but we also
--- 1392,1399 ----
defaultExpr = cookDefault(pstate, defaultRaw,
typTup->typbasetype,
typTup->typtypmod,
! NameStr(typTup->typname),
! false);
/*
* Expression must be stored as a nodeToString result, but we also
diff -dcrpN pgsql.orig/src/backend/commands/view.c pgsql/src/backend/commands/view.c
*** pgsql.orig/src/backend/commands/view.c 2007-03-13 11:39:31.000000000 +0100
--- pgsql/src/backend/commands/view.c 2007-04-13 18:27:48.000000000 +0200
*************** DefineVirtualRelation(const RangeVar *re
*** 127,132 ****
--- 127,134 ----
def->raw_default = NULL;
def->cooked_default = NULL;
def->constraints = NIL;
+ def->is_identity = false;
+ def->is_generated = false;
attrList = lappend(attrList, def);
}
diff -dcrpN pgsql.orig/src/backend/executor/execMain.c pgsql/src/backend/executor/execMain.c
*** pgsql.orig/src/backend/executor/execMain.c 2007-03-30 09:33:54.000000000 +0200
--- pgsql/src/backend/executor/execMain.c 2007-04-14 15:12:40.000000000 +0200
***************
*** 46,55 ****
--- 46,59 ----
#include "executor/nodeSubplan.h"
#include "miscadmin.h"
#include "optimizer/clauses.h"
+ #include "optimizer/var.h"
#include "parser/parse_clause.h"
+ #include "parser/parse_relation.h"
#include "parser/parsetree.h"
+ #include "rewrite/rewriteHandler.h"
#include "storage/smgr.h"
#include "utils/acl.h"
+ #include "utils/datum.h"
#include "utils/lsyscache.h"
#include "utils/memutils.h"
*************** static TupleTableSlot *ExecutePlan(EStat
*** 75,92 ****
CmdType operation,
long numberTuples,
ScanDirection direction,
! DestReceiver *dest);
static void ExecSelect(TupleTableSlot *slot,
DestReceiver *dest, EState *estate);
static void ExecInsert(TupleTableSlot *slot, ItemPointer tupleid,
TupleTableSlot *planSlot,
! DestReceiver *dest, EState *estate);
static void ExecDelete(ItemPointer tupleid,
TupleTableSlot *planSlot,
DestReceiver *dest, EState *estate);
static void ExecUpdate(TupleTableSlot *slot, ItemPointer tupleid,
TupleTableSlot *planSlot,
! DestReceiver *dest, EState *estate);
static void ExecProcessReturning(ProjectionInfo *projectReturning,
TupleTableSlot *tupleSlot,
TupleTableSlot *planSlot,
--- 79,104 ----
CmdType operation,
long numberTuples,
ScanDirection direction,
! DestReceiver *dest,
! List *modified_fields);
static void ExecSelect(TupleTableSlot *slot,
DestReceiver *dest, EState *estate);
static void ExecInsert(TupleTableSlot *slot, ItemPointer tupleid,
TupleTableSlot *planSlot,
! DestReceiver *dest, EState *estate,
! List *modified_fields);
static void ExecDelete(ItemPointer tupleid,
TupleTableSlot *planSlot,
DestReceiver *dest, EState *estate);
static void ExecUpdate(TupleTableSlot *slot, ItemPointer tupleid,
TupleTableSlot *planSlot,
! DestReceiver *dest, EState *estate,
! List *modified_fields);
! static HeapTuple ExecGenerated(TupleTableSlot *slot,
! HeapTuple tuple,
! EState *estate,
! CmdType commandType,
! List *overridden);
static void ExecProcessReturning(ProjectionInfo *projectReturning,
TupleTableSlot *tupleSlot,
TupleTableSlot *planSlot,
*************** ExecutorRun(QueryDesc *queryDesc,
*** 245,251 ****
operation,
count,
direction,
! dest);
/*
* shutdown tuple receiver, if we started it
--- 257,264 ----
operation,
count,
direction,
! dest,
! queryDesc->modified_fields);
/*
* shutdown tuple receiver, if we started it
*************** ExecutePlan(EState *estate,
*** 1051,1057 ****
CmdType operation,
long numberTuples,
ScanDirection direction,
! DestReceiver *dest)
{
JunkFilter *junkfilter;
TupleTableSlot *planSlot;
--- 1064,1071 ----
CmdType operation,
long numberTuples,
ScanDirection direction,
! DestReceiver *dest,
! List *modified_fields)
{
JunkFilter *junkfilter;
TupleTableSlot *planSlot;
*************** lnext: ;
*** 1260,1266 ****
break;
case CMD_INSERT:
! ExecInsert(slot, tupleid, planSlot, dest, estate);
result = NULL;
break;
--- 1274,1280 ----
break;
case CMD_INSERT:
! ExecInsert(slot, tupleid, planSlot, dest, estate, modified_fields);
result = NULL;
break;
*************** lnext: ;
*** 1270,1276 ****
break;
case CMD_UPDATE:
! ExecUpdate(slot, tupleid, planSlot, dest, estate);
result = NULL;
break;
--- 1284,1290 ----
break;
case CMD_UPDATE:
! ExecUpdate(slot, tupleid, planSlot, dest, estate, modified_fields);
result = NULL;
break;
*************** ExecInsert(TupleTableSlot *slot,
*** 1347,1353 ****
ItemPointer tupleid,
TupleTableSlot *planSlot,
DestReceiver *dest,
! EState *estate)
{
HeapTuple tuple;
ResultRelInfo *resultRelInfo;
--- 1361,1368 ----
ItemPointer tupleid,
TupleTableSlot *planSlot,
DestReceiver *dest,
! EState *estate,
! List *modified_fields)
{
HeapTuple tuple;
ResultRelInfo *resultRelInfo;
*************** ExecInsert(TupleTableSlot *slot,
*** 1396,1401 ****
--- 1411,1421 ----
}
/*
+ * Fill in the GENERATED columns
+ */
+ tuple = ExecGenerated(slot, tuple, estate, CMD_INSERT, modified_fields);
+
+ /*
* Check the constraints of the tuple
*/
if (resultRelationDesc->rd_att->constr)
*************** ExecUpdate(TupleTableSlot *slot,
*** 1579,1585 ****
ItemPointer tupleid,
TupleTableSlot *planSlot,
DestReceiver *dest,
! EState *estate)
{
HeapTuple tuple;
ResultRelInfo *resultRelInfo;
--- 1599,1606 ----
ItemPointer tupleid,
TupleTableSlot *planSlot,
DestReceiver *dest,
! EState *estate,
! List *modified_fields)
{
HeapTuple tuple;
ResultRelInfo *resultRelInfo;
*************** ExecUpdate(TupleTableSlot *slot,
*** 1638,1643 ****
--- 1659,1669 ----
}
/*
+ * Fill in the GENERATED columns
+ */
+ tuple = ExecGenerated(slot, tuple, estate, CMD_UPDATE, modified_fields);
+
+ /*
* Check the constraints of the tuple
*
* If we generate a new candidate tuple after EvalPlanQual testing, we
*************** ExecConstraints(ResultRelInfo *resultRel
*** 1831,1836 ****
--- 1857,1985 ----
}
/*
+ * ExecGenerated --- evaluate GENERATED columns
+ */
+ static HeapTuple
+ ExecGenerated(TupleTableSlot *slot, HeapTuple tuple, EState *estate,
+ CmdType commandType, List *modified_fields)
+ {
+ ResultRelInfo *resultRelInfo;
+ Relation resultRelationDesc;
+ TupleDesc tupleDesc;
+ ExprContext *econtext;
+ ExprState *exprstate;
+ int attnum;
+ Datum *values;
+ bool *isnull;
+ bool *replace;
+
+ resultRelInfo = estate->es_result_relation_info;
+ resultRelationDesc = resultRelInfo->ri_RelationDesc;
+
+ tupleDesc = resultRelationDesc->rd_att;
+
+ values = palloc0(tupleDesc->natts * sizeof(Datum));
+ isnull = palloc0(tupleDesc->natts * sizeof(bool));
+ replace= palloc0(tupleDesc->natts * sizeof(bool));
+
+ econtext = GetPerTupleExprContext(estate);
+ econtext->ecxt_scantuple = slot;
+
+ for (attnum = 1; attnum <= tupleDesc->natts; attnum++)
+ {
+ bool adidentity, adgenerated;
+ Form_pg_attribute att_tup = tupleDesc->attrs[attnum - 1];
+
+ if (att_tup->attisdropped)
+ continue;
+ column_default_properties(resultRelationDesc, attnum, &adidentity, &adgenerated);
+ if (adgenerated && !adidentity)
+ {
+ bool compute = false;
+ Expr *expr = NULL;
+ Datum const_val;
+ bool const_is_null;
+ int16 resultTypLen;
+ bool resultTypByVal;
+
+ if (list_member_int(modified_fields, attnum))
+ compute = true;
+ else if (commandType == CMD_UPDATE)
+ {
+ ListCell *cell;
+ ParseState *pstate;
+ RangeTblEntry *rte;
+ int varno;
+ int sublevels_up;
+
+ pstate = make_parsestate(NULL);
+ rte = addRangeTableEntryForRelation(pstate,
+ resultRelationDesc,
+ NULL,
+ false,
+ true);
+ addRTEtoQuery(pstate, rte, true, true, true);
+ varno = RTERangeTablePosn(pstate, rte, &sublevels_up);
+
+ expr = (Expr *)
+ build_column_default(resultRelationDesc,
+ attnum);
+
+ foreach(cell, modified_fields)
+ {
+ int attnum1 = lfirst_int(cell);
+ Node *var;
+ var = scanRTEForColumn(pstate,
+ rte,
+ get_attname(RelationGetRelid(resultRelationDesc), attnum1),
+ -1);
+ if (contain_var_reference((Node *)expr, varno, ((Var *)var)->varattno, sublevels_up))
+ {
+ compute = true;
+ break;
+ }
+ }
+ }
+
+ if (!compute)
+ continue;
+
+ if (!expr)
+ expr = (Expr *)
+ build_column_default(resultRelationDesc,
+ attnum);
+
+ exprstate = ExecPrepareExpr(expr, estate);
+ const_val = ExecEvalExpr(exprstate,
+ econtext,
+ &const_is_null,
+ NULL);
+ get_typlenbyval(att_tup->atttypid,
+ &resultTypLen,
+ &resultTypByVal);
+ if (!const_is_null)
+ const_val = datumCopy(const_val,
+ resultTypByVal,
+ resultTypLen);
+ values[attnum - 1] = const_val;
+ isnull[attnum - 1] = const_is_null;
+ replace[attnum - 1] = true;
+ }
+ }
+
+ tuple = heap_modify_tuple(tuple, tupleDesc, values, isnull, replace);
+
+ slot = ExecStoreTuple(tuple, slot, InvalidBuffer, false);
+
+ ResetPerTupleExprContext(estate);
+
+ pfree(values);
+ pfree(isnull);
+ pfree(replace);
+ return tuple;
+ }
+
+ /*
* ExecProcessReturning --- evaluate a RETURNING list and send to dest
*
* projectReturning: RETURNING projection info for current result rel
diff -dcrpN pgsql.orig/src/backend/nodes/copyfuncs.c pgsql/src/backend/nodes/copyfuncs.c
*** pgsql.orig/src/backend/nodes/copyfuncs.c 2007-04-04 10:11:16.000000000 +0200
--- pgsql/src/backend/nodes/copyfuncs.c 2007-04-14 14:33:14.000000000 +0200
*************** _copyPlannedStmt(PlannedStmt *from)
*** 83,88 ****
--- 83,89 ----
COPY_NODE_FIELD(returningLists);
COPY_NODE_FIELD(rowMarks);
COPY_SCALAR_FIELD(nParamExec);
+ COPY_NODE_FIELD(modified_fields);
return newnode;
}
*************** _copyColumnDef(ColumnDef *from)
*** 1754,1759 ****
--- 1755,1762 ----
COPY_NODE_FIELD(raw_default);
COPY_STRING_FIELD(cooked_default);
COPY_NODE_FIELD(constraints);
+ COPY_SCALAR_FIELD(is_identity);
+ COPY_SCALAR_FIELD(is_generated);
return newnode;
}
*************** _copyConstraint(Constraint *from)
*** 1770,1775 ****
--- 1773,1781 ----
COPY_NODE_FIELD(keys);
COPY_NODE_FIELD(options);
COPY_STRING_FIELD(indexspace);
+ COPY_SCALAR_FIELD(is_identity);
+ COPY_SCALAR_FIELD(is_generated);
+ COPY_NODE_FIELD(seq_opts);
return newnode;
}
*************** _copyQuery(Query *from)
*** 1834,1839 ****
--- 1840,1847 ----
COPY_NODE_FIELD(limitCount);
COPY_NODE_FIELD(rowMarks);
COPY_NODE_FIELD(setOperations);
+ COPY_SCALAR_FIELD(commandOverride);
+ COPY_NODE_FIELD(modified_fields);
return newnode;
}
*************** _copyInsertStmt(InsertStmt *from)
*** 1847,1852 ****
--- 1855,1861 ----
COPY_NODE_FIELD(cols);
COPY_NODE_FIELD(selectStmt);
COPY_NODE_FIELD(returningList);
+ COPY_SCALAR_FIELD(commandOverride);
return newnode;
}
*************** _copyAlterTableCmd(AlterTableCmd *from)
*** 1940,1945 ****
--- 1949,1956 ----
COPY_NODE_FIELD(def);
COPY_NODE_FIELD(transform);
COPY_SCALAR_FIELD(behavior);
+ COPY_SCALAR_FIELD(is_identity);
+ COPY_SCALAR_FIELD(is_generated);
return newnode;
}
diff -dcrpN pgsql.orig/src/backend/nodes/equalfuncs.c pgsql/src/backend/nodes/equalfuncs.c
*** pgsql.orig/src/backend/nodes/equalfuncs.c 2007-04-04 10:11:16.000000000 +0200
--- pgsql/src/backend/nodes/equalfuncs.c 2007-04-14 14:33:43.000000000 +0200
*************** _equalQuery(Query *a, Query *b)
*** 737,742 ****
--- 737,744 ----
COMPARE_NODE_FIELD(limitCount);
COMPARE_NODE_FIELD(rowMarks);
COMPARE_NODE_FIELD(setOperations);
+ COMPARE_SCALAR_FIELD(commandOverride);
+ COMPARE_NODE_FIELD(modified_fields);
return true;
}
*************** _equalInsertStmt(InsertStmt *a, InsertSt
*** 748,753 ****
--- 750,756 ----
COMPARE_NODE_FIELD(cols);
COMPARE_NODE_FIELD(selectStmt);
COMPARE_NODE_FIELD(returningList);
+ COMPARE_SCALAR_FIELD(commandOverride);
return true;
}
*************** _equalAlterTableCmd(AlterTableCmd *a, Al
*** 829,834 ****
--- 832,839 ----
COMPARE_NODE_FIELD(def);
COMPARE_NODE_FIELD(transform);
COMPARE_SCALAR_FIELD(behavior);
+ COMPARE_SCALAR_FIELD(is_identity);
+ COMPARE_SCALAR_FIELD(is_generated);
return true;
}
*************** _equalColumnDef(ColumnDef *a, ColumnDef
*** 1765,1770 ****
--- 1770,1777 ----
COMPARE_NODE_FIELD(raw_default);
COMPARE_STRING_FIELD(cooked_default);
COMPARE_NODE_FIELD(constraints);
+ COMPARE_SCALAR_FIELD(is_identity);
+ COMPARE_SCALAR_FIELD(is_generated);
return true;
}
*************** _equalConstraint(Constraint *a, Constrai
*** 1779,1784 ****
--- 1786,1794 ----
COMPARE_NODE_FIELD(keys);
COMPARE_NODE_FIELD(options);
COMPARE_STRING_FIELD(indexspace);
+ COMPARE_SCALAR_FIELD(is_identity);
+ COMPARE_SCALAR_FIELD(is_generated);
+ COMPARE_NODE_FIELD(seq_opts);
return true;
}
diff -dcrpN pgsql.orig/src/backend/nodes/outfuncs.c pgsql/src/backend/nodes/outfuncs.c
*** pgsql.orig/src/backend/nodes/outfuncs.c 2007-03-28 17:16:12.000000000 +0200
--- pgsql/src/backend/nodes/outfuncs.c 2007-04-14 14:34:22.000000000 +0200
*************** _outPlannedStmt(StringInfo str, PlannedS
*** 250,255 ****
--- 250,256 ----
WRITE_NODE_FIELD(returningLists);
WRITE_NODE_FIELD(rowMarks);
WRITE_INT_FIELD(nParamExec);
+ WRITE_NODE_FIELD(modified_fields);
}
/*
*************** _outColumnDef(StringInfo str, ColumnDef
*** 1620,1625 ****
--- 1621,1628 ----
WRITE_NODE_FIELD(raw_default);
WRITE_STRING_FIELD(cooked_default);
WRITE_NODE_FIELD(constraints);
+ WRITE_BOOL_FIELD(is_identity);
+ WRITE_BOOL_FIELD(is_generated);
}
static void
*************** _outQuery(StringInfo str, Query *node)
*** 1709,1714 ****
--- 1712,1719 ----
WRITE_NODE_FIELD(limitCount);
WRITE_NODE_FIELD(rowMarks);
WRITE_NODE_FIELD(setOperations);
+ WRITE_BOOL_FIELD(commandOverride);
+ WRITE_NODE_FIELD(modified_fields);
}
static void
*************** _outConstraint(StringInfo str, Constrain
*** 1973,1980 ****
--- 1978,1992 ----
appendStringInfo(str, "DEFAULT");
WRITE_NODE_FIELD(raw_expr);
WRITE_STRING_FIELD(cooked_expr);
+ WRITE_BOOL_FIELD(is_identity);
+ WRITE_BOOL_FIELD(is_generated);
break;
+ case CONSTR_IDENTITY:
+ appendStringInfo(str, "IDENTITY");
+ WRITE_BOOL_FIELD(is_generated);
+ break;
+
case CONSTR_NOTNULL:
appendStringInfo(str, "NOT_NULL");
break;
diff -dcrpN pgsql.orig/src/backend/nodes/readfuncs.c pgsql/src/backend/nodes/readfuncs.c
*** pgsql.orig/src/backend/nodes/readfuncs.c 2007-03-28 17:16:12.000000000 +0200
--- pgsql/src/backend/nodes/readfuncs.c 2007-04-14 14:34:53.000000000 +0200
*************** _readQuery(void)
*** 154,159 ****
--- 154,161 ----
READ_NODE_FIELD(limitCount);
READ_NODE_FIELD(rowMarks);
READ_NODE_FIELD(setOperations);
+ READ_BOOL_FIELD(commandOverride);
+ READ_NODE_FIELD(modified_fields);
READ_DONE();
}
diff -dcrpN pgsql.orig/src/backend/optimizer/plan/planner.c pgsql/src/backend/optimizer/plan/planner.c
*** pgsql.orig/src/backend/optimizer/plan/planner.c 2007-02-27 08:27:37.000000000 +0100
--- pgsql/src/backend/optimizer/plan/planner.c 2007-04-14 14:36:18.000000000 +0200
*************** planner(Query *parse, bool isCursor, int
*** 163,168 ****
--- 163,169 ----
result->returningLists = root->returningLists;
result->rowMarks = parse->rowMarks;
result->nParamExec = list_length(glob->paramlist);
+ result->modified_fields = parse->modified_fields;
return result;
}
diff -dcrpN pgsql.orig/src/backend/parser/analyze.c pgsql/src/backend/parser/analyze.c
*** pgsql.orig/src/backend/parser/analyze.c 2007-03-13 11:39:44.000000000 +0100
--- pgsql/src/backend/parser/analyze.c 2007-04-13 18:25:44.000000000 +0200
*************** transformInsertStmt(ParseState *pstate,
*** 453,458 ****
--- 453,459 ----
ListCell *lc;
qry->commandType = CMD_INSERT;
+ qry->commandOverride = stmt->commandOverride;
pstate->p_is_insert = true;
/*
*************** transformColumnDefinition(ParseState *ps
*** 940,945 ****
--- 941,949 ----
bool saw_nullable;
Constraint *constraint;
ListCell *clist;
+ bool is_identity;
+ bool is_generated;
+ List *seq_opts;
cxt->columns = lappend(cxt->columns, column);
*************** transformColumnDefinition(ParseState *ps
*** 965,975 ****
}
}
/* Do necessary work on the column type declaration */
transformColumnType(pstate, column);
! /* Special actions for SERIAL pseudo-types */
! if (is_serial)
{
Oid snamespaceid;
char *snamespace;
--- 969,1006 ----
}
}
+ /* Check for GENERATED ... AS IDENTITY constructs */
+ is_identity = false;
+ is_generated = false;
+ seq_opts = NULL;
+ foreach(clist, column->constraints)
+ {
+ constraint = lfirst(clist);
+
+ switch (constraint->contype) {
+ case CONSTR_IDENTITY:
+ if (is_identity)
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("multiple GENERATED ... AS IDENTITY constructs specified for column \"%s\" of table \"%s\"",
+ column->colname, cxt->relation->relname)));
+ if (constraint->is_identity)
+ {
+ is_identity = true;
+ is_generated = constraint->is_generated;
+ seq_opts = constraint->seq_opts;
+ }
+ break;
+ default:
+ continue;
+ }
+ }
+
/* Do necessary work on the column type declaration */
transformColumnType(pstate, column);
! /* Special actions for SERIAL pseudo-types and IDENTITY columns */
! if (is_serial || is_identity)
{
Oid snamespaceid;
char *snamespace;
*************** transformColumnDefinition(ParseState *ps
*** 1011,1017 ****
*/
seqstmt = makeNode(CreateSeqStmt);
seqstmt->sequence = makeRangeVar(snamespace, sname);
! seqstmt->options = NIL;
cxt->blist = lappend(cxt->blist, seqstmt);
--- 1042,1048 ----
*/
seqstmt = makeNode(CreateSeqStmt);
seqstmt->sequence = makeRangeVar(snamespace, sname);
! seqstmt->options = seq_opts;
cxt->blist = lappend(cxt->blist, seqstmt);
*************** transformColumnDefinition(ParseState *ps
*** 1057,1062 ****
--- 1088,1096 ----
constraint->raw_expr = (Node *) funccallnode;
constraint->cooked_expr = NULL;
constraint->keys = NIL;
+ /* Don't treat SERIAL and IDENTITY differently */
+ constraint->is_identity = is_serial | is_identity;
+ constraint->is_generated = is_generated;
column->constraints = lappend(column->constraints, constraint);
constraint = makeNode(Constraint);
*************** transformColumnDefinition(ParseState *ps
*** 1068,1073 ****
--- 1102,1109 ----
transformConstraintAttrs(column->constraints);
saw_nullable = false;
+ column->is_identity = false;
+ column->is_generated = false;
foreach(clist, column->constraints)
{
*************** transformColumnDefinition(ParseState *ps
*** 1118,1126 ****
--- 1154,1167 ----
errmsg("multiple default values specified for column \"%s\" of table \"%s\"",
column->colname, cxt->relation->relname)));
column->raw_default = constraint->raw_expr;
+ column->is_identity = constraint->is_identity;
+ column->is_generated = constraint->is_generated;
Assert(constraint->cooked_expr == NULL);
break;
+ case CONSTR_IDENTITY:
+ break;
+
case CONSTR_PRIMARY:
case CONSTR_UNIQUE:
if (constraint->keys == NIL)
*************** transformInhRelation(ParseState *pstate,
*** 1293,1298 ****
--- 1334,1341 ----
def->raw_default = NULL;
def->cooked_default = NULL;
def->constraints = NIL;
+ def->is_identity = false;
+ def->is_generated = false;
/*
* Add to column list
*************** transformInhRelation(ParseState *pstate,
*** 1305,1310 ****
--- 1348,1356 ----
if (attribute->atthasdef && including_defaults)
{
char *this_default = NULL;
+ bool this_identity = false;
+ bool this_generated = false;
+
AttrDefault *attrdef;
int i;
*************** transformInhRelation(ParseState *pstate,
*** 1316,1321 ****
--- 1362,1369 ----
if (attrdef[i].adnum == parent_attno)
{
this_default = attrdef[i].adbin;
+ this_identity = attrdef[i].adidentity;
+ this_generated = attrdef[i].adgenerated;
break;
}
}
*************** transformInhRelation(ParseState *pstate,
*** 1327,1332 ****
--- 1375,1382 ----
*/
def->cooked_default = pstrdup(this_default);
+ def->is_identity = this_identity;
+ def->is_generated = this_generated;
}
}
diff -dcrpN pgsql.orig/src/backend/parser/gram.y pgsql/src/backend/parser/gram.y
*** pgsql.orig/src/backend/parser/gram.y 2007-04-12 13:15:43.000000000 +0200
--- pgsql/src/backend/parser/gram.y 2007-04-14 10:38:26.000000000 +0200
*************** static Node *makeXmlExpr(XmlExprOp op, c
*** 286,292 ****
%type fetch_direction select_limit_value select_offset_value
! %type OptSeqList
%type OptSeqElem
%type insert_rest
--- 286,292 ----
%type fetch_direction select_limit_value select_offset_value
! %type OptSeqList SeqList
%type OptSeqElem
%type insert_rest
*************** static Node *makeXmlExpr(XmlExprOp op, c
*** 314,319 ****
--- 314,320 ----
%type relation_expr
%type relation_expr_opt_alias
%type target_el single_set_clause set_target insert_column_item
+ %type OptOverride UserSystem
%type Typename SimpleTypename ConstTypename
GenericType Numeric opt_float
*************** static Node *makeXmlExpr(XmlExprOp op, c
*** 341,346 ****
--- 342,349 ----
%type TableLikeOption
%type ColQualList
%type ColConstraint ColConstraintElem ConstraintAttr
+ %type AlwaysByDefault IdentityGenerated IdentitySpec
+ %type SetIdentityAlwaysByDefault
%type key_actions key_delete key_match key_update key_action
%type ConstraintAttributeSpec ConstraintDeferrabilitySpec
ConstraintTimeSpec
*************** static Node *makeXmlExpr(XmlExprOp op, c
*** 390,400 ****
FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOR FORCE FOREIGN FORWARD
FREEZE FROM FULL FUNCTION
! GLOBAL GRANT GRANTED GREATEST GROUP_P
HANDLER HAVING HEADER_P HOLD HOUR_P
! IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P INCLUDING INCREMENT
INDEX INDEXES INHERIT INHERITS INITIALLY INNER_P INOUT INPUT_P
INSENSITIVE INSERT INSTEAD INT_P INTEGER INTERSECT
INTERVAL INTO INVOKER IS ISNULL ISOLATION
--- 393,403 ----
FALSE_P FAMILY FETCH FIRST_P FLOAT_P FOR FORCE FOREIGN FORWARD
FREEZE FROM FULL FUNCTION
! GENERATED GLOBAL GRANT GRANTED GREATEST GROUP_P
HANDLER HAVING HEADER_P HOLD HOUR_P
! IDENTITY IF_P ILIKE IMMEDIATE IMMUTABLE IMPLICIT_P IN_P INCLUDING INCREMENT
INDEX INDEXES INHERIT INHERITS INITIALLY INNER_P INOUT INPUT_P
INSENSITIVE INSERT INSTEAD INT_P INTEGER INTERSECT
INTERVAL INTO INVOKER IS ISNULL ISOLATION
*************** static Node *makeXmlExpr(XmlExprOp op, c
*** 414,420 ****
NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF NULLS_P NUMERIC
OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OR
! ORDER OUT_P OUTER_P OVERLAPS OVERLAY OWNED OWNER
PARTIAL PASSWORD PLACING POSITION
PRECISION PRESERVE PREPARE PREPARED PRIMARY
--- 417,423 ----
NOT NOTHING NOTIFY NOTNULL NOWAIT NULL_P NULLIF NULLS_P NUMERIC
OBJECT_P OF OFF OFFSET OIDS OLD ON ONLY OPERATOR OPTION OR
! ORDER OUT_P OUTER_P OVERLAPS OVERLAY OVERRIDING OWNED OWNER
PARTIAL PASSWORD PLACING POSITION
PRECISION PRESERVE PREPARE PREPARED PRIMARY
*************** alter_table_cmd:
*** 1411,1416 ****
--- 1414,1461 ----
n->def = (Node *) makeString($6);
$$ = (Node *)n;
}
+ | ALTER opt_column ColId SET SeqList
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_SetSeqOpts;
+ n->name = $3;
+ n->def = (Node *) $5;
+ $$ = (Node *)n;
+ }
+ | ALTER opt_column ColId RESTART opt_with NumericOnly
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_SetSeqOpts;
+ n->name = $3;
+ n->def = (Node *)list_make1(makeDefElem("restart", (Node *)$6));
+ $$ = (Node *)n;
+ }
+ | ALTER opt_column ColId SET IDENTITY GENERATED SetIdentityAlwaysByDefault
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_SetIdentity;
+ n->name = $3;
+ n->is_identity = true;
+ n->is_generated = $7;
+ $$ = (Node *)n;
+ }
+ | ALTER opt_column ColId SET GENERATED ALWAYS AS '(' a_expr ')'
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_ColumnDefault;
+ n->name = $3;
+ n->def = $9;
+ n->is_identity = false;
+ n->is_generated = true;
+ $$ = (Node *)n;
+ }
+ | ALTER opt_column ColId DROP IDENTITY
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_DropIdentity;
+ n->name = $3;
+ $$ = (Node *)n;
+ }
/* ALTER TABLE DROP [COLUMN] [RESTRICT|CASCADE] */
| DROP opt_column ColId opt_drop_behavior
{
*************** alter_using:
*** 1651,1656 ****
--- 1696,1706 ----
| /* EMPTY */ { $$ = NULL; }
;
+ SetIdentityAlwaysByDefault:
+ ALWAYS { $$ = true; }
+ | BY DEFAULT { $$ = false; }
+ ;
+
/*****************************************************************************
*************** ClosePortalStmt:
*** 1690,1704 ****
*
*****************************************************************************/
! CopyStmt: COPY opt_binary qualified_name opt_column_list opt_oids
copy_from copy_file_name copy_delimiter opt_with copy_opt_list
{
CopyStmt *n = makeNode(CopyStmt);
n->relation = $3;
n->query = NULL;
n->attlist = $4;
! n->is_from = $6;
! n->filename = $7;
n->options = NIL;
/* Concatenate user-supplied flags */
--- 1740,1755 ----
*
*****************************************************************************/
! CopyStmt: COPY opt_binary qualified_name opt_column_list opt_oids OptOverride
copy_from copy_file_name copy_delimiter opt_with copy_opt_list
{
CopyStmt *n = makeNode(CopyStmt);
n->relation = $3;
n->query = NULL;
n->attlist = $4;
! n->overriding = $6;
! n->is_from = $7;
! n->filename = $8;
n->options = NIL;
/* Concatenate user-supplied flags */
*************** CopyStmt: COPY opt_binary qualified_name
*** 1706,1715 ****
n->options = lappend(n->options, $2);
if ($5)
n->options = lappend(n->options, $5);
! if ($8)
! n->options = lappend(n->options, $8);
! if ($10)
! n->options = list_concat(n->options, $10);
$$ = (Node *)n;
}
| COPY select_with_parens TO copy_file_name opt_with
--- 1757,1766 ----
n->options = lappend(n->options, $2);
if ($5)
n->options = lappend(n->options, $5);
! if ($9)
! n->options = lappend(n->options, $9);
! if ($11)
! n->options = list_concat(n->options, $11);
$$ = (Node *)n;
}
| COPY select_with_parens TO copy_file_name opt_with
*************** ColConstraintElem:
*** 2052,2057 ****
--- 2103,2169 ----
n->initdeferred = FALSE;
$$ = (Node *)n;
}
+ | GENERATED AlwaysByDefault
+ {
+ $$ = $2;
+ }
+ ;
+
+ AlwaysByDefault:
+ BY DEFAULT AS IDENTITY IdentitySpec { $$ = $5; }
+ | ALWAYS AS IdentityGenerated
+ {
+ Constraint *n = (Constraint *)$3;
+ n->is_generated = true;
+ $$ = (Node *)n;
+ }
+ ;
+
+ IdentityGenerated:
+ '(' a_expr ')'
+ {
+ Constraint *n = makeNode(Constraint);
+ n->contype = CONSTR_DEFAULT;
+ n->name = NULL;
+ n->raw_expr = $2;
+ n->cooked_expr = NULL;
+ n->keys = NULL;
+ n->indexspace = NULL;
+ $$ = (Node *)n;
+ }
+ | IDENTITY IdentitySpec
+ {
+ $$ = $2;
+ }
+ ;
+
+ IdentitySpec:
+ '(' SeqList ')'
+ {
+ Constraint *n = makeNode(Constraint);
+ n->contype = CONSTR_IDENTITY;
+ n->name = NULL;
+ n->raw_expr = NULL;
+ n->cooked_expr = NULL;
+ n->keys = NULL;
+ n->indexspace = NULL;
+ n->seq_opts = $2;
+ n->is_identity = true;
+ $$ = (Node *)n;
+ }
+ | /* EMPTY */
+ {
+ Constraint *n = makeNode(Constraint);
+ n->contype = CONSTR_IDENTITY;
+ n->name = NULL;
+ n->raw_expr = NULL;
+ n->cooked_expr = NULL;
+ n->keys = NULL;
+ n->indexspace = NULL;
+ n->seq_opts = NULL;
+ n->is_identity = true;
+ $$ = (Node *)n;
+ }
;
/*
*************** OptSeqList: OptSeqList OptSeqElem {
*** 2402,2407 ****
--- 2514,2524 ----
| /*EMPTY*/ { $$ = NIL; }
;
+ SeqList: SeqList OptSeqElem { $$ = lappend($1, $2); }
+ | OptSeqElem { $$ = list_make1($1); }
+ ;
+
+
OptSeqElem: CACHE NumericOnly
{
$$ = makeDefElem("cache", (Node *)$2);
*************** InsertStmt:
*** 5628,5644 ****
;
insert_rest:
! SelectStmt
{
$$ = makeNode(InsertStmt);
$$->cols = NIL;
! $$->selectStmt = $1;
}
! | '(' insert_column_list ')' SelectStmt
{
$$ = makeNode(InsertStmt);
$$->cols = $2;
! $$->selectStmt = $4;
}
| DEFAULT VALUES
{
--- 5745,5763 ----
;
insert_rest:
! OptOverride SelectStmt
{
$$ = makeNode(InsertStmt);
$$->cols = NIL;
! $$->commandOverride = $1;
! $$->selectStmt = $2;
}
! | '(' insert_column_list ')' OptOverride SelectStmt
{
$$ = makeNode(InsertStmt);
$$->cols = $2;
! $$->commandOverride = $4;
! $$->selectStmt = $5;
}
| DEFAULT VALUES
{
*************** opt_nowait: NOWAIT { $$ = TRUE; }
*** 5726,5731 ****
--- 5845,5866 ----
;
+ OptOverride:
+ OVERRIDING UserSystem VALUE_P
+ {
+ $$ = $2;
+ }
+ | /* EMPTY */
+ {
+ $$ = false;
+ }
+ ;
+
+ UserSystem:
+ SYSTEM_P { $$ = true; }
+ | USER { $$ = false; }
+ ;
+
/*****************************************************************************
*
* QUERY:
*************** unreserved_keyword:
*** 8818,8823 ****
--- 8953,8959 ----
| FORCE
| FORWARD
| FUNCTION
+ | GENERATED
| GLOBAL
| GRANTED
| HANDLER
*************** unreserved_keyword:
*** 8878,8883 ****
--- 9014,9020 ----
| OIDS
| OPERATOR
| OPTION
+ | OVERRIDING
| OWNED
| OWNER
| PARTIAL
*************** reserved_keyword:
*** 9104,9109 ****
--- 9241,9247 ----
| GRANT
| GROUP_P
| HAVING
+ | IDENTITY
| IN_P
| INITIALLY
| INTERSECT
diff -dcrpN pgsql.orig/src/backend/parser/keywords.c pgsql/src/backend/parser/keywords.c
*** pgsql.orig/src/backend/parser/keywords.c 2007-04-04 10:11:20.000000000 +0200
--- pgsql/src/backend/parser/keywords.c 2007-04-13 12:32:59.000000000 +0200
*************** static const ScanKeyword ScanKeywords[]
*** 159,164 ****
--- 159,165 ----
{"from", FROM},
{"full", FULL},
{"function", FUNCTION},
+ {"generated", GENERATED},
{"global", GLOBAL},
{"grant", GRANT},
{"granted", GRANTED},
*************** static const ScanKeyword ScanKeywords[]
*** 169,174 ****
--- 170,176 ----
{"header", HEADER_P},
{"hold", HOLD},
{"hour", HOUR_P},
+ {"identity", IDENTITY},
{"if", IF_P},
{"ilike", ILIKE},
{"immediate", IMMEDIATE},
*************** static const ScanKeyword ScanKeywords[]
*** 264,269 ****
--- 266,272 ----
{"outer", OUTER_P},
{"overlaps", OVERLAPS},
{"overlay", OVERLAY},
+ {"overriding", OVERRIDING},
{"owned", OWNED},
{"owner", OWNER},
{"partial", PARTIAL},
diff -dcrpN pgsql.orig/src/backend/rewrite/rewriteHandler.c pgsql/src/backend/rewrite/rewriteHandler.c
*** pgsql.orig/src/backend/rewrite/rewriteHandler.c 2007-03-23 12:17:41.000000000 +0100
--- pgsql/src/backend/rewrite/rewriteHandler.c 2007-04-14 22:45:24.000000000 +0200
***************
*** 15,20 ****
--- 15,21 ----
#include "access/heapam.h"
#include "catalog/pg_type.h"
+ #include "executor/executor.h"
#include "nodes/makefuncs.h"
#include "optimizer/clauses.h"
#include "parser/analyze.h"
*************** rewriteTargetList(Query *parsetree, Rela
*** 536,544 ****
--- 537,547 ----
List **attrno_list)
{
CmdType commandType = parsetree->commandType;
+ bool commandOverride = parsetree->commandOverride;
TargetEntry **new_tles;
List *new_tlist = NIL;
List *junk_tlist = NIL;
+ List *modified_fields = NIL;
Form_pg_attribute att_tup;
int attrno,
next_junk_attrno,
*************** rewriteTargetList(Query *parsetree, Rela
*** 612,617 ****
--- 615,622 ----
for (attrno = 1; attrno <= numattrs; attrno++)
{
TargetEntry *new_tle = new_tles[attrno - 1];
+ bool adidentity;
+ bool adgenerated;
att_tup = target_relation->rd_att->attrs[attrno - 1];
*************** rewriteTargetList(Query *parsetree, Rela
*** 620,634 ****
continue;
/*
! * Handle the two cases where we need to insert a default expression:
! * 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)))
{
Node *new_expr;
new_expr = build_column_default(target_relation, attrno);
/*
--- 625,658 ----
continue;
/*
! * Handle the three cases where we need to insert a default expression:
! * - it's an INSERT and there's no tlist entry for the column,
! * - it's an INSERT and the column is GENERATED ALWAYS AS IDENTITY, or
! * - the tlist entry is a DEFAULT placeholder node.
! * Fail for UPDATE to non-default on GENERATED columns.
*/
! column_default_properties(target_relation, attrno, &adidentity, &adgenerated);
! if (commandType == CMD_UPDATE && adgenerated && !adidentity &&
! new_tle && new_tle->expr && !IsA(new_tle->expr, SetToDefault))
! {
! ereport(ERROR,
! (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
! errmsg("UPDATE to non-default value prohibited on GENERATED attributes")));
! }
! if (((new_tle == NULL || (/*adidentity &&*/ adgenerated && !commandOverride)) && commandType == CMD_INSERT) ||
(new_tle && new_tle->expr && IsA(new_tle->expr, SetToDefault)))
{
Node *new_expr;
+ /* Skip computing DEFAULT for GENERATED columns */
+ if (adgenerated && !adidentity)
+ {
+ if ((commandType == CMD_INSERT && (!commandOverride || new_tle)) ||
+ (commandType == CMD_UPDATE && new_tle))
+ modified_fields = lappend_int(modified_fields, attrno);
+ continue;
+ }
+
new_expr = build_column_default(target_relation, attrno);
/*
*************** rewriteTargetList(Query *parsetree, Rela
*** 668,679 ****
--- 692,708 ----
}
if (new_tle)
+ {
+ if (!adgenerated || adidentity)
+ modified_fields = lappend_int(modified_fields, attrno);
new_tlist = lappend(new_tlist, new_tle);
+ }
}
pfree(new_tles);
parsetree->targetList = list_concat(new_tlist, junk_tlist);
+ parsetree->modified_fields = modified_fields;
}
*************** build_column_default(Relation rel, int a
*** 902,907 ****
--- 931,968 ----
}
+ void
+ column_default_properties(Relation rel, int attrno, bool *adidentity, bool *adgenerated)
+ {
+ TupleDesc rd_att = rel->rd_att;
+ bool adident = false;
+ bool adgen = false;
+
+ /*
+ * Scan to see if relation has a default for this column.
+ */
+ if (rd_att->constr && rd_att->constr->num_defval > 0)
+ {
+ AttrDefault *defval = rd_att->constr->defval;
+ int ndef = rd_att->constr->num_defval;
+
+ while (--ndef >= 0)
+ {
+ if (attrno == defval[ndef].adnum)
+ {
+ adident = defval[ndef].adidentity;
+ adgen = defval[ndef].adgenerated;
+ break;
+ }
+ }
+ }
+ if (adidentity)
+ *adidentity = adident;
+ if (adgenerated)
+ *adgenerated = adgen;
+ }
+
+
/* Does VALUES RTE contain any SetToDefault items? */
static bool
searchForDefault(RangeTblEntry *rte)
diff -dcrpN pgsql.orig/src/backend/tcop/pquery.c pgsql/src/backend/tcop/pquery.c
*** pgsql.orig/src/backend/tcop/pquery.c 2007-03-13 11:39:50.000000000 +0100
--- pgsql/src/backend/tcop/pquery.c 2007-04-14 14:37:16.000000000 +0200
*************** CreateQueryDesc(PlannedStmt *plannedstmt
*** 79,84 ****
--- 79,85 ----
qd->tupDesc = NULL;
qd->estate = NULL;
qd->planstate = NULL;
+ qd->modified_fields = plannedstmt->modified_fields;
return qd;
}
diff -dcrpN pgsql.orig/src/backend/utils/adt/ruleutils.c pgsql/src/backend/utils/adt/ruleutils.c
*** pgsql.orig/src/backend/utils/adt/ruleutils.c 2007-03-28 17:18:45.000000000 +0200
--- pgsql/src/backend/utils/adt/ruleutils.c 2007-04-13 13:21:28.000000000 +0200
*************** pg_get_serial_sequence(PG_FUNCTION_ARGS)
*** 1262,1272 ****
Oid tableOid;
char *column;
AttrNumber attnum;
! Oid sequenceId = InvalidOid;
! Relation depRel;
! ScanKeyData key[3];
! SysScanDesc scan;
! HeapTuple tup;
/* Get the OID of the table */
tablerv = makeRangeVarFromNameList(textToQualifiedNameList(tablename));
--- 1262,1268 ----
Oid tableOid;
char *column;
AttrNumber attnum;
! Oid sequenceId;
/* Get the OID of the table */
tablerv = makeRangeVarFromNameList(textToQualifiedNameList(tablename));
*************** pg_get_serial_sequence(PG_FUNCTION_ARGS)
*** 1283,1328 ****
errmsg("column \"%s\" of relation \"%s\" does not exist",
column, tablerv->relname)));
! /* Search the dependency table for the dependent sequence */
! depRel = heap_open(DependRelationId, AccessShareLock);
!
! ScanKeyInit(&key[0],
! Anum_pg_depend_refclassid,
! BTEqualStrategyNumber, F_OIDEQ,
! ObjectIdGetDatum(RelationRelationId));
! ScanKeyInit(&key[1],
! Anum_pg_depend_refobjid,
! BTEqualStrategyNumber, F_OIDEQ,
! ObjectIdGetDatum(tableOid));
! ScanKeyInit(&key[2],
! Anum_pg_depend_refobjsubid,
! BTEqualStrategyNumber, F_INT4EQ,
! Int32GetDatum(attnum));
!
! scan = systable_beginscan(depRel, DependReferenceIndexId, true,
! SnapshotNow, 3, key);
!
! while (HeapTupleIsValid(tup = systable_getnext(scan)))
! {
! Form_pg_depend deprec = (Form_pg_depend) GETSTRUCT(tup);
!
! /*
! * We assume any auto dependency of a sequence on a column must be
! * what we are looking for. (We need the relkind test because indexes
! * can also have auto dependencies on columns.)
! */
! if (deprec->classid == RelationRelationId &&
! deprec->objsubid == 0 &&
! deprec->deptype == DEPENDENCY_AUTO &&
! get_rel_relkind(deprec->objid) == RELKIND_SEQUENCE)
! {
! sequenceId = deprec->objid;
! break;
! }
! }
!
! systable_endscan(scan);
! heap_close(depRel, AccessShareLock);
if (OidIsValid(sequenceId))
{
--- 1279,1285 ----
errmsg("column \"%s\" of relation \"%s\" does not exist",
column, tablerv->relname)));
! sequenceId = get_relid_att_serial_sequence(tableOid, attnum);
if (OidIsValid(sequenceId))
{
diff -dcrpN pgsql.orig/src/backend/utils/cache/lsyscache.c pgsql/src/backend/utils/cache/lsyscache.c
*** pgsql.orig/src/backend/utils/cache/lsyscache.c 2007-04-04 10:12:37.000000000 +0200
--- pgsql/src/backend/utils/cache/lsyscache.c 2007-04-13 13:27:10.000000000 +0200
***************
*** 15,26 ****
--- 15,31 ----
*/
#include "postgres.h"
+ #include "access/genam.h"
#include "access/hash.h"
+ #include "access/heapam.h"
#include "access/nbtree.h"
#include "bootstrap/bootstrap.h"
+ #include "catalog/dependency.h"
+ #include "catalog/indexing.h"
#include "catalog/pg_amop.h"
#include "catalog/pg_amproc.h"
#include "catalog/pg_constraint.h"
+ #include "catalog/pg_depend.h"
#include "catalog/pg_namespace.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
***************
*** 32,37 ****
--- 37,43 ----
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/datum.h"
+ #include "utils/fmgroids.h"
#include "utils/lsyscache.h"
#include "utils/syscache.h"
*************** get_relid_attribute_name(Oid relid, Attr
*** 786,791 ****
--- 792,855 ----
}
/*
+ * get_relid_attribute_sequence
+ * Expose pg_get_serial_sequence() internally.
+ * Assumes that attnum is valid for the table.
+ */
+ Oid
+ get_relid_att_serial_sequence(Oid relid, AttrNumber attnum)
+ {
+ Oid sequenceId = InvalidOid;
+ Relation depRel;
+ ScanKeyData key[3];
+ SysScanDesc scan;
+ HeapTuple tup;
+
+ /* Search the dependency table for the dependent sequence */
+ depRel = heap_open(DependRelationId, AccessShareLock);
+
+ ScanKeyInit(&key[0],
+ Anum_pg_depend_refclassid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationRelationId));
+ ScanKeyInit(&key[1],
+ Anum_pg_depend_refobjid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(relid));
+ ScanKeyInit(&key[2],
+ Anum_pg_depend_refobjsubid,
+ BTEqualStrategyNumber, F_INT4EQ,
+ Int32GetDatum(attnum));
+
+ scan = systable_beginscan(depRel, DependReferenceIndexId, true,
+ SnapshotNow, 3, key);
+
+ while (HeapTupleIsValid(tup = systable_getnext(scan)))
+ {
+ Form_pg_depend deprec = (Form_pg_depend) GETSTRUCT(tup);
+
+ /*
+ * We assume any auto dependency of a sequence on a column must be
+ * what we are looking for. (We need the relkind test because indexes
+ * can also have auto dependencies on columns.)
+ */
+ if (deprec->classid == RelationRelationId &&
+ deprec->objsubid == 0 &&
+ deprec->deptype == DEPENDENCY_AUTO &&
+ get_rel_relkind(deprec->objid) == RELKIND_SEQUENCE)
+ {
+ sequenceId = deprec->objid;
+ break;
+ }
+ }
+
+ systable_endscan(scan);
+ heap_close(depRel, AccessShareLock);
+
+ return sequenceId;
+ }
+
+ /*
* get_attnum
*
* Given the relation id and the attribute name,
diff -dcrpN pgsql.orig/src/backend/utils/cache/relcache.c pgsql/src/backend/utils/cache/relcache.c
*** pgsql.orig/src/backend/utils/cache/relcache.c 2007-03-30 09:33:56.000000000 +0200
--- pgsql/src/backend/utils/cache/relcache.c 2007-04-14 22:56:19.000000000 +0200
*************** RelationBuildTupleDesc(Relation relation
*** 485,490 ****
--- 485,492 ----
sizeof(AttrDefault));
attrdef[ndef].adnum = attp->attnum;
attrdef[ndef].adbin = NULL;
+ attrdef[ndef].adidentity = false;
+ attrdef[ndef].adgenerated = false;
ndef++;
}
need--;
*************** AttrDefaultFetch(Relation relation)
*** 2701,2706 ****
--- 2703,2711 ----
else
found++;
+ attrdef[i].adidentity = adform->adidentity;
+ attrdef[i].adgenerated = adform->adgenerated;
+
val = fastgetattr(htup,
Anum_pg_attrdef_adbin,
adrel->rd_att, &isnull);
diff -dcrpN pgsql.orig/src/bin/pg_dump/pg_dump.c pgsql/src/bin/pg_dump/pg_dump.c
*** pgsql.orig/src/bin/pg_dump/pg_dump.c 2007-04-04 10:12:40.000000000 +0200
--- pgsql/src/bin/pg_dump/pg_dump.c 2007-04-15 00:10:16.000000000 +0200
*************** dumpTableData_insert(Archive *fout, void
*** 1192,1197 ****
--- 1192,1199 ----
appendPQExpBuffer(q, ") ");
archputs(q->data, fout);
}
+ if (tbinfo->hasgenerated)
+ archprintf(fout, "OVERRIDING SYSTEM VALUE ");
archprintf(fout, "VALUES (");
for (field = 0; field < nfields; field++)
{
*************** dumpTableData(Archive *fout, TableDataIn
*** 1293,1300 ****
/* must use 2 steps here 'cause fmtId is nonreentrant */
appendPQExpBuffer(copyBuf, "COPY %s ",
fmtId(tbinfo->dobj.name));
! appendPQExpBuffer(copyBuf, "%s %sFROM stdin;\n",
fmtCopyColumnList(tbinfo),
(tdinfo->oids && tbinfo->hasoids) ? "WITH OIDS " : "");
copyStmt = copyBuf->data;
}
--- 1295,1303 ----
/* must use 2 steps here 'cause fmtId is nonreentrant */
appendPQExpBuffer(copyBuf, "COPY %s ",
fmtId(tbinfo->dobj.name));
! appendPQExpBuffer(copyBuf, "%s %s%sFROM stdin;\n",
fmtCopyColumnList(tbinfo),
+ tbinfo->hasgenerated ? "OVERRIDING SYSTEM VALUE " : "",
(tdinfo->oids && tbinfo->hasoids) ? "WITH OIDS " : "");
copyStmt = copyBuf->data;
}
*************** getTableAttrs(TableInfo *tblinfo, int nu
*** 4436,4445 ****
tbinfo->dobj.name);
resetPQExpBuffer(q);
! if (g_fout->remoteVersion >= 70300)
{
appendPQExpBuffer(q, "SELECT tableoid, oid, adnum, "
! "pg_catalog.pg_get_expr(adbin, adrelid) AS adsrc "
"FROM pg_catalog.pg_attrdef "
"WHERE adrelid = '%u'::pg_catalog.oid",
tbinfo->dobj.catId.oid);
--- 4439,4458 ----
tbinfo->dobj.name);
resetPQExpBuffer(q);
! if (g_fout->remoteVersion >= 80300)
{
appendPQExpBuffer(q, "SELECT tableoid, oid, adnum, "
! "pg_catalog.pg_get_expr(adbin, adrelid) AS adsrc, "
! "adidentity, adgenerated "
! "FROM pg_catalog.pg_attrdef "
! "WHERE adrelid = '%u'::pg_catalog.oid",
! tbinfo->dobj.catId.oid);
! }
! else if (g_fout->remoteVersion >= 70300)
! {
! appendPQExpBuffer(q, "SELECT tableoid, oid, adnum, "
! "pg_catalog.pg_get_expr(adbin, adrelid) AS adsrc, "
! "false as adidentity, false as adgenerated "
"FROM pg_catalog.pg_attrdef "
"WHERE adrelid = '%u'::pg_catalog.oid",
tbinfo->dobj.catId.oid);
*************** getTableAttrs(TableInfo *tblinfo, int nu
*** 4448,4454 ****
{
/* 7.2 did not have OIDs in pg_attrdef */
appendPQExpBuffer(q, "SELECT tableoid, 0 as oid, adnum, "
! "pg_get_expr(adbin, adrelid) AS adsrc "
"FROM pg_attrdef "
"WHERE adrelid = '%u'::oid",
tbinfo->dobj.catId.oid);
--- 4461,4468 ----
{
/* 7.2 did not have OIDs in pg_attrdef */
appendPQExpBuffer(q, "SELECT tableoid, 0 as oid, adnum, "
! "pg_get_expr(adbin, adrelid) AS adsrc, "
! "false as adidentity, false as adgenerated "
"FROM pg_attrdef "
"WHERE adrelid = '%u'::oid",
tbinfo->dobj.catId.oid);
*************** getTableAttrs(TableInfo *tblinfo, int nu
*** 4456,4462 ****
else if (g_fout->remoteVersion >= 70100)
{
/* no pg_get_expr, so must rely on adsrc */
! appendPQExpBuffer(q, "SELECT tableoid, oid, adnum, adsrc "
"FROM pg_attrdef "
"WHERE adrelid = '%u'::oid",
tbinfo->dobj.catId.oid);
--- 4470,4477 ----
else if (g_fout->remoteVersion >= 70100)
{
/* no pg_get_expr, so must rely on adsrc */
! appendPQExpBuffer(q, "SELECT tableoid, oid, adnum, adsrc, "
! "false as adidentity, false as adgenerated "
"FROM pg_attrdef "
"WHERE adrelid = '%u'::oid",
tbinfo->dobj.catId.oid);
*************** getTableAttrs(TableInfo *tblinfo, int nu
*** 4466,4472 ****
/* no pg_get_expr, no tableoid either */
appendPQExpBuffer(q, "SELECT "
"(SELECT oid FROM pg_class WHERE relname = 'pg_attrdef') AS tableoid, "
! "oid, adnum, adsrc "
"FROM pg_attrdef "
"WHERE adrelid = '%u'::oid",
tbinfo->dobj.catId.oid);
--- 4481,4488 ----
/* no pg_get_expr, no tableoid either */
appendPQExpBuffer(q, "SELECT "
"(SELECT oid FROM pg_class WHERE relname = 'pg_attrdef') AS tableoid, "
! "oid, adnum, adsrc, "
! "false as adidentity, false as adgenerated "
"FROM pg_attrdef "
"WHERE adrelid = '%u'::oid",
tbinfo->dobj.catId.oid);
*************** getTableAttrs(TableInfo *tblinfo, int nu
*** 4488,4493 ****
--- 4504,4513 ----
attrdefs[j].adtable = tbinfo;
attrdefs[j].adnum = adnum = atoi(PQgetvalue(res, j, 2));
attrdefs[j].adef_expr = strdup(PQgetvalue(res, j, 3));
+ attrdefs[j].identity = (PQgetvalue(res, j, 4)[0] == 't');
+ attrdefs[j].generated = (PQgetvalue(res, j, 5)[0] == 't');
+ if (attrdefs[j].generated)
+ tbinfo->hasgenerated = true;
attrdefs[j].dobj.name = strdup(tbinfo->dobj.name);
attrdefs[j].dobj.namespace = tbinfo->dobj.namespace;
*************** getTableAttrs(TableInfo *tblinfo, int nu
*** 4501,4507 ****
* safe, we mark the default as needing to appear before the
* CREATE.
*/
! if (tbinfo->relkind == RELKIND_VIEW)
{
attrdefs[j].separate = true;
/* needed in case pre-7.3 DB: */
--- 4521,4528 ----
* safe, we mark the default as needing to appear before the
* CREATE.
*/
! if (tbinfo->relkind == RELKIND_VIEW ||
! attrdefs[j].identity || attrdefs[j].generated)
{
attrdefs[j].separate = true;
/* needed in case pre-7.3 DB: */
*************** dumpAttrDef(Archive *fout, AttrDefInfo *
*** 8147,8154 ****
appendPQExpBuffer(q, "ALTER TABLE %s ",
fmtId(tbinfo->dobj.name));
! appendPQExpBuffer(q, "ALTER COLUMN %s SET DEFAULT %s;\n",
! fmtId(tbinfo->attnames[adnum - 1]),
adinfo->adef_expr);
/*
--- 8168,8185 ----
appendPQExpBuffer(q, "ALTER TABLE %s ",
fmtId(tbinfo->dobj.name));
! appendPQExpBuffer(q, "ALTER COLUMN %s ",
! fmtId(tbinfo->attnames[adnum - 1]));
!
! if (adinfo->identity)
! appendPQExpBuffer(q, "SET IDENTITY GENERATED %s;\n",
! adinfo->generated ?
! "ALWAYS" : "BY DEFAULT");
! else if (adinfo->generated)
! appendPQExpBuffer(q, "SET GENERATED ALWAYS AS ( %s );\n",
! adinfo->adef_expr);
! else
! appendPQExpBuffer(q, "SET DEFAULT %s;\n",
adinfo->adef_expr);
/*
diff -dcrpN pgsql.orig/src/bin/pg_dump/pg_dump.h pgsql/src/bin/pg_dump/pg_dump.h
*** pgsql.orig/src/bin/pg_dump/pg_dump.h 2007-03-23 12:18:57.000000000 +0100
--- pgsql/src/bin/pg_dump/pg_dump.h 2007-04-14 23:25:03.000000000 +0200
*************** typedef struct _tableInfo
*** 265,270 ****
--- 265,271 ----
* were inherited.
*/
bool *notnull; /* Not null constraints on attributes */
+ bool hasgenerated; /* has at least one GENERATED ALWAYS columns */
struct _attrDefInfo **attrdefs; /* DEFAULT expressions */
bool *inhAttrs; /* true if each attribute is inherited */
bool *inhAttrDef; /* true if attr's default is inherited */
*************** typedef struct _attrDefInfo
*** 285,290 ****
--- 286,293 ----
int adnum;
char *adef_expr; /* decompiled DEFAULT expression */
bool separate; /* TRUE if must dump as separate item */
+ bool identity; /* GENERATED ... AS IDENTITY */
+ bool generated; /* GENERATED ALWAYS */
} AttrDefInfo;
typedef struct _tableDataInfo
diff -dcrpN pgsql.orig/src/include/access/tupdesc.h pgsql/src/include/access/tupdesc.h
*** pgsql.orig/src/include/access/tupdesc.h 2007-01-10 19:59:07.000000000 +0100
--- pgsql/src/include/access/tupdesc.h 2007-04-13 12:36:34.000000000 +0200
*************** typedef struct attrDefault
*** 23,28 ****
--- 23,30 ----
{
AttrNumber adnum;
char *adbin; /* nodeToString representation of expr */
+ bool adidentity;
+ bool adgenerated;
} AttrDefault;
typedef struct constrCheck
diff -dcrpN pgsql.orig/src/include/catalog/heap.h pgsql/src/include/catalog/heap.h
*** pgsql.orig/src/include/catalog/heap.h 2007-01-10 19:59:08.000000000 +0100
--- pgsql/src/include/catalog/heap.h 2007-04-13 13:10:17.000000000 +0200
*************** typedef struct RawColumnDefault
*** 21,26 ****
--- 21,28 ----
{
AttrNumber attnum; /* attribute to attach default to */
Node *raw_default; /* default value (untransformed parse tree) */
+ bool is_identity; /* IDENTITY */
+ bool is_generated; /* GENERATED */
} RawColumnDefault;
typedef struct CookedConstraint
*************** typedef struct CookedConstraint
*** 29,34 ****
--- 31,38 ----
char *name; /* name, or NULL if none */
AttrNumber attnum; /* which attr (only for DEFAULT) */
Node *expr; /* transformed default or check expr */
+ bool is_identity; /* IDENTITY */
+ bool is_generated; /* GENERATED */
} CookedConstraint;
extern Relation heap_create(const char *relname,
*************** extern List *AddRelationRawConstraints(R
*** 71,83 ****
List *rawColDefaults,
List *rawConstraints);
! extern void StoreAttrDefault(Relation rel, AttrNumber attnum, char *adbin);
extern Node *cookDefault(ParseState *pstate,
Node *raw_default,
Oid atttypid,
int32 atttypmod,
! char *attname);
extern int RemoveRelConstraints(Relation rel, const char *constrName,
DropBehavior behavior);
--- 75,89 ----
List *rawColDefaults,
List *rawConstraints);
! extern void StoreAttrDefault(Relation rel, AttrNumber attnum, char *adbin,
! bool adidentity, bool adgenerated);
extern Node *cookDefault(ParseState *pstate,
Node *raw_default,
Oid atttypid,
int32 atttypmod,
! char *attname,
! bool is_generated);
extern int RemoveRelConstraints(Relation rel, const char *constrName,
DropBehavior behavior);
diff -dcrpN pgsql.orig/src/include/catalog/pg_attrdef.h pgsql/src/include/catalog/pg_attrdef.h
*** pgsql.orig/src/include/catalog/pg_attrdef.h 2007-01-10 19:59:08.000000000 +0100
--- pgsql/src/include/catalog/pg_attrdef.h 2007-04-13 12:03:48.000000000 +0200
*************** CATALOG(pg_attrdef,2604)
*** 37,42 ****
--- 37,44 ----
{
Oid adrelid;
int2 adnum;
+ bool adidentity;
+ bool adgenerated;
text adbin;
text adsrc;
} FormData_pg_attrdef;
*************** typedef FormData_pg_attrdef *Form_pg_att
*** 52,61 ****
* compiler constants for pg_attrdef
* ----------------
*/
! #define Natts_pg_attrdef 4
#define Anum_pg_attrdef_adrelid 1
#define Anum_pg_attrdef_adnum 2
! #define Anum_pg_attrdef_adbin 3
! #define Anum_pg_attrdef_adsrc 4
#endif /* PG_ATTRDEF_H */
--- 54,65 ----
* compiler constants for pg_attrdef
* ----------------
*/
! #define Natts_pg_attrdef 6
#define Anum_pg_attrdef_adrelid 1
#define Anum_pg_attrdef_adnum 2
! #define Anum_pg_attrdef_adidentity 3
! #define Anum_pg_attrdef_adgenerated 4
! #define Anum_pg_attrdef_adbin 5
! #define Anum_pg_attrdef_adsrc 6
#endif /* PG_ATTRDEF_H */
diff -dcrpN pgsql.orig/src/include/executor/execdesc.h pgsql/src/include/executor/execdesc.h
*** pgsql.orig/src/include/executor/execdesc.h 2007-02-25 16:14:32.000000000 +0100
--- pgsql/src/include/executor/execdesc.h 2007-04-14 14:38:48.000000000 +0200
*************** typedef struct QueryDesc
*** 47,52 ****
--- 47,54 ----
TupleDesc tupDesc; /* descriptor for result tuples */
EState *estate; /* executor's query-wide state */
PlanState *planstate; /* tree of per-plan-node state */
+ List *modified_fields; /* list of modified regular columns and
+ GENERATED columns that have to be set to DEFAULT */
} QueryDesc;
/* in pquery.c */
diff -dcrpN pgsql.orig/src/include/nodes/execnodes.h pgsql/src/include/nodes/execnodes.h
*** pgsql.orig/src/include/nodes/execnodes.h 2007-03-28 17:22:27.000000000 +0200
--- pgsql/src/include/nodes/execnodes.h 2007-04-14 13:06:15.000000000 +0200
*************** typedef struct PlanState
*** 833,838 ****
--- 833,840 ----
*/
List *targetlist; /* target list to be computed at this node */
List *qual; /* implicitly-ANDed qual conditions */
+ List *overridden; /* list of attnums of explicitly given GENERATED columns
+ * in INSERT ... OVERRIDING SYSTEM VALUE */
struct PlanState *lefttree; /* input plan tree(s) */
struct PlanState *righttree;
List *initPlan; /* Init SubPlanState nodes (un-correlated expr
diff -dcrpN pgsql.orig/src/include/nodes/parsenodes.h pgsql/src/include/nodes/parsenodes.h
*** pgsql.orig/src/include/nodes/parsenodes.h 2007-04-12 13:15:45.000000000 +0200
--- pgsql/src/include/nodes/parsenodes.h 2007-04-14 14:31:13.000000000 +0200
*************** typedef struct Query
*** 129,134 ****
--- 129,137 ----
Node *setOperations; /* set-operation tree if this is top level of
* a UNION/INTERSECT/EXCEPT query */
+ bool commandOverride; /* OVERRIDING SYSTEM VALUE */
+ List *modified_fields; /* list of modified regular columns and
+ * GENERATED columns that have to be set to DEFAULT */
} Query;
*************** typedef struct ColumnDef
*** 389,394 ****
--- 392,399 ----
Node *raw_default; /* default value (untransformed parse tree) */
char *cooked_default; /* nodeToString representation */
List *constraints; /* other constraints on column */
+ bool is_identity; /* IDENTITY */
+ bool is_generated; /* GENERATED */
} ColumnDef;
/*
*************** typedef struct InsertStmt
*** 673,678 ****
--- 678,684 ----
List *cols; /* optional: names of the target columns */
Node *selectStmt; /* the source SELECT/VALUES, or NULL */
List *returningList; /* list of expressions to return */
+ bool commandOverride; /* OVERRIDE SYSTEM VALUE */
} InsertStmt;
/* ----------------------
*************** typedef enum AlterTableType
*** 909,915 ****
AT_EnableReplicaRule, /* ENABLE REPLICA RULE name */
AT_DisableRule, /* DISABLE RULE name */
AT_AddInherit, /* INHERIT parent */
! AT_DropInherit /* NO INHERIT parent */
} AlterTableType;
typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */
--- 915,925 ----
AT_EnableReplicaRule, /* ENABLE REPLICA RULE name */
AT_DisableRule, /* DISABLE RULE name */
AT_AddInherit, /* INHERIT parent */
! AT_DropInherit, /* NO INHERIT parent */
! AT_SetSeqOpts, /* SET sequence_options for IDENTITY columns */
! AT_SetIdentity, /* SET IDENTITY */
! AT_DropIdentity, /* DROP IDENTITY */
! AT_SetGenerated /* SET GENERATED */
} AlterTableType;
typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */
*************** typedef struct AlterTableCmd /* one subc
*** 922,927 ****
--- 932,940 ----
* index, constraint, or parent table */
Node *transform; /* transformation expr for ALTER TYPE */
DropBehavior behavior; /* RESTRICT or CASCADE for DROP cases */
+ bool is_identity;
+ bool is_generated; /* Parameters for SET/DROP IDENTITY and
+ * SET/DROP GENERATED */
} AlterTableCmd;
*************** typedef struct CopyStmt
*** 1040,1045 ****
--- 1053,1059 ----
bool is_from; /* TO or FROM */
char *filename; /* filename, or NULL for STDIN/STDOUT */
List *options; /* List of DefElem nodes */
+ bool overriding; /* OVERRIDING SYSTEM VALUE */
} CopyStmt;
/* ----------------------
*************** typedef enum ConstrType /* types of co
*** 1099,1105 ****
CONSTR_ATTR_DEFERRABLE, /* attributes for previous constraint node */
CONSTR_ATTR_NOT_DEFERRABLE,
CONSTR_ATTR_DEFERRED,
! CONSTR_ATTR_IMMEDIATE
} ConstrType;
typedef struct Constraint
--- 1113,1120 ----
CONSTR_ATTR_DEFERRABLE, /* attributes for previous constraint node */
CONSTR_ATTR_NOT_DEFERRABLE,
CONSTR_ATTR_DEFERRED,
! CONSTR_ATTR_IMMEDIATE,
! CONSTR_IDENTITY
} ConstrType;
typedef struct Constraint
*************** typedef struct Constraint
*** 1113,1118 ****
--- 1128,1136 ----
List *options; /* options from WITH clause */
char *indexspace; /* index tablespace for PKEY/UNIQUE
* constraints; NULL for default */
+ bool is_identity; /* IDENTITY */
+ bool is_generated; /* GENERATED */
+ List *seq_opts; /* sequence options for IDENTITY */
} Constraint;
/* ----------
diff -dcrpN pgsql.orig/src/include/nodes/plannodes.h pgsql/src/include/nodes/plannodes.h
*** pgsql.orig/src/include/nodes/plannodes.h 2007-02-27 08:27:40.000000000 +0100
--- pgsql/src/include/nodes/plannodes.h 2007-04-14 14:31:04.000000000 +0200
*************** typedef struct PlannedStmt
*** 67,72 ****
--- 67,74 ----
List *rowMarks; /* a list of RowMarkClause's */
int nParamExec; /* number of PARAM_EXEC Params used */
+ List *modified_fields; /* list of modified regular columns and
+ GENERATED columns that have to be set to DEFAULT */
} PlannedStmt;
/* macro for fetching the Plan associated with a SubPlan node */
diff -dcrpN pgsql.orig/src/include/rewrite/rewriteHandler.h pgsql/src/include/rewrite/rewriteHandler.h
*** pgsql.orig/src/include/rewrite/rewriteHandler.h 2007-01-10 19:59:13.000000000 +0100
--- pgsql/src/include/rewrite/rewriteHandler.h 2007-04-13 13:17:35.000000000 +0200
***************
*** 20,24 ****
--- 20,25 ----
extern List *QueryRewrite(Query *parsetree);
extern void AcquireRewriteLocks(Query *parsetree);
extern Node *build_column_default(Relation rel, int attrno);
+ extern void column_default_properties(Relation rel, int attrno, bool *adidentity, bool *adgenerated);
#endif /* REWRITEHANDLER_H */
diff -dcrpN pgsql.orig/src/include/utils/lsyscache.h pgsql/src/include/utils/lsyscache.h
*** pgsql.orig/src/include/utils/lsyscache.h 2007-04-04 10:12:43.000000000 +0200
--- pgsql/src/include/utils/lsyscache.h 2007-04-13 13:34:42.000000000 +0200
*************** extern Oid get_opfamily_proc(Oid opfamil
*** 53,58 ****
--- 53,59 ----
int16 procnum);
extern char *get_attname(Oid relid, AttrNumber attnum);
extern char *get_relid_attribute_name(Oid relid, AttrNumber attnum);
+ extern Oid get_relid_att_serial_sequence(Oid relid, AttrNumber attnum);
extern AttrNumber get_attnum(Oid relid, const char *attname);
extern Oid get_atttype(Oid relid, AttrNumber attnum);
extern int32 get_atttypmod(Oid relid, AttrNumber attnum);
diff -dcrpN pgsql.orig/src/test/regress/expected/identity.out pgsql/src/test/regress/expected/identity.out
*** pgsql.orig/src/test/regress/expected/identity.out 1970-01-01 01:00:00.000000000 +0100
--- pgsql/src/test/regress/expected/identity.out 2007-04-14 22:47:27.000000000 +0200
***************
*** 0 ****
--- 1,841 ----
+ --
+ -- IDENTITY
+ -- 2006.08.01 Zoltan Boszormenyi
+ --
+ --
+ -- Test GENERATED BY DEFAULT AS IDENTITY with default sequence
+ --
+ create table id (id serial generated by default as identity primary key, name text);
+ NOTICE: CREATE TABLE will create implicit sequence "id_id_seq" for serial column "id.id"
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "id_pkey" for table "id"
+ insert into id (name) values ('a');
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (0, 'a');
+ --
+ -- This must complain about UNIQUE violation
+ --
+ insert into id (id, name) values (0, 'a');
+ ERROR: duplicate key violates unique constraint "id_pkey"
+ select * from id;
+ id | name
+ ----+------
+ 1 | a
+ 2 | a
+ 3 | a
+ 4 | a
+ 0 | a
+ (5 rows)
+
+ --
+ -- This must complain because SERIAL and IDENTITY has
+ -- implicit NOT NULL constraint
+ --
+ insert into id (id, name) values (null, 'a');
+ ERROR: null value in column "id" violates not-null constraint
+ select * from id;
+ id | name
+ ----+------
+ 1 | a
+ 2 | a
+ 3 | a
+ 4 | a
+ 0 | a
+ (5 rows)
+
+ update id set id = default where id = 0;
+ select * from id;
+ id | name
+ ----+------
+ 1 | a
+ 2 | a
+ 3 | a
+ 4 | a
+ 5 | a
+ (5 rows)
+
+ drop table id;
+ --
+ -- Test GENERATED BY DEFAULT AS IDENTITY with modified sequence
+ --
+ create table id (id serial generated by default as identity(start 3) primary key, name text);
+ NOTICE: CREATE TABLE will create implicit sequence "id_id_seq" for serial column "id.id"
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "id_pkey" for table "id"
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (0, 'a');
+ --
+ -- This must complain about UNIQUE violation
+ --
+ insert into id (id, name) values (0, 'a');
+ ERROR: duplicate key violates unique constraint "id_pkey"
+ select * from id;
+ id | name
+ ----+------
+ 3 | a
+ 4 | a
+ 5 | a
+ 0 | a
+ (4 rows)
+
+ drop table id;
+ --
+ -- Test GENERATED ALWAYS AS IDENTITY with default sequence
+ --
+ create table id (id serial generated always as identity primary key, name text);
+ NOTICE: CREATE TABLE will create implicit sequence "id_id_seq" for serial column "id.id"
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "id_pkey" for table "id"
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (0, 'a');
+ insert into id (id, name) values (0, 'a');
+ insert into id (id, name) values (0, 'a');
+ select * from id;
+ id | name
+ ----+------
+ 1 | a
+ 2 | a
+ 3 | a
+ 4 | a
+ 5 | a
+ 6 | a
+ (6 rows)
+
+ --
+ -- It should update 0 rows
+ --
+ update id set name = 'b' where id = 0;
+ select * from id;
+ id | name
+ ----+------
+ 1 | a
+ 2 | a
+ 3 | a
+ 4 | a
+ 5 | a
+ 6 | a
+ (6 rows)
+
+ drop table id;
+ --
+ -- Test GENERATED ALWAYS AS IDENTITY with modified sequence
+ --
+ create table id (id serial generated always as identity(start 3) primary key, name text);
+ NOTICE: CREATE TABLE will create implicit sequence "id_id_seq" for serial column "id.id"
+ NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "id_pkey" for table "id"
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (0, 'a');
+ insert into id (id, name) values (0, 'a');
+ insert into id (id, name) values (0, 'a');
+ select * from id;
+ id | name
+ ----+------
+ 3 | a
+ 4 | a
+ 5 | a
+ 6 | a
+ 7 | a
+ 8 | a
+ (6 rows)
+
+ --
+ -- It should update 0 rows
+ --
+ update id set name = 'b' where id = 0;
+ select * from id;
+ id | name
+ ----+------
+ 3 | a
+ 4 | a
+ 5 | a
+ 6 | a
+ 7 | a
+ 8 | a
+ (6 rows)
+
+ drop table id;
+ --
+ -- Test GENERATED BY DEFAULT AS (expr)
+ -- It must give a syntax error but drop it anyway.
+ --
+ create table id (id integer generated by default as (1), name text);
+ ERROR: syntax error at or near "("
+ LINE 1: ...eate table id (id integer generated by default as (1), name ...
+ ^
+ drop table id;
+ ERROR: table "id" does not exist
+ --
+ -- Test GENERATED ALWAYS AS ( expr )
+ --
+ create table id (id integer generated always as (1), name text);
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (0, 'a');
+ select * from id;
+ id | name
+ ----+------
+ 1 | a
+ 1 | a
+ (2 rows)
+
+ --
+ -- It should update 0 rows
+ --
+ update id set name = 'b' where id = 0;
+ select * from id;
+ id | name
+ ----+------
+ 1 | a
+ 1 | a
+ (2 rows)
+
+ drop table id;
+ --
+ -- Test IDENTITY with non-integer columns
+ --
+ create table id (id decimal(10,2) generated always as identity(start 3), name text);
+ NOTICE: CREATE TABLE will create implicit sequence "id_id_seq" for serial column "id.id"
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (0, 'a');
+ select * from id;
+ id | name
+ ------+------
+ 3.00 | a
+ 4.00 | a
+ (2 rows)
+
+ alter table id alter id type real;
+ insert into id (id, name) values (0, 'a');
+ select * from id;
+ id | name
+ ----+------
+ 3 | a
+ 4 | a
+ 5 | a
+ (3 rows)
+
+ alter table id alter id type integer;
+ insert into id (id, name) values (0, 'a');
+ select * from id;
+ id | name
+ ----+------
+ 3 | a
+ 4 | a
+ 5 | a
+ 6 | a
+ (4 rows)
+
+ --
+ -- It should update 0 rows
+ --
+ update id set name = 'b' where id = 0;
+ select * from id;
+ id | name
+ ----+------
+ 3 | a
+ 4 | a
+ 5 | a
+ 6 | a
+ (4 rows)
+
+ update id set name = 'b' where id = 5;
+ select * from id;
+ id | name
+ ----+------
+ 3 | a
+ 4 | a
+ 6 | a
+ 5 | b
+ (4 rows)
+
+ drop table id;
+ --
+ -- Test OVERRIDING and UPDATE on GENERATED ALWAYS columns
+ --
+ create table id (id serial generated always as identity, name text generated always as ('x'));
+ NOTICE: CREATE TABLE will create implicit sequence "id_id_seq" for serial column "id.id"
+ insert into id (id, name) values (0, 'a');
+ insert into id (id, name) overriding user value values (0, 'a');
+ insert into id (id, name) overriding system value values (0, 'a');
+ update id set id = 6 where id = 0;
+ select * from id;
+ id | name
+ ----+------
+ 1 | x
+ 2 | x
+ 6 | a
+ (3 rows)
+
+ update id set id = default where id = 6;
+ select * from id;
+ id | name
+ ----+------
+ 1 | x
+ 2 | x
+ 3 | a
+ (3 rows)
+
+ --
+ -- It's expected to fail because of GENERATED
+ --
+ update id set name = 'b' where id = 3;
+ ERROR: UPDATE to non-default value prohibited on GENERATED attributes
+ --
+ -- It's expected to succeed
+ --
+ update id set name = default where id = 3;
+ select * from id;
+ id | name
+ ----+------
+ 1 | x
+ 2 | x
+ 3 | x
+ (3 rows)
+
+ --
+ -- Test ALTER TABLE sequence options
+ --
+ alter table id alter id restart with 10;
+ insert into id (id, name) values (0, 'a');
+ alter table id alter id set increment by 5;
+ insert into id (id, name) values (0, 'a');
+ select * from id;
+ id | name
+ ----+------
+ 1 | x
+ 2 | x
+ 3 | x
+ 10 | x
+ 15 | x
+ (5 rows)
+
+ --
+ -- Test ALTER TABLE RENAME
+ --
+ select pg_get_serial_sequence('id', 'id');
+ pg_get_serial_sequence
+ ------------------------
+ public.id_id_seq
+ (1 row)
+
+ alter table id rename id to di;
+ NOTICE: supporting sequence for column "di" renamed to "id_di_seq"
+ insert into id (di, name) values (0, 'a');
+ select pg_get_serial_sequence('id', 'di');
+ pg_get_serial_sequence
+ ------------------------
+ public.id_di_seq
+ (1 row)
+
+ alter table id rename to di;
+ NOTICE: supporting sequence for column "di" renamed to "di_di_seq"
+ insert into di (di, name) values (0, 'a');
+ select pg_get_serial_sequence('di', 'di');
+ pg_get_serial_sequence
+ ------------------------
+ public.di_di_seq
+ (1 row)
+
+ select * from di;
+ di | name
+ ----+------
+ 1 | x
+ 2 | x
+ 3 | x
+ 10 | x
+ 15 | x
+ 20 | x
+ 25 | x
+ (7 rows)
+
+ --
+ -- Test SET/DROP DEFAULT
+ --
+ alter table di alter di drop default;
+ alter table di alter di set default 1;
+ insert into di (di, name) values (default, 'a');
+ select * from di;
+ di | name
+ ----+------
+ 1 | x
+ 2 | x
+ 3 | x
+ 10 | x
+ 15 | x
+ 20 | x
+ 25 | x
+ 1 | x
+ (8 rows)
+
+ alter table di alter di set identity generated always;
+ insert into di (di, name) values (default, 'a');
+ select * from di;
+ di | name
+ ----+------
+ 1 | x
+ 2 | x
+ 3 | x
+ 10 | x
+ 15 | x
+ 20 | x
+ 25 | x
+ 1 | x
+ 30 | x
+ (9 rows)
+
+ alter table di alter di set generated always as ( nextval(pg_get_serial_sequence('di', 'di')) );
+ insert into di (di, name) values (default, 'a');
+ select * from di;
+ di | name
+ ----+------
+ 1 | x
+ 2 | x
+ 3 | x
+ 10 | x
+ 15 | x
+ 20 | x
+ 25 | x
+ 1 | x
+ 30 | x
+ 35 | x
+ (10 rows)
+
+ alter table di alter di set identity generated always;
+ insert into di (di, name) values (default, 'a');
+ select * from di;
+ di | name
+ ----+------
+ 1 | x
+ 2 | x
+ 3 | x
+ 10 | x
+ 15 | x
+ 20 | x
+ 25 | x
+ 1 | x
+ 30 | x
+ 35 | x
+ 40 | x
+ (11 rows)
+
+ --
+ -- Test ALTER TABLE DROP COLUMN
+ --
+ alter table di add column id integer;
+ update di set id = di;
+ alter table di drop column di;
+ select * from di;
+ name | id
+ ------+----
+ x | 1
+ x | 2
+ x | 3
+ x | 10
+ x | 15
+ x | 20
+ x | 25
+ x | 1
+ x | 30
+ x | 35
+ x | 40
+ (11 rows)
+
+ drop table di;
+ --
+ -- Test multiple IDENTITY columns in a single table
+ --
+ -- This must fail, no multiple IDENTITY allowed
+ create table id (
+ id1 serial generated always as identity (start 3),
+ id2 serial generated always as identity (start 5)
+ );
+ NOTICE: CREATE TABLE will create implicit sequence "id_id1_seq" for serial column "id.id1"
+ NOTICE: CREATE TABLE will create implicit sequence "id_id2_seq" for serial column "id.id2"
+ ERROR: multiple IDENTITY columns are not allowed
+ -- This succeeds
+ create table id (id1 serial generated always as identity (start 3), t text not null);
+ NOTICE: CREATE TABLE will create implicit sequence "id_id1_seq" for serial column "id.id1"
+ insert into id (t) values (null) returning id1;
+ ERROR: null value in column "t" violates not-null constraint
+ insert into id (t) values ('a') returning id1;
+ id1
+ -----
+ 4
+ (1 row)
+
+ select * from id;
+ id1 | t
+ -----+---
+ 4 | a
+ (1 row)
+
+ -- This must fail, no multiple IDENTITY allowed
+ alter table id add id2 serial generated always as identity (start 5);
+ NOTICE: ALTER TABLE will create implicit sequence "id_id2_seq" for serial column "id.id2"
+ ERROR: multiple IDENTITY columns are not allowed
+ -- Downgrade id1 to normal serial
+ alter table id alter id1 drop identity;
+ --
+ -- Try to drop IDENTITY again, it fails
+ --
+ alter table id alter id1 drop identity;
+ ERROR: column "id1" of relation "id" is a non-IDENTITY serial column
+ insert into id (t) values (null) returning id1;
+ ERROR: null value in column "t" violates not-null constraint
+ insert into id (t) values ('a') returning id1;
+ id1
+ -----
+ 6
+ (1 row)
+
+ select * from id;
+ id1 | t
+ -----+---
+ 4 | a
+ 6 | a
+ (2 rows)
+
+ alter table id add id2 serial generated always as identity (start 5);
+ NOTICE: ALTER TABLE will create implicit sequence "id_id2_seq" for serial column "id.id2"
+ insert into id (t) values (null) returning id1, id2;
+ ERROR: null value in column "t" violates not-null constraint
+ insert into id (t) values ('a') returning id1, id2;
+ id1 | id2
+ -----+-----
+ 8 | 8
+ (1 row)
+
+ select * from id;
+ id1 | t | id2
+ -----+---+-----
+ 4 | a | 5
+ 6 | a | 6
+ 8 | a | 8
+ (3 rows)
+
+ -- Downgrade id2 to have simple DEFAULT nextval(...)
+ alter table id alter id2 drop identity;
+ insert into id (t) values (null) returning id1, id2;
+ ERROR: null value in column "t" violates not-null constraint
+ insert into id (t) values ('a') returning id1, id2;
+ id1 | id2
+ -----+-----
+ 10 | 10
+ (1 row)
+
+ select * from id;
+ id1 | t | id2
+ -----+---+-----
+ 4 | a | 5
+ 6 | a | 6
+ 8 | a | 8
+ 10 | a | 10
+ (4 rows)
+
+ -- Upgrade id1 to identity
+ alter table id alter id1 set identity generated always;
+ -- Upgrade id2 to identity, this must fail
+ alter table id alter id2 set identity generated always;
+ ERROR: multiple IDENTITY columns are not allowed
+ select * from id;
+ id1 | t | id2
+ -----+---+-----
+ 4 | a | 5
+ 6 | a | 6
+ 8 | a | 8
+ 10 | a | 10
+ (4 rows)
+
+ drop table id;
+ --
+ -- Test GENERATED columns
+ --
+ create table id (
+ id serial generated always as identity,
+ g integer generated always as ( id + 1 ));
+ NOTICE: CREATE TABLE will create implicit sequence "id_id_seq" for serial column "id.id"
+ insert into id (g) values (default);
+ select * from id;
+ id | g
+ ----+---
+ 1 | 2
+ (1 row)
+
+ drop table id;
+ --
+ -- This should fail, GENERATED cannot be referenced by GENERATED
+ --
+ create table id (
+ id serial generated always as identity,
+ i1 integer,
+ i2 integer,
+ g1 integer generated always as ( i1 + i2 ),
+ g2 integer generated always as ( i1 + g1 ));
+ NOTICE: CREATE TABLE will create implicit sequence "id_id_seq" for serial column "id.id"
+ ERROR: GENERATED column "g1" of table "id" cannot be referenced by GENERATED column "g2"
+ drop table id;
+ ERROR: table "id" does not exist
+ --
+ -- Test simple expression (col1 + col2) as GENERATED column
+ --
+ --
+ -- Test INSERT with GENERATED columns
+ --
+ create table id (
+ id serial generated always as identity,
+ i1 integer,
+ i2 integer,
+ i3 integer,
+ g1 integer generated always as ( i1 + i2 ));
+ NOTICE: CREATE TABLE will create implicit sequence "id_id_seq" for serial column "id.id"
+ --
+ -- This should fail, GENERATED cannot be referenced by GENERATED
+ --
+ alter table id add g2 integer generated always as ( i1 + g1 );
+ ERROR: GENERATED column "g1" of table "id" cannot be referenced by GENERATED column "g2"
+ --
+ -- This succeeds,
+ --
+ alter table id add g2 integer generated always as ( i2 + i3 );
+ --
+ -- 1st record: g1 == 3, g2 == 5
+ -- 2nd record: g1 == 3, g2 == 99
+ --
+ insert into id (i1, i2, i3) values (1, 2, 3);
+ insert into id (id, i1, i2, i3, g1, g2) overriding system value values (default, 1, 2, 3, default, 99);
+ select * from id;
+ id | i1 | i2 | i3 | g1 | g2
+ ----+----+----+----+----+----
+ 1 | 1 | 2 | 3 | 3 | 5
+ 2 | 1 | 2 | 3 | 3 | 99
+ (2 rows)
+
+ --
+ -- g1 -> 4, g2 stays 5
+ --
+ update id set i1 = 2 where id = 1;
+ select * from id;
+ id | i1 | i2 | i3 | g1 | g2
+ ----+----+----+----+----+----
+ 2 | 1 | 2 | 3 | 3 | 99
+ 1 | 2 | 2 | 3 | 4 | 5
+ (2 rows)
+
+ --
+ -- g1 -> 3, g2 -> 4
+ --
+ update id set i2 = 1 where id = 1;
+ select * from id;
+ id | i1 | i2 | i3 | g1 | g2
+ ----+----+----+----+----+----
+ 2 | 1 | 2 | 3 | 3 | 99
+ 1 | 2 | 1 | 3 | 3 | 4
+ (2 rows)
+
+ --
+ -- g1 -> 4, g2 stays 99
+ --
+ update id set i1 = 2 where id = 2;
+ select * from id;
+ id | i1 | i2 | i3 | g1 | g2
+ ----+----+----+----+----+----
+ 1 | 2 | 1 | 3 | 3 | 4
+ 2 | 2 | 2 | 3 | 4 | 99
+ (2 rows)
+
+ --
+ -- g1 -> 3, g2 -> 4
+ --
+ update id set i2 = 1 where id = 2;
+ select * from id;
+ id | i1 | i2 | i3 | g1 | g2
+ ----+----+----+----+----+----
+ 1 | 2 | 1 | 3 | 3 | 4
+ 2 | 2 | 1 | 3 | 3 | 4
+ (2 rows)
+
+ drop table id;
+ --
+ -- Test complex expression as GENERATED column
+ --
+ create table id (
+ id serial generated always as identity,
+ i1 integer,
+ i2 integer,
+ g1 integer generated always as (
+ case when i1 is null and i2 is null then -1
+ when i1 is null then i2
+ when i2 is null then i1
+ else i1 + i2
+ end ));
+ NOTICE: CREATE TABLE will create implicit sequence "id_id_seq" for serial column "id.id"
+ insert into id (i1, i2) values (null, null);
+ insert into id (i1, i2) values (10, null);
+ insert into id (i1, i2) values (null, 20 );
+ insert into id (i1, i2) values (10, 20 );
+ select * from id;
+ id | i1 | i2 | g1
+ ----+----+----+----
+ 1 | | | -1
+ 2 | 10 | | 10
+ 3 | | 20 | 20
+ 4 | 10 | 20 | 30
+ (4 rows)
+
+ --
+ -- Test DROP COLUMN on a column referenced by the GENERATED column
+ --
+ \d id
+ Table "public.id"
+ Column | Type | Modifiers
+ --------+---------+----------------------------------------------------------------------------------------------------------------------------------
+ id | integer | not null default nextval('id_id_seq'::regclass)
+ i1 | integer |
+ i2 | integer |
+ g1 | integer | default CASE WHEN ((i1 IS NULL) AND (i2 IS NULL)) THEN -1 WHEN (i1 IS NULL) THEN i2 WHEN (i2 IS NULL) THEN i1 ELSE (i1 + i2) END
+
+ alter table id drop i2;
+ NOTICE: default for table id column g1 depends on table id column i2
+ ERROR: cannot drop table id column i2 because other objects depend on it
+ HINT: Use DROP ... CASCADE to drop the dependent objects too.
+ \d id
+ Table "public.id"
+ Column | Type | Modifiers
+ --------+---------+----------------------------------------------------------------------------------------------------------------------------------
+ id | integer | not null default nextval('id_id_seq'::regclass)
+ i1 | integer |
+ i2 | integer |
+ g1 | integer | default CASE WHEN ((i1 IS NULL) AND (i2 IS NULL)) THEN -1 WHEN (i1 IS NULL) THEN i2 WHEN (i2 IS NULL) THEN i1 ELSE (i1 + i2) END
+
+ alter table id drop i2 cascade;
+ NOTICE: drop cascades to default for table id column g1
+ \d id
+ Table "public.id"
+ Column | Type | Modifiers
+ --------+---------+-------------------------------------------------
+ id | integer | not null default nextval('id_id_seq'::regclass)
+ i1 | integer |
+ g1 | integer |
+
+ drop table id;
+ --
+ -- Test plain COPY on IDENTITY/GENERATED columns
+ -- with missing column values. These should be generated.
+ --
+ create table id (
+ id serial generated always as identity,
+ i1 integer,
+ i2 integer,
+ g1 integer generated always as (
+ case when i1 is null and i2 is null then -1
+ when i1 is null then i2
+ when i2 is null then i1
+ else i1 + i2
+ end ));
+ NOTICE: CREATE TABLE will create implicit sequence "id_id_seq" for serial column "id.id"
+ copy id (i1, i2) from stdin;
+ select * from id;
+ id | i1 | i2 | g1
+ ----+----+----+----
+ 1 | | | -1
+ 2 | 10 | | 10
+ 3 | | 20 | 20
+ 4 | 10 | 20 | 30
+ (4 rows)
+
+ drop table id;
+ --
+ -- Test COPY OVERRIDING SYSTEM VALUE on IDENTITY/GENERATED columns
+ -- with missing column values. These should be generated.
+ --
+ create table id (
+ id serial generated always as identity,
+ i1 integer,
+ i2 integer,
+ g1 integer generated always as (
+ case when i1 is null and i2 is null then -1
+ when i1 is null then i2
+ when i2 is null then i1
+ else i1 + i2
+ end ));
+ NOTICE: CREATE TABLE will create implicit sequence "id_id_seq" for serial column "id.id"
+ copy id (i1, i2) overriding system value from stdin;
+ select * from id;
+ id | i1 | i2 | g1
+ ----+----+----+----
+ 1 | | | -1
+ 2 | 10 | | 10
+ 3 | | 20 | 20
+ 4 | 10 | 20 | 30
+ (4 rows)
+
+ drop table id;
+ --
+ -- Test COPY OVERRIDING SYSTEM VALUE on IDENTITY/GENERATED columns
+ -- with explicit column values. These should accept explicit values.
+ --
+ create table id (
+ id serial generated always as identity,
+ i1 integer,
+ i2 integer,
+ g1 integer generated always as (
+ case when i1 is null and i2 is null then -1
+ when i1 is null then i2
+ when i2 is null then i1
+ else i1 + i2
+ end ));
+ NOTICE: CREATE TABLE will create implicit sequence "id_id_seq" for serial column "id.id"
+ copy id (id, i1, i2, g1) overriding system value from stdin;
+ select * from id;
+ id | i1 | i2 | g1
+ ----+----+----+----
+ 1 | | | -2
+ 2 | 10 | | 37
+ 3 | | 20 | 36
+ 4 | 10 | 20 | 25
+ (4 rows)
+
+ --
+ -- Regenerate GENERATED values
+ --
+ update id set g1 = default where id = id;
+ select * from id;
+ id | i1 | i2 | g1
+ ----+----+----+----
+ 1 | | | -1
+ 2 | 10 | | 10
+ 3 | | 20 | 20
+ 4 | 10 | 20 | 30
+ (4 rows)
+
+ drop table id;
+ --
+ -- Test plain COPY on IDENTITY/GENERATED columns
+ -- with explicit column values. These should modify explicit values.
+ --
+ create table id (
+ id serial generated always as identity (start 5),
+ i1 integer,
+ i2 integer,
+ g1 integer generated always as (
+ case when i1 is null and i2 is null then -1
+ when i1 is null then i2
+ when i2 is null then i1
+ else i1 + i2
+ end ));
+ NOTICE: CREATE TABLE will create implicit sequence "id_id_seq" for serial column "id.id"
+ copy id (id, i1, i2, g1) from stdin;
+ select * from id;
+ id | i1 | i2 | g1
+ ----+----+----+----
+ 5 | | | -1
+ 6 | 10 | | 10
+ 7 | | 20 | 20
+ 8 | 10 | 20 | 30
+ (4 rows)
+
+ drop table id;
diff -dcrpN pgsql.orig/src/test/regress/parallel_schedule pgsql/src/test/regress/parallel_schedule
*** pgsql.orig/src/test/regress/parallel_schedule 2007-04-04 10:12:44.000000000 +0200
--- pgsql/src/test/regress/parallel_schedule 2007-04-13 12:32:59.000000000 +0200
***************
*** 2,8 ****
# The first group of parallel test
# $PostgreSQL: pgsql/src/test/regress/parallel_schedule,v 1.42 2007/04/02 03:49:42 tgl Exp $
# ----------
! test: boolean char name varchar text int2 int4 int8 oid float4 float8 bit numeric uuid enum
# Depends on things setup during char, varchar and text
test: strings
--- 2,8 ----
# The first group of parallel test
# $PostgreSQL: pgsql/src/test/regress/parallel_schedule,v 1.42 2007/04/02 03:49:42 tgl Exp $
# ----------
! test: boolean char name varchar text int2 int4 int8 oid float4 float8 bit numeric uuid enum identity
# Depends on things setup during char, varchar and text
test: strings
diff -dcrpN pgsql.orig/src/test/regress/serial_schedule pgsql/src/test/regress/serial_schedule
*** pgsql.orig/src/test/regress/serial_schedule 2007-04-04 10:12:44.000000000 +0200
--- pgsql/src/test/regress/serial_schedule 2007-04-13 12:32:59.000000000 +0200
*************** test: bit
*** 15,20 ****
--- 15,21 ----
test: numeric
test: uuid
test: enum
+ test: identity
test: strings
test: numerology
test: point
diff -dcrpN pgsql.orig/src/test/regress/sql/identity.sql pgsql/src/test/regress/sql/identity.sql
*** pgsql.orig/src/test/regress/sql/identity.sql 1970-01-01 01:00:00.000000000 +0100
--- pgsql/src/test/regress/sql/identity.sql 2007-04-14 22:40:16.000000000 +0200
***************
*** 0 ****
--- 1,405 ----
+ --
+ -- IDENTITY
+ -- 2006.08.01 Zoltan Boszormenyi
+ --
+
+ --
+ -- Test GENERATED BY DEFAULT AS IDENTITY with default sequence
+ --
+ create table id (id serial generated by default as identity primary key, name text);
+ insert into id (name) values ('a');
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (0, 'a');
+ --
+ -- This must complain about UNIQUE violation
+ --
+ insert into id (id, name) values (0, 'a');
+ select * from id;
+ --
+ -- This must complain because SERIAL and IDENTITY has
+ -- implicit NOT NULL constraint
+ --
+ insert into id (id, name) values (null, 'a');
+ select * from id;
+ update id set id = default where id = 0;
+ select * from id;
+ drop table id;
+ --
+ -- Test GENERATED BY DEFAULT AS IDENTITY with modified sequence
+ --
+ create table id (id serial generated by default as identity(start 3) primary key, name text);
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (0, 'a');
+ --
+ -- This must complain about UNIQUE violation
+ --
+ insert into id (id, name) values (0, 'a');
+ select * from id;
+ drop table id;
+ --
+ -- Test GENERATED ALWAYS AS IDENTITY with default sequence
+ --
+ create table id (id serial generated always as identity primary key, name text);
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (0, 'a');
+ insert into id (id, name) values (0, 'a');
+ insert into id (id, name) values (0, 'a');
+ select * from id;
+ --
+ -- It should update 0 rows
+ --
+ update id set name = 'b' where id = 0;
+ select * from id;
+ drop table id;
+ --
+ -- Test GENERATED ALWAYS AS IDENTITY with modified sequence
+ --
+ create table id (id serial generated always as identity(start 3) primary key, name text);
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (0, 'a');
+ insert into id (id, name) values (0, 'a');
+ insert into id (id, name) values (0, 'a');
+ select * from id;
+ --
+ -- It should update 0 rows
+ --
+ update id set name = 'b' where id = 0;
+ select * from id;
+ drop table id;
+ --
+ -- Test GENERATED BY DEFAULT AS (expr)
+ -- It must give a syntax error but drop it anyway.
+ --
+ create table id (id integer generated by default as (1), name text);
+ drop table id;
+ --
+ -- Test GENERATED ALWAYS AS ( expr )
+ --
+ create table id (id integer generated always as (1), name text);
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (0, 'a');
+ select * from id;
+ --
+ -- It should update 0 rows
+ --
+ update id set name = 'b' where id = 0;
+ select * from id;
+ drop table id;
+ --
+ -- Test IDENTITY with non-integer columns
+ --
+ create table id (id decimal(10,2) generated always as identity(start 3), name text);
+ insert into id (id, name) values (default, 'a');
+ insert into id (id, name) values (0, 'a');
+ select * from id;
+ alter table id alter id type real;
+ insert into id (id, name) values (0, 'a');
+ select * from id;
+ alter table id alter id type integer;
+ insert into id (id, name) values (0, 'a');
+ select * from id;
+ --
+ -- It should update 0 rows
+ --
+ update id set name = 'b' where id = 0;
+ select * from id;
+ update id set name = 'b' where id = 5;
+ select * from id;
+ drop table id;
+ --
+ -- Test OVERRIDING and UPDATE on GENERATED ALWAYS columns
+ --
+ create table id (id serial generated always as identity, name text generated always as ('x'));
+ insert into id (id, name) values (0, 'a');
+ insert into id (id, name) overriding user value values (0, 'a');
+ insert into id (id, name) overriding system value values (0, 'a');
+ update id set id = 6 where id = 0;
+ select * from id;
+ update id set id = default where id = 6;
+ select * from id;
+ --
+ -- It's expected to fail because of GENERATED
+ --
+ update id set name = 'b' where id = 3;
+ --
+ -- It's expected to succeed
+ --
+ update id set name = default where id = 3;
+ select * from id;
+ --
+ -- Test ALTER TABLE sequence options
+ --
+ alter table id alter id restart with 10;
+ insert into id (id, name) values (0, 'a');
+ alter table id alter id set increment by 5;
+ insert into id (id, name) values (0, 'a');
+ select * from id;
+ --
+ -- Test ALTER TABLE RENAME
+ --
+ select pg_get_serial_sequence('id', 'id');
+ alter table id rename id to di;
+ insert into id (di, name) values (0, 'a');
+ select pg_get_serial_sequence('id', 'di');
+ alter table id rename to di;
+ insert into di (di, name) values (0, 'a');
+ select pg_get_serial_sequence('di', 'di');
+ select * from di;
+ --
+ -- Test SET/DROP DEFAULT
+ --
+ alter table di alter di drop default;
+ alter table di alter di set default 1;
+ insert into di (di, name) values (default, 'a');
+ select * from di;
+ alter table di alter di set identity generated always;
+ insert into di (di, name) values (default, 'a');
+ select * from di;
+ alter table di alter di set generated always as ( nextval(pg_get_serial_sequence('di', 'di')) );
+ insert into di (di, name) values (default, 'a');
+ select * from di;
+ alter table di alter di set identity generated always;
+ insert into di (di, name) values (default, 'a');
+ select * from di;
+ --
+ -- Test ALTER TABLE DROP COLUMN
+ --
+ alter table di add column id integer;
+ update di set id = di;
+ alter table di drop column di;
+ select * from di;
+ drop table di;
+ --
+ -- Test multiple IDENTITY columns in a single table
+ --
+ -- This must fail, no multiple IDENTITY allowed
+ create table id (
+ id1 serial generated always as identity (start 3),
+ id2 serial generated always as identity (start 5)
+ );
+ -- This succeeds
+ create table id (id1 serial generated always as identity (start 3), t text not null);
+ insert into id (t) values (null) returning id1;
+ insert into id (t) values ('a') returning id1;
+ select * from id;
+ -- This must fail, no multiple IDENTITY allowed
+ alter table id add id2 serial generated always as identity (start 5);
+ -- Downgrade id1 to normal serial
+ alter table id alter id1 drop identity;
+ --
+ -- Try to drop IDENTITY again, it fails
+ --
+ alter table id alter id1 drop identity;
+ insert into id (t) values (null) returning id1;
+ insert into id (t) values ('a') returning id1;
+ select * from id;
+ alter table id add id2 serial generated always as identity (start 5);
+ insert into id (t) values (null) returning id1, id2;
+ insert into id (t) values ('a') returning id1, id2;
+ select * from id;
+ -- Downgrade id2 to have simple DEFAULT nextval(...)
+ alter table id alter id2 drop identity;
+ insert into id (t) values (null) returning id1, id2;
+ insert into id (t) values ('a') returning id1, id2;
+ select * from id;
+ -- Upgrade id1 to identity
+ alter table id alter id1 set identity generated always;
+ -- Upgrade id2 to identity, this must fail
+ alter table id alter id2 set identity generated always;
+ select * from id;
+ drop table id;
+ --
+ -- Test GENERATED columns
+ --
+ create table id (
+ id serial generated always as identity,
+ g integer generated always as ( id + 1 ));
+ insert into id (g) values (default);
+ select * from id;
+ drop table id;
+ --
+ -- This should fail, GENERATED cannot be referenced by GENERATED
+ --
+ create table id (
+ id serial generated always as identity,
+ i1 integer,
+ i2 integer,
+ g1 integer generated always as ( i1 + i2 ),
+ g2 integer generated always as ( i1 + g1 ));
+ drop table id;
+ --
+ -- Test simple expression (col1 + col2) as GENERATED column
+ --
+ --
+ -- Test INSERT with GENERATED columns
+ --
+ create table id (
+ id serial generated always as identity,
+ i1 integer,
+ i2 integer,
+ i3 integer,
+ g1 integer generated always as ( i1 + i2 ));
+ --
+ -- This should fail, GENERATED cannot be referenced by GENERATED
+ --
+ alter table id add g2 integer generated always as ( i1 + g1 );
+ --
+ -- This succeeds,
+ --
+ alter table id add g2 integer generated always as ( i2 + i3 );
+ --
+ -- 1st record: g1 == 3, g2 == 5
+ -- 2nd record: g1 == 3, g2 == 99
+ --
+ insert into id (i1, i2, i3) values (1, 2, 3);
+ insert into id (id, i1, i2, i3, g1, g2) overriding system value values (default, 1, 2, 3, default, 99);
+ select * from id;
+ --
+ -- g1 -> 4, g2 stays 5
+ --
+ update id set i1 = 2 where id = 1;
+ select * from id;
+ --
+ -- g1 -> 3, g2 -> 4
+ --
+ update id set i2 = 1 where id = 1;
+ select * from id;
+ --
+ -- g1 -> 4, g2 stays 99
+ --
+ update id set i1 = 2 where id = 2;
+ select * from id;
+ --
+ -- g1 -> 3, g2 -> 4
+ --
+ update id set i2 = 1 where id = 2;
+ select * from id;
+ drop table id;
+ --
+ -- Test complex expression as GENERATED column
+ --
+ create table id (
+ id serial generated always as identity,
+ i1 integer,
+ i2 integer,
+ g1 integer generated always as (
+ case when i1 is null and i2 is null then -1
+ when i1 is null then i2
+ when i2 is null then i1
+ else i1 + i2
+ end ));
+ insert into id (i1, i2) values (null, null);
+ insert into id (i1, i2) values (10, null);
+ insert into id (i1, i2) values (null, 20 );
+ insert into id (i1, i2) values (10, 20 );
+ select * from id;
+ --
+ -- Test DROP COLUMN on a column referenced by the GENERATED column
+ --
+ \d id
+ alter table id drop i2;
+ \d id
+ alter table id drop i2 cascade;
+ \d id
+ drop table id;
+ --
+ -- Test plain COPY on IDENTITY/GENERATED columns
+ -- with missing column values. These should be generated.
+ --
+ create table id (
+ id serial generated always as identity,
+ i1 integer,
+ i2 integer,
+ g1 integer generated always as (
+ case when i1 is null and i2 is null then -1
+ when i1 is null then i2
+ when i2 is null then i1
+ else i1 + i2
+ end ));
+ copy id (i1, i2) from stdin;
+ \N \N
+ 10 \N
+ \N 20
+ 10 20
+ \.
+ select * from id;
+ drop table id;
+ --
+ -- Test COPY OVERRIDING SYSTEM VALUE on IDENTITY/GENERATED columns
+ -- with missing column values. These should be generated.
+ --
+ create table id (
+ id serial generated always as identity,
+ i1 integer,
+ i2 integer,
+ g1 integer generated always as (
+ case when i1 is null and i2 is null then -1
+ when i1 is null then i2
+ when i2 is null then i1
+ else i1 + i2
+ end ));
+ copy id (i1, i2) overriding system value from stdin;
+ \N \N
+ 10 \N
+ \N 20
+ 10 20
+ \.
+ select * from id;
+ drop table id;
+ --
+ -- Test COPY OVERRIDING SYSTEM VALUE on IDENTITY/GENERATED columns
+ -- with explicit column values. These should accept explicit values.
+ --
+ create table id (
+ id serial generated always as identity,
+ i1 integer,
+ i2 integer,
+ g1 integer generated always as (
+ case when i1 is null and i2 is null then -1
+ when i1 is null then i2
+ when i2 is null then i1
+ else i1 + i2
+ end ));
+ copy id (id, i1, i2, g1) overriding system value from stdin;
+ 1 \N \N -2
+ 2 10 \N 37
+ 3 \N 20 36
+ 4 10 20 25
+ \.
+ select * from id;
+ --
+ -- Regenerate GENERATED values
+ --
+ update id set g1 = default where id = id;
+ select * from id;
+ drop table id;
+ --
+ -- Test plain COPY on IDENTITY/GENERATED columns
+ -- with explicit column values. These should modify explicit values.
+ --
+ create table id (
+ id serial generated always as identity (start 5),
+ i1 integer,
+ i2 integer,
+ g1 integer generated always as (
+ case when i1 is null and i2 is null then -1
+ when i1 is null then i2
+ when i2 is null then i1
+ else i1 + i2
+ end ));
+ copy id (id, i1, i2, g1) from stdin;
+ 1 \N \N -2
+ 2 10 \N 37
+ 3 \N 20 36
+ 4 10 20 25
+ \.
+ select * from id;
+ drop table id;