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

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