geog_brin_inclusion upgrade issue

[lr at pcorp.us]

> https://git.osgeo.org/gitea/postgis/postgis/raw/commit/890833a97846218
> > 5620165645d368bf044b33fa7/utils/fix_brin.sql
> 
> Thanks for having a look.  The script fails as below.
> 
> BTW, it seems possible that our issue could been caused by early releases
of
> new postgis versions.
> 
> I've tended to upgrade our customers' DB's shortly after new postgres
> releases, so we've run a bunch of .0 postgis releases over the years.
> Availability of RPMs/DEBs has been the controlling factor in how upgrades
> have been done.
> 
> In case it's useful, the upgrade history is like this:
> And from the oldold centos6 VM which preceded that:
> 
> /mnt/tmp/var/log/yum.log:Oct 09 00:44:43 Installed: postgis24_11-2.4.5-
> 1.rhel6.1.x86_64
> /mnt/tmp/var/log/yum.log-20180101:May 02 14:04:45 Installed:
> postgis2_96-2.3.2-1.rhel6.x86_64 /mnt/tmp/var/log/yum.log-
> 20180101:Oct 12 12:00:08 Installed: postgis24_96-2.4.0-1.rhel6.x86_64
> /mnt/tmp/var/log/yum.log-20180101:Oct 12 12:00:47 Installed:
> postgis24_10-2.4.0-1.rhel6.x86_64 /mnt/tmp/var/log/yum.log-
> 20190101:Feb 28 13:49:26 Updated: postgis24_10-2.4.3-1.rhel6.x86_64
> /mnt/tmp/var/log/yum.log-20190101:Oct 19 11:05:47 Updated:
> postgis24_10-2.4.5-1.rhel6.x86_64 /mnt/tmp/var/log/yum.log-
> 20190101:Oct 19 11:06:30 Installed: postgis24_11-2.4.5-1.rhel6.1.x86_64
> 
> Note that it's possible that we sometimes didn't run
> postgis_extensions_upgrade().  I found that I added a nagios check for un-
> upgraded postgis in March, 2021, after which it would've been done
> consistently.
> 
> 

Yah that's my assumption that the problem was introduced around 2.3 - 2.5 
cause that was the time when if you weren't running 9.5 or higher those BRIN
sections would not have been installed
because BRIN wasn't supported for < 9.5, and if you jumped using pg_upgrade
to a version after 9.5 and PostGIS 2.3
it was assumed you already had these bits installed.  I think something went
wrong early on and we didn't completely catch these cases. 

> 
> ts=# begin;
> BEGIN
> ts=*# \i ./fix_brin.sql
> DO
> DO
> psql:fix_brin.sql:562: ERROR:  duplicate key value violates unique
constraint
> "pg_amop_fam_strat_index"
> DETAIL:  Key (amopfamily, amoplefttype, amoprighttype,
> amopstrategy)=(17690, 17039, 16458, 3) already exists.
> CONTEXT:  SQL statement "CREATE OPERATOR CLASS
> brin_geography_inclusion_ops
>     DEFAULT FOR TYPE geography
>     USING brin AS
>         FUNCTION      1        brin_inclusion_opcinfo(internal),
>         FUNCTION      2        geog_brin_inclusion_add_value(internal,
internal,
> internal, internal),
>         FUNCTION      3        brin_inclusion_consistent(internal,
internal, internal),
>         FUNCTION      4        brin_inclusion_union(internal, internal,
internal),
>         -- FUNCTION      11       geog_brin_inclusion_merge(internal,
internal),
>         OPERATOR      3        &&(geography, geography),
>         OPERATOR      3        &&(geography, gidx),
>         OPERATOR      3        &&(gidx, geography),
>         OPERATOR      3        &&(gidx, gidx),
>     STORAGE gidx"
> PL/pgSQL function inline_code_block line 8 at SQL statement

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;


And 

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;




Thanks,
Regina