Re: BUG #18161: ALTER TABLE ... ALTER COLUMN does not remove dependencies in automatically from column and sequence.

On Wed, 2023-10-18 at 17:43 +0000, PG Bug reporting form wrote:
> PostgreSQL version: 15.4
>
> example=# CREATE TABLE example (i serial , j text);
> CREATE TABLE
> example=# alter table example add column i_new bigint;
> ALTER TABLE
> example=# alter table example alter column i drop default ;
> ALTER TABLE
> example=# alter table example alter column i_new set default
> nextval('example_i_seq'::regclass);
> ALTER TABLE
> example=# alter table example drop column i;
> ERROR:  cannot drop column i of table example because other objects depend
> on it
> DETAIL:  default value for column i_new of table example depends on sequence example_i_seq
> HINT:  Use DROP ... CASCADE to drop the dependent objects too.

That is working as intended.

If you create a "serial" column, PostgreSQL adds a dependency, just like
this statement would:

ALTER SEQUENCE example_i_seq OWNED BY example.i;

That dependency makes sure that the sequence is automatically deleted when
you drop the column. That relationship is not broken if you change the
default value or use the sequence elsewhere.

You never created the sequence explicitly, so you should consider it an
implementation details of "serial", just like the column default.
Manually changing the default or using the sequence for something else
messes with that on a lower level.

It is easy to remove the dependency:

ALTER SEQUENCE example_i_seq OWNED BY NONE;

Consider using the more advanced and standard conforming alternative
of identity columns. You will still find ways to mess with the underlying
sequence, but there is no column default you can change, and the sequence
name is not visible in the output of "\d", so you are less likely to fall
into this trap.

Yours,
Laurenz Albe