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

[Sandro Santilli]

On Thu, Feb 03, 2022 at 12:32:21AM +0100, Even Rouault wrote:
> 
> > > Well, if the DELETE doesn't delete, then the test is broken anyway, since it
> > > relies on DELETE FROM spatial_ref_sys resulting in an empty view/table.
> > Ouch, I didn't consider this case.
> > If silently ignoring doesn't really give us backward compatibility now
> > I wonder if it was better to error out :?
>
> Erroring out would be better IMHO

Tonight I've had another idea: let edits of spatial_ref_sys behave as
in older PostGIS, that is: your autotest DELETE FROM spatial_ref_sys
would result in next SELECT FROM spatial_ref_sys returning NO rows
(ie: will delete both system and user tables).

System tables could then be reconstructed by running
`postgis_extensions_upgrade()` (or sourcing spatial_ref_sys.sql again)

User tables could not be easily reconstructed but users deleting
from spatial_ref_sys should already know this.

This new approach was committed in the `spatial-ref-sys-view` branch
as of commit a363cfcc1d7e3664547790e7d0f907b5f33bf087.

Note that your `DELETE FROM spatial_ref_sys` will now be very noisy
as a WARNING will be sent to client (with associated HINT) for each
and every row in the table.

--strk;