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

Hello

attached patch implement unified behave for DROP IF EXISTS statements as
was discussed

Regards

Pavel



2013/11/27 Dean Rasheed <dean.a.rasheed@gmail.com>

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