New version of IDENTITY/GENERATED
Hi,
I started working on my previous patch, encouraged
by the fact that it became a wishlist item for 8.3. :-)
The changes in this version are:
- Refreshed to almost current (5 days old)
CVS version of 8.3 devel
- The original SERIAL pseudo type is left alone,
you _have to_ spell out GENERATED
{ ALWAYS | BY DEFAULT} AS IDENTITY
to get an identity column.
- The "action-at-a-distance" behaviour is actually working
for the IDENTITY/GENERATED columns on INSERT
so the DEFAULT value is generated for them
after all the regular columns were validated via
ExecConstraints(). This way, if the validation fails,
the sequence isn't inflated.
- Test case is updated to reflect the above.
- Documentation is updated, "Identity columns" have a new
subsection now.
- Dropped my pg_dump changes, as the altered sequence is
also dumped in 8.2, thanks to Tom Lane.
I am considering the following:
- Since the IDENTITY is a new feature (plain old SERIAL
behaves the same as always) I will restore the SQL:2003
confromant check that there can be only one identity column
in a table at any time.
- I read somewhere (but couldn't find it now in SQL:2003)
that CHECK constraints cannot be defined for GENERATED
(and IDENTITY?) columns. Maybe it was in the latest draft,
I have to look at it... Anyway, I have to implement checks
to disallow CHECKs for such columns.
- Introduce an ALTER TABLE SET|DROP IDENTITY so
a serial can be "upgraded" to an identity. This way, an identity
column can be built by hand and pg_dump will need it, too.
SET IDENTITY will either have to issue an error if CHECKs
defined for such columns or automatically drop every such
constraints.
And I have a question, too. Is there a way to use ExecEvalExpr*()
so values from a given tuples are used for "current" row? E.g.
at present, UPDATE table SET f1 = f1 + 1, f2 = f1 + 1;
sets both fields' new value to (f1 value before UPDATE) + 1.
For a GENERATED column, value _after_ UPDATE
is needed, so
CREATE TABLE table (
f1 INTEGER,
f2 INTEGER GENERATED ALWAYS AS (f1 + 1));
and no matter which one of the following is used:
UPDATE table SET f1 = f1 + 1;
or
UPDATE table SET f1 = f1 + 1, f2 = default;
the f2 current value = f1 current value + 1 is always maintained.
Best regards,
Zolt�n B�sz�rm�nyi
Attachments:
Hi,
here comes the next version. Changes:
* Implemented ALTER TABLE ... DROP IDENTITY
and SET GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY
* Extended documentation and testcase for the above.
* pg_dump now dumps the proper ALTER for such columns.
Reintroduced the pg_dump feature to dump
OVERRIDING SYSTEM VALUE for INSERT and
COPY statements if the table schema contains any
GENERATED ALWAYS column.
* There can be only one IDENTITY column in a table at any time.
This is checked on CREATE, ALTER TABLE ADD and
ALTER TABLE SET ... IDENTITY and required for SQL:2003
compliance.
* On update, the GENERATED columns' late generation
of DEFAULT value is also implemented. The GENERATED
ALWAYS AS ( expr ) still only deals within the same
limits as regular DEFAULT.
* Implemented a check so CHECK constraints cannot
reference IDENTITY/GENERATED columns.
It is checked on CREATE AND ALTER TABLE
ADD CONSTRAINT and ALTER TABLE ADD COLUMN.
It makes sense because IDENTITY and GENERATED
columns has to be assigned after all other columns were
assigned with values and validated via constraints.
I hereby declare the IDENTITY part feature complete.
Please review.
Best regards,
Zolt�n B�sz�rm�nyi