Re: Final version of IDENTITY/GENERATED patch

Hi!

Thanks.

However, in the meantime I made some changes
so the IDENTITY column only advances its sequence
if it fails its CHECK constraints or UNIQUE indexes.
I still have some work with expression indexes.
Should I post an incremental patch against this version
or a full patch when it's ready?
An incremental patch can still be posted when the feature
is agreed to be in 8.3 and actually applied. It only changes
some details in the new feature and doesn't change
behaviour of existing features.

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

Bruce Momjian írta:
> 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
>>
>
>