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

On 26 November 2013 19:54, Peter Eisentraut <peter_e@gmx.net> wrote:
> On 11/24/13, 2:28 PM, Pavel Stehule wrote:
>> 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).
>
> Right, we might as well consider 'trigger ON tablename' to be the full
> name of the trigger and just treat it like a unit.
>

Yeah, that's how I would view it.

> But then a single IF EXISTS clause is still inconsistent with DROP TABLE
> nonexistent.foo, which fails if the schema does not exist.  In other
> words, the IF EXISTS clause only applies to the end of an name chain.
>

Actually the IF EXISTS in DROP TABLE now applies to the schema as
well. Unfortunately there is currently no consistency across the
various DROP commands --- some tolerate a non-existent schema, while
others error out. Also amongst those that tolerate a non-existent
schema, the resulting notices are not consistent --- some report the
schema-qualified object name, while others just report the local
object name.

Here is the current state of HEAD:

DROP AGGREGATE IF EXISTS no_such_schema.foo(int);
ERROR:  schema "no_such_schema" does not exist

DROP CAST IF EXISTS (no_such_schema.foo AS no_such_schema.bar);
ERROR:  schema "no_such_schema" does not exist

DROP COLLATION IF EXISTS no_such_schema.foo;
NOTICE:  collation "no_such_schema.foo" does not exist, skipping
DROP COLLATION

DROP CONVERSION IF EXISTS no_such_schema.foo;
NOTICE:  conversion "no_such_schema.foo" does not exist, skipping
DROP CONVERSION

DROP DOMAIN IF EXISTS no_such_schema.foo;
ERROR:  schema "no_such_schema" does not exist

DROP FOREIGN TABLE IF EXISTS no_such_schema.foo;
NOTICE:  foreign table "foo" does not exist, skipping
DROP FOREIGN TABLE

DROP FUNCTION IF EXISTS no_such_schema.foo();
ERROR:  schema "no_such_schema" does not exist

DROP INDEX IF EXISTS no_such_schema.foo;
NOTICE:  index "foo" does not exist, skipping
DROP INDEX

DROP MATERIALIZED VIEW IF EXISTS no_such_schema.foo;
NOTICE:  materialized view "foo" does not exist, skipping
DROP MATERIALIZED VIEW

DROP OPERATOR IF EXISTS no_such_schema.+ (int, int);
ERROR:  schema "no_such_schema" does not exist

DROP OPERATOR CLASS IF EXISTS no_such_schema.widget_ops USING btree;
ERROR:  schema "no_such_schema" does not exist

DROP OPERATOR FAMILY IF EXISTS no_such_schema.float_ops USING btree;
ERROR:  schema "no_such_schema" does not exist

DROP RULE IF EXISTS foo ON no_such_schema.bar;
ERROR:  schema "no_such_schema" does not exist

DROP SEQUENCE IF EXISTS no_such_schema.foo;
NOTICE:  sequence "foo" does not exist, skipping
DROP SEQUENCE

DROP TABLE IF EXISTS no_such_schema.foo;
NOTICE:  table "foo" does not exist, skipping
DROP TABLE

DROP TEXT SEARCH CONFIGURATION IF EXISTS no_such_schema.foo;
NOTICE:  text search configuration "no_such_schema.foo" does not exist, skipping
DROP TEXT SEARCH CONFIGURATION

DROP TEXT SEARCH DICTIONARY IF EXISTS no_such_schema.foo;
NOTICE:  text search dictionary "no_such_schema.foo" does not exist, skipping
DROP TEXT SEARCH DICTIONARY

DROP TEXT SEARCH PARSER IF EXISTS no_such_schema.foo;
NOTICE:  text search parser "no_such_schema.foo" does not exist, skipping
DROP TEXT SEARCH PARSER

DROP TEXT SEARCH TEMPLATE IF EXISTS no_such_schema.foo;
NOTICE:  text search template "no_such_schema.foo" does not exist, skipping
DROP TEXT SEARCH TEMPLATE

DROP TRIGGER IF EXISTS foo ON no_such_schema.bar;
ERROR:  schema "no_such_schema" does not exist

DROP TYPE IF EXISTS no_such_schema.foo;
ERROR:  schema "no_such_schema" does not exist

DROP VIEW IF EXISTS no_such_schema.foo;
NOTICE:  view "foo" does not exist, skipping
DROP VIEW

That's a lot of inconsistency --- 10 errors vs 12 notices (6 with
schema-qualified names and 6 with only local names).

Regards,
Dean