Re: identity columns

On 3/21/17 16:11, Vitaly Burovoy wrote:
> I've just checked and still get an error about a type, not about
> absence of a column:
> test=# CREATE TABLE itest3 (a int generated by default as identity, b text);
> CREATE TABLE
> test=# ALTER TABLE itest3 ALTER COLUMN o ADD GENERATED ALWAYS AS IDENTITY;
> ERROR: identity column type must be smallint, integer, or bigint

OK, I have a fix.

> My argument is consistency.
> Since IDENTITY is a property of a column (similar to DEFAULT, NOT
> NULL, attributes, STORAGE, etc.), it follows a different rule: it is
> either set or not set. If it did not set before, the "SET" DDL "adds"
> it, if that property already present, the DDL replaces it.
> There is no "ADD" clause in DDLs like "...ALTER table ALTER column..."
> (only "SET", "RESET" and "DROP")[2].
> Your patch introduces the single DDL version with "...ALTER column
> ADD..." for a property.

But it creates a sequence, so it creates state. So mistakes could
easily be masked. With my patch, if you do ADD twice, you get an error.
With your proposal, you'd have to use SET, and you could overwrite
existing sequence state without realizing it.

>> It does change the type, but changing the type doesn't change the
>> limits. That is a property of how ALTER SEQUENCE works, which was
>> separately discussed.
>
> Are you about the thread[1]? If so, I'd say the current behavior is not good.
> I sent an example with users' bad experience who will know nothing
> about sequences (because they'll deal with identity columns).
> Would it be better to change bounds of a sequence if they match the
> bounds of an old type (to the bounds of a new type)?

That's an idea, but that's for a separate patch.

--
Peter Eisentraut http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services