Re: Re: [BUGS] BUG #7873: pg_restore --clean tries to drop tables that don't exist

2013/11/21 Peter Eisentraut <peter_e(at)gmx(dot)net>

> On 11/21/13, 2:35 AM, Pavel Stehule wrote:
> > I am feeling, so almost all people prefer
> >
> > DROP TRIGGER [ IF EXISTS ] name ON table_name [ CASCADE | RESTRICT ];
> >
> > Can we live with it?
>
> Fine with me.
>
> I think it helps if you consider IF EXISTS an attribute of the command,
> not an attribute of the command parameters.
>
> Now we should be aware that this sort of sets a precedent for ALTER
> TABLE IF EXISTS ... DROP ANYTHING ... and similar composite commands.
>
> If might be worth checking other SQL databases. We stole the IF EXISTS
> from somewhere, I believe.
>
>
I did some searching:

So DROP TRIGGER IF EXISTS is supported by

SQL anywhere, MySQL

Doesn't support:

MS SQL server (conditional drops is by T-SQL IF EXISTS() statement),
Oracle, DB2,

But significant difference between PostgreSQL and other databases is
requirement to specify table in DROP statement. So in SQL anywhere or in
MySQL DROP TRIGGER IF EXISTS is fully fault tolerant, there are not
possibility to specify table.

Note: DROP TRIGGER ON tablename is PostgreSQL feature - no other databases
(without PostgreSQL forks) uses this syntax - so we don't need thinking
what is in (or what will be) in ANSI standard (or what other databases
does). In this moment syntax of DROP TRIGGER is non standard. So if we can
adopt design (idea) in SQL anywhere or MySQL, then DROP TRIGGER IF EXISTS
should be enough. In our implementation there are two conditions, but we
should not to check if target table exists (from statement purpose).

So now, +1 for using "DROP TRIGGER IF EXISTS name ON tablename" without
requirement for tablename

Regards

Pavel