pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #18161: ALTER TABLE ... ALTER COLUMN does not remove dependencies in automatically from column and sequence.
3+ messages / 3 participants
[nested] [flat]
* BUG #18161: ALTER TABLE ... ALTER COLUMN does not remove dependencies in automatically from column and sequence.
@ 2023-10-18 17:43 PG Bug reporting form <noreply@postgresql.org>
2023-10-18 20:06 ` Re: BUG #18161: ALTER TABLE ... ALTER COLUMN does not remove dependencies in automatically from column and sequence. Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 1 reply; 3+ messages in thread
From: PG Bug reporting form @ 2023-10-18 17:43 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: asotolongo@gmail.com
The following bug has been logged on the website:
Bug reference: 18161
Logged by: Anthony Sotolongo León
Email address: asotolongo@gmail.com
PostgreSQL version: 15.4
Operating system: Ubuntu 22.04.3 LTS
Description:
I am trying to change the default value(a sequence) for a column to another
column, but the dependencies are not removed from the pg_depend table, then
I cannot delete the old column, for example:
example=# CREATE TABLE example (i serial , j text);
CREATE TABLE
example=# \d+ example
Table
"public.example"
Column | Type | Collation | Nullable | Default
| Storage | Compression | Stats target | Description
--------+---------+-----------+----------+------------------------------------+----------+-------------+--------------+-------------
i | integer | | not null |
nextval('example_i_seq'::regclass) | plain | | |
j | text | | |
| extended | | |
Access method: heap
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=# \d+ example
Table
"public.example"
Column | Type | Collation | Nullable | Default
| Storage | Compression | Stats target | Description
--------+---------+-----------+----------+------------------------------------+----------+-------------+--------------+-------------
i | integer | | not null |
| plain | | |
j | text | | |
| extended | | |
i_new | bigint | | |
nextval('example_i_seq'::regclass) | plain | | |
Access method: heap
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.
example=# SELECT t.oid::regclass AS table_name,
a.attname AS column_name,
s.relname AS sequence_name, d.*
FROM pg_class AS t
JOIN pg_attribute AS a
ON a.attrelid = t.oid
JOIN pg_depend AS d
ON d.refobjid = t.oid
AND d.refobjsubid = a.attnum
JOIN pg_class AS s
ON s.oid = d.objid
WHERE d.classid = 'pg_catalog.pg_class'::regclass
AND d.refclassid = 'pg_catalog.pg_class'::regclass
AND d.deptype IN ('i', 'a')
AND t.relkind IN ('r', 'P')
AND s.relkind = 'S' ;
table_name | column_name | sequence_name | classid | objid | objsubid |
refclassid | refobjid | refobjsubid | deptype
------------+-------------+---------------+---------+---------+----------+------------+----------+-------------+---------
example | i | example_i_seq | 1259 | 3217347 | 0 |
1259 | 3217348 | 1 | a
(1 row)
--if i delete the dependency manually, then I can drop the column:
example=# delete from pg_depend where objid=3217347 and refclassid=1259 and
refobjid=3217348;
DELETE 1
example=# alter table example drop column i;
ALTER TABLE
In addition, neither I can not see the relationship between the i_new column
and the sequence
all of these, It is normal behavior or is it a bug?
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18161: ALTER TABLE ... ALTER COLUMN does not remove dependencies in automatically from column and sequence.
2023-10-18 17:43 BUG #18161: ALTER TABLE ... ALTER COLUMN does not remove dependencies in automatically from column and sequence. PG Bug reporting form <noreply@postgresql.org>
@ 2023-10-18 20:06 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-10-18 20:14 ` Re: BUG #18161: ALTER TABLE ... ALTER COLUMN does not remove dependencies in automatically from column and sequence. Anthony Sotolongo <asotolongo@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Laurenz Albe @ 2023-10-18 20:06 UTC (permalink / raw)
To: asotolongo@gmail.com; pgsql-bugs@lists.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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: BUG #18161: ALTER TABLE ... ALTER COLUMN does not remove dependencies in automatically from column and sequence.
2023-10-18 17:43 BUG #18161: ALTER TABLE ... ALTER COLUMN does not remove dependencies in automatically from column and sequence. PG Bug reporting form <noreply@postgresql.org>
2023-10-18 20:06 ` Re: BUG #18161: ALTER TABLE ... ALTER COLUMN does not remove dependencies in automatically from column and sequence. Laurenz Albe <laurenz.albe@cybertec.at>
@ 2023-10-18 20:14 ` Anthony Sotolongo <asotolongo@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Anthony Sotolongo @ 2023-10-18 20:14 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-bugs@lists.postgresql.org
Hi Laurenz, thank for your explanation, I understand now what I was
missing, the process of: ALTER SEQUENCE sequence_name OWNED BY
table_column_name;
now all make sense
Good point
Thanks again
El mié, 18 de oct. de 2023 5:06 p. m., Laurenz Albe <
laurenz.albe@cybertec.at> escribió:
> 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
>
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2023-10-18 20:14 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-10-18 17:43 BUG #18161: ALTER TABLE ... ALTER COLUMN does not remove dependencies in automatically from column and sequence. PG Bug reporting form <noreply@postgresql.org>
2023-10-18 20:06 ` Laurenz Albe <laurenz.albe@cybertec.at>
2023-10-18 20:14 ` Anthony Sotolongo <asotolongo@gmail.com>
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