From: | Robins Tharakan <tharakan(at)gmail(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | DROP TABLE CASCADE doesn't drop dependencies |
Date: | 2019-02-03 23:22:43 |
Message-ID: | CAEP4nAzV3RGu7hgh7dXC44UsnDeFYEt4yZU_dnzmShNn33FGDQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hi,
Is it expected for DROP TABLE CASCADE to find a related table but not drop
it?
The case in point, is when the base table is used as a column type.
This can at least be reproduced in v9.6 and v10, where it silently drops
the column! (Am not a developer, but wild guess the code that is supposed
to check for an existing FK and drop just the FK, is dropping the column in
this case).
In v12 (see below) it doesn't drop the column (or the table), but \d fails
loudly.
###################
localhost postgres(at)t=# create table typ (b integer);
CREATE TABLE
localhost postgres(at)t=# create table tbl (abc typ);
CREATE TABLE
localhost postgres(at)t=# \d
List of relations
┌────────┬──────┬───────┬──────────┐
│ Schema │ Name │ Type │ Owner │
├────────┼──────┼───────┼──────────┤
│ public │ tbl │ table │ postgres │
│ public │ typ │ table │ postgres │
└────────┴──────┴───────┴──────────┘
(2 rows)
localhost postgres(at)t=# drop table typ;
ERROR: 2BP01: cannot drop table typ because other objects depend on it
DETAIL: column abc of table tbl depends on type typ
HINT: Use DROP ... CASCADE to drop the dependent objects too.
LOCATION: reportDependentObjects, dependency.c:973
localhost postgres(at)t=# drop table typ cascade;
NOTICE: 00000: drop cascades to column abc of table tbl
LOCATION: reportDependentObjects, dependency.c:997
DROP TABLE
localhost postgres(at)t=# \d
List of relations
┌────────┬──────┬───────┬──────────┐
│ Schema │ Name │ Type │ Owner │
├────────┼──────┼───────┼──────────┤
│ public │ tbl │ table │ postgres │
└────────┴──────┴───────┴──────────┘
(1 row)
localhost postgres(at)t=# select version();
┌────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ version
│
├────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ PostgreSQL 12devel on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-28), 64-bit │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(1 row)
localhost postgres(at)t=# \d tbl
ERROR: 42703: column c.relhasoids does not exist
LINE 1: ...riggers, c.relrowsecurity, c.relforcerowsecurity, c.relhasoi...
^
LOCATION: errorMissingColumn, parse_relation.c:3313
###################
-
robins
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2019-02-03 23:42:20 | Re: DROP TABLE CASCADE doesn't drop dependencies |
Previous Message | Peter Geoghegan | 2019-02-03 23:09:49 | Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes |