Re: Final version of IDENTITY/GENERATED patch
От | Bruce Momjian |
---|---|
Тема | Re: Final version of IDENTITY/GENERATED patch |
Дата | |
Msg-id | 200703021837.l22IbpF04311@momjian.us обсуждение исходный текст |
Ответ на | Re: Final version of IDENTITY/GENERATED patch (Zoltan Boszormenyi <zboszor@dunaweb.hu>) |
Ответы |
Re: Final version of IDENTITY/GENERATED patch
(Zoltan Boszormenyi <zboszor@dunaweb.hu>)
|
Список | 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. +
В списке pgsql-patches по дате отправления: