Re: GENERATED ... AS IDENTITY, Was: Re: Feature Freeze

On Tue, 2006-08-01 at 18:10 +0200, Zoltan Boszormenyi wrote:
> Hi,
>
> I have progressed a bit with my pet project, a.k.a $SUBJECT.
>
> Now GENERATED ALWAYS AS IDENTITY and
> GENERATED ALWAYS AS ( expr ) work as
> intended. Documentation was also extended.

I'm only commenting because I debated trying to implement this feature a
couple of times. The ugliness required for pg_dump put me off of doing
it.

I did not see a test for enforcement during COPY. UPDATE restrictions
appear to have been missed as well:

4) If <set clause> SC specifies an <object column> that
references a column of which some underlying column is either a
generated column or an identity column whose descriptor
indicates that values are always generated, then the <update
source> specified in SC shall consist of a <default
specification>.

<object column> is the <update target>, or the left hand side of the
equation. In short, if a column marked GENERATED ALWAYS is updated then
it must be to DEFAULT or not provided as an update target.

CREATE TABLE tab (col integer GENERATED ALWAYS AS IDENTITY);
UPDATE tab SET col = DEFAULT; -- ACCEPTED
UPDATE tab SET col = 1; -- ERROR

For db restoration (pg_dump), how do you restore to the same values as
previously if it is always regenerated? By making ALWAYS a suggestion
for some users instead of always enforced and providing an override
mechanism for it. I assume it only works for relation owners but I've
not figured out how the spec does permissions.

<override clause> ::=
OVERRIDING USER VALUE
| OVERRIDING SYSTEM VALUE

In short, pg_dump should append OVERRIDING SYSTEM VALUE to any insert or
copy for relations with an GENERATED ALWAYS identity column and the
backend will need to respect that.

ALWAYS is really only enforced for anyone who doesn't have permission to
specify otherwise.

Another one that got me is what do you do if you do this:

CREATE TABLE tab (col integer);
INSERT INTO tab VALUES (10);
ALTER TABLE tab ALTER col GENERATED ALWAYS AS IDENTITY;

What is the value for "tab"."col"? It would seem that the table should
be rewritten with all values for "col" recalculated -- thus it would be
'1'. But wait! Can we add the <override clause> here too to keep the old
values and change the enforcement for new tuples only?

> Some test cases are also included, that shows
> that ALTER TABLE ALTER TYPE keeps both
> the sequence and the GENERATED ALWAYS
> property. Gzipped patch is attached.
>
> Next steps are:
> - pg_dump support
> - more ALTER TABLE support for adding and
> dropping IDENTITY and GENERATED ALWAYS
> features
> - more testing
>
> I still maintain that I don't see any standard
> requirement between the GENERATED AS IDENTITY
> and NEXT VALUE FOR but obviously both
> require SEQUENCE as supported feature
> in parallel. I can be proven wrong, though,
> but please, quote section# and text where
> it can be found in the standard.
>
> As for why GENERATED ALWAYS AS IDENTITY
> is useful? Consider someone who is coming from
> another DBMS (Informix, Access, etc.) where
> "INSERT INTO table (id, ...) VALUES (0, ...);"
> inserts the next value for the autoincrementer field
> instead of 0. Leaving out fields from INSERT is
> not allowed in the source because of documentation
> reasons and writing DEFAULT is not handy or not
> found in that legacy DBMS' features.
> Multiply it with N applications that was written
> that way over the years of the lifespan of a large
> project, count in the human resistance to learn
> something new (say 2.5x multiplier, but that may be
> under-estimated :-) ) and a feature that help porting
> easier will be a cheered feature. IIRC Bruce Momjian
> himself wrote in this list that ease-of-use features
> can boost PostgreSQL userbase pretty quickly.
>
> So, please, review my patch in it's current state
> and decide whether it's a 8.2-worthy feature.
>
> BTW, is there anyone working on COPY FROM ( select ) feature?
>
> Thanks in advance and best regards,
> Zoltán Böszörményi
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
--