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

Thank you again. Some further feedback and a final (?) solution below.

On Wed, Jun 8, 2016 at 3:07 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

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