[postgis-devel] ERROR: operator class "brin_geography_inclusion_ops" does not exist for access method "brin"

[Regina Obe]

> On Sun, Oct 31, 2021 at 09:26:45PM -0400, Regina Obe wrote:
> > > ts=# ALTER EXTENSION postgis ADD OPERATOR CLASS
> > > brin_geography_inclusion_ops USING brin;
> > > ERROR:  extension "postgis" does not exist
> > >
> > > Thanks for looking at this.
> 
> > [Regina Obe]
> > Okay that's puzzling.  Did you not change this one to be an extension
based
> one?
> 
> Yea, it's what I said:
> 
> On Sat, Oct 09, 2021 at 12:11:22PM -0500, Justin Pryzby wrote:
> > On Sat, Oct 09, 2021 at 01:02:40PM -0400, Regina Obe wrote:
> > > When did you switch from non-extension to extension version?
> >
> > Do you mean CREATE EXTENSION rather than \i postgis.sql ?  I suppose
> > the answer is that I haven't yet switched, or finished switching.
> >
> > I'd prefer to use "CREATE EXTENSION", but it looks like on many of our
> > instances, postgis is not listed in psql \dx, and it was never clear
> > to me how to fix it.  We only have postgis for 2 columns used by
> > mapscript, so if that works, it's good enough for us.
> 
> I found this old ticket/thread where I reported a previous upgrade issue,
and
> gave some more context.
> https://trac.osgeo.org/postgis/ticket/4516
> 
> If the response ends up being that "you needed to switch from scripts to
> extensions, and you didn't do that, and it's not supported", that's okay.
> Essentially my problem report is to point out that that that upgrade was
never
> enforced, and so you're not guaranteed that anybody actually did it.  And
> postgis_extensions_upgrade() is sometimes failing to handle that case.  If
> that's not expected to work for unpackaged postgis, it should probably
detect
> that case and throw an error, to force users like me to run
> uninstall_postgis.sql and then recreate it as an extension.  Or maybe it's
> intended to work..
> 
> --
> Justin
[Regina Obe] 
The intension was it would work for unpackaged and force packaging as I
recall.
So the issue is real and something we missed why it's not working.

DO NOT RUN uninstall_postgis.sql -- it will destroy your data.

I'll go thru the scripts later this week to have a mechanism to install
missing bits.
We just might need to do more checking to compensate for incomplete
upgrades.

If you are up for it, I can let you know when I think I have something and
you can test it out.
It will be hard I think to be sure if we got it working right without having
an install already suffering the issue.

For PostgreSQL 13+ it is true they got rid of unpackaged so it made it much
more difficult to create an extension from unpackaged.  I thought  Sandro
came up with a way to work around it and packaged in upgrade or maybe not
(as he needed that feature to unbundle the postgis raster extension from the
postgis extension.

The approach basically is to create a naked PostGIS extension and add in all
unpackaged functions to it.
You can fake it by having an empty postgis--fake.sql file in your
share/extension folder and then doing

CREATE EXTENSION postgis VERSION "fake";

and then add all the functions etc. to it

with ALTER EXTENSION postgis ADD ....

Then you have a postgis--fake--3.1.4.sql (or whatever version which is just
a copy of one of the regular postgis--3.1.0--3.1.4.sql doesn't matter which
as they are all identical) -- to allow upgrading to the real thing.


Thanks,
Regina