Re: BUG #16492: DROP VIEW IF EXISTS error

po 15. 6. 2020 v 16:45 odesílatel David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> napsal:

> On Monday, June 15, 2020, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> wrote:
>
>>
>>
>> po 15. 6. 2020 v 8:51 odesílatel Nina Marlow <postgresql(dot)2020(at)t-net(dot)ruhr>
>> napsal:
>>
>>> > 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.
>>>
>>> As the documentation seems to always have said that "IF EXISTS" doesn't
>>> raise an error, there's no script that could get broken.
>>>
>>> On the other side, currently I currently don't see a way of dropping a
>>> view or table without knowing its exact type.
>>>
>>> So to drop a *view*, I need to be sure that there is no *table* with the
>>> same name. I have to check that first before using DROP. But that makes IF
>>> EXISTS more or less useless because I might just as well check whether the
>>> view exists and depending on the result either do a DROP or not.
>>>
>>
>> It is harder when you introduce schemas and search_path.
>>
>> We know so in one schema there cannot be view and table with same name,
>> but you can have more schemas on search_path
>>
>> So the behaviour can be little bit different if you use qualified name or
>> not
>>
>
> Huh? The lack of concrete examples makes it difficult to take seriously
> your defense of the current behavior.
>

CREATE SCHEMA s1;
CREATE SCHEMA s2;

CREATE VIEW s1.rel AS SELECT 1;
CREATE TABLE s2.rel (a int, b int)

SET SEARCH_PATH TO s1, s2;

DROP TABLE IF EXISTS rel;
CREATE TABLE rel(a int, b int);

This is a synthetic example. But this case shows so the behaviour is same,
and search_path should not be calculated. This script fails in both cases
(both variants of DROP TABLE IF EXISTS) with same error messages like
example when search_path will not be used.

> David J.
>