Re: pg_restore issues with intarray - Mailing list pgsql-general

Kevin Brannen <KBrannen@efji.com> writes:

>>From: Jerry Sievers <gsievers19@comcast.net>
>>
>>>Kevin Brannen <KBrannen@efji.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
theextension 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.consulting@comcast.net