Re: Behavior of GENERATED columns per SQL2003

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.

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.

The lack of any behavioral difference for INSERT seems surprising
and counterintuitive; have I just missed something in the spec?

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.

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