Re: BUG #15853: DROP TABLE CASCADE drops sequence that other tables depend on

Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> writes:
> On 2019-Jun-15, PG Bug reporting form wrote:
>> The following SQL executed will drop the sequence `t_id_seq`:
>> CREATE TABLE t(id SERIAL, value INT NOT NULL);
>> CREATE TABLE t_bak LIKE t INCLUDING DEFAULTS INCLUDING INDEXES INCLUDING
>> COMMENTS INCLUDING CONSTRAINTS);
>> DROP TABLE t CASCADE;
>> This will drop default value of column `value` in t_bak.

> Yes. The reason the sequence is dropped is that it is owned by the t.id
> column, so when the column goes away, so does the sequence. And this
> cascades to that default value.

Yeah, not a bug. The OP might find that generated-as-identity columns
work more to his liking than SERIAL does: copying them with LIKE creates
an independent new sequence.

regression=# create table src (f1 int generated always as identity);
CREATE TABLE
regression=# create table dest (like src including identity);
CREATE TABLE
regression=# \d+ dest
Table "public.dest"
Column | Type | Collation | Nullable | Default | Storag
e | Stats target | Description
--------+---------+-----------+----------+------------------------------+-------
--+--------------+-------------
f1 | integer | | not null | generated always as identity | plain
| |
Access method: heap

regression=# insert into dest default values;
INSERT 0 1
regression=# insert into dest default values;
INSERT 0 1
regression=# table dest;
f1
----
1
2
(2 rows)

regression=# drop table src;
DROP TABLE
regression=# insert into dest default values;
INSERT 0 1
regression=# insert into dest default values;
INSERT 0 1
regression=# table dest;
f1
----
1
2
3
4
(4 rows)

regards, tom lane