DROP TABLE CASCADE doesn't drop dependencies

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|

DROP TABLE CASCADE doesn't drop dependencies

Robins Tharakan-2
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
Reply | Threaded
Open this post in threaded view
|

Re: DROP TABLE CASCADE doesn't drop dependencies

Tom Lane-2
Robins Tharakan <[hidden email]> 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

Reply | Threaded
Open this post in threaded view
|

Re: DROP TABLE CASCADE doesn't drop dependencies

Robins Tharakan-2
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


On Mon, 4 Feb 2019 at 10:42, Tom Lane <[hidden email]> wrote:
Robins Tharakan <[hidden email]> 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
Reply | Threaded
Open this post in threaded view
|

Re: DROP TABLE CASCADE doesn't drop dependencies

Michael Paquier-2
In reply to this post by Robins Tharakan-2
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

signature.asc (849 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: DROP TABLE CASCADE doesn't drop dependencies

Robins Tharakan-2
Upgrading the client did help.

Apologies for the noise and thanks (again) for prompt responses!
-
robins


On Mon, 4 Feb 2019 at 12:03, Michael Paquier <[hidden email]> 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