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

On Tue, Jun 7, 2016 at 3:27 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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(dot)eric(at)gmail(dot)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?

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