Re: Behavior of GENERATED columns per SQL2003 - Mailing list pgsql-hackers

Zoltan Boszormenyi <zb@cybertec.at> writes:
> Tom Lane �rta:
>> 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.

Hm.  So what we've got here is that the committee has decided the 2003
spec is broken, and they may someday come out with a revised definition
that might, or might not, bear any resemblance to the current 200n
working papers.  According to some off-list discussion, nobody is
entirely sure what the current draft is trying to say anyway.

That about tears it for me: I think we should reject at least the
IDENTITY parts of this patch, and very likely the entire thing.  I've
spent more than three days now trying to get it into a committable form,
time I can't really afford to spend right now on a patch that adds such
a marginal feature.

AFAICS the only actual new feature that IDENTITY adds is the ability to
make the default expression silently override user-specified insertion
data, as in fact was the use-case argued by you here:
http://archives.postgresql.org/pgsql-hackers/2006-08/msg00038.php

Now we find that as the spec is actually written, it doesn't work that
way: if you try to specify a value other than DEFAULT for an inserted
column, you get a syntax error, unless you specify OVERRIDING SYSTEM
VALUE or OVERRIDING USER VALUE, neither of which are going to be found
in legacy apps ported from other DBMSes, and neither of which add any
actual new functionality (if you have to write OVERRIDING USER VALUE,
you could just as well not specify the column).  So I'm seeing a lot
of complexity and a lot of confusion added for not much, not even
considering the risks of trying to track a moving standards target.

As for GENERATED ALWAYS AS (expr), now that we understand that it's not
supposed to define a virtual column, what's the point?  You can get the
same behavior with a trivial BEFORE INSERT/UPDATE trigger that
recomputes the derived value, and you don't have to buy into the rather
ill-defined spec behavior (in particular the point that the generated
column is effectively undefined during trigger firing seems really
poorly done).  In fact, given that the only plausible use-cases involve
expressions that are expensive to compute, a trigger can probably do
*better* than the built-in feature, since it can make use of application
knowledge about when a recomputation is really necessary.  The current
patch recomputes the expression on every UPDATE, and would have a hard
time being any brighter than that, given that we don't know what BEFORE
triggers might do to the row.

So at this point I'm feeling that we've poured a lot of effort down a
hole :-(.  We are not really close to having a patch that implements
the current 200n draft (in particular note that OVERRIDING USER VALUE
is not a no-op according to this morning's understanding of the draft).
Even if we were, I'd vote against implementing a part of the draft
that's clearly still in flux --- if they change it again, we'd be stuck.
But the real bottom line is that I don't see enough use-case for these
features to justify the work done already, let alone a bunch more work.
        regards, tom lane