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

[Sandro Santilli]

On Mon, Feb 07, 2022 at 02:05:20PM -0800, Paul Ramsey wrote:
> I still feel like we're doing heart surgery here for a very thinly populated user population. I would still just rather not do this. I am not seeing the outcry and user demand for this, and it's not a "use it if you want it, ignore it if you don't" kind of a thing. 
> 
> Anyways, I wanted to at least try it, so I got the branch and did an upgrade. 
> 
>   createdb srs  
>   psql srs
>   -- old version
>   create extension postgis version '3.2.1dev';
>   -- user-generated srs
>   insert into spatial_ref_sys select 200000 as srid, auth_name, auth_srid, srtext, proj4text from spatial_ref_sys where srid = 900913;
> 
>   -- master built from spatial-ref-sys-view branch
>   alter extension postgis update to '3.3.0dev';
> 
>   select srid from spatial_ref_sys_user;
> 
>      4893
>      6319
>      6782
>    200000
> 
> The upgrade proceeded silently and did drop my srs into the right place, very nice. I'm a bit baffled why it picked up those others though, and they are also in the system table.

I'm afraid we need to be more specific about which version of 3.2.1dev
you got there, please include output of postgis_full_version().

Here's me running your own test:

  =# create extension postgis version '3.2.1dev';
  CREATE EXTENSION

  =# select postgis_scripts_installed();
    postgis_scripts_installed
  ------------------------------
   3.2.1dev 3.2.0-28-g4443d6989
  (1 row)

  =# insert into spatial_ref_sys select 200000 as srid, auth_name, auth_srid, srtext, proj4text from spatial_ref_sys where srid = 900913;
  INSERT 0 1

  =# alter extension postgis update to '3.3.0dev';
  ALTER EXTENSION

  =# select postgis_scripts_installed();
  postgis_perf=# select postgis_scripts_installed();
     postgis_scripts_installed
  -------------------------------
   3.3.0dev 3.2.0-506-g3fe3da966
  (1 row)

  =# select srid from spatial_ref_sys_user;
    srid
  --------
   200000
  (1 row)

The upgrade script will keep records that are not fully equal between
the system and the user installs, so could it be your 3.2.1dev version
had 4893, 6319 and 6782 records having different values ?

Check out the output of postgis_full_version(), it should tell you
that you have 3 SRID overrides, as a warning.

--strk;

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