Re: Behavior of GENERATED columns per SQL2003

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/