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(dot)a(dot)rasheed(at)gmail(dot)com>

> On 26 November 2013 19:54, Peter Eisentraut <peter_e(at)gmx(dot)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
>