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

Your patch has been added to the PostgreSQL unapplied patches list at:

    http://momjian.postgresql.org/cgi-bin/pgpatches

It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.

---------------------------------------------------------------------------


Zoltan Boszormenyi wrote:
> 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
>

[ application/x-tar is not supported, skipping... ]

>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
>                http://archives.postgresql.org

--
  Bruce Momjian  <bruce@momjian.us>          http://momjian.us
  EnterpriseDB                               http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +