geog_brin_inclusion upgrade issue

[Justin Pryzby]

On Sat, Mar 15, 2025 at 08:20:14PM -0400, lr at pcorp.us wrote:
> Justin,
> 
> We discussed this on PostGIS IRC, and decided it's better to provide a
> standalone script to fix a broken brin install instead of trying to fold it
> in.
> 
> Can you try installing this SQL script.
> 
> https://git.osgeo.org/gitea/postgis/postgis/raw/commit/890833a978462185620165645d368bf044b33fa7/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:

from the old centos7 VM:
/mnt/tmp/var/log/yum.log-20210101:Sep 24 17:07:57 Updated: postgis30_12-3.0.2-1.rhel7.x86_64
/mnt/tmp/var/log/yum.log-20210101:Sep 24 17:07:58 Installed: postgis31_13-3.1.0-alpha2_1.rhel7.x86_64
/mnt/tmp/var/log/yum.log-20210101:Sep 24 17:08:00 Updated: postgis30_11-3.0.2-1.rhel7.x86_64
/mnt/tmp/var/log/yum.log-20210101:Sep 24 17:08:00 Updated: postgis30_11-client-3.0.2-1.rhel7.x86_64
/mnt/tmp/var/log/yum.log-20210101:Sep 24 17:08:00 Installed: postgis31_13-client-3.1.0-alpha2_1.rhel7.x86_64
/mnt/tmp/var/log/yum.log-20210101:Sep 24 17:08:00 Updated: postgis30_12-client-3.0.2-1.rhel7.x86_64
/mnt/tmp/var/log/yum.log-20220101:Apr 09 16:51:44 Updated: postgis31_13-3.1.1-3.rhel7.x86_64
/mnt/tmp/var/log/yum.log-20220101:Apr 09 16:51:44 Updated: postgis31_13-client-3.1.1-3.rhel7.x86_64
/mnt/tmp/var/log/yum.log-20220101:Oct 10 14:57:59 Updated: postgis31_13-3.1.4-1.rhel7.x86_64
/mnt/tmp/var/log/yum.log-20220101:Oct 10 14:57:59 Updated: postgis31_13-client-3.1.4-1.rhel7.x86_64
/mnt/tmp/var/log/yum.log-20220101:Oct 10 14:58:38 Installed: postgis31_14-3.1.4-1.rhel7.x86_64
/mnt/tmp/var/log/yum.log-20220101:Oct 10 14:58:38 Installed: postgis31_14-client-3.1.4-1.rhel7.x86_64
/mnt/tmp/var/log/yum.log-20230101:Oct 27 19:04:17 Installed: postgis33_15-3.3.1-2.rhel7.x86_64
/mnt/tmp/var/log/yum.log-20230101:Oct 27 19:04:18 Installed: postgis33_15-client-3.3.1-2.rhel7.x86_64
/mnt/tmp/var/log/yum.log-20240101:Feb 16 12:11:30 Updated: postgis33_15-3.3.2-1.rhel7.x86_64
/mnt/tmp/var/log/yum.log-20240101:Feb 16 12:11:30 Updated: postgis33_15-client-3.3.2-1.rhel7.x86_64
/mnt/tmp/var/log/yum.log-20240101:Sep 19 00:22:02 Installed: postgis34_16-3.4.0-1PGDG.el7.x86_64
/mnt/tmp/var/log/yum.log-20240101:Dec 04 21:13:04 Updated: postgis34_16-3.4.1-1PGDG.el7.x86_64

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.



ts=# begin;
BEGIN
ts=*# \i ./fix_brin.sql 
DO
DO
DO
DO
DO
DO
DO
DO
DO
DO
DO
DO
DO
DO
DO
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