[postgis-tickets] [SCM] PostGIS branch stable-2.3 updated. e719d49c3153e6ca1483a97167965fd11e908089
git at osgeo.org
git at osgeo.org
Sun Mar 1 12:24:32 PST 2020
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, stable-2.3 has been updated
via e719d49c3153e6ca1483a97167965fd11e908089 (commit)
from 8475f493275ee1f262f00ef007a24eef16737dfa (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 e719d49c3153e6ca1483a97167965fd11e908089
Author: Regina Obe <lr at pcorp.us>
Date: Sun Mar 1 15:24:24 2020 -0500
Replace use of consrc with pg_get_constraintdef in postgis views, raster functions and views closes #4518 for PostGIS 2.3.11
diff --git a/NEWS b/NEWS
index 0b353f3..bfa2948 100644
--- a/NEWS
+++ b/NEWS
@@ -4,6 +4,8 @@ XXXX/XX/XX
* Bug Fixes and Enhancements *
- #4475, Avoid reading into empty ptarray (Paul Ramsey)
+ - #4518, Fix geometry_columns and raster views
+ to not use pg_constraints.consrc which was removed in PG 12 (Regina Obe)
- #4492, Fix ST_Simplify ignoring the value of the 3rd parameter (Raúl Marín)
- #4494, Fix ST_Simplify output having an outdated bbox (Raúl Marín)
- #4493, Fix ST_RemoveRepeatedPoints output having an outdated bbox (Raúl Marín)
diff --git a/postgis/postgis.sql.in b/postgis/postgis.sql.in
index 3cea410..90d4a1f 100644
--- a/postgis/postgis.sql.in
+++ b/postgis/postgis.sql.in
@@ -5357,6 +5357,8 @@ 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 scheman, table, column cast to improve performance
+-- Changed: 2.3.11 List also Parent partitioned tables
+-- Changed: 2.3.11 use pg_get_constraindef since consrc removed in pg12
CREATE OR REPLACE VIEW geometry_columns AS
SELECT current_database()::character varying(256) AS f_table_catalog,
n.nspname AS f_table_schema,
@@ -5372,25 +5374,28 @@ CREATE OR REPLACE VIEW geometry_columns AS
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))
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))
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))
- WHERE (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'm'::"char", 'f'::"char"]))
+ WHERE (c.relkind = ANY (ARRAY['r'::"char", 'v'::"char", 'm'::"char", 'f'::"char", 'p'::"char"]))
AND NOT c.relname = 'raster_columns'::name AND t.typname = 'geometry'::name
AND NOT pg_is_other_temp_schema(c.relnamespace) AND has_table_privilege(c.oid, 'SELECT'::text);
diff --git a/raster/rt_pg/rtpostgis.sql.in b/raster/rt_pg/rtpostgis.sql.in
index bec32dd..b48eed5 100644
--- a/raster/rt_pg/rtpostgis.sql.in
+++ b/raster/rt_pg/rtpostgis.sql.in
@@ -6881,6 +6881,7 @@ CREATE OR REPLACE FUNCTION _drop_raster_constraint(rastschema name, rasttable na
$$ LANGUAGE 'plpgsql' VOLATILE STRICT
COST 100;
+-- Changed: 2.3.11 change to use pg_get_contraintdef instead of deprecated consrc column
CREATE OR REPLACE FUNCTION _raster_constraint_info_srid(rastschema name, rasttable name, rastcolumn name)
RETURNS integer AS $$
SELECT
@@ -6888,7 +6889,9 @@ CREATE OR REPLACE FUNCTION _raster_constraint_info_srid(rastschema name, rasttab
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) s
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -6947,6 +6950,7 @@ CREATE OR REPLACE FUNCTION _drop_raster_constraint_srid(rastschema name, rasttab
LANGUAGE 'sql' VOLATILE STRICT
COST 100;
+-- Changed: 2.3.11 change to use pg_get_contraintdef instead of deprecated consrc column
CREATE OR REPLACE FUNCTION _raster_constraint_info_scale(rastschema name, rasttable name, rastcolumn name, axis char)
RETURNS double precision AS $$
WITH c AS (SELECT
@@ -6960,7 +6964,8 @@ CREATE OR REPLACE FUNCTION _raster_constraint_info_scale(rastschema name, rastta
),
'[ ''''\(\)]', '', '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) s
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -7036,6 +7041,7 @@ CREATE OR REPLACE FUNCTION _drop_raster_constraint_scale(rastschema name, rastta
$$ LANGUAGE 'plpgsql' VOLATILE STRICT
COST 100;
+-- Changed: 2.3.11 change to use pg_get_contraintdef instead of deprecated consrc column
CREATE OR REPLACE FUNCTION _raster_constraint_info_blocksize(rastschema name, rasttable name, rastcolumn name, axis text)
RETURNS integer AS $$
SELECT
@@ -7048,7 +7054,8 @@ CREATE OR REPLACE FUNCTION _raster_constraint_info_blocksize(rastschema name, ra
'[\(\)]', '', '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) s
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -7120,11 +7127,13 @@ CREATE OR REPLACE FUNCTION _drop_raster_constraint_blocksize(rastschema name, ra
$$ LANGUAGE 'plpgsql' VOLATILE STRICT
COST 100;
+-- Changed: 2.3.11 change to use pg_get_contraintdef instead of deprecated consrc column
CREATE OR REPLACE FUNCTION _raster_constraint_info_extent(rastschema name, rasttable name, rastcolumn name)
RETURNS geometry AS $$
SELECT
trim(both '''' from split_part(trim(split_part(s.consrc, ' @ ', 2)), '::', 1))::@extschema at .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) s
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -7188,11 +7197,13 @@ CREATE OR REPLACE FUNCTION _drop_raster_constraint_extent(rastschema name, rastt
LANGUAGE 'sql' VOLATILE STRICT
COST 100;
+-- Changed: 2.3.11 change to use pg_get_contraintdef instead of deprecated consrc column
CREATE OR REPLACE FUNCTION _raster_constraint_info_alignment(rastschema name, rasttable name, rastcolumn name)
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) s
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -7249,11 +7260,14 @@ CREATE OR REPLACE FUNCTION _drop_raster_constraint_alignment(rastschema name, ra
LANGUAGE 'sql' VOLATILE STRICT
COST 100;
+-- Changed: 2.3.11 change to use pg_get_contraintdef instead of deprecated consrc column
CREATE OR REPLACE FUNCTION _raster_constraint_info_spatially_unique(rastschema name, rasttable name, rastcolumn name)
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) s
+ , pg_index idx, pg_operator op
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -7294,6 +7308,7 @@ CREATE OR REPLACE FUNCTION _add_raster_constraint_spatially_unique(rastschema na
$$ LANGUAGE 'plpgsql' VOLATILE STRICT
COST 100;
+-- Changed: 2.3.11 change to use pg_get_contraintdef instead of deprecated consrc column
CREATE OR REPLACE FUNCTION _drop_raster_constraint_spatially_unique(rastschema name, rasttable name, rastcolumn name)
RETURNS boolean AS $$
DECLARE
@@ -7301,7 +7316,9 @@ CREATE OR REPLACE FUNCTION _drop_raster_constraint_spatially_unique(rastschema n
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) s
+ , pg_index idx, pg_operator op
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -7320,11 +7337,13 @@ CREATE OR REPLACE FUNCTION _drop_raster_constraint_spatially_unique(rastschema n
$$ LANGUAGE 'plpgsql' VOLATILE STRICT
COST 100;
+-- Changed: 2.3.11 change to use pg_get_contraintdef instead of deprecated consrc column
CREATE OR REPLACE FUNCTION _raster_constraint_info_coverage_tile(rastschema name, rasttable name, rastcolumn name)
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) s
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -7428,6 +7447,7 @@ CREATE OR REPLACE FUNCTION _drop_raster_constraint_regular_blocking(rastschema n
LANGUAGE 'sql' VOLATILE STRICT
COST 100;
+-- Changed: 2.3.11 to use pg_get_constraintdef instead of consrc. consrc got removed in PG 12
CREATE OR REPLACE FUNCTION _raster_constraint_info_num_bands(rastschema name, rasttable name, rastcolumn name)
RETURNS integer AS $$
SELECT
@@ -7435,7 +7455,8 @@ CREATE OR REPLACE FUNCTION _raster_constraint_info_num_bands(rastschema name, ra
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) s
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -7493,6 +7514,7 @@ CREATE OR REPLACE FUNCTION _drop_raster_constraint_num_bands(rastschema name, ra
LANGUAGE 'sql' VOLATILE STRICT
COST 100;
+-- Changed: 2.3.11 to use pg_get_constraintdef instead of consrc. consrc got removed in PG 12
CREATE OR REPLACE FUNCTION _raster_constraint_info_pixel_types(rastschema name, rasttable name, rastcolumn name)
RETURNS text[] AS $$
SELECT
@@ -7505,7 +7527,8 @@ CREATE OR REPLACE FUNCTION _raster_constraint_info_pixel_types(rastschema name,
'::', 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) s
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -7578,6 +7601,7 @@ CREATE OR REPLACE FUNCTION _drop_raster_constraint_pixel_types(rastschema name,
$$ SELECT @extschema at ._drop_raster_constraint($1, $2, 'enforce_pixel_types_' || $3) $$
LANGUAGE 'sql' VOLATILE STRICT;
+-- Changed: 2.3.11 to use pg_get_constraintdef instead of consrc. consrc got removed in PG 12
CREATE OR REPLACE FUNCTION _raster_constraint_info_nodata_values(rastschema name, rasttable name, rastcolumn name)
RETURNS double precision[] AS $$
SELECT
@@ -7590,7 +7614,8 @@ CREATE OR REPLACE FUNCTION _raster_constraint_info_nodata_values(rastschema name
'::', 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) s
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -7669,6 +7694,7 @@ CREATE OR REPLACE FUNCTION _drop_raster_constraint_nodata_values(rastschema name
LANGUAGE 'sql' VOLATILE STRICT
COST 100;
+-- Changed: 2.3.11 to use pg_get_constraintdef instead of consrc. consrc got removed in PG 12
CREATE OR REPLACE FUNCTION _raster_constraint_info_out_db(rastschema name, rasttable name, rastcolumn name)
RETURNS boolean[] AS $$
SELECT
@@ -7681,7 +7707,8 @@ CREATE OR REPLACE FUNCTION _raster_constraint_info_out_db(rastschema name, rastt
'::', 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) s
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -8314,6 +8341,7 @@ CREATE OR REPLACE FUNCTION _overview_constraint(ov raster, factor integer, refsc
LANGUAGE 'sql' STABLE
COST 100;
+-- Changed: 2.3.11 to use pg_get_constraintdef instead of consrc. consrc got removed in PG 12
CREATE OR REPLACE FUNCTION _overview_constraint_info(
ovschema name, ovtable name, ovcolumn name,
OUT refschema name, OUT reftable name, OUT refcolumn name, OUT factor integer
@@ -8324,7 +8352,8 @@ CREATE OR REPLACE FUNCTION _overview_constraint_info(
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) s
WHERE n.nspname = $1
AND c.relname = $2
AND a.attname = $3
@@ -8378,6 +8407,7 @@ CREATE OR REPLACE FUNCTION _drop_overview_constraint(ovschema name, ovtable name
------------------------------------------------------------------------------
-- Availability: 2.0.0
-- Changed: 2.2.0
+-- Changed: 2.3.11 to use pg_get_constraintdef instead of consrc. consrc got removed in PG 12
CREATE OR REPLACE VIEW raster_overviews AS
SELECT
current_database() AS o_table_catalog,
@@ -8394,7 +8424,8 @@ CREATE OR REPLACE VIEW raster_overviews AS
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) s
WHERE t.typname = 'raster'::name
AND a.attisdropped = false
AND a.atttypid = t.oid
-----------------------------------------------------------------------
Summary of changes:
NEWS | 2 ++
postgis/postgis.sql.in | 13 +++++++---
raster/rt_pg/rtpostgis.sql.in | 59 +++++++++++++++++++++++++++++++++----------
3 files changed, 56 insertions(+), 18 deletions(-)
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list