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