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
psql-serial-30.diff.gz
Description: Unix tar archive
---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
