Re: pg_restore issues with intarray

On 9/5/19 2:57 PM, Kevin Brannen wrote:
> I think I need some help to understand what’s going here because I can’t
> figure it out and google isn’t helping…
>
> This is for Pg 9.6.5. (Yes, we are upgrading to 12.x in a few months, or
> so the plan is.) Pg code came from the community and we compiled it with
> no changes. This is on Centos 6.7, though I think the OS doesn’t matter.
>
> I’ve found that when we do a pg_restore, that sometimes we get “errors”.
> I quote that because it turns out they’re really only warnings we can
> ignore, but when you check the return code ($?) after pg_restore, you
> get a non-zero value.
>
> We’re calling pg_restore like:
>
> $PGPATH/pg_restore -jobs=$NCPU --dbname=x .
>
> FWIW, the backup was created with:
>
> $PGPATH/pg_dump --clean --create --format=d --jobs=$NCPU --file=$EXP
> --dbname=x

The options you are adding for --clean, --create only have meaning for
plain text dumps. If you want those actions to occur on the restore
then add them to the pg_restore line. Though if you are going to create
a new database it will inherit objects from template1(as you found
below), assuming you have not set WITH TEMPLATE to something else.

>
> The issue I’m seeing is:
>
> pg_restore: [archiver (db)] Error while PROCESSING TOC:
>
> pg_restore: [archiver (db)] Error from TOC entry 3605; 2753 18784
> OPERATOR FAMILY gin__int_ops postgres
>
> pg_restore: [archiver (db)] could not execute query: ERROR:  operator
> family "gin__int_ops" for access method "gin" already exists
>
>     Command was: CREATE OPERATOR FAMILY gin__int_ops USING gin;
>
> pg_restore: [archiver (db)] Error from TOC entry 3606; 2753 18806
> OPERATOR FAMILY gist__int_ops postgres
>
> pg_restore: [archiver (db)] could not execute query: ERROR:  operator
> family "gist__int_ops" for access method "gist" already exists
>
>     Command was: CREATE OPERATOR FAMILY gist__int_ops USING gist;
>
> pg_restore: [archiver (db)] Error from TOC entry 3607; 2753 18829
> OPERATOR FAMILY gist__intbig_ops postgres
>
> pg_restore: [archiver (db)] could not execute query: ERROR:  operator
> family "gist__intbig_ops" for access method "gist" already exists
>
>     Command was: CREATE OPERATOR FAMILY gist__intbig_ops USING gist;
>
> Those operators come from the *intarray* extension. Looking into the
> toc.dat, I can see entries like:
>
> ^(at)3079^@^E^(at)^@^(at)16441^@^H^(at)^@^(at)intarray^@
> ^(at)^@^(at)EXTENSION^@^B^(at)^@^(at)^@<^(at)^@^(at)CREATE EXTENSION IF NOT EXISTS
> intarray WITH SCHEMA common;
>
> ^A^A^(at)^@^(at)^@^F^(at)^@^(at)public^A^A^@^(at)^@^(at)^G^@^(at)^@nmsroot^(at)^E^@^(at)^@false^(at)^B^@^(at)^@15^(at)^A^@^(at)^@3^A^A^(at)^@^(at)^@^(at)^@^(at)^@^(at)^U^N^@^(at)^@^(at)^@^(at)^@^(at)^D^@^(at)^@2753^(at)^E^@^(at)^@18784^(at)^L^@^(at)^@gin__int_ops^(at)^O^@^(at)^@OPERATOR
> FAMILY^(at)^B^@^(at)^@^@/^(at)^@^(at)CREATE OPERATOR FAMILY gin__int_ops USING gin;
>
> …same sort of thing for the other 2…
>
> Those seem reasonable to me. It seems the problem is with the CREATE
> OPERATOR as there is not “if not exists” for it. Considering we created
> a new DB for the restore and went with --create to pg_restore, there
> should be nothing in the DB to create a conflict, yet somehow it already
> exists!
>
> Interestingly, this doesn’t happen all the time. It seems that once we
> can get a restore in, that it never happens again in any subsequent
> restores. My theory for this is that the databases (or really installs)
> where this happens started are an earlier version that was pg_upgrade’d
> (probably from 9.5 or even from 9.3). But I can take the shell script
> that runs this, turn off checking for the return code from pg_restore
> and we’ve found no issues with the DB (hence I can treat them like
> warnings). Of course, the downside there is that if I always ignore the
> return code from pg_restore, how do I catch real errors? 😊
>
> If it matters, here’s the version we’re dealing with for intarray:
>
> # \dx
>
>                                           List of installed extensions
>
>         Name        | Version |   Schema   |
> Description
>
> --------------------+---------+------------+--------------------------------------------------------------------
>
> intarray           | 1.2     | common     | functions, operators, and
> index support for 1-D arrays of integers
>
> If there is a logical reason for this, I’d appreciate an explanation so
> I can deal with it properly. If more research is needed, I can do that
> and pointers as to what to look for would be great. I have a VM and a
> backup I can restore & test as many times as needed.
>
> Last minute discovery and thought. It just occurred to me to look in
> template1 and intarray is in there. Is it possible that gives me a
> pre-existing extension & operators and therefore the source of the
> conflict? If so, is the solution as simple as deleting that extension
> from template1? In fact, should I delete all the extensions from
> template1 that it shows as being in the public schema?
>
> Thanks!
>
> Kevin
>
> This e-mail transmission, and any documents, files or previous e-mail
> messages attached to it, may contain confidential information. If you
> are not the intended recipient, or a person responsible for delivering
> it to the intended recipient, you are hereby notified that any
> disclosure, distribution, review, copy or use of any of the information
> contained in or attached to this message is STRICTLY PROHIBITED. If you
> have received this transmission in error, please immediately notify us
> by reply e-mail, and destroy the original transmission and its
> attachments without reading them or saving them to disk. Thank you.

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