Re: pg_upgrade error regarding hstore operator

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 in 9.5 which I have separated with ***:
select classid::regclass, objid, objsubid, refclassid::regclass, refobjid, refobjsubid, deptype, opcname, opfname from pg_depend pgd left join pg_opclass on pgd.objid = pg_opclass.oid left join pg_opfamily on pgd.objid = pg_opfamily.oid where classid 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(at)sss(dot)pgh(dot)pa(dot)us]
Sent: Tuesday, April 12, 2016 5:40 PM
To: Feld, Michael (IMS) <FeldM(at)imsweb(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] pg_upgrade error regarding hstore operator

"Feld, Michael (IMS)" <FeldM(at)imsweb(dot)com> writes:
> Thanks for the reply Tom. template1 is definitely empty and does not contain any hstore objects. I did a little debugging and placed the below SQL before and after the hstore creation in the file produced by the pg_dump and determined that these operator objects only become present immediately after the creation of the hstore extension, and not before. Then, much later down the pg_dump file, it attempts to create the operator family for these 4 items producing the errors. I did a pg_dump of the same database on a 9.1 instance and it does not produce the operator creation objects SQL outside of the extension. This seems to be something that happened as part of the pg_upgrade. Any idea why 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 not the addressee(s), or an employee or agent of the addressee(s), please note that any dissemination, distribution, or copying of this communication is strictly prohibited. If you have received this e-mail in error, please notify the sender of the error.