Identity columns don't work if they own more than one sequence.
So if one tries to convert a "serial" column to an identity column,
the following can happen:
test=> CREATE TABLE ser(id serial);
CREATE TABLE
test=> ALTER TABLE ser ALTER id ADD GENERATED ALWAYS AS IDENTITY;
ERROR: column "id" of relation "ser" already has a default value
Hm, ok, let's drop the column default value.
test=> ALTER TABLE ser ALTER id DROP DEFAULT;
ALTER TABLE
Now it works:
test=> ALTER TABLE ser ALTER id ADD GENERATED ALWAYS AS IDENTITY;
ALTER TABLE
But not very much:
test=> INSERT INTO ser (id) VALUES (DEFAULT);
ERROR: more than one owned sequence found
I propose that we check if there already is a dependent sequence
before adding an identity column.
The attached patch does that, and also forbids setting the ownership
of a sequence to an identity column.
I think this should be backpatched.
Yours,
Laurenz Albe
