Tom Lane írta:
After some more study of the SQL spec, the distinction between GENERATED
ALWAYS AS IDENTITY and GENERATED BY DEFAULT AS IDENTITY is not what
I thought it was.
* As far as I can find from the spec, there is *no* difference between
the two cases for INSERT commands. The rule is that you ignore any
user-supplied data and use the default (ie, nextval()) unless OVERRIDING
SYSTEM VALUE is specified. It is not an error to try to insert data
into an identity column, it's just ignored unless OVERRIDING SYSTEM
VALUE.
* The difference for UPDATE commands is that you can update a BY DEFAULT
identity column to anything you want, whereas for an ALWAYS identity
it's an error to update to anything but DEFAULT (which causes a fresh
nextval() to be assigned). Both behaviors are different from a
generated column, which is updated whether you mention it or not.
The quoted SIGMOD paper mentioned that specifying a value
for a generated column should raise an error in INSERT but
this behaviour is not mentioned by the standard.
BTW, do you know what's a "self-referencing column"?
I haven't found a definition of it and there are places where the standard
uses this term on behaviour that would be natural for generated columns.
E.g. page 860 in latest drafts, section 10.14, or SQL:2003, section 14.8,
about INSERT statement: the value the user specified should be stored if
"some underlying column of Ci is a self-referencing column and
OVERRIDING SYSTEM VALUE is specified."
This means that GENERATED BY DEFAULT AS IDENTITY is not at all
equivalent to our historical behavior for SERIAL columns and hence we
cannot merge the two cases.
Yes, they are equivalent if you read 5IWD2-02-Foundation-2006-04.pdf
or 5CD2-02-Foundation-2006-01.pdf, i.e. the latest two drafts.
(The latter seems to be misnamed considering that www.wiscorp.com
refreshed the sql200n.zip on 2007-03-11.) Page 860, section 14.10, INSERT.
The value the user provides should be accepted for storage if:
- the column is an identity column and you provide
OVERRIDING SYSTEM VALUE, or
- the column is an GENERATED BY DEFAULT AS IDENTITY
and you provide neither OVERRIDING USER VALUE nor
the DEFAULT specification for the column.
I think the babble about OVERRIDING USER VALUE
is somewhat controversial. Why would you want to do e.g.
INSERT INTO tabname (id, ...) OVERRIDING USER VALUE (N, ...);
where N is an explicit constant? And I haven't even implemented
handling it. Anyway, without specifying OVERRIDING USER VALUE
the GENERATED BY DEFAULT AS IDENTITY is
equivalent with traditional SERIAL in PostgreSQL.
Implementing OVERRIDING USER VALUE behaviour means
that GENERATED BY DEFAULT AS IDENTITY (or SERIAL) would be
marked as an identity as well, not as a column simply having a DEFAULT
clause.
Otherwise OVERRIDING USER VALUE would override every
user-specified value for regular columns having a DEFAULT expression.
The lack of any behavioral difference for INSERT seems surprising
and counterintuitive; have I just missed something in the spec?
No, I was just ahead of the times and read newer drafts than SQL:2003.
BTW, I found what they did about the problem that generated columns
are out of sync with their underlying columns during BEFORE-trigger
execution: in 11.39
12)If BEFORE is specified, then:
...
c) The <triggered action> shall not contain a <field
reference> that references a field in the new transition
variable corresponding to a generated column of T.
I vaguely remember reading it, although the idea seem to have remained
in my mind. :-)
IOW they just pretend you can't look. So I think we need not worry
about leaving the values out-of-date until after the triggers fire.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
--
----------------------------------
Zoltán Böszörményi
Cybertec Geschwinde & Schönig GmbH
http://www.postgresql.at/
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
