Re: Unexpected behavior of DROP VIEW/TABLE IF EXISTS

On Mon, Jul 2, 2018 at 5:25 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> On Tue, Jun 26, 2018 at 1:06 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>>> Certainly we *could* change it, but it's not at all clear that it's a good
>>> idea. The current behavior seemed sensible when it was implemented, and
>>> it has stood for quite some years now. Now, we have one person
>>> complaining that it wasn't what he expected. If we change the behavior on
>>> the strength of that, will we change it back on the first complaint from
>>> someone else? What about the possibility that the change breaks peoples'
>>> applications?
>
>> Hmm. I think we generally allow ALTER TABLE to work on non-table
>> relations. For example, ALTER TABLE .. OWNER TO happily accepts a
>> view or materialized view as an argument. I have had my doubts about
>> that policy from time to time (cf.
>> 1489e2f26a4c0318938b3085f50976512f321d84) but it does seem to be the
>> policy (cf. b14206862278347a379f2bb72d92d16fb9dcea45). Is there some
>> reason to think that the same policy that we apply to ALTER should not
>> also apply to DROP?
>
> Uh ... error proofing? The thing about ALTER is that if you mistakenly
> point at the wrong object, it's quite likely your command will fail
> because the properties of the object don't match (eg, it doesn't have a
> column by that name). Or if it goes through anyway, many varieties of
> ALTER are reversible -- certainly ALTER OWNER is. There's no comparable
> safety margin with DROP, and it's not reversible, and the consequences of
> a mistake could be pretty bad.

I suppose that's true, but it still seems inconsistent.

> Note that in any case this wasn't what the OP was proposing, if I
> understood that correctly.

Yes, the original proposal was that we should be relaxed about it.

Another possibility that would also seem to meet the OP's needs is to
make it do this:

DROP TABLE IF EXISTS X;
NOTICE: relation "X" is not a table, skipping

His complaint was really that it generated an ERROR, IIUC.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company