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