[postgis-users] ST_Union on empty polygon loses SRID

[Regina Obe]

Okay I didn't completely read the error:

> > > SELECT postgis_extensions_upgrade();
> > > NOTICE:  Updating extension postgis from 3.0.2 to 3.1.0alpha1
> > > ERROR:  extension "postgis" has no update path from version "3.0.2"
> > > to
> > version "3.1.0alpha1"
> > > CONTEXT:  SQL statement "ALTER EXTENSION postgis UPDATE TO
> > "3.1.0alpha1";"
> > > PL/pgSQL function postgis_extensions_upgrade() line 68 at EXECUTE
> >

That error means your new version is 3.1.0alpha1 but you are scripts currently on 3.0.2.  So something is wrong with your binary install.  It could be you have a postgis installed for a different PG than what you are checking. PostGIS versions are tied to a specific PostgreSQL instance, and debian/ubuntu makes it easy to run multiple.


I would first check how many clusters you have and versions with below command:

pg_lsclusters

Mine for example shows:
Ver Cluster Port Status Owner    Data directory              Log file
13  main    5432 online postgres /var/lib/postgresql/13/main /var/log/postgresql/postgresql-13-main.log

Whatever you have active, you need to run something like the below replacing the 13 with whatever version of PostgreSQL you have active.

apt install postgresql-13-postgis-3


You shouldn't have to run the below steps I mentioned because your scripts are already on a released version postgis-3.0.2  but are bound to a in-development binary.  If you ran it already, it's still okay cause all the upgrade scripts are copies of each other.

> -----Original Message-----
> From: Regina Obe [mailto:lr at pcorp.us]
> Sent: Sunday, November 28, 2021 12:27 PM
> To: 'PostGIS Users Discussion' <postgis-users at lists.osgeo.org>;
> 'jonathan at schultz.la' <jonathan at schultz.la>
> Subject: RE: [postgis-users] ST_Union on empty polygon loses SRID
> 
> All the upgrade scripts are the same. We just delete the pre-release scripts to
> reduce disk space.
> 
> To upgrade yours do the following
> -- this changes all your installed versions to 3.1.0 so you have an upgrade path
> UPDATE pg_catalog.pg_extension SET extversion = '3.1.0' WHERE extname
> IN('postgis', 'postgis_raster','postgis_sfcgal', 'postgis_topology',
> 'postgis_tiger_geocoder');
> 
> SELECT postgis_extensions_upgrade();
> 
> > -----Original Message-----
> > From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On
> > Behalf Of Jonathan Schultz
> > Sent: Sunday, November 28, 2021 7:50 AM
> > To: PostGIS Users Discussion <postgis-users at lists.osgeo.org>
> > Subject: Re: [postgis-users] ST_Union on empty polygon loses SRID
> >
> > This is actually in response to Regina Obe's email which I didn't
> > receive but only saw on the list archives. Maybe there is another way
> > of replying to her message but I don't know it.
> >
> > I have definitely upgraded my debian/unstable installation.
> >
> >  > $ dpkg-query -W postgis
> >  > postgis 3.1.4+dfsg-3+b1
> >
> > but still I get the previously mentioned response to
> > postgis_full_version()
> >
> > I had presumed that this was just due to debian and postgis
> > maintaining different versioning systems. But is it possible that
> > postgresql is for some reason not registering the upgrade to postgis?
> >
> > One possibly related problem I found was:
> >
> > > SELECT postgis_extensions_upgrade();
> > > NOTICE:  Updating extension postgis from 3.0.2 to 3.1.0alpha1
> > > ERROR:  extension "postgis" has no update path from version "3.0.2"
> > > to
> > version "3.1.0alpha1"
> > > CONTEXT:  SQL statement "ALTER EXTENSION postgis UPDATE TO
> > "3.1.0alpha1";"
> > > PL/pgSQL function postgis_extensions_upgrade() line 68 at EXECUTE
> >
> > Any suggestions?
> >
> > Thanks!
> >
> >
> > On 27/11/21 15:18, Darafei "Kom?pa" Praliaskouski wrote:
> > > Does not reproduce on my 3.2 and 3.1.4 installs:
> > >
> > > 10:17:37[gis] > select postgis_full_version();
> > > +-------------------------------------------------------------------
> > > +----------------------------
> > ----------------------------------------------------------------------
> > ----------------------------
> > ----------------------------------------------------------------+
> > >
> > >
> > >
> > >
> > >                                                postgis_full_version
> > >
> > >
> > > +-------------------------------------------------------------------
> > > +--
> > > +-------------------------------------------------------------------
> > > +--
> > > +-------------------------------------------------------------------
> > > +--
> > > +--------------------------------------------------
> > >
> > >   POSTGIS="3.2.0dev 3.2.0beta1-23-g94d4588ac" [EXTENSION]
> > PGSQL="140"
> > > GEOS="3.10.0dev-CAPI-1.15.0" SFCGAL="1.3.8" PROJ="7.2.1"
> GDAL="GDAL
> > > 3.2.2, released 2021/03/05" LIBXML="2.9.12" LIBJSON="0.15"
> > > LIBPROTOBUF="1.3.3" WAGYU="0.5.0 (Internal)" TOPOLOGY RASTER
> > > +-------------------------------------------------------------------
> > > +----------------------------
> > ----------------------------------------------------------------------
> > ----------------------------
> > ----------------------------------------------------------------+
> > >
> > > (1 row)
> > >
> > > Time: 47,743 ms
> > > 10:17:43[gis] > select ST_SRID(ST_Union(ST_GeomFromText('POLYGON
> > > EMPTY', 4283)));
> > > +---------+
> > >   st_srid
> > > +---------
> > >      4283
> > > +---------+
> > > (1 row)
> > >
> > > Time: 1,531 ms
> > >
> > >
> > > On Sat, Nov 27, 2021 at 7:34 AM Jonathan Schultz
> > > <jonathan at schultz.la <mailto:jonathan at schultz.la>> wrote:
> > >
> > >     Hello,
> > >
> > >     I have found with a query that makes a union of geometries which
> > >     includes an empty polygon produces a result without an SRID. For a
> > >     trivial example:
> > >
> > >       > => select ST_SRID(ST_Union(ST_GeomFromText('POLYGON EMPTY',
> > 4283)));
> > >       >  st_srid ---------
> > >       >        0
> > >
> > >     This seems like a bug to me but maybe there is a good explanation?
> > >
> > >     Thanks,
> > >     Jonathan
> > >     _______________________________________________
> > >     postgis-users mailing list
> > >     postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
> > >     https://lists.osgeo.org/mailman/listinfo/postgis-users
> > >     <https://lists.osgeo.org/mailman/listinfo/postgis-users>
> > >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at lists.osgeo.org
> > https://lists.osgeo.org/mailman/listinfo/postgis-users