Re: pg_restore issues with intarray

Kevin Brannen <KBrannen(at)efji(dot)com> writes:

>>From: Jerry Sievers <gsievers19(at)comcast(dot)net>
>>
>>>Kevin Brannen <KBrannen(at)efji(dot)com> writes:
>>>
>>> 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.
>>>
>>
>>I've seen this sort of problem before.
>>
>>It was due to some legacy DBs where I work having a few missing extension membership registrations.
>>
>>pg_dump wants to include any such things in the output which may run afoul of same having been already created by the extension load.
>
> Ugh! That would explain why it's only on some installs and only on the
> "older" ones. Thanks for the info!
>
> Did you ever find any work around? Or is it just a matter of recognizing
> what's going on, ignore those "warnings", and moving to a later version to
> get past this so it doesn't happen again?

Try running \dx+ for intarray on one of your deviant systems. You may
find the item pg_dump is trying to be explicit about *missing* from the
extension member list.

In such a case, see the ALTER EXTENSION ADD... which can be run manually
to register whatever is missing.

You will do this on the system that is *origin* for the pg_dump.

This is what we've done.

YMMV

postgres# \dx+ intarray
Objects in extension "intarray"
Object description
------------------------------------------------------------------------------------------------------
function boolop(integer[],query_int)
function bqarr_in(cstring)
function bqarr_out(query_int)
function ginint4_consistent(internal,smallint,integer[],integer,internal,internal,internal,internal)
function ginint4_queryextract(integer[],internal,smallint,internal,internal,internal,internal)
function g_intbig_compress(internal)
function g_intbig_consistent(internal,integer[],smallint,oid,internal)
function g_intbig_decompress(internal)
function g_intbig_penalty(internal,internal,internal)
function g_intbig_picksplit(internal,internal)
function g_intbig_same(intbig_gkey,intbig_gkey,internal)
function g_intbig_union(internal,internal)
function g_int_compress(internal)
function g_int_consistent(internal,integer[],smallint,oid,internal)
function g_int_decompress(internal)
function g_int_penalty(internal,internal,internal)
function g_int_picksplit(internal,internal)
function g_int_same(integer[],integer[],internal)
function g_int_union(internal,internal)
function icount(integer[])
function idx(integer[],integer)
function intarray_del_elem(integer[],integer)
function intarray_push_array(integer[],integer[])
function intarray_push_elem(integer[],integer)
function _intbig_in(cstring)
function _intbig_out(intbig_gkey)
function _int_contained(integer[],integer[])
function _int_contained_joinsel(internal,oid,internal,smallint,internal)
function _int_contained_sel(internal,oid,internal,integer)
function _int_contains(integer[],integer[])
function _int_contains_joinsel(internal,oid,internal,smallint,internal)
function _int_contains_sel(internal,oid,internal,integer)
function _int_different(integer[],integer[])
function _int_inter(integer[],integer[])
function _int_matchsel(internal,oid,internal,integer)
function _int_overlap(integer[],integer[])
function _int_overlap_joinsel(internal,oid,internal,smallint,internal)
function _int_overlap_sel(internal,oid,internal,integer)
function _int_same(integer[],integer[])
function intset(integer)
function intset_subtract(integer[],integer[])
function intset_union_elem(integer[],integer)
function _int_union(integer[],integer[])
function querytree(query_int)
function rboolop(query_int,integer[])
function sort_asc(integer[])
function sort_desc(integer[])
function sort(integer[])
function sort(integer[],text)
function subarray(integer[],integer)
function subarray(integer[],integer,integer)
function uniq(integer[])
operator class gin__int_ops for access method gin
operator class gist__intbig_ops for access method gist
operator class gist__int_ops for access method gist
operator family gin__int_ops for access method gin
operator family gist__intbig_ops for access method gist
operator family gist__int_ops for access method gist
operator ~(integer[],integer[])
operator <@(integer[],integer[])
operator |(integer[],integer)
operator |(integer[],integer[])
operator -(integer[],integer)
operator -(integer[],integer[])
operator @>(integer[],integer[])
operator @(integer[],integer[])
operator &(integer[],integer[])
operator &&(integer[],integer[])
operator #(integer[],integer)
operator +(integer[],integer)
operator +(integer[],integer[])
operator @@(integer[],query_int)
operator #(NONE,integer[])
operator ~~(query_int,integer[])
type intbig_gkey
type query_int
(76 rows)

meta_a:postgres# \h alter extension
Command: ALTER EXTENSION
Description: change the definition of an extension
Syntax:
ALTER EXTENSION name UPDATE [ TO new_version ]
ALTER EXTENSION name SET SCHEMA new_schema
ALTER EXTENSION name ADD member_object
ALTER EXTENSION name DROP member_object

where member_object is:

ACCESS METHOD object_name |
AGGREGATE aggregate_name ( aggregate_signature ) |
CAST (source_type AS target_type) |
COLLATION object_name |
CONVERSION object_name |
DOMAIN object_name |
EVENT TRIGGER object_name |
FOREIGN DATA WRAPPER object_name |
FOREIGN TABLE object_name |
FUNCTION function_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
MATERIALIZED VIEW object_name |
OPERATOR operator_name (left_type, right_type) |
OPERATOR CLASS object_name USING index_method |
OPERATOR FAMILY object_name USING index_method |
[ PROCEDURAL ] LANGUAGE object_name |
PROCEDURE procedure_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
ROUTINE routine_name [ ( [ [ argmode ] [ argname ] argtype [, ...] ] ) ] |
SCHEMA object_name |
SEQUENCE object_name |
SERVER object_name |
TABLE object_name |
TEXT SEARCH CONFIGURATION object_name |
TEXT SEARCH DICTIONARY object_name |
TEXT SEARCH PARSER object_name |
TEXT SEARCH TEMPLATE object_name |
TRANSFORM FOR type_name LANGUAGE lang_name |
TYPE object_name |
VIEW object_name

and aggregate_signature is:

* |
[ argmode ] [ argname ] argtype [ , ... ] |
[ [ argmode ] [ argname ] argtype [ , ... ] ] ORDER BY [ argmode ] [ argname ] argtype [ , ... ]

URL: https://www.postgresql.org/docs/12/sql-alterextension.html

>
> 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.

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres(dot)consulting(at)comcast(dot)net