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

[Paul Ramsey]

> On Mar 2, 2025, at 2:24 PM, Justin Pryzby <pryzby at telsasoft.com> wrote:
> 
> On Sun, Mar 02, 2025 at 04:52:48PM -0500, lr at pcorp.us wrote:
>> I've ticketed this issue here:  https://trac.osgeo.org/postgis/ticket/5856
>> 
>> 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%';
> 
> The DB where we hit the error says:
> 
> ts=# SELECT * FROM pg_catalog.pg_opclass WHERE opcname ILIKE 'brin%';
>  oid  | opcmethod |            opcname             | opcnamespace | opcowner | opcfamily | opcintype | opcdefault | opckeytype 
> -------+-----------+--------------------------------+--------------+----------+-----------+-----------+------------+------------
> 17704 |      3580 | brin_geometry_inclusion_ops_2d |         2200 |       10 |     17694 |     16419 | t          |      16454
> 17716 |      3580 | brin_geometry_inclusion_ops_3d |         2200 |       10 |     17712 |     16419 | f          |      16458
> 17726 |      3580 | brin_geometry_inclusion_ops_4d |         2200 |       10 |     17722 |     16419 | f          |      16458
> (3 rows)
> 
> And the 2nd instance I mentioned which has no
> opcname='brin_geography_inclusion_ops' says:
> 
> ts=# SELECT * FROM pg_catalog.pg_opclass WHERE opcname ILIKE 'brin%';
> oid | opcmethod | opcname | opcnamespace | opcowner | opcfamily | opcintype | opcdefault | opckeytype 
> -----+-----------+---------+--------------+----------+-----------+-----------+------------+------------
> (0 rows)


Weird, they have been around since forever, I wonder where they disappeared to?

-- Availability: 2.3.0
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;