Re: pg_upgrade error regarding hstore operator - Mailing list pgsql-general

Thanks for the reply Tom. For the first query, I get the following result on both 9.1 and pg_upgrade'd 9.5.
SELECT am.amname AS index_method,
        opf.opfname AS opfamily_name,
        amop.amopopr::regoperator AS opfamily_operator
     FROM pg_am am, pg_opfamily opf, pg_amop amop
     WHERE opf.opfmethod = am.oid AND
           amop.amopfamily = opf.oid and opf.opfname like '%hstore%'
     ORDER BY index_method, opfamily_name, opfamily_operator;

index_method;opfamily_name;opfamily_operator
"btree";"btree_hstore_ops";"=(hstore,hstore)"
"btree";"btree_hstore_ops";"#>#(hstore,hstore)"
"btree";"btree_hstore_ops";"#>=#(hstore,hstore)"
"btree";"btree_hstore_ops";"#<#(hstore,hstore)"
"btree";"btree_hstore_ops";"#<=#(hstore,hstore)"
"gin";"gin_hstore_ops";"?(hstore,text)"
"gin";"gin_hstore_ops";"?|(hstore,text[])"
"gin";"gin_hstore_ops";"?&(hstore,text[])"
"gin";"gin_hstore_ops";"@>(hstore,hstore)"
"gist";"gist_hstore_ops";"?(hstore,text)"
"gist";"gist_hstore_ops";"?|(hstore,text[])"
"gist";"gist_hstore_ops";"?&(hstore,text[])"
"gist";"gist_hstore_ops";"@>(hstore,hstore)"
"gist";"gist_hstore_ops";"@(hstore,hstore)"
"hash";"hash_hstore_ops";"=(hstore,hstore)"

For the second query you provided, I get this for the database on 9.1:
select * from pg_opclass where opcintype = 'hstore'::regtype;

opcmethod;opcname;opcnamespace;opcowner;opcfamily;opcintype;opcdefault;opckeytype
403;"btree_hstore_ops";2200;16384;325462122;325462056;t;0
405;"hash_hstore_ops";2200;16384;325462131;325462056;t;0
783;"gist_hstore_ops";2200;16384;325462146;325462056;t;325462135
2742;"gin_hstore_ops";2200;16384;325462163;325462056;t;25

And this for the same database on pg_upgrade'd 9.5:

opcmethod;opcname;opcnamespace;opcowner;opcfamily;opcintype;opcdefault;opckeytype
403;"btree_hstore_ops";2200;16384;17079;325462056;t;0
2742;"gin_hstore_ops";2200;16384;17087;325462056;t;25
783;"gist_hstore_ops";2200;16384;17097;325462056;t;325462135
405;"hash_hstore_ops";2200;16384;17111;325462056;t;0

In addition, I noticed the following differences in the pg_depend catalog, there are 4 entries in 9.1 that are missing
in9.5 which I have separated with ***: 
select classid::regclass, objid, objsubid, refclassid::regclass, refobjid, refobjsubid, deptype, opcname, opfname from
pg_dependpgd left join pg_opclass on pgd.objid = pg_opclass.oid left join pg_opfamily on pgd.objid = pg_opfamily.oid
whereclassid in ('pg_opfamily'::regclass, 'pg_opclass'::regclass) order by 7,8,9; 

