Re: pg_restore issues with intarray

On 9/6/19 8:45 AM, Kevin Brannen wrote:
>> From: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
>> On 9/5/19 5:05 PM, Kevin Brannen wrote:
>>>
>>> It feels like the restore is adding the intarray extension, which does
>>> a CREATE OPERATOR FAMILY on its own, then later the restore does
>>> CREATE OPERATOR FAMILY on again causing the problem. Yet this doesn't
>>> happen on most of our databases, just a few. It's maddening to me.
>>>
>> What does \dx show in the database you taking the dump from?
>
> Sadly, I don't have access to that system.
>
>> What if you do a restore to a file only the schema e.g.:
>>
>> pg_restore -s -f some_file.sql
>>
>> This will create a plain text version of only the schema objects in some_file.sql instead of restoring to the database. It might help shed some light.
>
> No CREATE EXTENSION or CREATE OPERATOR FAMILY statements.

I do not see that as possible. Something ran CREATE OPERATOR FAMILY:

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;

So either someone opened up template0 and loaded the extension into it
or the dump file has the CREATE OPERATOR FAMILY in it. Another thought
search on intarray.

>
> Jerry's post indicates this is something that just happens with some older
> versions and it seems I got unlucky. I do have a work around (ignore) but
> I'd rather be proactive in knowing I'm ignoring something I should be and
> not ignoring meaningful errors.

We are not dealing with magic here, there is some mechanism at work.
Dollars to donuts there is an unpackaged version of the extension in one
or more of the source databases. See below for more information on this:

https://www.postgresql.org/docs/11/extend-extensions.html

38.16.5. Extension Updates

You will need to look at the source databases in situ.

>
> Thanks for the help Adrian, I really appreciate it!

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