geog_brin_inclusion upgrade issue

[lr at pcorp.us]

> > This one is a bit puzzling why this fails. It seems somehow you
> > managed to have an operator family created without an Operator class.
> > I think there was a time a long time ago when we did have to
> > explicitly create operator families and operator classes.
> > But these days when you create an operator class it automatically
> > creates an operator family.
> >
> > Please output the results of these two queries:
> >
> > SELECT fam.opfname AS family, tl.typname AS tl_typname, tr.typname AS
> > tr_typname, o.oprcode, am.amopmethod, am.amopsortfamily
> > 	FROM pg_catalog.pg_amop AS am
> > 		INNER JOIN pg_catalog.pg_opfamily AS fam ON
> am.amopfamily = fam.oid
> > 		INNER JOIN  pg_catalog.pg_type AS tl ON  am.amoplefttype =
> tl.oid
> > 		INNER JOIN  pg_catalog.pg_type AS tr ON  am.amoprighttype
> = tr.oid
> > 		INNER JOIN pg_catalog.pg_operator AS o ON am.amopopr =
> o.oid WHERE
> > fam.opfname = 'brin_geography_inclusion_ops' AND am.amopstrategy = 3;
> 
>             family            | tl_typname | tr_typname |       oprcode
| amopmethod
> | amopsortfamily
>
------------------------------+------------+------------+-------------------
---+------------
> +----------------
>  brin_geography_inclusion_ops | gidx       | gidx       |
public.overlaps_geog |
> 3580 |              0
>  brin_geography_inclusion_ops | gidx       | geography  |
public.overlaps_geog
> |       3580 |              0
>  brin_geography_inclusion_ops | geography  | gidx       |
public.overlaps_geog
> |       3580 |              0
> (3 rows)
> 

It's stange you have only 3 here,  I get 4 in all the databases I check,
though the last one  for some reason is not schema qualified in any of the
dbs I've check
This is the output from one of my dbs

            family            | tl_typname | tr_typname |        oprcode
| amopmethod | amopsortfamily
------------------------------+------------+------------+-------------------
----+------------+----------------
 brin_geography_inclusion_ops | gidx       | gidx       |
postgis.overlaps_geog |       3580 |              0
 brin_geography_inclusion_ops | geography  | gidx       |
postgis.overlaps_geog |       3580 |              0
 brin_geography_inclusion_ops | gidx       | geography  |
postgis.overlaps_geog |       3580 |              0
 brin_geography_inclusion_ops | geography  | geography  | geography_overlaps
|       3580 |              0
(4 rows)


> > SELECT fam.opfname AS family, tl.typname AS tl_typname, tr.typname AS
> > tr_typname, o.oprcode, am.amopmethod, am.amopstrategy,
> am.amopsortfamily
> > 	FROM pg_catalog.pg_amop AS am
> > 		INNER JOIN pg_catalog.pg_opfamily AS fam ON
> am.amopfamily = fam.oid
> > 		INNER JOIN  pg_catalog.pg_type AS tl ON  am.amoplefttype =
> tl.oid
> > 		INNER JOIN  pg_catalog.pg_type AS tr ON  am.amoprighttype
> = tr.oid
> > 		INNER JOIN pg_catalog.pg_operator AS o ON am.amopopr =
> o.oid WHERE
> > am.amopfamily = 17690 AND am.amoplefttype = 17039 AND
> am.amoprighttype
> > = 16458;
> 
>             family            | tl_typname | tr_typname |       oprcode
| amopmethod
> | amopstrategy | amopsortfamily
>
------------------------------+------------+------------+-------------------
---+------------
> +--------------+----------------
>  brin_geography_inclusion_ops | geography  | gidx       |
public.overlaps_geog
> |       3580 |            3 |              0
> (1 row)
> 
> I originally said that we had a 2nd instance with this issue, but it seems
I was
> wrong.
> 
> If you think this is something that just got broken in this DB, and not
likely to
> affect anyone else, feel free to give up, and I'll kick it into shape.  We
can drop
> and re-add the extension without much fuss.
> Which might be a good idea -- this one problem might be indicative of some
> weird upgrade path with other symptoms that we haven't hit yet.
> 
> --
> Justin

Well I think it's something broken, but worried that yours is not an
isolated situation.
So is the database that is failing the one that had missing bits of
geography

Or is this in all your broken databases.  I wasn't sure if it was one or
just this particular one.


Thanks,
Regina