9.1:
classid;objid;objsubid;refclassid;refobjid;refobjsubid;deptype;opcname;opfname
"pg_opclass";325462123;0;"pg_opfamily";325462122;0;"a";"btree_hstore_ops";""
"pg_opclass";325462164;0;"pg_opfamily";325462163;0;"a";"gin_hstore_ops";""
"pg_opclass";325462147;0;"pg_opfamily";325462146;0;"a";"gist_hstore_ops";""
"pg_opclass";325462132;0;"pg_opfamily";325462131;0;"a";"hash_hstore_ops";""
"pg_opclass";325462123;0;"pg_extension";325462055;0;"e";"btree_hstore_ops";""
"pg_opclass";325462164;0;"pg_extension";325462055;0;"e";"gin_hstore_ops";""
"pg_opclass";325462147;0;"pg_extension";325462055;0;"e";"gist_hstore_ops";""
"pg_opclass";325462132;0;"pg_extension";325462055;0;"e";"hash_hstore_ops";""
***
"pg_opfamily";325462122;0;"pg_extension";325462055;0;"e";"";"btree_hstore_ops"
"pg_opfamily";325462163;0;"pg_extension";325462055;0;"e";"";"gin_hstore_ops"
"pg_opfamily";325462146;0;"pg_extension";325462055;0;"e";"";"gist_hstore_ops"
"pg_opfamily";325462131;0;"pg_extension";325462055;0;"e";"";"hash_hstore_ops"
***
"pg_opclass";325462123;0;"pg_type";325462056;0;"n";"btree_hstore_ops";""
"pg_opclass";325462123;0;"pg_namespace";2200;0;"n";"btree_hstore_ops";""
"pg_opclass";325462164;0;"pg_namespace";2200;0;"n";"gin_hstore_ops";""
"pg_opclass";325462164;0;"pg_type";325462056;0;"n";"gin_hstore_ops";""
"pg_opclass";325462147;0;"pg_type";325462056;0;"n";"gist_hstore_ops";""
"pg_opclass";325462147;0;"pg_type";325462135;0;"n";"gist_hstore_ops";""
"pg_opclass";325462147;0;"pg_namespace";2200;0;"n";"gist_hstore_ops";""
"pg_opclass";325462132;0;"pg_type";325462056;0;"n";"hash_hstore_ops";""
"pg_opclass";325462132;0;"pg_namespace";2200;0;"n";"hash_hstore_ops";""
"pg_opfamily";325462122;0;"pg_namespace";2200;0;"n";"";"btree_hstore_ops"
"pg_opfamily";325462163;0;"pg_namespace";2200;0;"n";"";"gin_hstore_ops"
"pg_opfamily";325462146;0;"pg_namespace";2200;0;"n";"";"gist_hstore_ops"
"pg_opfamily";325462131;0;"pg_namespace";2200;0;"n";"";"hash_hstore_ops"

9.5:
classid;objid;objsubid;refclassid;refobjid;refobjsubid;deptype;opcname;opfname
"pg_opclass";17080;0;"pg_opfamily";17079;0;"a";"btree_hstore_ops";""
"pg_opclass";17088;0;"pg_opfamily";17087;0;"a";"gin_hstore_ops";""
"pg_opclass";17098;0;"pg_opfamily";17097;0;"a";"gist_hstore_ops";""
"pg_opclass";17112;0;"pg_opfamily";17111;0;"a";"hash_hstore_ops";""
"pg_opclass";17080;0;"pg_extension";16651;0;"e";"btree_hstore_ops";""
"pg_opclass";17088;0;"pg_extension";16651;0;"e";"gin_hstore_ops";""
"pg_opclass";17098;0;"pg_extension";16651;0;"e";"gist_hstore_ops";""
"pg_opclass";17112;0;"pg_extension";16651;0;"e";"hash_hstore_ops";""
"pg_opclass";17080;0;"pg_type";325462056;0;"n";"btree_hstore_ops";""
"pg_opclass";17080;0;"pg_namespace";2200;0;"n";"btree_hstore_ops";""
"pg_opclass";17088;0;"pg_namespace";2200;0;"n";"gin_hstore_ops";""
"pg_opclass";17088;0;"pg_type";325462056;0;"n";"gin_hstore_ops";""
"pg_opclass";17098;0;"pg_type";325462056;0;"n";"gist_hstore_ops";""
"pg_opclass";17098;0;"pg_type";325462135;0;"n";"gist_hstore_ops";""
"pg_opclass";17098;0;"pg_namespace";2200;0;"n";"gist_hstore_ops";""
"pg_opclass";17112;0;"pg_type";325462056;0;"n";"hash_hstore_ops";""
"pg_opclass";17112;0;"pg_namespace";2200;0;"n";"hash_hstore_ops";""
"pg_opfamily";17079;0;"pg_namespace";2200;0;"n";"";"btree_hstore_ops"
"pg_opfamily";17087;0;"pg_namespace";2200;0;"n";"";"gin_hstore_ops"
"pg_opfamily";17097;0;"pg_namespace";2200;0;"n";"";"gist_hstore_ops"
"pg_opfamily";17111;0;"pg_namespace";2200;0;"n";"";"hash_hstore_ops"

output of \dx+ hstore:

