pg_upgrade error regarding hstore operator

On 03/08/2016 10:27 AM, Feld, Michael (IMS) wrote:

I am attempting to upgrade my organization's database cluster from 9.1.19 to 9.5.1 using the pg_upgrade utility. After some processing, the tool bails out with the following error in the log:

pg_restore: creating OPERATOR "public.=>"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 5660; 2617 5655672 OPERATOR => postgres
pg_restore: [archiver (db)] could not execute query: ERROR: syntax error at or near "=>"
LINE 1: CREATE OPERATOR => (
^
Command was: CREATE OPERATOR => (
PROCEDURE = "hstore",
LEFTARG = "text",
RIGHTARG = "text"
);

-- For binary upgrade, handle...

I tried dropping the operator before doing the upgrade but it's dependent on the existence of the hstore extension. Ideas?

Believe it has to do with this:

http://www.postgresql.org/docs/9.5/interactive/release-9-5.html

"
Allow => to specify named parameters in function calls (Pavel Stehule)

E.2.3.7. Functions

Previously only := could be used. This requires removing the possibility
for => to be a user-defined operator. Creation of user-defined =>
operators has been issuing warnings since PostgreSQL 9.0."

and the removal of => as operator from here:

http://www.postgresql.org/docs/9.5/interactive/hstore.html

I remember a similar problem with just a regular dump/restore. If I
remember correctly I solved it by doing:

\dx in psql to see what extensions where installed and when I did not
see hstore doing:

http://www.postgresql.org/docs/9.1/interactive/sql-createextension.html
"
CREATE EXTENSION hstore SCHEMA public FROM unpackaged;

Be careful to specify the schema in which you installed the existing
hstore objects."

on the older Postgres version and then repeating the dump/restore.

________________________________

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.

--
Adrian Klaver
adrian.klaver@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"Feld, Michael (IMS)" <FeldM@imsweb.com> writes:

I am attempting to upgrade my organization's database cluster from
9.1.19 to 9.5.1 using the pg_upgrade utility.

That's kind of a big jump :-( ... you missed the versions where =>
was deprecated as an operator name.

I tried dropping the operator before doing the upgrade but it's dependent on the existence of the hstore extension. Ideas?

The clean solution would be to copy share/extension/hstore--1.0--1.1.sql
from the 9.5 installation into the 9.1 installation and then do

ALTER EXTENSION hstore UPDATE TO '1.1';

Under the hood that's just doing

ALTER EXTENSION hstore DROP OPERATOR => (text, text);
DROP OPERATOR => (text, text);

but if you did that manually, you'd have a problem when you want to update
hstore to current versions later. If you do what I suggest, the extension
will properly look like it's 1.1 after pg_upgrade'ing.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thanks for the assist Tom. That worked for us. Noticing a different issue following the pg_upgrade. If we take a pg_dump of a database on this upgraded instance with the hstore extension and try to pg_restore it back up to the same instance we get the following errors (ignore the likeness to your name, apparently when this cluster was created years ago they chose to set it up in your honor):

pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 3879; 2753 745119 OPERATOR FAMILY btree_ hstore_ops tomlane
pg_restore: [archiver (db)] could not execute query: ERROR: operator family "btree_hstore_ops" for access method "btree" already exists
Command was: CREATE OPERATOR FAMILY btree_hstore_ops USING btree;

pg_restore: [archiver (db)] Error from TOC entry 3880; 2753 745127 OPERATOR FAMILY gin_hs tore_ops tomlane
pg_restore: [archiver (db)] could not execute query: ERROR: operator family "gin_hstore_ops" for access method "gin" already exists
Command was: CREATE OPERATOR FAMILY gin_hstore_ops USING gin;

pg_restore: [archiver (db)] Error from TOC entry 3881; 2753 745137 OPERATOR FAMILY gist_h store_ops tomlane
pg_restore: [archiver (db)] could not execute query: ERROR: operator family "gist_hstore_ops" for access method "gist" already exists
Command was: CREATE OPERATOR FAMILY gist_hstore_ops USING gist;

pg_restore: [archiver (db)] Error from TOC entry 3882; 2753 745151 OPERATOR FAMILY hash_h store_ops tomlane
pg_restore: [archiver (db)] could not execute query: ERROR: operator family "hash_hstore_ops" for access method "hash" already exists
Command was: CREATE OPERATOR FAMILY hash_hstore_ops USING hash;

We do not have this issue for any new databases created following the pg_upgrade. I noticed that new databases have the hstore 1.3 version while the originals still have the 1.1 version. I updated the extension on one of these to see if that would resolve the issue and it did not. Thanks for any help you can offer.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, March 08, 2016 6:22 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:

I am attempting to upgrade my organization's database cluster from
9.1.19 to 9.5.1 using the pg_upgrade utility.

That's kind of a big jump :-( ... you missed the versions where => was deprecated as an operator name.

I tried dropping the operator before doing the upgrade but it's dependent on the existence of the hstore extension. Ideas?

The clean solution would be to copy share/extension/hstore--1.0--1.1.sql
from the 9.5 installation into the 9.1 installation and then do

ALTER EXTENSION hstore UPDATE TO '1.1';

Under the hood that's just doing

ALTER EXTENSION hstore DROP OPERATOR => (text, text); DROP OPERATOR => (text, text);

but if you did that manually, you'd have a problem when you want to update hstore to current versions later. If you do what I suggest, the extension will properly look like it's 1.1 after pg_upgrade'ing.

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"Feld, Michael (IMS)" <FeldM@imsweb.com> writes:

Thanks for the assist Tom. That worked for us. Noticing a different
issue following the pg_upgrade. If we take a pg_dump of a database on
this upgraded instance with the hstore extension and try to pg_restore
it back up to the same instance we get the following errors

Those are the *only* errors you get? That seems rather odd. I could
believe something like this happening if, say, you had an "unpackaged"
(that is, pre-extensions) version of hstore lying about. But then
you'd probably get conflicts on all the hstore-related objects, not
only the opclasses.

In any case, by far the most likely explanation is that you're trying to
restore into a non-empty database, probably because you've put stuff into
template1 and are cloning the new database from there.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

Mike

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;

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Wednesday, April 06, 2016 7:01 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 assist Tom. That worked for us. Noticing a different
issue following the pg_upgrade. If we take a pg_dump of a database on
this upgraded instance with the hstore extension and try to pg_restore
it back up to the same instance we get the following errors

Those are the *only* errors you get? That seems rather odd. I could believe something like this happening if, say, you had an "unpackaged"
(that is, pre-extensions) version of hstore lying about. But then you'd probably get conflicts on all the hstore-related objects, not only the opclasses.

In any case, by far the most likely explanation is that you're trying to restore into a non-empty database, probably because you've put stuff into
template1 and are cloning the new database from there.

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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 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@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 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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"Feld, Michael (IMS)" <FeldM@imsweb.com> writes:

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;
***
"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"
***

Ah-hah, there's the problem: the pg_upgrade process fails to restore the
extension membership of the opfamilies that are implicitly created by
hstore's CREATE OPERATOR CLASS commands. The reason for this is that in
binary-upgrade mode, the backend doesn't install any extension membership
entries automatically at all, but leaves it for the pg_dump script to do;
and pg_dump doesn't realize that it would need to do ALTER EXTENSION ADD
for the families as well as the classes.

It's hard to solve this locally in pg_dump's opclass handling, because
you can't very easily tell whether a CREATE OPERATOR CLASS command will
result in creation of an opfamily or not. (It has to not have a FAMILY
clause, *and* the opfamily has to not already exist.) I'm inclined to
think that the best fix for this is to stop leaving operator families
implicit in the dump, but create them explicitly (at which point the
extension membership would get added correctly). The comments in
dumpOpfamily say

* We want to dump the opfamily only if (1) it contains "loose" operators
* or functions, or (2) it contains an opclass with a different name or
* owner. Otherwise it's sufficient to let it be created during creation
* of the contained opclass, and not dumping it improves portability of
* the dump.

The "portability" consideration in question is whether the dump file
can be fed to a pre-8.3 server without changes. I think we could blow
that off now; there are almost certainly other things that would have
to be tweaked to load a modern dump file into such old versions.

So my proposal for fixing this is to remove the logic in pg_dump that
tries to suppress explicit dumps of operator families. This would need
to be back-patched to 9.1, because it's certainly broken like this in
all extension-supporting versions. (Surprising that nobody's noticed.)

Objections, better ideas?

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hello, All
I have the same issue after pg_upgrade from 9.3 to 9.5.
pg_dump generates excess commands like
CREATE OPERATOR FAMILY bit_ops USING gin;
...
while all of this is done during CREATE EXTENSION
(i have only btree_gin and plpgsql installed)

--
View this message in context: http://postgresql.nabble.com/pg-upgrade-error-regarding-hstore-operator-tp5891425p5904776.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

parihaaraka <parihaaraka@gmail.com> writes:

I have the same issue after pg_upgrade from 9.3 to 9.5.
pg_dump generates excess commands like
CREATE OPERATOR FAMILY bit_ops USING gin;
...
while all of this is done during CREATE EXTENSION

This is fixed in the latest round of minor releases, but not in a way that
will help you if you already pg_upgrade'd such an extension:

http://git.postgresql.org/gitweb/?p=postgresql.git&amp;a=commitdiff&amp;h=6cead413b

Per the comments there, you could manually add that operator family back
to its extension, or just ignore the duplicate-opfamily complaints you'd
get during a restore.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general