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

On Saturday, June 15, 2019, PG Bug reporting form <noreply@postgresql.org>
wrote:

The following bug has been logged on the website:

Bug reference: 15853
Logged by: wondertx
Email address: wondertx@gmail.com
PostgreSQL version: 11.3
Operating system: Arch Linux
Description:

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

From what i understand this behavior is documented. What is it that you
expect to happen?

David J.

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

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

Thank you all. The configurability of Postgre is splendid

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