Re: Identity columns should own only one sequence

On Thu, 2019-04-25 at 09:55 +0900, Michael Paquier wrote:
> On Sun, Apr 14, 2019 at 05:51:47PM +0200, Laurenz Albe wrote:
> > test=> INSERT INTO ser (id) VALUES (DEFAULT);
> > ERROR: more than one owned sequence found
>
> Yes this should never be user-triggerable, so it seems that we need to
> fix and back-patch something if possible.
>
> > I propose that we check if there already is a dependent sequence
> > before adding an identity column.
>
> That looks awkward. Souldn't we make sure that when dropping the
> default associated with a serial column then the dependency between
> the column and the sequence is removed instead? This implies more
> complication in ATExecColumnDefault().
>
> > The attached patch does that, and also forbids setting the ownership
> > of a sequence to an identity column.
> >
> > I think this should be backpatched.
>
> Could you add some test cases with what you think is adapted?

You are right! Dropping the dependency with the DEFAULT is the
cleanest approach.

I have left the checks to prevent double sequence ownership from
happening.

I added regression tests covering all added code.

Yours,
Laurenz Albe