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

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?

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