Hi,
after some more reading, I am finally starting
to grasp what Tom Lane meant with "action at a
distance". I outline below the information that
I collected from the SQL2003 standard.
Under section 11.5 <default clause>:
Case:
a) If the descriptor of S indicates that it represents a column of which some underlying column is an identity column
or a generated column, then S is marked as unassigned. NOTE 250 — The notion of a site being unassigned is
onlyfor definitional purposes in this International Standard. It is not a state that can persist so as to be visible
inSQL-data. The treatment of unassigned sites is given in Subclause 14.19, "Effect of inserting tables into base
tables",and Subclause 14.22, "Effect of replacing rows in base tables".
I gathered this from those sections:
- During both INSERT and UPDATE, the IDENTITY and GENERATED columns are evaluated after all base columns. [1]
- Generated columns get their values based on the row's newly evaluated values, unlike base columns, that got their
valuesbased on the old row values. E.g. CREATE tab ( c1 GENERATED ALWAYS AS ( c2 * c2 ), c2 integer DEFAULT 1);
INSERTINTO tab (c2) VALUES (3); -- (c1, c2) := (9, 3) UPDATE tab SET c1 = DEFAULT, c2 = c1 + 1; -- (c1, c2) := (100,
10)
- If a column C is modified that the generated column GC depends on, effectively an ", gc = DEFAULT" is pulled in
implicitely.
Also, these have to be also implemented:
- the expression in GENERATED ALWAYS AS (expr) can be pretty much everything just like with the CHECK constraint. Like
CASE,column references from the same table, etc. No subselects.
- If a column C is dropped and there is a generated column GC that depend on C, GC should also be dropped
automatically.
- Trigger definition cannot contain reference to any generated columns.
[1]
So, if I recall correctly what was said about
NEXT VALUE FOR, if the above behaviour is
implemented, NEXT VALUE FOR can be an alias
of nextval(). It is still true, if I consider
the following explicit definition
CREATE SEQUENCE seq1;
CREATE TABLE tab ( col1 integer DEFAULT NEXT VALUE FOR seq1, ...);
Since col1 is a base column, not an identity,
the value must be computed during the first
evaluation pass, just like DEFAULT nextval(),
e.g. SERIAL works currently.
Did I misunderstood something?
Best regards,
Zoltán Böszörményi
