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.eric@gmail.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 =========>
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
worden.eric@gmail.com writes:
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...
Hmm. Is there, by any chance, a CREATE OPERATOR FAMILY "btree_hstore_ops"
command somewhere later in the dump? Also, if you do
\dx+ hstore
in the problematic 9.4 database, do you see lines like
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
? I'm suspicious that there may only be operator classes, not operator
families, linked to the extension.
If you don't see them, then I'm betting that you previously pg_upgraded
this same database from 9.3 or before, and fell victim to a bug we
recently fixed that caused pg_upgrade to drop such operator families
from their extensions. You could fix that with manual ALTER EXTENSION
ADD OPERATOR FAMILY commands in affected database(s). After that,
pg_upgrade'ing should work.
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.
Not if there's an explicit FAMILY clause; that's supposed to refer to
a pre-existing family.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
On Tue, Jun 7, 2016 at 3:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Thank you, your diagnosis was correct! The upgrade completed successfully.
I've added replies to your questions below, with a new question about a
possible bug.
worden.eric@gmail.com writes:
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 thepg_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...Hmm. Is there, by any chance, a CREATE OPERATOR FAMILY "btree_hstore_ops"
command somewhere later in the dump?
No there wasn't. However I believe your diagnosis below was correct (I
don't know the history of this system). I did CREATE OPERATOR FAMILY,
followed by ALTER EXTENSION
ADD OPERATOR FAMILY.
Also, if you do
\dx+ hstore
in the problematic 9.4 database, do you see lines like
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
No I did not. Now in the upgraded system I do see those. However, before
upgrade in the 9.4 cluster I created an empty test database and did CREATE
EXTENSION hstore. In the test database \dx+ hstore does not list the lines
above in the 9.4 or 9.5 system. Is this a problem?
? I'm suspicious that there may only be operator classes, not operator
families, linked to the extension.If you don't see them, then I'm betting that you previously pg_upgraded
this same database from 9.3 or before, and fell victim to a bug we
recently fixed that caused pg_upgrade to drop such operator families
from their extensions. You could fix that with manual ALTER EXTENSION
ADD OPERATOR FAMILY commands in affected database(s). After that,
pg_upgrade'ing should work.
It did work, thank you.
Eric
Import Notes
Reply to msg id not found: CAN5pzZg6Z0kG=V=QwzDFvgCtyT30pU5n0X=U4B9fmF85SXycxQ@mail.gmail.com
Eric Worden <worden.eric@gmail.com> writes:
On Tue, Jun 7, 2016 at 3:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Hmm. Is there, by any chance, a CREATE OPERATOR FAMILY "btree_hstore_ops"
command somewhere later in the dump?
No there wasn't. However I believe your diagnosis below was correct (I
don't know the history of this system). I did CREATE OPERATOR FAMILY,
followed by ALTER EXTENSION
ADD OPERATOR FAMILY.
[ squint ... ] This seems quite wrong. It is not possible to have an
operator class that's not part of an operator family, or at least I hope
not, so there should definitely have been an opfamily present even if
it was not marked as belonging to the extension. I wonder if you don't
now have *two* operator families, presumably within different schemas.
Also, if you do
\dx+ hstore
in the problematic 9.4 database, do you see lines like
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
No I did not. Now in the upgraded system I do see those. However, before
upgrade in the 9.4 cluster I created an empty test database and did CREATE
EXTENSION hstore. In the test database \dx+ hstore does not list the lines
above in the 9.4 or 9.5 system. Is this a problem?
That makes no sense at all. I definitely do see this in 9.4 after
creating hstore 1.3:
regression=# \dx+ 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
...
And, again, it does not look like it's possible to have an opclass
without a containing opfamily --- if CREATE OPERATOR CLASS does not
find a family to link to, it will make one. So there should be
an entry by that name, even if it somehow doesn't get attached to the
extension. It might be interesting to do
select oid,* from pg_opfamily where opfname like '%hstore%';
select oid,* from pg_opclass where opcname like '%hstore%';
and see what you get.
regards, tom lane
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Thank you again. Some further feedback and a final (?) solution below.
On Wed, Jun 8, 2016 at 3:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Eric Worden <worden.eric@gmail.com> writes:
On Tue, Jun 7, 2016 at 3:27 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Hmm. Is there, by any chance, a CREATE OPERATOR FAMILY
"btree_hstore_ops"
command somewhere later in the dump?
No there wasn't. However I believe your diagnosis below was correct (I
don't know the history of this system). I did CREATE OPERATOR FAMILY,
followed by ALTER EXTENSION
ADD OPERATOR FAMILY.[ squint ... ] This seems quite wrong. It is not possible to have an
operator class that's not part of an operator family, or at least I hope
not, so there should definitely have been an opfamily present even if
it was not marked as belonging to the extension. I wonder if you don't
now have *two* operator families, presumably within different schemas.
Indeed I did have two operator families. I think I made an error in my
first scanning of the dump file sql.
Also, if you do
\dx+ hstore
in the problematic 9.4 database, do you see lines like
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 hashNo I did not. Now in the upgraded system I do see those. However,
before
upgrade in the 9.4 cluster I created an empty test database and did
CREATE
EXTENSION hstore. In the test database \dx+ hstore does not list the
lines
above in the 9.4 or 9.5 system. Is this a problem?
That makes no sense at all. I definitely do see this in 9.4 after
creating hstore 1.3:
This was due to template1 having the same buggy condition.
It might be interesting to do
select oid,* from pg_opfamily where opfname like '%hstore%';
select oid,* from pg_opclass where opcname like '%hstore%';
and see what you get.
The sql above revealed that I had two sets of operator families after my
attempted fix. I started over, this time only issuing ALTER EXTENSION ADD
OPERATOR FAMILY in each affected database. The result was that everything
matched a virgin cluster and database having the hstore extension. I think
this is resolved now. Thank you for your help.
Eric