9.1
cast from text[] to hstore
 function akeys(hstore)
 function avals(hstore)
 function defined(hstore,text)
 function delete(hstore,hstore)
 function delete(hstore,text)
 function delete(hstore,text[])
 function each(hstore)
 function exist(hstore,text)
 function exists_all(hstore,text[])
 function exists_any(hstore,text[])
 function fetchval(hstore,text)
 function ghstore_compress(internal)
 function ghstore_consistent(internal,internal,integer,oid,internal)
 function ghstore_decompress(internal)
 function ghstore_in(cstring)
 function ghstore_out(ghstore)
 function ghstore_penalty(internal,internal,internal)
 function ghstore_picksplit(internal,internal)
 function ghstore_same(internal,internal,internal)
 function ghstore_union(internal,internal)
 function gin_consistent_hstore(internal,smallint,internal,integer,internal,internal)
 function gin_extract_hstore(internal,internal)
 function gin_extract_hstore_query(internal,internal,smallint,internal,internal)
 function hs_concat(hstore,hstore)
 function hs_contained(hstore,hstore)
 function hs_contains(hstore,hstore)
 function hstore(record)
 function hstore(text,text)
 function hstore(text[])
 function hstore(text[],text[])
 function hstore_cmp(hstore,hstore)
 function hstore_eq(hstore,hstore)
 function hstore_ge(hstore,hstore)
 function hstore_gt(hstore,hstore)
 function hstore_hash(hstore)
 function hstore_in(cstring)
 function hstore_le(hstore,hstore)
 function hstore_lt(hstore,hstore)
 function hstore_ne(hstore,hstore)
 function hstore_out(hstore)
 function hstore_recv(internal)
 function hstore_send(hstore)
 function hstore_to_array(hstore)
 function hstore_to_matrix(hstore)
 function hstore_version_diag(hstore)
 function isdefined(hstore,text)
 function isexists(hstore,text)
 function populate_record(anyelement,hstore)
 function skeys(hstore)
 function slice(hstore,text[])
 function slice_array(hstore,text[])
 function svals(hstore)
 function tconvert(text,text)
 operator #<#(hstore,hstore)
 operator #<=#(hstore,hstore)
 operator #=(anyelement,hstore)
 operator #>#(hstore,hstore)
 operator #>=#(hstore,hstore)
 operator %#(NONE,hstore)
 operator %%(NONE,hstore)
 operator -(hstore,hstore)
 operator -(hstore,text)
 operator -(hstore,text[])
 operator ->(hstore,text)
 operator ->(hstore,text[])
 operator <>(hstore,hstore)
 operator <@(hstore,hstore)
 operator =(hstore,hstore)
 operator =>(text,text)
 operator ?&(hstore,text[])
 operator ?(hstore,text)
 operator ?|(hstore,text[])
 operator @(hstore,hstore)
 operator @>(hstore,hstore)
 operator class btree_hstore_ops for access method btree
 operator class gin_hstore_ops for access method gin
 operator class gist_hstore_ops for access method gist
 operator class hash_hstore_ops for access method hash
 operator family btree_hstore_ops for access method btree
 operator family gin_hstore_ops for access method gin
 operator family gist_hstore_ops for access method gist
 operator family hash_hstore_ops for access method hash
 operator ||(hstore,hstore)
 operator ~(hstore,hstore)
 type ghstore
 type hstore

