Обсуждение: Query regarding selectDumpableExtension()
Hi
,
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
?Thanks
&
Regards,Amul
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
alreadyexists 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
extensionsbased on their OIDs; see * selectDumpableExtension. */
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
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
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
<p dir="ltr">On 31 Oct 2016 6:48 pm, "Tom Lane" <<a href="mailto:tgl@sss.pgh.pa.us">tgl@sss.pgh.pa.us</a>> wrote:<br/> ><br /> > amul sul <<a href="mailto:sulamul@gmail.com">sulamul@gmail.com</a>> writes:<br /> >> On Fri, Oct 28, 2016 at 6:22 PM, Robert Haas <<a href="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>>wrote:<br /> > >> There's a comment in dumpExtension()that explains it.<br /> ><br /> > > Let me explain the case I'm trying to tackle. I have two olddump<br /> > > data, each of them have couple objects depend on plpgsql. I have<br /> > > restored first dumpand trying restore second dump using 'pg_restore<br /> > > -c' command, it is failing with following error:<br/> > > ERROR: cannot drop extension plpgsql because other objects depend on it<br /> ><br /> > Thisis hardly specific to extensions. If you try a restore with -c into<br /> > a database that has other random objectsbesides what's in the dump, you<br /> > could get errors from<br /> > * dropping tables that are referencedby foreign keys from tables not<br /> > known in the dump<br /> > * dropping functions that are used inviews not known in the dump<br /> > * dropping operators or opclasses used by indexes not known in the dump<br /> >etc etc.<br /> ><br /> > > Works well without '-c' option, but that what not a general solution, IMHO.<br />><br /> > The general solution is either don't restore into a database containing<br /> > unrelated objects, orbe prepared to ignore errors from the DROP commands.<br /> > The extension case actually works more smoothly than mostof the others.<br /> ><p dir="ltr">Thanks for your explanation, I agree that this is not a single scenario where weneed special care, but still my question stands there, why do we really need to dump built-in extension?<p dir="ltr">Ofcourse you could ask, why not? And my answer will be same, "to placate pg_restore at least in the case I've explainedbefore" :)<p dir="ltr">Regards, <br /> Amul<br /> ----------------------------------------------------------------------------------------------------<br/> Sent from a mobiledevice. 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