[postgis-tickets] [SCM] PostGIS branch spatial-ref-sys-view updated. 3.2.0-463-ga363cfcc1
git at osgeo.org
git at osgeo.org
Thu Feb 3 04:16:20 PST 2022
This is an automated email from the git hooks/post-receive script. It was
generated because a ref change was pushed to the repository containing
the project "PostGIS".
The branch, spatial-ref-sys-view has been updated
via a363cfcc1d7e3664547790e7d0f907b5f33bf087 (commit)
from 26390a56feccc172fb2b8f1f3114a77b9e2ab72d (commit)
Those revisions listed above that are new to this repository have
not appeared on any other notification email; so we list those
revisions in full, below.
- Log -----------------------------------------------------------------
commit a363cfcc1d7e3664547790e7d0f907b5f33bf087
Author: Sandro Santilli <strk at kbt.io>
Date: Thu Feb 3 13:15:14 2022 +0100
Make spatial_ref_sys editing more backward compatible
Raise a WARNING on all operations (insert/delete/update) with hints
about the new way of doing things. Behave like spatial_ref_sys
was a table (next SELECT will see effects of your operation)
diff --git a/postgis/postgis.sql.in b/postgis/postgis.sql.in
index 3023c7532..2141c3357 100644
--- a/postgis/postgis.sql.in
+++ b/postgis/postgis.sql.in
@@ -2087,35 +2087,35 @@ CREATE OR REPLACE VIEW spatial_ref_sys AS (
CREATE OR REPLACE FUNCTION spatial_ref_sys_trigger()
RETURNS trigger AS $BODY$
BEGIN
+ RAISE WARNING 'Editing spatial_ref_sys is deprecated.'
+ USING HINT = 'Edit spatial_ref_sys_user instead';
IF ( TG_OP = 'INSERT' ) THEN
- BEGIN
- INSERT INTO spatial_ref_sys_user VALUES (NEW.*);
- EXCEPTION
- WHEN unique_violation THEN
- RAISE unique_violation
- USING MESSAGE = format('Duplicate user override for SRID value (%s) already exists.', NEW.srid);
- END;
- IF EXISTS ( SELECT 1 FROM spatial_ref_sys_postgis WHERE srid = NEW.srid )
+ IF EXISTS ( SELECT 1 FROM spatial_ref_sys_postgis
+ WHERE srid = NEW.srid )
THEN
- RAISE NOTICE 'User override for system SRID value % added', NEW.srid;
+ RAISE unique_violation USING
+ MESSAGE = format('System entry for SRID value (%s) already exists.', NEW.srid),
+ HINT = 'Insert to spatial_ref_sys_user for overriding it';
+ RETURN NULL;
+ ELSE
+ INSERT INTO spatial_ref_sys_user VALUES (NEW.*);
+ RETURN NEW;
END IF;
- RETURN NEW;
ELSIF ( TG_OP = 'DELETE' ) THEN
DELETE FROM spatial_ref_sys_user WHERE srid = OLD.srid;
- IF NOT FOUND THEN
- IF EXISTS ( SELECT 1 FROM spatial_ref_sys_postgis WHERE srid = OLD.srid )
- THEN
- RAISE NOTICE 'System SRID (%) will not be deleted', OLD.srid;
- END IF;
- RETURN NULL;
- ELSE
- RAISE DEBUG 'User override for system SRID value % deleted', OLD.srid;
- RETURN OLD;
+ IF FOUND THEN
+ RAISE NOTICE 'User SRID % deleted', OLD.srid;
+ END IF;
+ DELETE FROM spatial_ref_sys_postgis WHERE srid = OLD.srid;
+ IF FOUND THEN
+ RAISE WARNING 'System SRID % deleted, ', OLD.srid
+ USING HINT = 'use postgis_extension_upgrade() to restore';
END IF;
+ RETURN OLD; -- this will be the override, if present, or system (if present)
ELSIF ( TG_OP = 'UPDATE' ) THEN
BEGIN
INSERT INTO spatial_ref_sys_user VALUES (NEW.*);
- RAISE DEBUG 'User entry for SRID value % added', OLD.srid;
+ RAISE NOTICE 'User entry for SRID value % added', OLD.srid;
EXCEPTION
WHEN unique_violation THEN
UPDATE spatial_ref_sys_user
diff --git a/regress/core/spatial_ref_sys.sql b/regress/core/spatial_ref_sys.sql
index db32f7a0e..a3bfd1096 100644
--- a/regress/core/spatial_ref_sys.sql
+++ b/regress/core/spatial_ref_sys.sql
@@ -1,70 +1,74 @@
-set client_min_messages to NOTICE;
+set client_min_messages to WARNING;
-- https://trac.osgeo.org/postgis/ticket/5024
SELECT '#5024', 'spatial_ref_sys entries' AS table_name, count(*) FROM spatial_ref_sys;
--- Report custom SRIDs
-SELECT 'initial user srids', array_agg(srid order by srid) FROM spatial_ref_sys_user;
+-- Backward compatibility tests
--- Results in override being added
+-- Check test preconditions
+SELECT 'bc.preconditions',
+ (select count(srid) FROM spatial_ref_sys_user),
+ (select count(srid) FROM spatial_ref_sys_postgis WHERE srid >= 901000);
+
+-- Existing system SRIDs cannot be inserted to spatial_ref_sys
INSERT INTO spatial_ref_sys(srid,auth_name,auth_srid,srtext,proj4text)
VALUES (4326,'insert-existing-system-srid',0,'','')
- RETURNING 'insert-override-srid', srid, auth_name;
+ RETURNING 'bc.should-fail', 'insert-existing-system-srid', srid, auth_name;
+
+-- Non-existing system SRIDs inserts to spatial_ref_sys end up
+-- in user table
+INSERT INTO spatial_ref_sys(srid,auth_name,auth_srid,srtext,proj4text)
+ VALUES (901000,'insert-non-existing-system-srid',0,'','')
+ RETURNING 'bc1', 'insert-non-existing-system-srid', srid, auth_name;
-- Report overridden SRIDs
-SELECT 'user srids after insert override srid', array_agg(srid order by srid) FROM spatial_ref_sys_user;
+SELECT 'bc1.report', 'user srids', array_agg(srid order by srid) FROM spatial_ref_sys_user;
-- Fails: cannot insert a duplicate key (overridden srid)
INSERT INTO spatial_ref_sys(srid,auth_name,auth_srid,srtext,proj4text)
- VALUES (4326,'insert-existing-system-srid-dup',0,'','')
- RETURNING 'insert-duplicate-overridden-srid', srid, auth_name;
+ VALUES (901000,'insert-existing-user-srid',0,'','')
+ RETURNING 'bc.should-fail', 'insert-existing-user-srid', srid, auth_name;
-- Update override added by previous insert
UPDATE spatial_ref_sys
SET auth_name = 'update-existing-user-srid'
- WHERE srid = 4326
- RETURNING 'update-override-srid', srid, auth_name;
-
--- Report overridden SRIDs
-SELECT 'user srids after update override srid', array_agg(srid order by srid) FROM spatial_ref_sys_user;
-
--- Delete override
-DELETE FROM spatial_ref_sys WHERE srid = 4326
-RETURNING 'delete-override', srid, auth_name;
+ WHERE srid = 901000
+ RETURNING 'bc2', 'update-existing-user-srid', srid, auth_name;
-- Report overridden SRIDs
-SELECT 'user srids after delete override srid', array_agg(srid order by srid) FROM spatial_ref_sys_user;
+SELECT 'bc2.report', 'user srids after update override srid', array_agg(srid order by srid) FROM spatial_ref_sys_user;
--- Update inserts a new override
+-- Update of system srid inserts a new override
UPDATE spatial_ref_sys
SET auth_name = 'update-existing-system-srid'
WHERE srid = 4326
- RETURNING 'update-system-srid', srid, auth_name;
+ RETURNING 'bc3', 'update-system-srid', srid, auth_name;
-- Report overridden SRIDs
-SELECT 'user srids after update system srid', array_agg(srid order by srid) FROM spatial_ref_sys_user;
-
--- Insert custom (non-system) SRID
-INSERT INTO spatial_ref_sys(srid,auth_name,auth_srid,srtext,proj4text)
- VALUES (901000,'inserted_custom',0,'','')
- RETURNING 'insert-custom-srid', srid, auth_name;
-
--- Update custom srid
-UPDATE spatial_ref_sys
- SET auth_name = 'updated_custom'
- WHERE srid = 901000
- RETURNING 'update-custom-srid', srid, auth_name;
-
--- Report user and override SRIDs
-SELECT 'user srids after insert custom srid', array_agg(srid order by srid) FROM spatial_ref_sys_user;
-SELECT 'reported full_version overrides', (regexp_matches(postgis_full_version(), '([^(]*overridden SRID)'))[1];
-
--- Delete user entries (cleanup)
-WITH deleted AS (
- DELETE FROM spatial_ref_sys_user
- RETURNING 'deleted user srid', srid
-) SELECT FROM deleted ORDER BY srid;
-
--- Delete system SRIDs is a (noisy) no-op
-DELETE FROM spatial_ref_sys WHERE srid = 4326
-RETURNING 'delete-again-return', srid, auth_name;
+SELECT 'bc3.report', 'user srids after update override srid', array_agg(srid order by srid) FROM spatial_ref_sys_user;
+
+-- Check postgis_full_version() reporting
+SELECT 'bc3.report', 'reported full_version overrides', (regexp_matches(postgis_full_version(), '([^(]*overridden SRID)'))[1];
+
+-- Delete from spatial_ref_sys clears both
+-- user and system rows
+BEGIN;
+ SET client_min_messages TO ERROR;
+ DELETE FROM spatial_ref_sys;
+ -- Check test preconditions
+ SELECT 'bc4.delete',
+ (select count(srid) FROM spatial_ref_sys_user),
+ (select count(srid) FROM spatial_ref_sys_postgis);
+ROLLBACK;
+
+-- Cleanup
+WITH cleanup AS (
+ DELETE FROM spatial_ref_sys_user WHERE srid in ( 901000, 4326 )
+ RETURNING srid
+)
+SELECT 'bc.cleanup', array_agg(srid ORDER BY srid) FROM cleanup;
+
+-- Check test postconditions
+SELECT 'bc.postcondition',
+ (select count(srid) FROM spatial_ref_sys_user),
+ (select count(srid) FROM spatial_ref_sys_postgis WHERE srid >= 901000);
diff --git a/regress/core/spatial_ref_sys_expected b/regress/core/spatial_ref_sys_expected
index 84888358d..c03fc590e 100644
--- a/regress/core/spatial_ref_sys_expected
+++ b/regress/core/spatial_ref_sys_expected
@@ -1,17 +1,19 @@
#5024|spatial_ref_sys entries|8500
-initial user srids|
-NOTICE: User override for system SRID value 4326 added
-insert-override-srid|4326|insert-existing-system-srid
-user srids after insert override srid|{4326}
-ERROR: Duplicate user override for SRID value (4326) already exists.
-update-override-srid|4326|update-existing-user-srid
-user srids after update override srid|{4326}
-delete-override|4326|update-existing-user-srid
-user srids after delete override srid|
-update-system-srid|4326|update-existing-system-srid
-user srids after update system srid|{4326}
-insert-custom-srid|901000|inserted_custom
-update-custom-srid|901000|updated_custom
-user srids after insert custom srid|{4326,901000}
-reported full_version overrides|1 overridden SRID
-NOTICE: System SRID (4326) will not be deleted
+bc.preconditions|0|0
+WARNING: Editing spatial_ref_sys is deprecated.
+ERROR: System entry for SRID value (4326) already exists.
+WARNING: Editing spatial_ref_sys is deprecated.
+bc1|insert-non-existing-system-srid|901000|insert-non-existing-system-srid
+bc1.report|user srids|{901000}
+WARNING: Editing spatial_ref_sys is deprecated.
+ERROR: duplicate key value violates unique constraint "spatial_ref_sys_user_pkey"
+WARNING: Editing spatial_ref_sys is deprecated.
+bc2|update-existing-user-srid|901000|update-existing-user-srid
+bc2.report|user srids after update override srid|{901000}
+WARNING: Editing spatial_ref_sys is deprecated.
+bc3|update-system-srid|4326|update-existing-system-srid
+bc3.report|user srids after update override srid|{4326,901000}
+bc3.report|reported full_version overrides|1 overridden SRID
+bc4.delete|0|0
+bc.cleanup|{4326,901000}
+bc.postcondition|0|0
-----------------------------------------------------------------------
Summary of changes:
postgis/postgis.sql.in | 40 +++++++--------
regress/core/spatial_ref_sys.sql | 96 ++++++++++++++++++-----------------
regress/core/spatial_ref_sys_expected | 34 +++++++------
3 files changed, 88 insertions(+), 82 deletions(-)
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list