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

Alvaro Herrera <alvherre@2ndquadrant.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