Cannot drop foreign key constraints that include a column with a serial type

[kvch]

When we are trying to drop a foreign key constraint (e.g. with drop_multicolumn_constraint) including a column that has serial type, we get an error when we try to complete the migration:

cannot drop column {serial_column} of table {tablename} because other objects depend on it

When we are duplicating a column of serial type, the underlying sequence is not duplicated. See provider_id is duplicated to _pgroll_new_provider_id, but the default value is set to the same sequence:

postgres=# \d phonebook
                                                 Table "public.phonebook"
         Column          |          Type          | Collation | Nullable |                    Default
-------------------------+------------------------+-----------+----------+------------------------------------------------
 id                      | integer                |           | not null | nextval('phonebook_id_seq'::regclass)
 provider_id             | integer                |           | not null | nextval('phonebook_provider_id_seq'::regclass)
 _pgroll_new_provider_id | integer                |           |          | nextval('phonebook_provider_id_seq'::regclass)

Foreign-key constraints:
    "provider_fk" FOREIGN KEY (provider_id) REFERENCES telephone_providers(id) ON UPDATE CASCADE ON DELETE CASCADE

When we try to drop the column as usual (without cascade) we get an error:

postgres=# alter table phonebook drop column provider_id;
ERROR:  cannot drop column provider_id of table phonebook because other objects depend on it
DETAIL:  default value for column _pgroll_new_provider_id of table phonebook depends on sequence phonebook_provider_id_seq
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

But if we drop the column with CASCADE option, the default value of the duplicated column is gone:

postgres=# \d phonebook
                                            Table "public.phonebook"
         Column          |          Type          | Collation | Nullable |                Default
-------------------------+------------------------+-----------+----------+---------------------------------------
 id                      | integer                |           | not null | nextval('phonebook_id_seq'::regclass)
 _pgroll_new_provider_id | integer                |           |          |

We must fix two things:

• drop the original column (provider_id)
• preserve the default value for the duplicated column (_pgroll_new_provider_id)