9.5:
cast from text[] to hstore
 function akeys(hstore)
 function avals(hstore)
 function defined(hstore,text)
 function delete(hstore,hstore)
 function delete(hstore,text)
 function delete(hstore,text[])
 function each(hstore)
 function exist(hstore,text)
 function exists_all(hstore,text[])
 function exists_any(hstore,text[])
 function fetchval(hstore,text)
 function ghstore_compress(internal)
 function ghstore_consistent(internal,internal,integer,oid,internal)
 function ghstore_decompress(internal)
 function ghstore_in(cstring)
 function ghstore_out(ghstore)
 function ghstore_penalty(internal,internal,internal)
 function ghstore_picksplit(internal,internal)
 function ghstore_same(internal,internal,internal)
 function ghstore_union(internal,internal)
 function gin_consistent_hstore(internal,smallint,internal,integer,internal,internal)
 function gin_extract_hstore(internal,internal)
 function gin_extract_hstore_query(internal,internal,smallint,internal,internal)
 function hs_concat(hstore,hstore)
 function hs_contained(hstore,hstore)
 function hs_contains(hstore,hstore)
 function hstore(record)
 function hstore(text,text)
 function hstore(text[])
 function hstore(text[],text[])
 function hstore_cmp(hstore,hstore)
 function hstore_eq(hstore,hstore)
 function hstore_ge(hstore,hstore)
 function hstore_gt(hstore,hstore)
 function hstore_hash(hstore)
 function hstore_in(cstring)
 function hstore_le(hstore,hstore)
 function hstore_lt(hstore,hstore)
 function hstore_ne(hstore,hstore)
 function hstore_out(hstore)
 function hstore_recv(internal)
 function hstore_send(hstore)
 function hstore_to_array(hstore)
 function hstore_to_matrix(hstore)
 function hstore_version_diag(hstore)
 function isdefined(hstore,text)
 function isexists(hstore,text)
 function populate_record(anyelement,hstore)
 function skeys(hstore)
 function slice(hstore,text[])
 function slice_array(hstore,text[])
 function svals(hstore)
 function tconvert(text,text)
 operator #<#(hstore,hstore)
 operator #<=#(hstore,hstore)
 operator #=(anyelement,hstore)
 operator #>#(hstore,hstore)
 operator #>=#(hstore,hstore)
 operator %#(NONE,hstore)
 operator %%(NONE,hstore)
 operator -(hstore,hstore)
 operator -(hstore,text)
 operator -(hstore,text[])
 operator ->(hstore,text)
 operator ->(hstore,text[])
 operator <>(hstore,hstore)
 operator <@(hstore,hstore)
 operator =(hstore,hstore)
 operator ?&(hstore,text[])
 operator ?(hstore,text)
 operator ?|(hstore,text[])
 operator @(hstore,hstore)
 operator @>(hstore,hstore)
 operator class btree_hstore_ops for access method btree
 operator class gin_hstore_ops for access method gin
 operator class gist_hstore_ops for access method gist
 operator class hash_hstore_ops for access method hash
 operator ||(hstore,hstore)
 operator ~(hstore,hstore)
 type ghstore
 type hstore
(82 rows)

Thanks again for you time and assistance,
Mike

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, April 12, 2016 5:40 PM
To: Feld, Michael (IMS) <FeldM@imsweb.com>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] pg_upgrade error regarding hstore operator

"Feld, Michael (IMS)" <FeldM@imsweb.com> writes:
> Thanks for the reply Tom. template1 is definitely empty and does not contain any hstore objects. I did a little
debuggingand placed the below SQL before and after the hstore creation in the file produced by the pg_dump and
determinedthat these operator objects only become present immediately after the creation of the hstore extension, and
notbefore. Then, much later down the pg_dump file, it attempts to create the operator family for these 4 items
producingthe errors. I did a pg_dump of the same database on a 9.1 instance and it does not produce the operator
creationobjects SQL outside of the extension. This seems to be something that happened as part of the pg_upgrade. Any
ideawhy these have showed up outside the extension? Is there anything I can do to fix this? Thanks for all of your
help.

I got some time today to try to reproduce this problem, and failed.
What I did was:
* install hstore 1.0 extension in a 9.1 database
* upgrade the extension to 1.1 (as per instructions upthread)
* pg_upgrade the database to HEAD
* pg_dump the database

The pg_dump output shows the expected CREATE EXTENSION command and no other extraneous objects.  So the procedure is
fine. I have to conclude there was something weird about the initial state of your 
9.1 database.  I have too little info to say what exactly.

> SELECT am.amname AS index_method,
>        opf.opfname AS opfamily_name,
>        amop.amopopr::regoperator AS opfamily_operator
>     FROM pg_am am, pg_opfamily opf, pg_amop amop
>     WHERE opf.opfmethod = am.oid AND
>           amop.amopfamily = opf.oid and opf.opfname like '%hstore%'
>     ORDER BY index_method, opfamily_name, opfamily_operator;

Uh, what did you get from that query?  Might also be useful to see

select * from pg_opclass where opcintype = 'hstore'::regtype;

and the output of "\dx+ hstore" in psql.

regards, tom lane

________________________________

Information in this e-mail may be confidential. It is intended only for the addressee(s) identified above. If you are
notthe addressee(s), or an employee or agent of the addressee(s), please note that any dissemination, distribution, or
copyingof this communication is strictly prohibited. If you have received this e-mail in error, please notify the
senderof the error.