BUG #14181: pg_upgrade: operator family "btree_hstore_ops" does not exist

The following bug has been logged on the website:

Bug reference: 14181
Logged by: Eric Worden
Email address: worden(dot)eric(at)gmail(dot)com
PostgreSQL version: 9.5.3
Operating system: Red Hat Enterprise Linux Server release 7.2 (Maipo
Description:

I'm attempting to upgrade from 9.4.8. hstore installed version on the 9.4
cluster is 1.3. It also failed with version 1.2.

pg_upgrade is exiting with failure during the step "Restoring database
schemas in the new cluster". Several database schemas are restored
successfully, then one fails. The pg_restore log indicated by the pg_upgrade
output ends with:

pg_restore: [archiver (db)] could not execute query: ERROR: operator family
"btree_hstore_ops" does not exist for access method "btree"
Command was: CREATE OPERATOR CLASS "btree_hstore_ops"
DEFAULT FOR TYPE "hstore" USING "btree" FAMILY "btree_hstore_ops" AS
OPERAT...

The documentation for CREATE OPERATOR CLASS says that the operator family
will be created if it does not already exist, contrary to what the error
message says here.

Here is an excerpt of the pg_restore log created by pg_upgrade:

<======= excerpt =========>
command: "/usr/pgsql-9.5/bin/pg_dump" --host "/var/lib/pgsql/77694" --port
5432 --username "postgres" --schema-only --quote-all-identifiers
--binary-upgrade --format=custom --file="pg_upgrade_dump_16420.custom"
"foo" >> "pg_upgrade_dump_16420.log" 2>&1

command: "/usr/pgsql-9.5/bin/pg_restore" --host "/var/lib/pgsql/77694"
--port 5432 --username "postgres" --exit-on-error --verbose --dbname "foo"
"pg_upgrade_dump_16420.custom" >> "pg_upgrade_dump_16420.log" 2>&1
pg_restore: connecting to database for restore
pg_restore: creating pg_largeobject "pg_largeobject"
pg_restore: creating pg_largeobject_metadata "pg_largeobject_metadata"
pg_restore: creating SCHEMA "ais"
...
pg_restore: creating EXTENSION "fuzzystrmatch"
pg_restore: creating COMMENT "EXTENSION "fuzzystrmatch""
pg_restore: creating EXTENSION "hstore"
pg_restore: creating COMMENT "EXTENSION "hstore""
pg_restore: creating EXTENSION "tablefunc"
pg_restore: creating COMMENT "EXTENSION "tablefunc""
pg_restore: creating SHELL TYPE "pg_catalog.ghstore"
pg_restore: creating FUNCTION "pg_catalog.ghstore_in("cstring")"
pg_restore: creating FUNCTION "pg_catalog.ghstore_out("ghstore")"
pg_restore: creating TYPE "pg_catalog.ghstore"
pg_restore: creating SHELL TYPE "pg_catalog.hstore"
pg_restore: creating FUNCTION "pg_catalog.hstore_in("cstring")"
pg_restore: creating FUNCTION "pg_catalog.hstore_out("hstore")"
pg_restore: creating FUNCTION "pg_catalog.hstore_recv("internal")"
pg_restore: creating FUNCTION "pg_catalog.hstore_send("hstore")"
pg_restore: creating TYPE "pg_catalog.hstore"
pg_restore: creating TYPE "public.asset_type"
...
pg_restore: creating FUNCTION "pg_catalog.each("hstore")"
pg_restore: creating FUNCTION "pg_catalog.exist("hstore", "text")"
pg_restore: creating FUNCTION "pg_catalog.exists_all("hstore", "text"[])"
pg_restore: creating FUNCTION "pg_catalog.exists_any("hstore", "text"[])"
pg_restore: creating FUNCTION "pg_catalog.fetchval("hstore", "text")"
...
pg_restore: creating OPERATOR "pg_catalog.~"
pg_restore: creating OPERATOR CLASS "pg_catalog.btree_hstore_ops"
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 5680; 2616 18772 OPERATOR
CLASS btree_hstore_ops dbadmin
pg_restore: [archiver (db)] could not execute query: ERROR: operator family
"btree_hstore_ops" does not exist for access method "btree"
Command was: CREATE OPERATOR CLASS "btree_hstore_ops"
DEFAULT FOR TYPE "hstore" USING "btree" FAMILY "btree_hstore_ops" AS
OPERAT...
</======= excerpt =========>

Here is an excerpt of the pg_dump file used by the failing pg_restore:

<======= excerpt =========>
--
-- PostgreSQL database dump
--

-- Dumped from database version 9.4.8
-- Dumped by pg_dump version 9.5.3

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET row_security = off;

[...]

--
-- Name: hstore; Type: EXTENSION; Schema: -; Owner:
--

-- For binary upgrade, create an empty extension and insert objects into
it
DROP EXTENSION IF EXISTS "hstore";
SELECT pg_catalog.binary_upgrade_create_empty_extension('hstore',
'pg_catalog', true, '1.3', NULL, NULL, ARRAY[]::pg_catalog.text[]);

--
-- Name: EXTENSION "hstore"; Type: COMMENT; Schema: -; Owner:
--
--
-- Name: ghstore; Type: SHELL TYPE; Schema: pg_catalog; Owner: dbadmin
--

-- For binary upgrade, must preserve pg_type oid
SELECT
pg_catalog.binary_upgrade_set_next_pg_type_oid('18471'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_type array oid
SELECT
pg_catalog.binary_upgrade_set_next_array_pg_type_oid('683524'::pg_catalog.oid);

CREATE TYPE "ghstore";

--
-- Name: ghstore_in("cstring"); Type: FUNCTION; Schema: pg_catalog; Owner:
dbadmin
--

CREATE FUNCTION "ghstore_in"("cstring") RETURNS "ghstore"
LANGUAGE "c" IMMUTABLE STRICT
AS '$libdir/hstore', 'ghstore_in';

-- For binary upgrade, handle extension membership the hard way
ALTER EXTENSION "hstore" ADD FUNCTION "ghstore_in"("cstring");

ALTER FUNCTION "pg_catalog"."ghstore_in"("cstring") OWNER TO dbadmin;

--
-- Name: ghstore_out("ghstore"); Type: FUNCTION; Schema: pg_catalog; Owner:
dbadmin
--

CREATE FUNCTION "ghstore_out"("ghstore") RETURNS "cstring"
LANGUAGE "c" IMMUTABLE STRICT
AS '$libdir/hstore', 'ghstore_out';

-- For binary upgrade, handle extension membership the hard way
ALTER EXTENSION "hstore" ADD FUNCTION "ghstore_out"("ghstore");

ALTER FUNCTION "pg_catalog"."ghstore_out"("ghstore") OWNER TO dbadmin;

--
-- Name: ghstore; Type: TYPE; Schema: pg_catalog; Owner: dbadmin
--

-- For binary upgrade, must preserve pg_type oid
SELECT
pg_catalog.binary_upgrade_set_next_pg_type_oid('18471'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_type array oid
SELECT
pg_catalog.binary_upgrade_set_next_array_pg_type_oid('683524'::pg_catalog.oid);

CREATE TYPE "ghstore" (
INTERNALLENGTH = variable,
INPUT = "ghstore_in",
OUTPUT = "ghstore_out",
ALIGNMENT = int4,
STORAGE = plain
);

-- For binary upgrade, handle extension membership the hard way
ALTER EXTENSION "hstore" ADD TYPE "ghstore";

ALTER TYPE ghstore OWNER TO dbadmin;

--
-- Name: hstore; Type: SHELL TYPE; Schema: pg_catalog; Owner: dbadmin
--

-- For binary upgrade, must preserve pg_type oid
SELECT
pg_catalog.binary_upgrade_set_next_pg_type_oid('683521'::pg_catalog.oid);

-- For binary upgrade, must preserve pg_type array oid
SELECT
pg_catalog.binary_upgrade_set_next_array_pg_type_oid('683445'::pg_catalog.oid);

CREATE TYPE "hstore";

--
-- Name: hstore_in("cstring"); Type: FUNCTION; Schema: pg_catalog; Owner:
dbadmin
--

CREATE FUNCTION "hstore_in"("cstring") RETURNS "hstore"
LANGUAGE "c" IMMUTABLE STRICT
AS '$libdir/hstore', 'hstore_in';

-- For binary upgrade, handle extension membership the hard way
ALTER EXTENSION "hstore" ADD FUNCTION "hstore_in"("cstring");

[...]

CREATE TYPE "hstore" (
INTERNALLENGTH = variable,
INPUT = "hstore_in",
OUTPUT = "hstore_out",
RECEIVE = "hstore_recv",
SEND = "hstore_send",
ALIGNMENT = int4,
STORAGE = extended
);

-- For binary upgrade, handle extension membership the hard way
ALTER EXTENSION "hstore" ADD TYPE "hstore";

ALTER TYPE hstore OWNER TO dbadmin;

[...]

ALTER OPERATOR "pg_catalog".~ ("hstore", "hstore") OWNER TO dbadmin;

--
-- Name: btree_hstore_ops; Type: OPERATOR CLASS; Schema: pg_catalog; Owner:
dbadmin
--

CREATE OPERATOR CLASS "btree_hstore_ops"
DEFAULT FOR TYPE "hstore" USING "btree" FAMILY "btree_hstore_ops" AS
OPERATOR 1 #<#("hstore","hstore") ,
OPERATOR 2 #<=#("hstore","hstore") ,
OPERATOR 3 =("hstore","hstore") ,
OPERATOR 4 #>=#("hstore","hstore") ,
OPERATOR 5 #>#("hstore","hstore") ,
FUNCTION 1 ("hstore", "hstore") "hstore_cmp"("hstore","hstore");

-- For binary upgrade, handle extension membership the hard way
ALTER EXTENSION "hstore" ADD OPERATOR CLASS "btree_hstore_ops" USING
"btree";

ALTER OPERATOR CLASS "pg_catalog"."btree_hstore_ops" USING "btree" OWNER TO
dbadmin;

[...]

</======= excerpt =========>