Re: identity columns

On 3/21/17, Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com> wrote:
> On 3/21/17 16:11, Vitaly Burovoy wrote:
>> 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.

Right. But it is an internal mechanism. DDL is not about creating a
sequence, it is about changing a property.

> So mistakes could easily be masked. With my patch, if you do ADD twice, you get an error.

Agree. But what's for? Whether that parameters are incompatible (and
can't be changed later)?

> With your proposal, you'd have to use SET, and you could overwrite
> existing sequence state without realizing it.

I can't overwrite its state (current value), only its settings like
start, maxval, etc.

In fact when I write a DDL I want to change a schema. For
non-properties it is natural to write "CREATE" (schema, table) and
"ADD" (column, constraints) because there can be many of them (with
different names) in a single object: many schemas in a DB, many tables
in a schema, many columns in a table and even many constraints in a
table. So ADD is used for adding objects which have a name to some
container (DB, schema, table).
It is not true for the IDENTITY property. You can have many identity
columns, but you can not have many of them in a single column.

Column's IDENTITY behavior is very similar to a DEFAULT one. We write
"SET DEFAULT" and don't care whether it was set before or not, because
we can't have many of them for a single column. Why should we do that
for IDENTITY?

Whether I write "ADD" or "SET" I want to have a column with some
behavior and I don't mind what behavior it has until it is
incompatible with my wish (e.g. it has DEFAULT, but I want IDENTITY or
vice versa).

>>> 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.

It is very likely to have one in Postgres10. I'm afraid in the other
case we'll impact with many bug reports similar to my example.

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

--
Best regards,
Vitaly Burovoy