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-26 10:26:46.000000000 +0200 *************** *** 744,749 **** --- 744,759 ---- + adtyp + bool + + Behaviour of computing the DEFAULT expression + ' ': the column has a DEFAULT clause + 'i': the column has a GENERATED ALWAYS AS IDENTITY clause + 'g': the column has a GENERATED ALWAYS AS ( expression ) clause + + + adbin text diff -dcrpN pgsql.orig/doc/src/sgml/ddl.sgml pgsql/doc/src/sgml/ddl.sgml *** pgsql.orig/doc/src/sgml/ddl.sgml 2007-02-09 07:22:55.000000000 +0100 --- pgsql/doc/src/sgml/ddl.sgml 2007-04-26 11:45:19.000000000 +0200 *************** CREATE TABLE products ( *** 232,237 **** --- 232,368 ---- + + Identity Columns + + + identity column + + + + A column can be an IDENTITY column which + is similar to the pseudo data types serial and + bigserial. But unlike these pseudo data types + the column can be of any type which have an implicit cast from + bigint. A sequence and the DEFAULT + clause for the column will be implicitely created as for + the pseudo data types serial and bigserial. + + + + There are two further differences. The first difference is that + the initial sequence parameters can be defined at table creation. + This is a syntax-only feature, since the implicitely created + sequence for serial and bigserial can + also be modified with ALTER SEQUENCE. + + + + The second difference is that an IDENTITY + column can either be GENERATED BY DEFAULT or + GENERATED ALWAYS. + + + + A GENERATED BY DEFAULT AS IDENTITY column + behaves the same as serial or bigserial, + i.e. on INSERT the record will contain + the explicitly provided constant value or the next sequence value + if the column was missing from the column list or in the case of + the explicit DEFAULT keyword. + A GENERATED ALWAYS AS IDENTITY column + on the other hand will always get its sequence's next value + even if the column list contained the IDENTITY + column and was provided with an explicit constant. This will + work similarly as an auto-incrementing field in other DBMS systems: + + INSERT INTO tabname (id, ...) VALUES (0, ...); + + It has to be emphasized that other than 0 values will also be + ignored and the next sequence value will be generated for + the column. Unlike other DBMS systems that modify the next sequence + value if you provide a high enough value in INSERT, + PostgreSQL provides other ways to modify the sequence behind + such columns. These are ALTER SEQUENCE which + existed before PostgreSQL 8.3: + + ALTER SEQUENCE seq_name RESTART [WITH] parameter; + + and the new ALTER TABLE syntax in PostgreSQL 8.3 + + ALTER TABLE tabname ALTER colname RESTART [WITH] parameter; + + This form below allows all the sequence options that + ALTER SEQUENCE knows about. + + ALTER TABLE tabname ALTER colname SET sequence_options; + + + + + In a table definition, the identity clause is listed after the column + data type. For example: + + CREATE TABLE products ( + product_no integer GENERATED ALWAYS AS IDENTITY ( START 30000001 ), + name text, + price numeric DEFAULT 9.99 + ); + + + + + The IDENTITY column in PostgreSQL mostly + conforms to the SQL:2003 standard with two notable differences. + The first comes from the PostgreSQL legacy that allows having + more than one SERIAL columns in the same table. + The other is that IDENTITY values are allowed to be UPDATEd. + + + + + + Generated Columns + + + generated column + + + + A column can also be a GENERATED column. + It is achieved with the GENERATED ALWAYS AS ( expression ) + clause which, like the DEFAULT clause + is listed after the column data type in a table definition. + For example: + + CREATE TABLE mytable ( + id serial, + i1 integer, + i2 integer, + g1 integer GENERATED ALWAYS AS ( id + 1 ), + g2 text GENERATED ALWAYS AS ( CASE WHEN i1 IS NULL THEN i2 + WHEN i2 IS NULL then i1 + ELSE i1::text || i2::text ) + ); + + + + + As can be seen in the above example, the GENERATED + column's generation expression can reference other columns + from the same table. The expression is computed after + all other columns were assigned with their values. + To avoid circular dependencies, a GENERATED + column cannot reference another GENERATED column. + + + + The implementation of GENERATED column + in PostgreSQL conforms to SQL:2003. + + + + Constraints 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-26 18:53:48.000000000 +0200 *************** where act *** 36,41 **** --- 36,45 ---- ALTER [ COLUMN ] column TYPE type [ USING expression ] ALTER [ COLUMN ] column SET DEFAULT expression ALTER [ COLUMN ] column DROP DEFAULT + ALTER [ COLUMN ] column SET GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] + ALTER [ COLUMN ] column RESTART [WITH] expression + ALTER [ COLUMN ] column SET sequence_options + 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 **** --- 123,183 ---- 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. + SET/DROP DEFAULT is allowed + on IDENTITY and GENERATED + columns, in which case these columns lose their IDENTITY + or GENERATED properties together with their previous + DEFAULT or generation expression. + SET/DROP DEFAULT on an + IDENTITY column keeps the OWNED + sequence. + + + + + + SET GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY [ ( sequence_options ) ] + + + This form makes a column an IDENTITY column, + keeping the column's type. It adds an OWNED sequence to the column + if it doesn't already have and sets the column's DEFAULT + expression to NEXTVAL('tablename_colname_seq') + with the proper generation behaviour. If sequence options are + also given then the OWNED sequence will also be altered or + created with these options. + + + + + + RESTART [ WITH ] + + + This resets the next value of the OWNED + sequence associated with the column. This has the same effect + as the ALTER SEQUENCE ... RESTART ... command. + + + + + + SET sequence_options + + + This modifies the parameters of the OWNED + sequence associated with the column. This command has the same + effect as the ALTER SEQUENCE ... command + and also handles every options. + + + + + + SET GENERATED ALWAYS AS ( expression ) + + + This form modifies a column to be a GENERATED column. *************** where act *** 385,390 **** --- 444,456 ---- (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. + *************** ALTER TABLE myschema.distributors SET SC *** 860,866 **** The ADD, DROP, and SET DEFAULT ! forms conform with the SQL standard. The other forms are PostgreSQL extensions of the SQL standard. Also, the ability to specify more than one manipulation in a single ALTER TABLE command is an extension. --- 926,934 ---- The ADD, DROP, and SET DEFAULT ! forms mostly conform with the SQL standard. The behaviour to allow ! SET/DROP DEFAULT on IDENTITY and ! GENERATED columns and the other forms are PostgreSQL extensions of the SQL standard. Also, the ability to specify more than one manipulation in a single ALTER TABLE command is an extension. 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-04-18 09:47:01.000000000 +0200 --- pgsql/doc/src/sgml/ref/copy.sgml 2007-04-26 12:04:09.000000000 +0200 *************** PostgreSQL documentation *** 24,29 **** --- 24,30 ---- COPY tablename [ ( column [, ...] ) ] FROM { 'filename' | STDIN } [ [ WITH ] + [ OVERRIDING SYSTEM VALUE ] [ BINARY ] [ OIDS ] [ DELIMITER [ AS ] 'delimiter' ] *************** COPY { ta *** 145,150 **** --- 146,162 ---- + OVERRIDING SYSTEM VALUE + + + Specifies that values override enforced default values + for GENERATED ALWAYS columns. Only effective for + COPY tablename FROM + + + + + BINARY 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-26 10:26:46.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-26 10:26:46.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 num_defval - 1; i >= 0; i--) { if (constr->defval[i].adbin) + { cpy->defval[i].adbin = pstrdup(constr->defval[i].adbin); + cpy->defval[i].adtyp = constr->defval[i].adtyp; + } } } *************** equalTupleDescs(TupleDesc tupdesc1, Tupl *** 387,392 **** --- 391,398 ---- } if (j >= n) return false; + if (defval1->adtyp != defval2->adtyp) + return false; if (strcmp(defval1->adbin, defval2->adbin) != 0) return false; } *************** BuildDescForRelation(List *schema) *** 561,566 **** --- 567,573 ---- if (attrdef == NULL) attrdef = (AttrDefault *) palloc(natts * sizeof(AttrDefault)); attrdef[ndef].adnum = attnum; + attrdef[ndef].adtyp = DEFAULT_NORMAL; attrdef[ndef].adbin = pstrdup(entry->cooked_default); ndef++; desc->attrs[attnum - 1]->atthasdef = true; diff -dcrpN pgsql.orig/src/backend/catalog/heap.c pgsql/src/backend/catalog/heap.c *** pgsql.orig/src/backend/catalog/heap.c 2007-04-04 10:11:07.000000000 +0200 --- pgsql/src/backend/catalog/heap.c 2007-04-26 10:26:46.000000000 +0200 *************** heap_drop_with_catalog(Oid relid) *** 1292,1305 **** * The expression must be presented as a nodeToString() string. */ void ! StoreAttrDefault(Relation rel, AttrNumber attnum, char *adbin) { Node *expr; char *adsrc; Relation adrel; HeapTuple tuple; ! Datum values[4]; ! static char nulls[4] = {' ', ' ', ' ', ' '}; Relation attrrel; HeapTuple atttup; Form_pg_attribute attStruct; --- 1292,1305 ---- * The expression must be presented as a nodeToString() string. */ void ! StoreAttrDefault(Relation rel, AttrNumber attnum, char *adbin, char adtyp) { Node *expr; char *adsrc; Relation adrel; HeapTuple tuple; ! Datum values[Natts_pg_attrdef]; ! static char nulls[Natts_pg_attrdef]; Relation attrrel; HeapTuple atttup; Form_pg_attribute attStruct; *************** StoreAttrDefault(Relation rel, AttrNumbe *** 1325,1330 **** --- 1325,1331 ---- */ values[Anum_pg_attrdef_adrelid - 1] = RelationGetRelid(rel); values[Anum_pg_attrdef_adnum - 1] = attnum; + values[Anum_pg_attrdef_adtyp - 1] = CharGetDatum(adtyp); values[Anum_pg_attrdef_adbin - 1] = DirectFunctionCall1(textin, CStringGetDatum(adbin)); values[Anum_pg_attrdef_adsrc - 1] = DirectFunctionCall1(textin, *************** StoreAttrDefault(Relation rel, AttrNumbe *** 1332,1337 **** --- 1333,1339 ---- adrel = heap_open(AttrDefaultRelationId, RowExclusiveLock); + MemSet(nulls, ' ', Natts_pg_attrdef); tuple = heap_formtuple(adrel->rd_att, values, nulls); attrdefOid = simple_heap_insert(adrel, tuple); *************** StoreAttrDefault(Relation rel, AttrNumbe *** 1385,1391 **** /* * Record dependencies on objects used in the expression, too. */ ! recordDependencyOnExpr(&defobject, expr, NIL, DEPENDENCY_NORMAL); } /* --- 1387,1397 ---- /* * Record dependencies on objects used in the expression, too. */ ! if (adtyp == DEFAULT_GENERATED) ! recordDependencyOnSingleRelExpr(&defobject, expr, RelationGetRelid(rel), ! DEPENDENCY_NORMAL, DEPENDENCY_NORMAL); ! else ! recordDependencyOnExpr(&defobject, expr, NIL, DEPENDENCY_NORMAL); } /* *************** StoreConstraints(Relation rel, TupleDesc *** 1504,1510 **** for (i = 0; i < constr->num_defval; i++) StoreAttrDefault(rel, constr->defval[i].adnum, ! constr->defval[i].adbin); for (i = 0; i < constr->num_check; i++) StoreRelCheck(rel, constr->check[i].ccname, --- 1510,1517 ---- for (i = 0; i < constr->num_defval; i++) StoreAttrDefault(rel, constr->defval[i].adnum, ! constr->defval[i].adbin, ! constr->defval[i].adtyp); for (i = 0; i < constr->num_check; i++) StoreRelCheck(rel, constr->check[i].ccname, *************** AddRelationRawConstraints(Relation rel, *** 1547,1552 **** --- 1554,1560 ---- TupleDesc tupleDesc; TupleConstr *oldconstr; int numoldchecks; + int numolddefval; ParseState *pstate; RangeTblEntry *rte; int numchecks; *************** AddRelationRawConstraints(Relation rel, *** 1554,1559 **** --- 1562,1569 ---- ListCell *cell; Node *expr; CookedConstraint *cooked; + int varno; + int sublevels_up; /* * Get info about existing constraints. *************** AddRelationRawConstraints(Relation rel, *** 1561,1569 **** --- 1571,1585 ---- tupleDesc = RelationGetDescr(rel); oldconstr = tupleDesc->constr; if (oldconstr) + { numoldchecks = oldconstr->num_check; + numolddefval = oldconstr->num_defval; + } else + { numoldchecks = 0; + numolddefval = 0; + } /* * Create a dummy ParseState and insert the target relation as its sole *************** AddRelationRawConstraints(Relation rel, *** 1577,1582 **** --- 1593,1600 ---- true); addRTEtoQuery(pstate, rte, true, true, true); + varno = RTERangeTablePosn(pstate, rte, &sublevels_up); + /* * Process column default expressions. */ *************** AddRelationRawConstraints(Relation rel, *** 1584,1601 **** { RawColumnDefault *colDef = (RawColumnDefault *) lfirst(cell); Form_pg_attribute atp = rel->rd_att->attrs[colDef->attnum - 1]; expr = cookDefault(pstate, colDef->raw_default, atp->atttypid, atp->atttypmod, ! NameStr(atp->attname)); ! StoreAttrDefault(rel, colDef->attnum, nodeToString(expr)); cooked = (CookedConstraint *) palloc(sizeof(CookedConstraint)); cooked->contype = CONSTR_DEFAULT; cooked->name = NULL; cooked->attnum = colDef->attnum; cooked->expr = expr; cookedConstraints = lappend(cookedConstraints, cooked); } --- 1602,1671 ---- { RawColumnDefault *colDef = (RawColumnDefault *) lfirst(cell); Form_pg_attribute atp = rel->rd_att->attrs[colDef->attnum - 1]; + Node *var; expr = cookDefault(pstate, colDef->raw_default, atp->atttypid, atp->atttypmod, ! NameStr(atp->attname), ! colDef->behaviour); ! if (colDef->behaviour == DEFAULT_GENERATED) ! { ! ListCell *cell_ref; ! ! foreach(cell_ref, rawColDefaults) ! { ! RawColumnDefault *colDef_ref = (RawColumnDefault *) lfirst(cell_ref); ! Form_pg_attribute atp_ref = rel->rd_att->attrs[colDef_ref->attnum - 1]; ! ! if (colDef_ref->behaviour == DEFAULT_GENERATED) ! { ! 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\"", ! get_attname(RelationGetRelid(rel), colDef_ref->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].adtyp == DEFAULT_GENERATED) ! { ! 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->behaviour); cooked = (CookedConstraint *) palloc(sizeof(CookedConstraint)); cooked->contype = CONSTR_DEFAULT; cooked->name = NULL; cooked->attnum = colDef->attnum; cooked->expr = expr; + cooked->behaviour = colDef->behaviour; cookedConstraints = lappend(cookedConstraints, cooked); } *************** AddRelationRawConstraints(Relation rel, *** 1722,1727 **** --- 1792,1798 ---- cooked->name = ccname; cooked->attnum = 0; cooked->expr = expr; + cooked->behaviour = DEFAULT_NORMAL; cookedConstraints = lappend(cookedConstraints, cooked); } *************** cookDefault(ParseState *pstate, *** 1799,1805 **** Node *raw_default, Oid atttypid, int32 atttypmod, ! char *attname) { Node *expr; --- 1870,1877 ---- 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"))); --- 1885,1891 ---- /* * 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-04-18 09:47:01.000000000 +0200 --- pgsql/src/backend/commands/copy.c 2007-04-26 12:48:17.000000000 +0200 *************** *** 23,28 **** --- 23,29 ---- #include "access/heapam.h" #include "access/xact.h" #include "catalog/namespace.h" + #include "catalog/pg_attrdef.h" #include "catalog/pg_type.h" #include "commands/copy.h" #include "commands/trigger.h" *************** typedef struct CopyStateData *** 103,108 **** --- 104,110 ---- 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 *** 816,821 **** --- 818,831 ---- errmsg("conflicting or redundant options"))); force_notnull = (List *) defel->arg; } + else if (strcmp(defel->defname, "overriding") == 0) + { + if (cstate->overriding) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("conflicting or redundant options"))); + cstate->overriding = intVal(defel->arg); + } else elog(ERROR, "option \"%s\" not recognized", defel->defname); *************** CopyFrom(CopyState cstate) *** 1626,1631 **** --- 1636,1642 ---- Oid in_func_oid; Datum *values; char *nulls; + char *replace; int nfields; char **field_strings; bool done = false; *************** CopyFrom(CopyState cstate) *** 1635,1640 **** --- 1646,1652 ---- TupleTableSlot *slot; bool file_has_oids; int *defmap; + char *deftyp; ExprState **defexprs; /* array of default att expressions */ ExprContext *econtext; /* used for ExecEvalExpr for default atts */ MemoryContext oldcontext = CurrentMemoryContext; *************** CopyFrom(CopyState cstate) *** 1642,1647 **** --- 1654,1660 ---- 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 **** --- 1778,1784 ---- 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 **** --- 1789,1802 ---- 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)); + deftyp = (char *) palloc(num_phys_attrs * sizeof(char)); defexprs = (ExprState **) palloc(num_phys_attrs * sizeof(ExprState *)); + hasgenerated = false; for (attnum = 1; attnum <= num_phys_attrs; attnum++) { + char adtyp; + /* 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 */ --- 1811,1818 ---- fmgr_info(in_func_oid, &in_functions[attnum - 1]); /* Get default info if needed */ ! adtyp = column_default_behaviour(cstate->rel, attnum); ! if (!list_member_int(cstate->attnumlist, attnum) || adtyp != DEFAULT_NORMAL) { /* attribute is NOT to be copied from input */ /* use default value if one exists */ *************** CopyFrom(CopyState cstate) *** 1804,1809 **** --- 1823,1830 ---- defexprs[num_defaults] = ExecPrepareExpr((Expr *) defexpr, estate); defmap[num_defaults] = attnum - 1; + deftyp[num_defaults] = adtyp; + hasgenerated = hasgenerated || (adtyp != DEFAULT_GENERATED); num_defaults++; } } *************** CopyFrom(CopyState cstate) *** 1870,1875 **** --- 1891,1897 ---- 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) --- 2082,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 (deftyp[i] == DEFAULT_GENERATED) + continue; + if (deftyp[i] == DEFAULT_IDENTITY && + nulls[defmap[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 (deftyp[i] == DEFAULT_GENERATED && + (!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,2207 ---- pfree(values); pfree(nulls); + pfree(replace); pfree(field_strings); pfree(in_functions); pfree(typioparams); pfree(defmap); + pfree(deftyp); 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-26 10:26:46.000000000 +0200 *************** *** 19,24 **** --- 19,25 ---- #include "access/xact.h" #include "catalog/dependency.h" #include "catalog/namespace.h" + #include "catalog/pg_attrdef.h" #include "catalog/pg_type.h" #include "commands/defrem.h" #include "commands/sequence.h" *************** DefineSequence(CreateSeqStmt *seq) *** 129,134 **** --- 130,136 ---- coldef->raw_default = NULL; coldef->cooked_default = NULL; coldef->constraints = NIL; + coldef->behaviour = DEFAULT_NORMAL; 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-26 18:38:22.000000000 +0200 *************** *** 24,29 **** --- 24,30 ---- #include "catalog/index.h" #include "catalog/indexing.h" #include "catalog/namespace.h" + #include "catalog/pg_attrdef.h" #include "catalog/pg_constraint.h" #include "catalog/pg_depend.h" #include "catalog/pg_inherits.h" *************** *** 34,39 **** --- 35,41 ---- #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 **** --- 176,183 ---- 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, --- 221,227 ---- static void ATExecSetNotNull(AlteredTableInfo *tab, Relation rel, const char *colName); static void ATExecColumnDefault(Relation rel, const char *colName, ! Node *newDefault, char behaviour); 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, char behaviour, List *seq_opts); static void copy_relation_data(Relation rel, SMgrRelation dst); *************** DefineRelation(CreateStmt *stmt, char re *** 480,485 **** --- 486,492 ---- rawEnt = (RawColumnDefault *) palloc(sizeof(RawColumnDefault)); rawEnt->attnum = attnum; rawEnt->raw_default = colDef->raw_default; + rawEnt->behaviour = colDef->behaviour; rawDefaults = lappend(rawDefaults, rawEnt); } } *************** MergeAttributes(List *schema, List *supe *** 931,936 **** --- 938,944 ---- def->raw_default = NULL; def->cooked_default = NULL; def->constraints = NIL; + def->behaviour = DEFAULT_NORMAL; inhSchema = lappend(inhSchema, def); newattno[parent_attno - 1] = ++child_attno; } *************** MergeAttributes(List *schema, List *supe *** 941,946 **** --- 949,955 ---- if (attribute->atthasdef) { char *this_default = NULL; + char this_behaviour = DEFAULT_NORMAL; AttrDefault *attrdef; int i; *************** MergeAttributes(List *schema, List *supe *** 952,957 **** --- 961,967 ---- if (attrdef[i].adnum == parent_attno) { this_default = attrdef[i].adbin; + this_behaviour = attrdef[i].adtyp; break; } } *************** MergeAttributes(List *schema, List *supe *** 969,979 **** --- 979,993 ---- */ Assert(def->raw_default == NULL); if (def->cooked_default == NULL) + { def->cooked_default = pstrdup(this_default); + def->behaviour = this_behaviour; + } else if (strcmp(def->cooked_default, this_default) != 0) { def->cooked_default = bogus_marker; have_bogus_defaults = true; + def->behaviour = DEFAULT_NORMAL; } } } *************** MergeAttributes(List *schema, List *supe *** 1065,1070 **** --- 1079,1085 ---- { def->raw_default = newdef->raw_default; def->cooked_default = newdef->cooked_default; + def->behaviour = newdef->behaviour; } } else *************** setRelhassubclassInRelation(Oid relation *** 1409,1414 **** --- 1424,1459 ---- } + 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 **** --- 1656,1667 ---- 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 **** --- 1683,1689 ---- 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 **** --- 1731,1745 ---- /* 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 **** --- 2032,2039 ---- case AT_DisableRule: case AT_AddInherit: /* INHERIT / NO INHERIT */ case AT_DropInherit: + case AT_SetSeqOpts: + case AT_SetIdentity: 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); --- 2131,2137 ---- ATExecAddColumn(tab, rel, (ColumnDef *) cmd->def); break; case AT_ColumnDefault: /* ALTER COLUMN DEFAULT */ ! ATExecColumnDefault(rel, cmd->name, cmd->def, cmd->default_behaviour); break; case AT_DropNotNull: /* ALTER COLUMN DROP NOT NULL */ ATExecDropNotNull(rel, cmd->name); *************** ATExecCmd(AlteredTableInfo *tab, Relatio *** 2193,2198 **** --- 2256,2267 ---- 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, cmd->default_behaviour, cmd->seq_opts); + break; default: /* oops */ elog(ERROR, "unrecognized alter table type: %d", (int) cmd->subtype); *************** ATExecAddColumn(AlteredTableInfo *tab, R *** 3096,3101 **** --- 3165,3171 ---- rawEnt = (RawColumnDefault *) palloc(sizeof(RawColumnDefault)); rawEnt->attnum = attribute->attnum; rawEnt->raw_default = copyObject(colDef->raw_default); + rawEnt->behaviour = colDef->behaviour; /* * 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; --- 3402,3409 ---- */ static void ATExecColumnDefault(Relation rel, const char *colName, ! Node *newDefault, ! char behaviour) { AttrNumber attnum; *************** ATExecColumnDefault(Relation rel, const *** 3368,3373 **** --- 3439,3445 ---- rawEnt = (RawColumnDefault *) palloc(sizeof(RawColumnDefault)); rawEnt->attnum = attnum; rawEnt->raw_default = newDefault; + rawEnt->behaviour = behaviour; /* * This function is intended for CREATE TABLE, so it processes a *************** ATExecAlterColumnType(AlteredTableInfo * *** 4837,4842 **** --- 4909,4915 ---- ScanKeyData key[3]; SysScanDesc scan; HeapTuple depTup; + char adtyp; attrelation = heap_open(AttributeRelationId, RowExclusiveLock); *************** ATExecAlterColumnType(AlteredTableInfo * *** 4879,4884 **** --- 4952,4958 ---- if (attTup->atthasdef) { defaultexpr = build_column_default(rel, attnum); + adtyp = column_default_behaviour(rel, attnum); Assert(defaultexpr); defaultexpr = strip_implicit_coercions(defaultexpr); defaultexpr = coerce_to_target_type(NULL, /* no UNKNOWN params */ *************** ATExecAlterColumnType(AlteredTableInfo * *** 4893,4899 **** --- 4967,4976 ---- colName, TypeNameToString(typename)))); } else + { defaultexpr = NULL; + adtyp = DEFAULT_NORMAL; + } /* * 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 */ --- 5219,5225 ---- */ RemoveAttrDefault(RelationGetRelid(rel), attnum, DROP_RESTRICT, true); ! StoreAttrDefault(rel, attnum, nodeToString(defaultexpr), adtyp); } /* Cleanup */ *************** ATExecAddInherit(Relation child_rel, Ran *** 6030,6035 **** --- 6107,6290 ---- heap_close(parent_rel, NoLock); } + static void + alter_owned_sequence(Oid sequenceId, List *seq_opts) + { + 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); + } + } + } + + /* + * 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)) + alter_owned_sequence(sequenceId, seq_opts); + else + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("column \"%s\" of relation \"%s\" is not an IDENTITY column", + column, RelationGetRelationName(rel)))); + } + + + /* + * ALTER TABLE SET GENERATED ... AS IDENTITY [ ( sequence options ) ] + */ + static void + ATExecIdentity(Relation rel, char *column, char behaviour, List *seq_opts) + { + AttrNumber attnum; + Oid tableOid; + Oid seqOid; + char *qstring; + Oid snamespaceid; + char *snamespace; + char *sname; + RawColumnDefault *rawEnt; + A_Const *snamenode; + FuncCall *funccallnode; + + /* + * get the number of the attribute + */ + 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)))); + + /* Prevent them from altering a system attribute */ + if (attnum <= 0) + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("cannot alter system column \"%s\"", + column))); + + seqOid = get_relid_att_serial_sequence(tableOid, attnum); + + if (!OidIsValid(seqOid)) + { + CreateSeqStmt *seqstmt = makeNode(CreateSeqStmt); + List *attnamelist; + + snamespaceid = get_rel_namespace(tableOid); + snamespace = get_namespace_name(snamespaceid); + sname = ChooseRelationName(RelationGetRelationName(rel), + column, + "seq", + snamespaceid); + + ereport(NOTICE, + (errmsg("ALTER TABLE will create implicit sequence \"%s\" for serial column \"%s.%s\"", + sname, + RelationGetRelationName(rel), + column))); + + seqstmt->sequence = makeRangeVar(snamespace, sname); + + attnamelist = list_make3(makeString(snamespace), + makeString(RelationGetRelationName(rel)), + makeString(column)); + + seqstmt->options = lappend(seq_opts, makeDefElem("owned_by", (Node *) attnamelist)); + + DefineSequence(seqstmt); + + CommandCounterIncrement(); + + seqOid = get_relid_att_serial_sequence(tableOid, attnum); + } + + if (seq_opts != NIL) + alter_owned_sequence(seqOid, seq_opts); + + /* + * Remove the old DEFAULT and add a NEW one. + * + * 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. + */ + snamespaceid = get_rel_namespace(seqOid); + snamespace = get_namespace_name(snamespaceid); + sname = get_rel_name(seqOid); + + 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 = attnum; + rawEnt->raw_default = (Node *) funccallnode; + rawEnt->behaviour = behaviour; + + RemoveAttrDefault(tableOid, attnum, DROP_RESTRICT, false); + AddRelationRawConstraints(rel, list_make1(rawEnt), NIL); + } + + /* * 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-26 10:26:46.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, ! ' '); /* * 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), ! ' '); /* * 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-26 10:26:46.000000000 +0200 *************** *** 18,23 **** --- 18,24 ---- #include "access/xact.h" #include "catalog/dependency.h" #include "catalog/namespace.h" + #include "catalog/pg_attrdef.h" #include "commands/defrem.h" #include "commands/tablecmds.h" #include "commands/view.h" *************** DefineVirtualRelation(const RangeVar *re *** 127,132 **** --- 128,134 ---- def->raw_default = NULL; def->cooked_default = NULL; def->constraints = NIL; + def->behaviour = DEFAULT_NORMAL; 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-26 10:26:46.000000000 +0200 *************** *** 39,44 **** --- 39,45 ---- #include "catalog/heap.h" #include "catalog/namespace.h" #include "catalog/toasting.h" + #include "catalog/pg_attrdef.h" #include "commands/tablespace.h" #include "commands/trigger.h" #include "executor/execdebug.h" *************** *** 46,55 **** --- 47,60 ---- #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, --- 80,105 ---- 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 --- 258,265 ---- 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; --- 1065,1072 ---- 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; --- 1275,1281 ---- 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; --- 1285,1291 ---- 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; --- 1362,1369 ---- ItemPointer tupleid, TupleTableSlot *planSlot, DestReceiver *dest, ! EState *estate, ! List *modified_fields) { HeapTuple tuple; ResultRelInfo *resultRelInfo; *************** ExecInsert(TupleTableSlot *slot, *** 1396,1401 **** --- 1412,1422 ---- } /* + * 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; --- 1600,1607 ---- ItemPointer tupleid, TupleTableSlot *planSlot, DestReceiver *dest, ! EState *estate, ! List *modified_fields) { HeapTuple tuple; ResultRelInfo *resultRelInfo; *************** ExecUpdate(TupleTableSlot *slot, *** 1638,1643 **** --- 1660,1670 ---- } /* + * 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 **** --- 1858,1986 ---- } /* + * 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++) + { + char adtyp; + Form_pg_attribute att_tup = tupleDesc->attrs[attnum - 1]; + + if (att_tup->attisdropped) + continue; + adtyp = column_default_behaviour(resultRelationDesc, attnum); + if (adtyp == DEFAULT_GENERATED) + { + 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-26 14:16:29.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,1761 ---- COPY_NODE_FIELD(raw_default); COPY_STRING_FIELD(cooked_default); COPY_NODE_FIELD(constraints); + COPY_SCALAR_FIELD(behaviour); return newnode; } *************** _copyConstraint(Constraint *from) *** 1770,1775 **** --- 1772,1779 ---- COPY_NODE_FIELD(keys); COPY_NODE_FIELD(options); COPY_STRING_FIELD(indexspace); + COPY_SCALAR_FIELD(behaviour); + COPY_NODE_FIELD(seq_opts); return newnode; } *************** _copyQuery(Query *from) *** 1834,1839 **** --- 1838,1845 ---- 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 **** --- 1853,1859 ---- COPY_NODE_FIELD(cols); COPY_NODE_FIELD(selectStmt); COPY_NODE_FIELD(returningList); + COPY_SCALAR_FIELD(commandOverride); return newnode; } *************** _copyAlterTableCmd(AlterTableCmd *from) *** 1940,1945 **** --- 1947,1954 ---- COPY_NODE_FIELD(def); COPY_NODE_FIELD(transform); COPY_SCALAR_FIELD(behavior); + COPY_SCALAR_FIELD(default_behaviour); + COPY_NODE_FIELD(seq_opts); 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-26 14:16:39.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(default_behaviour); + COMPARE_NODE_FIELD(seq_opts); return true; } *************** _equalColumnDef(ColumnDef *a, ColumnDef *** 1765,1770 **** --- 1770,1776 ---- COMPARE_NODE_FIELD(raw_default); COMPARE_STRING_FIELD(cooked_default); COMPARE_NODE_FIELD(constraints); + COMPARE_SCALAR_FIELD(behaviour); return true; } *************** _equalConstraint(Constraint *a, Constrai *** 1779,1784 **** --- 1785,1792 ---- COMPARE_NODE_FIELD(keys); COMPARE_NODE_FIELD(options); COMPARE_STRING_FIELD(indexspace); + COMPARE_SCALAR_FIELD(behaviour); + 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-26 10:26:46.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,1627 ---- WRITE_NODE_FIELD(raw_default); WRITE_STRING_FIELD(cooked_default); WRITE_NODE_FIELD(constraints); + WRITE_CHAR_FIELD(behaviour); } static void *************** _outQuery(StringInfo str, Query *node) *** 1709,1714 **** --- 1711,1718 ---- 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 **** --- 1977,1990 ---- appendStringInfo(str, "DEFAULT"); WRITE_NODE_FIELD(raw_expr); WRITE_STRING_FIELD(cooked_expr); + WRITE_CHAR_FIELD(behaviour); break; + case CONSTR_IDENTITY: + appendStringInfo(str, "IDENTITY"); + WRITE_CHAR_FIELD(behaviour); + 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-26 10:26:46.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-04-16 12:55:52.000000000 +0200 --- pgsql/src/backend/optimizer/plan/planner.c 2007-04-26 10:26:46.000000000 +0200 *************** planner(Query *parse, int cursorOptions, *** 162,167 **** --- 162,168 ---- 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-26 10:26:46.000000000 +0200 *************** *** 31,36 **** --- 31,37 ---- #include "catalog/heap.h" #include "catalog/index.h" #include "catalog/namespace.h" + #include "catalog/pg_attrdef.h" #include "catalog/pg_type.h" #include "commands/defrem.h" #include "commands/prepare.h" *************** transformInsertStmt(ParseState *pstate, *** 453,458 **** --- 454,460 ---- ListCell *lc; qry->commandType = CMD_INSERT; + qry->commandOverride = stmt->commandOverride; pstate->p_is_insert = true; /* *************** transformColumnDefinition(ParseState *ps *** 940,945 **** --- 942,950 ---- bool saw_nullable; Constraint *constraint; ListCell *clist; + bool is_identity; + char behaviour; + 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; --- 970,1004 ---- } } + /* Check for GENERATED ... AS IDENTITY constructs */ + is_identity = false; + behaviour = DEFAULT_NORMAL; + 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))); + is_identity = true; + behaviour = constraint->behaviour; + 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); --- 1040,1046 ---- */ seqstmt = makeNode(CreateSeqStmt); seqstmt->sequence = makeRangeVar(snamespace, sname); ! seqstmt->options = seq_opts; cxt->blist = lappend(cxt->blist, seqstmt); *************** transformColumnDefinition(ParseState *ps *** 1057,1062 **** --- 1086,1092 ---- constraint->raw_expr = (Node *) funccallnode; constraint->cooked_expr = NULL; constraint->keys = NIL; + constraint->behaviour = behaviour; column->constraints = lappend(column->constraints, constraint); constraint = makeNode(Constraint); *************** transformColumnDefinition(ParseState *ps *** 1068,1073 **** --- 1098,1104 ---- transformConstraintAttrs(column->constraints); saw_nullable = false; + column->behaviour = DEFAULT_NORMAL; foreach(clist, column->constraints) { *************** transformColumnDefinition(ParseState *ps *** 1118,1126 **** --- 1149,1161 ---- errmsg("multiple default values specified for column \"%s\" of table \"%s\"", column->colname, cxt->relation->relname))); column->raw_default = constraint->raw_expr; + column->behaviour = constraint->behaviour; 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 **** --- 1328,1334 ---- def->raw_default = NULL; def->cooked_default = NULL; def->constraints = NIL; + def->behaviour = DEFAULT_NORMAL; /* * Add to column list *************** transformInhRelation(ParseState *pstate, *** 1305,1310 **** --- 1341,1348 ---- if (attribute->atthasdef && including_defaults) { char *this_default = NULL; + char this_behaviour = DEFAULT_NORMAL; + AttrDefault *attrdef; int i; *************** transformInhRelation(ParseState *pstate, *** 1316,1321 **** --- 1354,1360 ---- if (attrdef[i].adnum == parent_attno) { this_default = attrdef[i].adbin; + this_behaviour = attrdef[i].adtyp; break; } } *************** transformInhRelation(ParseState *pstate, *** 1327,1332 **** --- 1366,1372 ---- */ def->cooked_default = pstrdup(this_default); + def->behaviour = this_behaviour; } } diff -dcrpN pgsql.orig/src/backend/parser/gram.y pgsql/src/backend/parser/gram.y *** pgsql.orig/src/backend/parser/gram.y 2007-04-16 12:55:58.000000000 +0200 --- pgsql/src/backend/parser/gram.y 2007-04-26 14:13:46.000000000 +0200 *************** *** 53,58 **** --- 53,59 ---- #include "catalog/index.h" #include "catalog/namespace.h" + #include "catalog/pg_attrdef.h" #include "commands/defrem.h" #include "nodes/makefuncs.h" #include "parser/gramparse.h" *************** static Node *makeXmlExpr(XmlExprOp op, c *** 286,292 **** %type fetch_direction select_limit_value select_offset_value ! %type OptSeqList %type OptSeqElem %type insert_rest --- 287,293 ---- %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 **** --- 315,321 ---- %type relation_expr %type relation_expr_opt_alias %type target_el single_set_clause set_target insert_column_item + %type UserSystem %type Typename SimpleTypename ConstTypename GenericType Numeric opt_float *************** static Node *makeXmlExpr(XmlExprOp op, c *** 341,346 **** --- 343,349 ---- %type TableLikeOption %type ColQualList %type ColConstraint ColConstraintElem ConstraintAttr + %type IdentitySpec %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: *** 1375,1380 **** --- 1378,1384 ---- n->subtype = AT_ColumnDefault; n->name = $3; n->def = $4; + n->default_behaviour = DEFAULT_NORMAL; $$ = (Node *)n; } /* ALTER TABLE ALTER [COLUMN] DROP NOT NULL */ *************** alter_table_cmd: *** 1411,1416 **** --- 1415,1463 ---- 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 GENERATED ALWAYS AS '(' a_expr ')' + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_ColumnDefault; + n->name = $3; + n->def = $9; + n->default_behaviour = DEFAULT_GENERATED; + $$ = (Node *)n; + } + | ALTER opt_column ColId SET GENERATED ALWAYS AS IDENTITY IdentitySpec + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_SetIdentity; + n->name = $3; + n->default_behaviour = DEFAULT_IDENTITY; + n->seq_opts = $9; + $$ = (Node *)n; + } + | ALTER opt_column ColId SET GENERATED BY DEFAULT AS IDENTITY IdentitySpec + { + AlterTableCmd *n = makeNode(AlterTableCmd); + n->subtype = AT_SetIdentity; + n->name = $3; + n->default_behaviour = DEFAULT_NORMAL; + n->seq_opts = $10; + $$ = (Node *)n; + } /* ALTER TABLE DROP [COLUMN] [RESTRICT|CASCADE] */ | DROP opt_column ColId opt_drop_behavior { *************** copy_opt_item: *** 1791,1796 **** --- 1838,1847 ---- { $$ = makeDefElem("force_notnull", (Node *)$4); } + | OVERRIDING SYSTEM_P VALUE_P + { + $$ = makeDefElem("overriding", (Node *)makeInteger(TRUE)); + } ; /* The following exist for backward compatibility */ *************** ColConstraintElem: *** 2036,2041 **** --- 2087,2093 ---- n->cooked_expr = NULL; n->keys = NULL; n->indexspace = NULL; + n->behaviour = DEFAULT_NORMAL; $$ = (Node *)n; } | REFERENCES qualified_name opt_column_list key_match key_actions *************** ColConstraintElem: *** 2052,2057 **** --- 2104,2154 ---- n->initdeferred = FALSE; $$ = (Node *)n; } + | GENERATED ALWAYS AS '(' a_expr ')' + { + Constraint *n = makeNode(Constraint); + n->contype = CONSTR_DEFAULT; + n->name = NULL; + n->raw_expr = $5; + n->cooked_expr = NULL; + n->keys = NULL; + n->indexspace = NULL; + n->behaviour = DEFAULT_GENERATED; + $$ = (Node *)n; + } + | GENERATED ALWAYS AS IDENTITY IdentitySpec + { + 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 = $5; + n->behaviour = DEFAULT_IDENTITY; + $$ = (Node *)n; + } + | GENERATED BY DEFAULT AS IDENTITY IdentitySpec + { + 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 = $6; + n->behaviour = DEFAULT_NORMAL; + $$ = (Node *)n; + } + ; + + IdentitySpec: + '(' SeqList ')' + { $$ = $2; } + | /* EMPTY */ + { $$ = NIL; } ; /* *************** OptSeqList: OptSeqList OptSeqElem { *** 2402,2407 **** --- 2499,2509 ---- | /*EMPTY*/ { $$ = NIL; } ; + SeqList: SeqList OptSeqElem { $$ = lappend($1, $2); } + | OptSeqElem { $$ = list_make1($1); } + ; + + OptSeqElem: CACHE NumericOnly { $$ = makeDefElem("cache", (Node *)$2); *************** insert_rest: *** 5632,5645 **** --- 5734,5763 ---- { $$ = makeNode(InsertStmt); $$->cols = NIL; + $$->commandOverride = false; $$->selectStmt = $1; } + | OVERRIDING UserSystem VALUE_P SelectStmt + { + $$ = makeNode(InsertStmt); + $$->cols = NIL; + $$->commandOverride = $2; + $$->selectStmt = $4; + } | '(' insert_column_list ')' SelectStmt { $$ = makeNode(InsertStmt); $$->cols = $2; + $$->commandOverride = false; $$->selectStmt = $4; } + | '(' insert_column_list ')' OVERRIDING UserSystem VALUE_P SelectStmt + { + $$ = makeNode(InsertStmt); + $$->cols = $2; + $$->commandOverride = $5; + $$->selectStmt = $7; + } | DEFAULT VALUES { $$ = makeNode(InsertStmt); *************** opt_nowait: NOWAIT { $$ = TRUE; } *** 5726,5731 **** --- 5844,5854 ---- ; + UserSystem: + SYSTEM_P { $$ = true; } + | USER { $$ = false; } + ; + /***************************************************************************** * * QUERY: *************** b_expr: c_expr *** 7429,7436 **** { $$ = (Node *) makeA_Expr(AEXPR_OP, $2, $1, $3, @2); } | qual_Op b_expr %prec Op { $$ = (Node *) makeA_Expr(AEXPR_OP, $1, NULL, $2, @1); } - | b_expr qual_Op %prec POSTFIXOP - { $$ = (Node *) makeA_Expr(AEXPR_OP, $2, $1, NULL, @2); } | b_expr IS DISTINCT FROM b_expr %prec IS { $$ = (Node *) makeSimpleA_Expr(AEXPR_DISTINCT, "=", $1, $5, @2); --- 7552,7557 ---- *************** unreserved_keyword: *** 8817,8822 **** --- 8938,8944 ---- | FORCE | FORWARD | FUNCTION + | GENERATED | GLOBAL | GRANTED | HANDLER *************** unreserved_keyword: *** 8877,8882 **** --- 8999,9005 ---- | OIDS | OPERATOR | OPTION + | OVERRIDING | OWNED | OWNER | PARTIAL *************** reserved_keyword: *** 9103,9108 **** --- 9226,9232 ---- | 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-26 10:26:46.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-26 10:26:46.000000000 +0200 *************** *** 14,20 **** --- 14,22 ---- #include "postgres.h" #include "access/heapam.h" + #include "catalog/pg_attrdef.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 **** --- 538,548 ---- 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 **** --- 616,622 ---- for (attrno = 1; attrno <= numattrs; attrno++) { TargetEntry *new_tle = new_tles[attrno - 1]; + char behaviour; 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. */ ! behaviour = column_default_behaviour(target_relation, attrno); ! if (commandType == CMD_UPDATE && behaviour == DEFAULT_GENERATED && ! 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 || (behaviour != DEFAULT_NORMAL && !commandOverride)) && commandType == CMD_INSERT) || (new_tle && new_tle->expr && IsA(new_tle->expr, SetToDefault))) { Node *new_expr; + /* Skip computing DEFAULT for GENERATED columns */ + if (behaviour == DEFAULT_GENERATED) + { + 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 (behaviour == DEFAULT_NORMAL) + 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,963 ---- } + char + column_default_behaviour(Relation rel, int attrno) + { + TupleDesc rd_att = rel->rd_att; + char behaviour = DEFAULT_NORMAL; + + /* + * 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) + { + behaviour = defval[ndef].adtyp; + break; + } + } + } + return behaviour; + } + + /* 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-26 10:26:46.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-26 10:26:46.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)) { *************** get_oper_expr(OpExpr *expr, deparse_cont *** 4138,4157 **** Oid opno = expr->opno; List *args = expr->args; - if (!PRETTY_PAREN(context)) - appendStringInfoChar(buf, '('); if (list_length(args) == 2) { /* binary operator */ Node *arg1 = (Node *) linitial(args); Node *arg2 = (Node *) lsecond(args); get_rule_expr_paren(arg1, context, true, (Node *) expr); appendStringInfo(buf, " %s ", generate_operator_name(opno, exprType(arg1), exprType(arg2))); get_rule_expr_paren(arg2, context, true, (Node *) expr); } else { --- 4095,4118 ---- Oid opno = expr->opno; List *args = expr->args; if (list_length(args) == 2) { /* binary operator */ Node *arg1 = (Node *) linitial(args); Node *arg2 = (Node *) lsecond(args); + if (!PRETTY_PAREN(context)) + appendStringInfoChar(buf, '('); + get_rule_expr_paren(arg1, context, true, (Node *) expr); appendStringInfo(buf, " %s ", generate_operator_name(opno, exprType(arg1), exprType(arg2))); get_rule_expr_paren(arg2, context, true, (Node *) expr); + + if (!PRETTY_PAREN(context)) + appendStringInfoChar(buf, ')'); } else { *************** get_oper_expr(OpExpr *expr, deparse_cont *** 4169,4194 **** switch (optup->oprkind) { case 'l': appendStringInfo(buf, "%s ", generate_operator_name(opno, InvalidOid, exprType(arg))); get_rule_expr_paren(arg, context, true, (Node *) expr); break; case 'r': get_rule_expr_paren(arg, context, true, (Node *) expr); appendStringInfo(buf, " %s", generate_operator_name(opno, exprType(arg), InvalidOid)); break; default: elog(ERROR, "bogus oprkind: %d", optup->oprkind); } ReleaseSysCache(tp); } - if (!PRETTY_PAREN(context)) - appendStringInfoChar(buf, ')'); } /* --- 4130,4159 ---- switch (optup->oprkind) { case 'l': + if (!PRETTY_PAREN(context)) + appendStringInfoChar(buf, '('); appendStringInfo(buf, "%s ", generate_operator_name(opno, InvalidOid, exprType(arg))); get_rule_expr_paren(arg, context, true, (Node *) expr); + if (!PRETTY_PAREN(context)) + appendStringInfoChar(buf, ')'); break; case 'r': + appendStringInfoChar(buf, '('); get_rule_expr_paren(arg, context, true, (Node *) expr); appendStringInfo(buf, " %s", generate_operator_name(opno, exprType(arg), InvalidOid)); + appendStringInfoChar(buf, ')'); break; default: elog(ERROR, "bogus oprkind: %d", optup->oprkind); } ReleaseSysCache(tp); } } /* 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-26 10:26:46.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-26 10:26:46.000000000 +0200 *************** RelationBuildTupleDesc(Relation relation *** 484,489 **** --- 484,490 ---- relation->rd_rel->relnatts * sizeof(AttrDefault)); attrdef[ndef].adnum = attp->attnum; + attrdef[ndef].adtyp = DEFAULT_NORMAL; attrdef[ndef].adbin = NULL; ndef++; } *************** AttrDefaultFetch(Relation relation) *** 2701,2706 **** --- 2702,2709 ---- else found++; + attrdef[i].adtyp = adform->adtyp; + 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-17 00:40:24.000000000 +0200 --- pgsql/src/bin/pg_dump/pg_dump.c 2007-04-26 19:14:14.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,1301 **** /* 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; } else --- 1295,1304 ---- /* must use 2 steps here 'cause fmtId is nonreentrant */ appendPQExpBuffer(copyBuf, "COPY %s ", fmtId(tbinfo->dobj.name)); ! appendPQExpBuffer(copyBuf, "%s %sFROM stdin%s;\n", fmtCopyColumnList(tbinfo), ! (tdinfo->oids && tbinfo->hasoids) ? "WITH OIDS " : "", ! tbinfo->hasgenerated ? " OVERRIDING SYSTEM VALUE" : ""); copyStmt = copyBuf->data; } else *************** 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,4456 ---- 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, adtyp " ! "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, ' ' as adtyp " "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); --- 4459,4465 ---- { /* 7.2 did not have OIDs in pg_attrdef */ appendPQExpBuffer(q, "SELECT tableoid, 0 as oid, adnum, " ! "pg_get_expr(adbin, adrelid) AS adsrc, ' ' as adtyp " "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); --- 4467,4473 ---- else if (g_fout->remoteVersion >= 70100) { /* no pg_get_expr, so must rely on adsrc */ ! appendPQExpBuffer(q, "SELECT tableoid, oid, adnum, adsrc, ' ' as adtyp " "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); --- 4477,4483 ---- /* no pg_get_expr, no tableoid either */ appendPQExpBuffer(q, "SELECT " "(SELECT oid FROM pg_class WHERE relname = 'pg_attrdef') AS tableoid, " ! "oid, adnum, adsrc, ' ' as adtyp " "FROM pg_attrdef " "WHERE adrelid = '%u'::oid", tbinfo->dobj.catId.oid); *************** getTableAttrs(TableInfo *tblinfo, int nu *** 4488,4493 **** --- 4499,4507 ---- attrdefs[j].adtable = tbinfo; attrdefs[j].adnum = adnum = atoi(PQgetvalue(res, j, 2)); attrdefs[j].adef_expr = strdup(PQgetvalue(res, j, 3)); + attrdefs[j].adtyp = PQgetvalue(res, j, 4)[0]; + if (attrdefs[j].adtyp == 'g') + 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: */ --- 4515,4521 ---- * safe, we mark the default as needing to appear before the * CREATE. */ ! if (tbinfo->relkind == RELKIND_VIEW || attrdefs[j].adtyp != ' ') { 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); /* --- 8161,8176 ---- appendPQExpBuffer(q, "ALTER TABLE %s ", fmtId(tbinfo->dobj.name)); ! appendPQExpBuffer(q, "ALTER COLUMN %s ", ! fmtId(tbinfo->attnames[adnum - 1])); ! ! if (adinfo->adtyp == 'i') ! appendPQExpBuffer(q, "SET GENERATED ALWAYS AS IDENTITY;\n"); ! else if (adinfo->adtyp == 'g') ! 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-26 10:26:46.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 *** 283,288 **** --- 284,290 ---- DumpableObject dobj; TableInfo *adtable; /* link to table of attribute */ int adnum; + char adtyp; char *adef_expr; /* decompiled DEFAULT expression */ bool separate; /* TRUE if must dump as separate item */ } AttrDefInfo; diff -dcrpN pgsql.orig/src/bin/psql/copy.c pgsql/src/bin/psql/copy.c *** pgsql.orig/src/bin/psql/copy.c 2007-03-23 12:18:57.000000000 +0100 --- pgsql/src/bin/psql/copy.c 2007-04-26 11:15:02.000000000 +0200 *************** struct copy_options *** 63,68 **** --- 63,69 ---- char *file; /* NULL = stdin/stdout */ bool psql_inout; /* true = use psql stdin/stdout */ bool from; + bool overriding; bool binary; bool oids; bool csv_mode; *************** parse_slash_copy(const char *args) *** 214,219 **** --- 215,246 ---- goto error; } + if (pg_strcasecmp(token, "overriding") == 0) + { + bool overriding; + + token = strtokx(NULL, whitespace, NULL, NULL, + 0, false, false, pset.encoding); + if (pg_strcasecmp(token, "system") == 0) + overriding = true; + else if (pg_strcasecmp(token, "user") == 0) + overriding = false; + else + goto error; + + token = strtokx(NULL, whitespace, NULL, NULL, + 0, false, false, pset.encoding); + if (pg_strcasecmp(token, "value") != 0) + goto error; + + token = strtokx(NULL, whitespace, ".,()", "\"", + 0, false, false, pset.encoding); + if (!token) + goto error; + + result->overriding = overriding; + } + if (pg_strcasecmp(token, "from") == 0) result->from = true; else if (pg_strcasecmp(token, "to") == 0) *************** parse_slash_copy(const char *args) *** 373,378 **** --- 400,419 ---- else goto error; } + else if (pg_strcasecmp(token, "overriding") == 0) + { + token = strtokx(NULL, whitespace, NULL, NULL, + 0, false, false, pset.encoding); + if (pg_strcasecmp(token, "system") == 0) + result->overriding = true; + else + goto error; + + token = strtokx(NULL, whitespace, NULL, NULL, + 0, false, false, pset.encoding); + if (pg_strcasecmp(token, "value") != 0) + goto error; + } else goto error; *************** do_copy(const char *args) *** 450,456 **** else appendPQExpBuffer(&query, "TO STDOUT"); - if (options->binary) appendPQExpBuffer(&query, " BINARY "); --- 491,496 ---- *************** do_copy(const char *args) *** 481,486 **** --- 521,529 ---- if (options->force_notnull_list) appendPQExpBuffer(&query, " FORCE NOT NULL %s", options->force_notnull_list); + if (options->overriding) + appendPQExpBuffer(&query, " OVERRIDING SYSTEM VALUE "); + if (options->file) canonicalize_path(options->file); 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-26 10:26:46.000000000 +0200 *************** *** 22,27 **** --- 22,28 ---- typedef struct attrDefault { AttrNumber adnum; + char adtyp; char *adbin; /* nodeToString representation of expr */ } AttrDefault; 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-26 10:26:46.000000000 +0200 *************** *** 20,25 **** --- 20,26 ---- typedef struct RawColumnDefault { AttrNumber attnum; /* attribute to attach default to */ + char behaviour; /* normal DEFAULT, GENERATED ALWAYS AS { IDENTITY | ( expr ) } */ Node *raw_default; /* default value (untransformed parse tree) */ } RawColumnDefault; *************** typedef struct CookedConstraint *** 29,34 **** --- 30,36 ---- char *name; /* name, or NULL if none */ AttrNumber attnum; /* which attr (only for DEFAULT) */ Node *expr; /* transformed default or check expr */ + char behaviour; /* normal DEFAULT, GENERATED ALWAYS AS { IDENTITY | ( expr ) } */ } 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); --- 73,86 ---- List *rawColDefaults, List *rawConstraints); ! extern void StoreAttrDefault(Relation rel, AttrNumber attnum, char *adbin, char adtyp); extern Node *cookDefault(ParseState *pstate, Node *raw_default, Oid atttypid, int32 atttypmod, ! char *attname, ! char behaviour); 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-26 10:26:46.000000000 +0200 *************** CATALOG(pg_attrdef,2604) *** 37,42 **** --- 37,43 ---- { Oid adrelid; int2 adnum; + char adtyp; 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 */ --- 53,75 ---- * compiler constants for pg_attrdef * ---------------- */ ! #define Natts_pg_attrdef 5 #define Anum_pg_attrdef_adrelid 1 #define Anum_pg_attrdef_adnum 2 ! #define Anum_pg_attrdef_adtyp 3 ! #define Anum_pg_attrdef_adbin 4 ! #define Anum_pg_attrdef_adsrc 5 ! ! /* ---------------- ! * values for adtyp to control behaviour of ! * computing DEFAULT expressions ! * ' ' - normal DEFAULT expression ! * 'i' - GENERATED ALWAYS AS IDENTITY ! * 'g' - GENERATED ALWAYS AS ( expr ) ! * ---------------- ! */ ! #define DEFAULT_NORMAL ' ' ! #define DEFAULT_IDENTITY 'i' ! #define DEFAULT_GENERATED 'g' #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-26 10:26:46.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-26 10:26:46.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-16 12:56:01.000000000 +0200 --- pgsql/src/include/nodes/parsenodes.h 2007-04-26 14:15:01.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,398 ---- Node *raw_default; /* default value (untransformed parse tree) */ char *cooked_default; /* nodeToString representation */ List *constraints; /* other constraints on column */ + char behaviour; /* behaviour of computing DEFAULT expressions */ } ColumnDef; /* *************** typedef struct InsertStmt *** 673,678 **** --- 677,683 ---- 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 */ --- 914,923 ---- 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/DROP IDENTITY */ ! AT_SetGenerated /* SET GENERATED */ } AlterTableType; typedef struct AlterTableCmd /* one subcommand of an ALTER TABLE */ *************** typedef struct AlterTableCmd /* one subc *** 922,927 **** --- 930,937 ---- * index, constraint, or parent table */ Node *transform; /* transformation expr for ALTER TYPE */ DropBehavior behavior; /* RESTRICT or CASCADE for DROP cases */ + char default_behaviour; /* NORMAL, IDENTITY, GENERATED */ + List *seq_opts; } AlterTableCmd; *************** 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 --- 1109,1116 ---- 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 **** --- 1124,1131 ---- List *options; /* options from WITH clause */ char *indexspace; /* index tablespace for PKEY/UNIQUE * constraints; NULL for default */ + char behaviour; /* behaviour of computing DEFAULT expression */ + 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-26 10:26:46.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-26 10:26:46.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 char column_default_behaviour(Relation rel, int attrno); #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-26 10:26:46.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-26 18:50:19.000000000 +0200 *************** *** 0 **** --- 1,838 ---- + -- + -- 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 generated always as identity; + 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 generated always as identity; + 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 SET GENERATED ... AS IDENTITY + -- + create table id (id integer, name text); + alter table id alter id set generated by default as identity; + NOTICE: ALTER 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 (3, 'b'); + insert into id (id, name) values (default, 'c'); + select * from id; + id | name + ----+------ + 1 | a + 3 | b + 2 | c + (3 rows) + + alter table id alter id drop default; + -- DROP sequence to have it re-created + drop sequence id_id_seq; + delete from id; + alter table id alter id set generated always as identity; + NOTICE: ALTER 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 (3, 'b'); + insert into id (id, name) values (default, 'c'); + select * from id; + id | name + ----+------ + 1 | a + 2 | b + 3 | c + (3 rows) + + drop table id; + -- + -- Test bad (not castable from BIGINT) types as IDENTITY + -- + create table id (id box generated always as identity, name text); + NOTICE: CREATE TABLE will create implicit sequence "id_id_seq" for serial column "id.id" + ERROR: column "id" is of type box but default expression is of type bigint + HINT: You will need to rewrite or cast the expression. + drop table id; + ERROR: table "id" does not exist + create table id (id box, name text); + alter table id alter id set generated always as identity; + NOTICE: ALTER TABLE will create implicit sequence "id_id_seq" for serial column "id.id" + ERROR: column "id" is of type box but default expression is of type bigint + HINT: You will need to rewrite or cast the expression. + drop table id; + -- + -- 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 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) from stdin overriding system value; + 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) from stdin overriding system value; + 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; + -- + -- Test \copy psql builtin with 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) from stdin overriding system value + select * from id; + id | i1 | i2 | g1 + ----+----+----+---- + 1 | | | -2 + 2 | 10 | | 37 + 3 | | 20 | 36 + 4 | 10 | 20 | 25 + (4 rows) + + drop table id; + -- + -- Test \copy psql builtin 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-26 10:26:46.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-26 10:26:46.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-26 18:47:37.000000000 +0200 *************** *** 0 **** --- 1,440 ---- + -- + -- 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 generated always as identity; + 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 generated always as identity; + insert into di (di, name) values (default, 'a'); + select * from di; + -- + -- Test SET GENERATED ... AS IDENTITY + -- + create table id (id integer, name text); + alter table id alter id set generated by default as identity; + insert into id (id, name) values (default, 'a'); + insert into id (id, name) values (3, 'b'); + insert into id (id, name) values (default, 'c'); + select * from id; + alter table id alter id drop default; + -- DROP sequence to have it re-created + drop sequence id_id_seq; + delete from id; + alter table id alter id set generated always as identity; + insert into id (id, name) values (default, 'a'); + insert into id (id, name) values (3, 'b'); + insert into id (id, name) values (default, 'c'); + select * from id; + drop table id; + + -- + -- Test bad (not castable from BIGINT) types as IDENTITY + -- + create table id (id box generated always as identity, name text); + drop table id; + create table id (id box, name text); + alter table id alter id set generated always as identity; + drop table id; + + -- + -- 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 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) from stdin overriding system value; + \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) from stdin overriding system value; + 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; + -- + -- Test \copy psql builtin with 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) from stdin overriding system value + 1 \N \N -2 + 2 10 \N 37 + 3 \N 20 36 + 4 10 20 25 + \. + select * from id; + drop table id; + -- + -- Test \copy psql builtin 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;