Re: Unexpected behavior of DROP VIEW/TABLE IF EXISTS

On 06/26/2018 07:06 PM, Tom Lane wrote:
> Also worth noting is that similar issues arise elsewhere, eg we now
> have "procedures" vs "functions" in a single namespace. Let's not have
> DROP TABLE acting in a way that's inconsistent with other parts of the
> system.

I think, that

DROP <type> <identifier> ...

could search within the type's namespace for the <type><identifier> in
combination, and only fail if it cannot be found.

I use those commands in a project with an Java ORM in place, that
automatically generates/updates a schema on each startup. It wrongly
generates a table X, where it should generate a view X. Hence, I do the
following inside an sql-script after startup:

DROP TABLE X IF EXISTS ...
DROP VIEW X IF EXISTS ...
CREATE VIEW X ...

It works on the first run, but not on a subsequent one, because the view
X already exists, hence DROP TABLE X fails.

If I switch the first two lines, it fails already during the first run,
because a table X exists...

DROP VIEW X IF EXISTS ...
DROP TABLE X IF EXISTS ...
CREATE VIEW X ...

It is only solvable with two different calls to the database, and error
handling on the application side.

Intuitively, I (and also others, that I asked) think that this command
should only fail, if a search for <type><identifier> in combination
succeeds and the DROP itself fails.

In general my use-case is, that I want to delete an X in a certain
namespace, where the type is not known in advance. I could query the
catalog to get that information and then build a procedure to "execute"
the right DROP command (as Pavel Stehule suggested), but that adds
complexity to the application code, where it shouldn't be necessary IMHO.

Best regards,
Peter