Query regarding selectDumpableExtension()

On Thu, Oct 27, 2016 at 2:11 AM, amul sul <sulamul@gmail.com> wrote:

selectDumpableExtension() function skip
s dump of
built-in extensions in case of binary-upgrade only,
why not in normal
dump
?
Can't we assume those will already be installed in the target database
at restore
?

There's a comment in dumpExtension() that explains it.

/*
* In a regular dump, we use IF NOT EXISTS so that there isn't a
* problem if the extension already exists in the target database;
* this is essential for installed-by-default extensions such as
* plpgsql.
*
* In binary-upgrade mode, that doesn't work well, so instead we skip
* built-in extensions based on their OIDs; see
* selectDumpableExtension.
*/

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Fri, Oct 28, 2016 at 6:22 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Oct 27, 2016 at 2:11 AM, amul sul <sulamul@gmail.com> wrote:

selectDumpableExtension() function skip
s dump of
built-in extensions in case of binary-upgrade only,
why not in normal
dump
?
Can't we assume those will already be installed in the target database
at restore
?

Apologise for the delayed response.

There's a comment in dumpExtension() that explains it.

/*
* In a regular dump, we use IF NOT EXISTS so that there isn't a
* problem if the extension already exists in the target database;
* this is essential for installed-by-default extensions such as
* plpgsql.
*

Understood.

* In binary-upgrade mode, that doesn't work well, so instead we skip
* built-in extensions based on their OIDs; see
* selectDumpableExtension.
*/

I don't see the necessity of dumping it in normal mode, unless I'm
missing something.

Let me explain the case I'm trying to tackle. I have two old dump
data, each of them have couple objects depend on plpgsql. I have
restored first dump and trying restore second dump using 'pg_restore
-c' command, it is failing with following error:

ERROR: cannot drop extension plpgsql because other objects depend on it

Works well without '-c' option, but that what not a general solution, IMHO.

Regards,
Amul

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

amul sul <sulamul@gmail.com> writes:

On Fri, Oct 28, 2016 at 6:22 PM, Robert Haas <robertmhaas@gmail.com> wrote:

There's a comment in dumpExtension() that explains it.

Let me explain the case I'm trying to tackle. I have two old dump
data, each of them have couple objects depend on plpgsql. I have
restored first dump and trying restore second dump using 'pg_restore
-c' command, it is failing with following error:
ERROR: cannot drop extension plpgsql because other objects depend on it

This is hardly specific to extensions. If you try a restore with -c into
a database that has other random objects besides what's in the dump, you
could get errors from
* dropping tables that are referenced by foreign keys from tables not
known in the dump
* dropping functions that are used in views not known in the dump
* dropping operators or opclasses used by indexes not known in the dump
etc etc.

Works well without '-c' option, but that what not a general solution, IMHO.

The general solution is either don't restore into a database containing
unrelated objects, or be prepared to ignore errors from the DROP commands.
The extension case actually works more smoothly than most of the others.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 31 Oct 2016 6:48 pm, "Tom Lane" <tgl@sss.pgh.pa.us> wrote:

amul sul <sulamul@gmail.com> writes:

On Fri, Oct 28, 2016 at 6:22 PM, Robert Haas <robertmhaas@gmail.com>

wrote:

There's a comment in dumpExtension() that explains it.

Let me explain the case I'm trying to tackle. I have two old dump
data, each of them have couple objects depend on plpgsql. I have
restored first dump and trying restore second dump using 'pg_restore
-c' command, it is failing with following error:
ERROR: cannot drop extension plpgsql because other objects depend on it

This is hardly specific to extensions. If you try a restore with -c into
a database that has other random objects besides what's in the dump, you
could get errors from
* dropping tables that are referenced by foreign keys from tables not
known in the dump
* dropping functions that are used in views not known in the dump
* dropping operators or opclasses used by indexes not known in the dump
etc etc.

Works well without '-c' option, but that what not a general solution,

IMHO.

The general solution is either don't restore into a database containing
unrelated objects, or be prepared to ignore errors from the DROP commands.
The extension case actually works more smoothly than most of the others.

Thanks for your explanation, I agree that this is not a single scenario
where we need special care, but still my question stands there, why do we
really need to dump built-in extension?

Of course you could ask, why not? And my answer will be same, "to placate
pg_restore at least in the case I've explained before" :)

Regards,
Amul
----------------------------------------------------------------------------------------------------
Sent from a mobile device. Please excuse brevity and tpyos.

amul sul <sulamul@gmail.com> writes:

Thanks for your explanation, I agree that this is not a single scenario
where we need special care, but still my question stands there, why do we
really need to dump built-in extension?

It's not built-in. It's installed by default, yes, but it's also
droppable.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers