Re: Extensions support for pg_dump, patch v27

On Wed, Jan 26, 2011 at 02:57, David E. Wheeler <david@kineticode.com> wrote:
>>> Other than adminpack, I think it makes sense to say that extensions
>>> are not going into pg_catalog…
>>
>> Given this, we should maybe see about either making adminpack part of
>> PostgreSQL's core distribution (probably a good idea) or moving it out
>> of pg_catalog so we don't have an exception to the rule.
>
> +1

I doubt it can solve the real problem.
It is my understanding that we install them in pg_catalog because
they are designed to be installed in template1 for pgAdmin, right?

We have a problem in pg_dump when we install extension modules in
template1. If we create a database, installed functions are copied
from template1. However, they are also dumped with pg_dump unless
they are in pg_catalog. So, we encounter "function already exists"
errors when pg_restore.

Since pg_dump won't dump user objects in pg_catalog, adminpack can
avoid the above errors by installing functions in pg_catalog.
CREATE EXTENSION might have the same issue -- Can EXTENSION work
without errors when we install extensions in template databases?
To avoid errors, pg_dump might need to dump extensions as
"CREATE OR REPLACE EXTENSION" or "CREATE EXTENSION IF NOT EXISTS"
rather than "CREATE EXTENSION".

--
Itagaki Takahiro