[postgis-users] Should the spatial_ref_sys table be updated using the "ALTER EXTENSION...UPDATE" command?

[Regina Obe]

The table is not created during update, but should be updated with new records and corrections as needed.  I’ve confirmed I see the same issue in one of my databases that was upgraded to  3.1.4 (5757 rows) (compared to a fresh install 8500 rows).

 

I’ve ticketed here:

 

https://trac.osgeo.org/postgis/ticket/5024

 

As far as hard upgrade, that should  only be needed if going from Pre PostGIS 2.0 to PostGIS 2.0 and above.

 

 

 

From: postgis-users [mailto:postgis-users-bounces at lists.osgeo.org] On Behalf Of McCoy, Shawn
Sent: Monday, November 22, 2021 1:56 PM
To: postgis-users at lists.osgeo.org
Subject: [postgis-users] Should the spatial_ref_sys table be updated using the "ALTER EXTENSION...UPDATE" command?

 

I looked through code and didn't see any update commands or any calls to "spatial_ref_sys.sql" in the script "postgis--ANY--3.1.4.sql".  In 3.1.4 there are several new rows in the table.


1.  Is this object purposely not recreated via the update script?
2.  In the docs:   <https://postgis.net/docs/manual-3.0/postgis_installation.html#upgrading> https://postgis.net/docs/manual-3.0/postgis_installation.html#upgrading, is this why the "HARD UPGRADE" method is required?  If so, why is there even a symlink named "postgis--2.4.5--3.1.4.sql"?

 

Thanks for any information,

Shawn

 


quick repro of my question:

 

postgres=> create extension postgis version '2.4.5';

CREATE EXTENSION

postgres=> select count(*) from spatial_ref_sys;

 count

-------

  5757

(1 row)

 

postgres=> alter extension postgis update to '3.1.4';

WARNING:  unpackaging raster

WARNING:  PostGIS Raster functionality has been unpackaged

HINT:  type `SELECT postgis_extensions_upgrade();` to finish the upgrade. After upgrading, if you want to drop raster, run: DROP EXTENSION postgis_raster;

ALTER EXTENSION

postgres=> select count(*) from spatial_ref_sys;

 count

-------

  5757

(1 row)

 

whereas...

 

postgres=> create extension postgis version '3.1.4';

CREATE EXTENSION

postgres=> select count(*) from spatial_ref_sys;

 count

-------

  8500

(1 row)

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20211124/c9d9ca18/attachment.html>