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?