Thread: BUG #16492: DROP VIEW IF EXISTS error

On Friday, June 12, 2020, PG Bug reporting form <noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference:      16492
Logged by:          Nina Marlow
Email address:      postgresql.2020@t-net.ruhr
PostgreSQL version: 12.3
Operating system:   Linux/Docker
Description:       

When trying to use "DROP VIEW IF EXISTS x" while a table named "x" exists,
the DROP statement raises an "Error: x is not a view".

The documentation says about the "IF EXISTS" clause:

> Do not throw an error if the view does not exist. A notice is issued in
this case.

Imho, there shouldn't be an error if the view does not exist regardless of
whether or not a table with this name exists. The observed behavior is
contrary to the sense of the "IF EXISTS" clause as one would have to check
whether a table with the desired name exists before using "DROP VIEW IF
EXISTS". But then you could directly check whether or not the view itself
exists.

This thread from 2018 illustrates the current state of the discussion:

 https://www.postgresql.org/message-id/flat/CAFj8pRD-Lq%3D9ZTHuAdq1RYW4EN0O5Pr0cvCUZs5%3DMUzs2dLCXQ%40mail.gmail.com#9f9dfcca54742947c7140c9789e6b82a

I still concur this is indeed a bug that should be fixed.

David J.

On Friday, June 12, 2020, Pavel Stehule <pavel.stehule@gmail.com> wrote:

DROP TABLE IF EXISTS xxx;

CREATE TABLE xxx;

If the first statement doesn't fail, then the second statement will be successful with very high priority. For me is little bit more intuitive message "cannot to drop some" then "cannot to create some" when first command is DROP, and I have to investigate, why DROP was ignored.

Fixing this bug you’d still get: Error: cannot create table xxx, view with same name already exists.  Do you seriously expect a user to then ask why the drop table command didn’t tell them about the view with the same name?

The create command should deal with namespace sharing, the drop command just should do what is written on the tin.  Especially since that is all it is documented to be concerned with.  As demonstrated actual use cases are broken with the current behavior which exists seemingly to only try and reduce user confusion.  I’d rather have the defined and expected behavior here and deal with confused people on the mailing list then tell people on their valid uses are not as important.

David J.

pá 12. 6. 2020 v 22:34 odesílatel David G. Johnston <david.g.johnston@gmail.com> napsal:

On Friday, June 12, 2020, Pavel Stehule <pavel.stehule@gmail.com> wrote:

DROP TABLE IF EXISTS xxx;

CREATE TABLE xxx;

If the first statement doesn't fail, then the second statement will be successful with very high priority. For me is little bit more intuitive message "cannot to drop some" then "cannot to create some" when first command is DROP, and I have to investigate, why DROP was ignored.

Fixing this bug you’d still get: Error: cannot create table xxx, view with same name already exists.  Do you seriously expect a user to then ask why the drop table command didn’t tell them about the view with the same name?

The create command should deal with namespace sharing, the drop command just should do what is written on the tin.  Especially since that is all it is documented to be concerned with.  As demonstrated actual use cases are broken with the current behavior which exists seemingly to only try and reduce user confusion.  I’d rather have the defined and expected behavior here and deal with confused people on the mailing list then tell people on their valid uses are not as important.

If we change the behaviour then other group of users will be confused in other cases.

For me - this case is ambiguous, and the change doesn't do things better for all.

I try to think about it from a different perspective and I don't see any result. Minimally

postgres=# create table xxx (a int);
CREATE TABLE
postgres=# drop view xxx;
ERROR:  "xxx" is not a view
HINT:  Use DROP TABLE to remove a table.

DROP TABLE IF EXISTS and DROP TABLE are consistent now. The message is ""xxx" is not a view", it is not ""xxx" doesn't exist".

> DROP TABLE IF EXISTS and DROP TABLE are consistent now. The message is
> ""xxx" is not a view", it is not ""xxx" doesn't exist".

Yes, but according to the documentation, there shouldn't be an error at all ("Do not throw an error if the *view* does not exist."). It doesn't say, "do not throw an error if the *object* does not exist", but "if the *view* does not exist". This is very clear to me.

So it's either a documentation error or a code error. It cannot be none.

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> How is the proposed behavior more risky?  And no, the current behavior does
> not have any uniquely practical use.  Its only benefit is that it is how
> things have worked forever and that is only because its flaws are rarely
> encountered in practice.

I'm a little skeptical about the proposed change being of any benefit.

The usual reason for doing DROP IF EXISTS is that you're about to replace
the object.  It will not help for the DROP to succeed if the conflicting
object is still there, because the CREATE is going to fail anyway.

  Thus,
the most likely effect of such a change is that we fix no scripts, while
breaking any scripts that were dependent on the existing behavior.

What I'd prefer to see, I think, is a command DROP RELATION [IF EXISTS]
that is entirely un-picky about the object's relkind.  Once upon a time
DROP TABLE worked that way, IIRC, but it was "improved" with little
thought about the needs of schema-update scripts.

> the most likely effect of such a change is that we fix no scripts, while
> breaking any scripts that were dependent on the existing behavior.

As the documentation seems to always have said that "IF EXISTS" doesn't raise an error, there's no script that could get broken.

On the other side, currently I currently don't see a way of dropping a view or table without knowing its exact type.

So to drop a *view*, I need to be sure that there is no *table* with the same name. I have to check that first before using DROP. But that makes IF EXISTS more or less useless because I might just as well check whether the view exists and depending on the result either do a DROP or not.

> What I'd prefer to see, I think, is a command DROP RELATION [IF EXISTS]

If the already implemented DROPs behaved as documented, you'd not need an additional third statement. It'd be just identical to

DROP TABLE IF EXISTS
DROP VIEW IF EXISTS

On Monday, June 15, 2020, Pavel Stehule <pavel.stehule@gmail.com> wrote:

po 15. 6. 2020 v 8:51 odesílatel Nina Marlow <postgresql.2020@t-net.ruhr> napsal:

> the most likely effect of such a change is that we fix no scripts, while
> breaking any scripts that were dependent on the existing behavior.

As the documentation seems to always have said that "IF EXISTS" doesn't raise an error, there's no script that could get broken.

On the other side, currently I currently don't see a way of dropping a view or table without knowing its exact type.

So to drop a *view*, I need to be sure that there is no *table* with the same name. I have to check that first before using DROP. But that makes IF EXISTS more or less useless because I might just as well check whether the view exists and depending on the result either do a DROP or not.

It is harder when you introduce schemas and search_path.

We know so in one schema there cannot be view and table with same name, but you can have more schemas on search_path

So the behaviour can be little bit different if you use qualified name or not

Huh?  The lack of concrete examples makes it difficult to take seriously your defense of the current behavior.

David J.

Huh?  The lack of concrete examples makes it difficult to take seriously your defense of the current behavior.

CREATE SCHEMA s1;

CREATE SCHEMA s2;

CREATE VIEW s1.rel AS SELECT 1;

CREATE TABLE s2.rel (a int, b int)

SET SEARCH_PATH TO s1, s2;

DROP TABLE IF EXISTS rel;

CREATE TABLE rel(a int, b int);

This is a synthetic example. But this case shows so the behaviour is same, and search_path should not be calculated. This script fails in both cases (both variants of DROP TABLE IF EXISTS) with same error messages like example when search_path will not be used.