Re: 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(dot)klaver(at)aklaver(dot)com