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

[Sandro Santilli]

On Mon, Feb 07, 2022 at 03:15:35PM -0800, Paul Ramsey wrote:
> 
> 
> > On Feb 7, 2022, at 3:02 PM, Sandro Santilli <strk at kbt.io> wrote:
> > 
> > On Mon, Feb 07, 2022 at 02:35:27PM -0800, Paul Ramsey wrote:
> >>> On Feb 7, 2022, at 2:27 PM, Sandro Santilli <strk at kbt.io> wrote:
> >>> 
> >>>  3.2.1dev 3.2.0-28-g4443d6989
> >> 
> >> POSTGIS="3.2.1dev 3.2.0-339-gafaebf921" [EXTENSION] PGSQL="140" GEOS="3.11.0dev-CAPI-1.16.0" PROJ="8.1.0" LIBXML="2.9.4" LIBJSON="0.15" LIBPROTOBUF="1.4.0" WAGYU="0.5.0 (Internal)"
> > 
> > There seem to be NO CHANGE between mine and yours spatial_ref_sys.sql
> > in the source repository:
> > 
> >  git diff afaebf921 4443d6989 spatial_ref_sys.sql # none
> > 
> >> But I "see" now they are in fact different. There's two one-character differences in the SRTEXT.
> >> 
> >> So mystery solved.
> > 
> > Well, next mystery is WHY there are those one-character
> > differences if the spatial_ref_sys.sql in the repository
> > if it hadn't changed between your version and my version ?
> 
> Checking the spatial_ref_sys.sql in the 3.2 branch and master, the difference is right there in the files.

Uhm, are you sure ? The following command doesn't show any difference:

      git diff origin/stable-3.2 origin/master spatial_ref_sys.sql

> Someone clever re-generated the spatial_ref_sys in master and changed
> the multiple INSERTS into one huge multi-valued insert.

Nobody changed spatial_ref_sys.sql in master branch (currently
pointing at 1052dfb9f689a8cca8ba038a00bf9377846df1cb) but the change
you describe is in the "spatial-ref-sys-view" branch as of
1caa46edfbffad4d94ff3cf02c3250a7fa68df9c.

> So... the update is detecting the system-side changes as user changes
> and preserving them?

The update is aiming at not loosing any data, so if some entries
are not in the *new* system spatial_ref_sys_postgis table they are
retained in spatial_ref_sys_user. The retained records having SRID
value which are ALSO in the system table are reported as "overrides"
in the postgis_full_version() output (did you try that?).

> I mean, that seems impossible to do practically, if
> the user actually ALTERS the system codes, there's no way to distinguish
> between changes the user created and changes the EPSG has introduced.

Can you fully describe the scenario you are concerned about ?

  - UPDATE spatial_ref_sys table before the upgrade ?

    In that case the updated rows will be found in
    spatial_ref_sys_user after the upgrade

  - UPDATE spatial_ref_sys_postgis table after the upgrade ?

    In that case the updated rows will just *disappear*
    on next upgrade

  - UPDATE spatial_ref_sys view after the upgrade ?

    In that case the updated rows will be put in
    spatial_ref_sys_user (and user will be WARNED that
    editing spatial_ref_sys is deprecated)

Changes introduced by EPSG and not yet found in the spatial_ref_sys_postgis
table can be put by users in the spatial_ref_sys_user table, with
auth_name/auth_srid of their choice. Upon upgrading PostGIS to next
release they can decide whether or not to keep their manually added
entries, comparing theirs with the system ones.

As an added bonus, a 1:1 matching comparison is performed by the
upgrade procedure itself, which in that case will drop the manually
added entries.

--strk;