Re: Final version of IDENTITY/GENERATED patch - Mailing list pgsql-patches

Hi,

I think now this is really the final version.

Changes in this version is:
- when dropping a column that's referenced
   by a GENERATED column, the GENERATED
   column has to be also dropped. It's required by SQL:2003.
- COPY table FROM works correctly with IDENTITY
  and GENERATED columns
- extended testcase to show the above two

To reiterate all the features that accumulated
over time, here's the list:

- extended catalog (pg_attribute) to keep track whether
  the column is IDENTITY or GENERATED
- working GENERATED column that may reference
  other regular columns; it extends the DEFAULT
  infrastructure to allow storing complex expressions;
  syntax for such columns:
  colname type GENERATED ALWAYS AS ( expression )
- working IDENTITY column whose value is generated
  after all other columns (regular or GENERATED)
  are assigned with values and validated via their
  NOT NULL and CHECK constraints; this allows
  tighter numbering - the only case when there may be
  missing serials are when UNIQUE indexes are failed
  (which is checked on heap_insert() and heap_update()
   and is a tougher nut to crack)
  syntax is:
  colname type GENERATED { ALWAYS | BY DEFAULT }
              AS IDENTITY [ ( sequence options ) ]
  the original SERIAL pseudo-type is left unmodified, the IDENTITY
  concept is new and extends on it - PostgreSQL may have multiple
  SERIAL columns in a table, but SQL:2003 requires that at most
  one IDENITY column may exist in a table at any time
- Implemented the following TODOs:
  - %Have ALTER TABLE RENAME rename SERIAL sequence names
  - Allow SERIAL sequences to inherit permissions from the base table?
    Actually the roles that have INSERT or UPDATE permissions
    on the table gain permission on the sequence, too.
    This makes the following TODO unneeded:
  - Add DEFAULT .. AS OWNER so permission checks are done as the table owner
    This would be useful for SERIAL nextval() calls and CHECK constraints.
- DROP DEFAULT is prohibited on GENERATED and IDENTITY columns
- One SERIAL column can be upgraded to IDENTITY via
  ALTER COLUMN column SET GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
  Same for downgrading, via:
  ALTER COLUMN column DROP IDENTITY
- COPY and INSERT may use OVERRIDING SYSTEM VALUE
  clause to override automatic generation and allow
  to import dumped data unmodified
- Update is forbidden for GENERATED ALWAYS AS IDENTITY
  columns entirely and for GENERATED ALWAYS AS (expr)
  columns for other values than DEFAULT.
- ALTER COLUMN SET <sequence options> for
  altering the supporting sequence; works on any
  SERIAL-like or IDENTITY columns
- ALTER COLUMN RESTART [WITH] N
  for changing only the next generated number in the
  sequence.
- The essence of pg_get_serial_sequence() is exported
  as get_relid_att_serial_sequence() to be used internally
  by checks.
- CHECK constraints cannot reference IDENTITY or
  GENERATED columns
- GENERATED columns cannot reference IDENTITY or
  GENERATED columns
- dropping a column that's referenced by a GENERATED column
  also drops the GENERATED column
- pg_dump dumps correct schema for IDENTITY and
  GENERATED columns:
  - ALTER COLUMN SET GENERATED ... AS IDENTITY
    for IDENTITY columns after ALTER SEQUENCE OWNED BY
  - correct GENERATED AS ( expression ) caluse in the table schema
- pg_dump dumps COPY OVERRIDING SYSTEM VALUE
  for tables' date that have any GENERATED or
  GENERATED ALWAYS AS IDENTITY columns.
- documentation and testcases

Please, review.

Best regards,
Zoltán Böszörményi