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

Thank you all. The configurability of Postgre is splendid

On Sun, Jun 16, 2019 at 12:43 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> 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
>