From: Laurenz Albe <laurenz.albe@cybertec.at>
To: asotolongo@gmail.com
To: pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #18161: ALTER TABLE ... ALTER COLUMN does not remove dependencies in automatically from column and sequence.
Date: Wed, 18 Oct 2023 22:06:48 +0200
Message-ID: <28d214b20fbcc95b6e24fa647f19982e6c0ed19f.camel@cybertec.at> (raw)
In-Reply-To: <18161-6f5840d311f0a100@postgresql.org>
References: <18161-6f5840d311f0a100@postgresql.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-bugs@postgresql.org
Cc: laurenz.albe@cybertec.at, asotolongo@gmail.com
Subject: Re: BUG #18161: ALTER TABLE ... ALTER COLUMN does not remove dependencies in automatically from column and sequence.
In-Reply-To: <28d214b20fbcc95b6e24fa647f19982e6c0ed19f.camel@cybertec.at>
References: <18161-6f5840d311f0a100@postgresql.org> <28d214b20fbcc95b6e24fa647f19982e6c0ed19f.camel@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox