Обсуждение: BUG #15853: DROP TABLE CASCADE drops sequence that other tables depend on
BUG #15853: DROP TABLE CASCADE drops sequence that other tables depend on
От
PG Bug reporting form
Дата:
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;
Re: BUG #15853: DROP TABLE CASCADE drops sequence that other tablesdepend on
От
"David G. Johnston"
Дата:
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.
Re: BUG #15853: DROP TABLE CASCADE drops sequence that other tablesdepend on
От
Alvaro Herrera
Дата:
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:
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