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
psql-serial-34.diff.gz
Description: Unix tar archive
---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
