From: | Böszörményi Zoltán <zboszor(at)dunaweb(dot)hu> |
---|---|
To: | hackers(at)postgresql(dot)org |
Cc: | "Bruce Momjian" <bruce(at)momjian(dot)us>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Subject: | Question about GENERATED/IDENTITY |
Date: | 2006-08-17 09:01:40 |
Message-ID: | 4302.213.163.11.81.1155805300.squirrel@www.dunaweb.hu |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Stark | 2006-08-17 09:07:18 | Re: Enum proposal / design |
Previous Message | Stefan Kaltenbrunner | 2006-08-17 08:59:24 | Re: Going for "all green" buildfarm results |