[postgis-devel] Turning spatial_ref_sys into a view to separate system vs. user entries

[Sandro Santilli]

On Wed, Feb 02, 2022 at 03:08:09PM +0100, Raúl Marín wrote:
> On 2022-02-02 14:29, Sandro Santilli wrote:
> > What do you think ?
> 
> I can't really comment on this specific feature (view per user) since I've
> never had that specific need but currently there are a couple of pains with
> spatial_ref_sys that I think should be tackled (at some point in the future,
> not necessarily now):
> 
> * The table rows don't exists on pg_upgrade. The table is created, then
> other tables are created, then the table is populated. This makes it
> impossible to have an index over ST_Transform [1].

I'm afraid this should be postponed. ST_Transform is inherentily
VOLATILE as it reads data from tables. The fact we're marking it
as IMMUTABLE is just wrong. You cannot build an index based on a
VOLATILE function if I recall correctly (PostgreSQL raises an exception).

> * The table contains definitions that might be outdated or imprecise and
> PROJ has already way better alternatives for them. PROJ is already doing all
> the heavy lifting to keep good definitions and transformations so I think
> the default behaviour should be to defer to it, and only keep new
> (re-)definitions under spatial_ref_sys.

Are you basically saying we should just NEVER need "system"
spatial_ref_sys entries but ONLY custom and overridden ones?

--strk;