[postgis-tickets] r17029 - Remove deprecated functions postgis_constraint_srid, postgis_constraint_dims, postgis_constraint_type
Regina Obe
lr at pcorp.us
Sun Nov 18 03:12:34 PST 2018
Author: robe
Date: 2018-11-18 15:12:34 -0800 (Sun, 18 Nov 2018)
New Revision: 17029
Modified:
trunk/NEWS
trunk/postgis/postgis.sql.in
trunk/postgis/postgis_drop_after.sql
trunk/raster/rt_pg/rtpostgis.sql.in
Log:
Remove deprecated functions postgis_constraint_srid, postgis_constraint_dims, postgis_constraint_type
Replace use of pg_constraint.consrc with pg_get_constraintdef(oid) pg_constraint.consrc was removed in PostgreSQL 12 and pg_get_constraintdef function has existed at least since PostgreSQL 9.4 (our lowest supported for 2.5), so safe to use across all our supported versions
References #4321 for PostGIS 3.0
Modified: trunk/NEWS
===================================================================
--- trunk/NEWS 2018-11-18 21:32:17 UTC (rev 17028)
+++ trunk/NEWS 2018-11-18 23:12:34 UTC (rev 17029)
@@ -10,6 +10,12 @@
dimentions that aren't present in both operands.
Please REINDEX your ND indexes after upgrade.
+ - #4231, remove deprecated functions postgis_constraint_srid,
+ postgis_constraint_dims, postgis_constraint_type
+ These were deprecated in PostGIS 2.2.0
+ and used pg_constraint.consrc which was removed in PostgreSQL 12
+ (Regina Obe)
+
* New Features *
- #2902, postgis_geos_noop (Sandro Santilli)
- #4128, ST_AsMVT support for Feature ID (Stepan Kuzmin)
Modified: trunk/postgis/postgis.sql.in
===================================================================
--- trunk/postgis/postgis.sql.in 2018-11-18 21:32:17 UTC (rev 17028)
+++ trunk/postgis/postgis.sql.in 2018-11-18 23:12:34 UTC (rev 17029)
@@ -5669,62 +5669,11 @@
LANGUAGE 'sql' IMMUTABLE STRICT _PARALLEL COST 200;
-- Availability: 2.0.0
--- Deprecation in 2.2.0
-CREATE OR REPLACE FUNCTION postgis_constraint_srid(geomschema text, geomtable text, geomcolumn text) RETURNS integer AS
-$$
-SELECT replace(replace(split_part(s.consrc, ' = ', 2), ')', ''), '(', '')::integer
- FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
- WHERE n.nspname = $1
- AND c.relname = $2
- AND a.attname = $3
- AND a.attrelid = c.oid
- AND s.connamespace = n.oid
- AND s.conrelid = c.oid
- AND a.attnum = ANY (s.conkey)
- AND s.consrc LIKE '%srid(% = %';
-$$
-LANGUAGE 'sql' STABLE STRICT _PARALLEL;
-
--- Availability: 2.0.0
--- Deprecation in 2.2.0
-CREATE OR REPLACE FUNCTION postgis_constraint_dims(geomschema text, geomtable text, geomcolumn text) RETURNS integer AS
-$$
-SELECT replace(split_part(s.consrc, ' = ', 2), ')', '')::integer
- FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
- WHERE n.nspname = $1
- AND c.relname = $2
- AND a.attname = $3
- AND a.attrelid = c.oid
- AND s.connamespace = n.oid
- AND s.conrelid = c.oid
- AND a.attnum = ANY (s.conkey)
- AND s.consrc LIKE '%ndims(% = %';
-$$
-LANGUAGE 'sql' STABLE STRICT _PARALLEL;
-
--- support function to pull out geometry type from constraint check
--- will return pretty name instead of ugly name
--- Availability: 2.0.0
--- Deprecation in 2.2.0
-CREATE OR REPLACE FUNCTION postgis_constraint_type(geomschema text, geomtable text, geomcolumn text) RETURNS varchar AS
-$$
-SELECT replace(split_part(s.consrc, '''', 2), ')', '')::varchar
- FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
- WHERE n.nspname = $1
- AND c.relname = $2
- AND a.attname = $3
- AND a.attrelid = c.oid
- AND s.connamespace = n.oid
- AND s.conrelid = c.oid
- AND a.attnum = ANY (s.conkey)
- AND s.consrc LIKE '%geometrytype(% = %';
-$$
-LANGUAGE 'sql' STABLE STRICT _PARALLEL;
-
--- Availability: 2.0.0
-- Changed: 2.1.8 significant performance improvement for constraint based columns
-- Changed: 2.2.0 get rid of schema, table, column cast to improve performance
-- Changed: 2.4.0 List also Parent partitioned tables
+-- Changed: 2.5.2 replace use of pg_constraint.consrc with pg_get_constraintdef, consrc removed pg12
+
CREATE OR REPLACE VIEW geometry_columns AS
SELECT current_database()::character varying(256) AS f_table_catalog,
n.nspname AS f_table_schema,
@@ -5740,7 +5689,8 @@
LEFT JOIN ( SELECT s.connamespace,
s.conrelid,
s.conkey, replace(split_part(s.consrc, ''''::text, 2), ')'::text, ''::text) As type
- FROM pg_constraint AS s
+ FROM (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
+ FROM pg_constraint) AS s
WHERE s.consrc ~~* '%geometrytype(% = %'::text
) st ON st.connamespace = n.oid AND st.conrelid = c.oid AND (a.attnum = ANY (st.conkey))
@@ -5747,7 +5697,8 @@
LEFT JOIN ( SELECT s.connamespace,
s.conrelid,
s.conkey, replace(split_part(s.consrc, ' = '::text, 2), ')'::text, ''::text)::integer As ndims
- FROM pg_constraint AS s
+ FROM (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
+ FROM pg_constraint) AS s
WHERE s.consrc ~~* '%ndims(% = %'::text
) sn ON sn.connamespace = n.oid AND sn.conrelid = c.oid AND (a.attnum = ANY (sn.conkey))
@@ -5754,7 +5705,8 @@
LEFT JOIN ( SELECT s.connamespace,
s.conrelid,
s.conkey, replace(replace(split_part(s.consrc, ' = '::text, 2), ')'::text, ''::text), '('::text, ''::text)::integer As srid
- FROM pg_constraint AS s
+ FROM (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
+ FROM pg_constraint) AS s
WHERE s.consrc ~~* '%srid(% = %'::text
) sr ON sr.connamespace = n.oid AND sr.conrelid = c.oid AND (a.attnum = ANY (sr.conkey))
Modified: trunk/postgis/postgis_drop_after.sql
===================================================================
--- trunk/postgis/postgis_drop_after.sql 2018-11-18 21:32:17 UTC (rev 17028)
+++ trunk/postgis/postgis_drop_after.sql 2018-11-18 23:12:34 UTC (rev 17029)
@@ -175,7 +175,11 @@
DROP FUNCTION IF EXISTS _ST_DistanceRectTree(g1 geometry, g2 geometry);
DROP FUNCTION IF EXISTS _ST_DistanceRectTreeCached(g1 geometry, g2 geometry);
+DROP FUNCTION IF EXISTS postgis_constraint_srid(text,text,text);
+DROP FUNCTION IF EXISTS postgis_constraint_dims(text,text,text);
+DROP FUNCTION IF EXISTS postgis_constraint_type(text,text,text);
+
-- pgis_abs type was increased from 8 bytes in 2.1 to 16 bytes in 2.2
-- See #3460
UPDATE pg_type SET typlen=16 WHERE typname='pgis_abs' AND typlen=8;
Modified: trunk/raster/rt_pg/rtpostgis.sql.in
===================================================================
--- trunk/raster/rt_pg/rtpostgis.sql.in 2018-11-18 21:32:17 UTC (rev 17028)
+++ trunk/raster/rt_pg/rtpostgis.sql.in 2018-11-18 23:12:34 UTC (rev 17029)
@@ -7210,7 +7210,9 @@
split_part(s.consrc, ' = ', 2),
'[\(\)]', '', 'g'
)::integer
- FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
+ FROM pg_class c, pg_namespace n, pg_attribute a
+ , (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
+ FROM pg_constraint) AS s
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -7280,7 +7282,9 @@
),
'[ ''''\(\)]', '', 'g'
)::text AS val
- FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
+ FROM pg_class c, pg_namespace n, pg_attribute a
+ , (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
+ FROM pg_constraint) AS s
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -7366,7 +7370,9 @@
'[\(\)]', '', 'g'
)::integer
END
- FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
+ FROM pg_class c, pg_namespace n, pg_attribute a
+ , (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
+ FROM pg_constraint) AS s
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -7442,7 +7448,9 @@
RETURNS geometry AS $$
SELECT
trim(both '''' from split_part(trim(split_part(s.consrc, ' @ ', 2)), '::', 1))::geometry
- FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
+ FROM pg_class c, pg_namespace n, pg_attribute a
+ , (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
+ FROM pg_constraint) AS s
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -7504,7 +7512,9 @@
RETURNS boolean AS $$
SELECT
TRUE
- FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
+ FROM pg_class c, pg_namespace n, pg_attribute a
+ , (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
+ FROM pg_constraint) AS s
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -7561,7 +7571,10 @@
RETURNS boolean AS $$
SELECT
TRUE
- FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s, pg_index idx, pg_operator op
+ FROM pg_class c, pg_namespace n, pg_attribute a
+ , (SELECT connamespace, conrelid, conkey, contype, pg_get_constraintdef(oid) As consrc
+ FROM pg_constraint) AS s
+ , pg_index idx, pg_operator op
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -7609,7 +7622,10 @@
BEGIN
SELECT
s.conname INTO cn
- FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s, pg_index idx, pg_operator op
+ FROM pg_class c, pg_namespace n, pg_attribute a
+ , (SELECT connamespace, conrelid, conkey, contype, pg_get_constraintdef(oid) As consrc
+ FROM pg_constraint) AS s
+ , pg_index idx, pg_operator op
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -7632,7 +7648,9 @@
RETURNS boolean AS $$
SELECT
TRUE
- FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
+ FROM pg_class c, pg_namespace n, pg_attribute a
+ , (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
+ FROM pg_constraint) AS s
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -7743,7 +7761,9 @@
split_part(s.consrc, ' = ', 2),
'[\(\)]', '', 'g'
)::integer
- FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
+ FROM pg_class c, pg_namespace n, pg_attribute a
+ , (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
+ FROM pg_constraint) AS s
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -7810,7 +7830,9 @@
'::', 1
)
)::text[]
- FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
+ FROM pg_class c, pg_namespace n, pg_attribute a
+ , (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
+ FROM pg_constraint) AS s
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -7893,7 +7915,9 @@
'::', 1
)
)::double precision[]
- FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
+ FROM pg_class c, pg_namespace n, pg_attribute a
+ , (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
+ FROM pg_constraint) AS s
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -7982,7 +8006,9 @@
'::', 1
)
)::boolean[]
- FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
+ FROM pg_class c, pg_namespace n, pg_attribute a
+ , (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
+ FROM pg_constraint) AS s
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -8624,7 +8650,9 @@
split_part(split_part(s.consrc, '''::name', 2), '''', 2)::name,
split_part(split_part(s.consrc, '''::name', 3), '''', 2)::name,
trim(both from split_part(s.consrc, ',', 2))::integer
- FROM pg_class c, pg_namespace n, pg_attribute a, pg_constraint s
+ FROM pg_class c, pg_namespace n, pg_attribute a
+ , (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
+ FROM pg_constraint) AS s
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -8694,7 +8722,8 @@
pg_attribute a,
pg_type t,
pg_namespace n,
- pg_constraint s
+ (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
+ FROM pg_constraint) AS s
WHERE t.typname = 'raster'::name
AND a.attisdropped = false
AND a.atttypid = t.oid
More information about the postgis-tickets
mailing list