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

[Paul Ramsey]

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.

select srid from spatial_ref_sys_postgis where srid in (4893, 6319, 6782, 200000);

 4893
 6319
 6782


Anyways, I'm a -0 shading into a negative one unless you trot out some users who will worship this change.

P.


> On Feb 4, 2022, at 2:16 PM, Regina Obe <lr at pcorp.us> wrote:
> 
> I'm calling for a formal vote to put into master the hard work that Sandro
> has already done in -
> https://git.osgeo.org/gitea/postgis/postgis/src/branch/spatial-ref-sys-view 
> 
> +1 from me.
> 
> Reason:
> We need a clean way to update/insert new spatial_ref_sys entries without
> accidentally messing up ones that users explicitly put in or update.
> Right now we have a bug that we are not updating spatial_ref_sys at all so
> upgrades are incomplete without users manually filtering thru
> spatal_ref_sys.sql and adding the ones they are missing.
> 
> We had hoped to use ranges, but users get those from various sources, so I
> think the range idea is impractical for many users.
> 
> To summarize the plan:
> 
> 1) spatial_ref_sys will become a view with INSERT/UPDATE/DELETE instead of
> triggers
> And will be a union of spatial_ref_sys_postgis and spatial_ref_sys_user
> which I will describe in next bullets.
> The entries in spatial_ref_sys_user override those in
> spatial_ref_sys_postgis
> 
> 2) spatial_ref_sys_postgis will have all the entries we currently have 
> For upgrades
> 
> TRUNCATe spatial_ref_sys_postgis
> INSERT spatial_ref_sys_postgis
> :
> 
> 3) User entries will be in spatial_ref_sys_user
> 
> 4) For backward compatibility if a user inserts an entry in the
> spatial_ref_sys view, the INSERT will be redirected to
> spatial_ref_sys_user
> 
> 5) The clean idea - we can change later, but plan would be any entries in
> spatial_ref_sys_user that are exactly the same as the ones in
> spatial_ref_sys_postgis would be removed.
> Presumably most of these were added cause our spatial_ref_sys was out of
> date and people needed newer ones.
> 
> 
> 
> _______________________________________________
> postgis-devel mailing list
> postgis-devel at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-devel