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
