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

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.

> However, the following SQL will not drop the sequence:
> CREATE TABLE t(id SERIAL, value INT NOT NULL);
> DROP SEQUENCE t_id_seq;
> CREATE SEQUENCE t_id_seq;
> ALTER TABLE T ALTER COLUMN ID SET DEFAULT NEXTVAL('t_id_seq'::regclass);
> CREATE TABLE t_bak LIKE t INCLUDING DEFAULTS INCLUDING INDEXES INCLUDING
> COMMENTS INCLUDING CONSTRAINTS);
> DROP TABLE t CASCADE;

In this case, by dropping and recreating the sequence, you made it an
independent sequence. Setting as the default for the column doesn't
make it owned by it. If you throw
ALTER SEQUENCE t_id_set OWNED BY t.id;
in the mix, then it should work just like in the first case.
Conversely, you can make the first case behave like the second by doing
this
ALTER SEQUENCE t_id_set OWNED BY NONE;

In short, not a bug.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services