[postgis-devel] PSC Vote: Make spatial_ref_sys as a view

[Sandro Santilli]

On Mon, Feb 07, 2022 at 03:58:56PM -0800, Paul Ramsey wrote:

> I'm testing "stable-3.2" against "spatial-ref-sys-view".
> 
> Compare the entries for EPSG:4893, there are those tiny changes, for whatever reason.

Oh, I can think of a very likely reason: I did search&replace to turn
';' into ',' to move from multiple-statement to single-statement.
Now mystery solved :)

I've now fixed that (eventually I'll rebase and squash again).

> > Can you fully describe the scenario you are concerned about ?
> 
> The one I just ran into. I have system entries showing up in my
> spatial_ref_sys_user table, not because I changed anything but because
> the EPSG entries in the 3.2 version of postgis are oh-so-slightly
> different from the ones in the 3.3 (currently spatial-ref-sys-view)
> version of postgis.

Of course this can happen. We have a way to check, before release,
if we're making this mistake:

  regress/run_test.pl -v \
    --upgrade-path unpackaged3.2.0dev--:auto \
    regress/core/spatial_ref_sys

The above did indeed show those 3 overrides for me too (I wasn't
seeing them before because I was using extension and didn't install
the code in my branch).

  -bc.preconditions|0|0
  +bc.preconditions|3|0

Full testsuite is currently NOT guarding after this because Dronie
found out that in 2.5 we actually changed values for 74 SRIDs, not
sure if "oh-so-slightly" different or more. If you want to compare,
the list of those SRIDS can be found on line 4937 of this Dronie run:
https://dronie.osgeo.org/postgis/postgis/2798/1/3
Note that upgrades from all other versions tested (from 2.5, 3.0, 3.1
and 3.2) resulted in an empty spatial_ref_sys_user.

Do you have better ideas of which entries from spatial_ref_sys
in pre-view times could be sacrificed ? Personally I was not even
sure we wanted to drop the 100% equal entries...

As a user for me would be easy to do something like:

  DELETE FROM spatial_ref_sys_user
  WHERE auth_name != 'strk';

Or, if I trust PostGIS to know better than me about EPSG codes:

  DELETE FROM spatial_ref_sys_user
  WHERE auth_name = 'EPSG'
  AND (srid, auth_name, auth_srid) NOT IN (
    SELECT srid, auth_name, auth_srid FROM spatial_ref_sys_postgis
  );

Harder it would be if upgrading postgis got rid of something
valuable for me.

--strk; 

  Libre GIS consultant/developer
  https://strk.kbt.io/services.html