Re: Identity columns should own only one sequence

On Tue, 2019-05-07 at 13:06 +0900, Michael Paquier wrote:
> On Fri, May 03, 2019 at 08:14:35AM +0200, Laurenz Albe wrote:
> > On Thu, 2019-05-02 at 22:43 +0200, Peter Eisentraut wrote:
> >> I think the proper way to address this would be to create some kind of
> >> dependency between the sequence and the default.
> > 
> > That is certainly true.  But that's hard to retrofit into existing databases,
> > so it would probably be a modification that is not backpatchable.
> 
> And this is basically already the dependency which exists between the
> sequence and the relation created with the serial column.  So what's
> the advantage of adding more dependencies if we already have what we
> need?  I still think that we should be more careful to drop the
> dependency between the sequence and the relation's column if dropping
> the default using it.  If a DDL defines first a sequence, and then a
> default expression using nextval() on a column, then no serial-related

I believe we should have both:

- Identity columns should only use sequences with an INTERNAL dependency,
  as in Peter's patch.

- When a column default is dropped, remove all dependencies between the
  column and sequences.

In the spirit of moving this along, I have attached a patch which is
Peter's patch from above with a regression test.

Yours,
Laurenz Albe