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

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.

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