Re: BUG #16492: DROP VIEW IF EXISTS error

On Fri, Jun 12, 2020 at 2:16 PM Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
wrote:

>
>
> pá 12. 6. 2020 v 22:34 odesílatel David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com> napsal:
>
>> On Friday, June 12, 2020, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)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.
>

This isn't a matter of one group of users being confused over another. The
people reporting this as a bug have a valid behavioral complaint that
prevents a valid use case. The people we might be simply confusing are
just going to get an error in a different location - they will still have
to deal with their underlying naming issues caused by the shared relation
namespace.

Preventing confusion in the presence of a true namespace conflict is not a
superior objective compared to having this feature function in the manner
that is both explicitly documented and implied by its name. You will need
to describe a case where confusion leads to an actual problem if you wish
to convince me otherwise.

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

Yes, for most people it isn't better, just different - the error moves from
the DROP IF EXISTS to the subsequent CREATE. But for the minority of
people that simply want to ensure that a VIEW, and only a VIEW, of that
name doesn't exist this is a win. Sure, they have other options, querying
the catalog in a DO block, but the IF EXISTS feature says its supposed to
work for this purpose and they rightly are telling us it is a bug.

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

Why is that consistency a good thing? If the view "xxx" doesn't exist:

DROP VIEW IF EXISTS should only cause a failure if it actually attempts to
perform a drop and then during the dropping execution encounters a
problem. If it never attempts to perform a physical drop it succeeds and
the state of the database is just as the user expects, the VIEW "xxx" is
not present. The command has an implicit: "otherwise do nothing" - this is
a bug precisely because we don't do nothing, we also check for a namespace
conflict when that is immaterial to the operation at hand.

DROP VIEW "xxx" - says "you better drop this view, if you cannot for any
reason, including you cannot locate the view, abort". We could have gone
ahead and made DROP VIEW a no-op if it didn't find a target but we don't,
we have IF EXISTS for this.

I'll support not back-patching this and doing a back-patch only doc fix but
nothing I came up with or heard back then or now suggests to me that the
current behavior is superior.

David J.