ERROR: Could not add geog_brin_inclusion_merge to brin_geography_inclusion_ops..null value in column "refobjid" (Re: Do you use PostGIS BRIN?)

[lr at pcorp.us]

Justin,

I've ticketed this issue here:  https://trac.osgeo.org/postgis/ticket/5856

> Not sure if this the right place to report it, but it seems related to
this thread.
> 
> After upgrading to postgresql-17-postgis-3-3.5.2+dfsg-1.pgdg120+1, I got
> this:
> 
> # ALTER EXTENSION postgis UPDATE;
> DEBUG:  executing extension script for "postgis" update from version
'3.5.0' to
> 'ANY'
> DEBUG:  executing extension script for "postgis" update from version 'ANY'
to
> '3.5.2'
> ERROR:  Could not add geog_brin_inclusion_merge to
> brin_geography_inclusion_ops class: null value in column "refobjid" of
relation
> "pg_depend" violates not-null constraint
> CONTEXT:  PL/pgSQL function inline_code_block line 219 at RAISE
> 
> Due to:
> 
> # SELECT n.oid FROM pg_catalog.pg_opclass AS n WHERE opcname =
> 'brin_geography_inclusion_ops';
> (0 rows)
> 
> This is an instance running pg17.4 (having just been upgraded from 17.2),
> which was pg_upgraded from 16.4 on 2024-10-12 (and previously from
> earlier versions).
> 
> Also, the data dir was originally initdb'd under centos (7, and probably
earlier
> versions before that), and most recently migrated to debian (with the
requisite
> reindex).
> 
> In november, it was upgraded from: postgresql-17-postgis 3.4.3+dfsg-
> 2.pgdg120+1 to 3.5.0+dfsg-1.pgdg120+1.  It looks like we have one other
> instance which will hit the same problem.
> 
> --
> Justin

We did put in logic in 3.5.2 to do an upgrade of BRIN index logic pushing in
a function that was missing, without requiring a drop restore,
But looks like in your case the bits needed for upgrade are missing.

My guess is there was probably an incomplete upgrade of your BRIN a while
back so your system has an incomplete BRIN setup.

I'm going to try to revise the upgrade script to account for this scenario.

Can you do me a favor, send me the output of this from your problem
database:


SELECT * FROM pg_catalog.pg_opclass WHERE opcname ILIKE 'brin%';


Thanks,
Regina