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>:

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 &#8212; 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.

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

  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

Reply via email to