Re: Identity columns should own only one sequence

On Fri, 2019-04-26 at 15:23 +0200, Peter Eisentraut wrote:
> > 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
>
> You also need to run
>
> ALTER SEQUENCE ser_id_seq OWNED BY NONE;
>
> because dropping the default doesn't release the linkage of the sequence
> with the table. These are just weird artifacts of how serial is
> implemented, but that's why identity columns were added to improve
> things. I don't think we need to make things more complicated here.

What do you think of the patch I just posted on this thread to
remove ownership automatically when the default is dropped, as Michael
suggested? I think that would make things much more intuitive from
the user's perspective.

Correct me if I am wrong, but the sequence behind identity columns
should be an implementation detail that the user doesn't have to know about.
So the error message about "owned sequences" is likely to confuse users.

I have had a report by a confused user, so I think the problem is real.

Yours,
Laurenz Albe