Re: pg_restore --clean failing due to dependancies

On 11/15/2016 08:09 AM, Arnaud L. wrote:
> Le 15/11/2016 à 16:44, Tom Lane a écrit :
>> You'd have to provide a lot more detail before anyone could tell if there
>> was a fixable bug here, but I rather doubt it. There are at least two
>> ways this scenario might lose:
>>
>> 1. There are additional objects in the target database that have
>> dependencies on ones that are in the dump. In that case there is
>> no ordering of dropping the objects in the dump that will succeed.
>
> The target databased was restored from the dump in an empty database.
> So what I do is :
> dropdb -U postgres -h localhost db1
> createdb -U postgres -h localhost db1
> psql -U postgres -h localhost -c "CREATE EXTENSION postgis" db1
> pg_dump -h pgsql1 -U postgres -b -Fc -E UTF8 -N public -N osm -N osm2 -f
> "D:\temp.dump" db1
> pg_restore -U postgres -h localhost -j 2 -d db1 "D:\temp.dump"
>
> That works, no error, no warning.
>
> Then
> pg_restore -U postgres -h localhost --clean -d db1 "D:\temp.dump"
> fails.
>
> So can we exclude additional objects in the target database in this case ?
>
>
>> 2. There are dependency chains passing through objects that weren't
>> dumped (ie, if A depends on B which depends on C, and you omit B
>> from the dump, it might still be the case that A can't be restored
>> before C).
>
> Can I trust what pgadmin says about objects dependent on a schema ?

Believe that only shows objects that have are declared for that schema.
It does not show internal relationships of the objects to other objects
outside their schema. In other words a function that is public.some_fnc
but inside the function body operates on objects in another schema. Or a
table in one schema that has a FK to a table in another schema and so on.

> It says that public schema's dependent objects are only it's own
> operators, functions, etc. (i.e., what's in the postgis extension), and
> the same for the other two schemas.
> They don't show any dependent objects outside themselves.
>
>
>> If you think neither of those cases apply, please provide a
>> self-contained
>> test case.
>
> That's not going to be easy. I'll try to trim down a pg_dump -s to see
> how I can reproduce this.
>
> --
> Arnaud
>
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com