Re: BUG #16492: DROP VIEW IF EXISTS error

On Sun, Jun 14, 2020 at 10:12 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)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.
>

Apparently some people want to use it and I've yet to see any downside
regardless of how marginal that use case may be.

IF EXISTS exists for script simplicity - not having to know exactly what
was present previously in the database.

I have a TABLE. At some point in the future I want to "turn it" into a
VIEW. They should have the same name. I also need to be able to rebuild
the VIEW.

Initial:
TABLE exists; View does not
DROP TABLE IF EXISTS name; -- drops
DROP VIEW IF EXISTS name; -- no-op
-- in short, I know about the namespace problem and am happy ensuring that
the namespace does not contain the name I care about after these drop
commands run..
CREATE VIEW name; -- creates

Subsequent (this fails when it shouldn't):
TABLE does not exist; VIEW exists
DROP TABLE IF EXISTS name; -- error!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
DROP VIEW IF EXISTS name; -- never gets here - it should though
CREATE VIEW name; -- creates/rebuilds the view

Regardless of how marginal you believe the above to be I don't see any
reason why it cannot be supported.

People demonstrably want to do something like this, and because DROP
RELATION doesn't exist and the documentation says it should work they
rightly complain. The fix seems simple enough and without risk - even if
behavior changes a little bit. Other bug fix patches have contributed even
larger behavioral changes and have been back-patched, which I believe this
should be as well but I do accept the arguments against as at least
warranting only a fix of head.

> 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.

Right, so the fact that DROP provokes the same error in this case is not
useful.

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.
>

We cause scripts that wouldn't work before to now work as the author wanted
in the first place. And we don't "break" any scripts - i.e., issue an
error where one wasn't being issued before.

Sure, I suppose someone could have written:

psql -c "DROP TABLE IF EXISTS name"
if [ $? != 0 ]; then

fi
psql -c "CREATE TABLE"
# the above should never fail so do not error handling here...

But I'm doubtful, and would consider catering to that crowd less than
desirable.

> 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.
>
And this is where things stalled out last time. If you feel strongly
enough that this needs to stay this way until a superior and more invasive
patch is submitted can you please at least fix the documentation bug?

David J.