Re: Final version of IDENTITY/GENERATED patch

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(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://www.enterprisedb.com

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