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