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 only for definitional purposes
in this International Standard. It is not
a state that can persist so as to be visible
in SQL-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 values based on
the old row values. E.g.
CREATE tab (
c1 GENERATED ALWAYS AS ( c2 * c2 ),
c2 integer DEFAULT 1);
INSERT INTO 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
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
