Thread: DROP TABLE CASCADE doesn't drop dependencies
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@t=# create table typ (b integer);
CREATE TABLE
localhost postgres@t=# create table tbl (abc typ);
CREATE TABLE
localhost postgres@t=# \d
List of relations
┌────────┬──────┬───────┬──────────┐
│ Schema │ Name │ Type │ Owner │
├────────┼──────┼───────┼──────────┤
│ public │ tbl │ table │ postgres │
│ public │ typ │ table │ postgres │
└────────┴──────┴───────┴──────────┘
(2 rows)
localhost postgres@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@t=# drop table typ cascade;
NOTICE: 00000: drop cascades to column abc of table tbl
LOCATION: reportDependentObjects, dependency.c:997
DROP TABLE
localhost postgres@t=# \d
List of relations
┌────────┬──────┬───────┬──────────┐
│ Schema │ Name │ Type │ Owner │
├────────┼──────┼───────┼──────────┤
│ public │ tbl │ table │ postgres │
└────────┴──────┴───────┴──────────┘
(1 row)
localhost postgres@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@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
Robins Tharakan <tharakan@gmail.com> writes:
> 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!
Dropping the column is exactly what is supposed to happen: the scope
of the dependency is defined to be just the column of that type, not
the table containing it.
Not sure if this is documented anyplace in the SGML docs, but I'm
pretty sure we have regression test cases for it.
regards, tom lane
Thanks Tom for clarifying promptly (and effectively helping me out), since that pointed me to find v9.6.6 related bug-fix around this, that is currently affecting an upgrade.
The v12 DROP does seem like an unrelated issue though, which I happened to notice coincidentally.
-
robins
robins
On Mon, 4 Feb 2019 at 10:42, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robins Tharakan <tharakan@gmail.com> writes:
> 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!
Dropping the column is exactly what is supposed to happen: the scope
of the dependency is defined to be just the column of that type, not
the table containing it.
Not sure if this is documented anyplace in the SGML docs, but I'm
pretty sure we have regression test cases for it.
regards, tom lane
On Mon, Feb 04, 2019 at 10:22:43AM +1100, Robins Tharakan wrote: > localhost postgres@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 > ################### You may want to check your version of psql here. We do an effort with downward compatibility, not upward. And my guess here is that you are connected to a v12 server, using at most a v11 psql client. -- Michael
Attachment
Upgrading the client did help.
Apologies for the noise and thanks (again) for prompt responses!
-
robins
robins
On Mon, 4 Feb 2019 at 12:03, Michael Paquier <michael@paquier.xyz> wrote:
On Mon, Feb 04, 2019 at 10:22:43AM +1100, Robins Tharakan wrote:
> localhost postgres@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
> ###################
You may want to check your version of psql here. We do an effort with
downward compatibility, not upward. And my guess here is that you are
connected to a v12 server, using at most a v11 psql client.
--
Michael