[postgis-tickets] r17840 - Backport system views
Paul Ramsey
pramsey at cleverelephant.ca
Mon Sep 30 01:54:38 PDT 2019
Author: pramsey
Date: 2019-09-30 13:54:38 -0700 (Mon, 30 Sep 2019)
New Revision: 17840
Modified:
branches/2.4/NEWS
branches/2.4/configure.ac
branches/2.4/postgis/postgis.sql.in
branches/2.4/raster/rt_pg/rtpostgis.sql.in
branches/2.4/regress/estimatedextent.sql
branches/2.4/regress/run_test.pl
Log:
Backport system views
Closes #4518
Modified: branches/2.4/NEWS
===================================================================
--- branches/2.4/NEWS 2019-09-30 19:49:09 UTC (rev 17839)
+++ branches/2.4/NEWS 2019-09-30 20:54:38 UTC (rev 17840)
@@ -11,6 +11,7 @@
- #4493, Fix ST_RemoveRepeatedPoints output having an outdated bbox (Raúl Marín)
- #4495, Fix ST_SnapToGrid output having an outdated bbox (Raúl Marín)
- #4506, Remove tolerance in point-in-ring stabline tests (Paul Ramsey)
+ - #4518, Backport system views to make upgrade to PostGIS3 cleaner (Paul Ramsey)
PostGIS 2.4.8
Modified: branches/2.4/configure.ac
===================================================================
--- branches/2.4/configure.ac 2019-09-30 19:49:09 UTC (rev 17839)
+++ branches/2.4/configure.ac 2019-09-30 20:54:38 UTC (rev 17840)
@@ -462,6 +462,11 @@
AC_MSG_ERROR([PostGIS requires PostgreSQL >= 9.3])
fi
+ dnl Ensure that we are using PostgreSQL <= 11
+ dnl if test $POSTGIS_PGSQL_VERSION -ge 120; then
+ dnl AC_MSG_ERROR([PostGIS requires PostgreSQL <= 11])
+ dnl fi
+
HAVE_BRIN=no
if test $POSTGIS_PGSQL_VERSION -gt 94; then
HAVE_BRIN=yes
Modified: branches/2.4/postgis/postgis.sql.in
===================================================================
--- branches/2.4/postgis/postgis.sql.in 2019-09-30 19:49:09 UTC (rev 17839)
+++ branches/2.4/postgis/postgis.sql.in 2019-09-30 20:54:38 UTC (rev 17840)
@@ -5409,7 +5409,8 @@
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
+ 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
@@ -5426,7 +5427,8 @@
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
+ 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
@@ -5445,7 +5447,8 @@
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
+ 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
@@ -5476,7 +5479,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))
@@ -5483,7 +5487,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))
@@ -5490,7 +5495,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: branches/2.4/raster/rt_pg/rtpostgis.sql.in
===================================================================
--- branches/2.4/raster/rt_pg/rtpostgis.sql.in 2019-09-30 19:49:09 UTC (rev 17839)
+++ branches/2.4/raster/rt_pg/rtpostgis.sql.in 2019-09-30 20:54:38 UTC (rev 17840)
@@ -6935,7 +6935,8 @@
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
@@ -7007,7 +7008,8 @@
),
'[ ''''\(\)]', '', '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
@@ -7095,7 +7097,8 @@
'[\(\)]', '', '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
@@ -7171,7 +7174,8 @@
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
@@ -7235,7 +7239,8 @@
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
@@ -7296,7 +7301,8 @@
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, 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
@@ -7344,7 +7350,8 @@
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, 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
@@ -7367,7 +7374,8 @@
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
@@ -7478,7 +7486,8 @@
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
@@ -7548,7 +7557,8 @@
'::', 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
@@ -7633,7 +7643,8 @@
'::', 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
@@ -7724,7 +7735,8 @@
'::', 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
@@ -8368,7 +8380,8 @@
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
@@ -8438,7 +8451,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) s
WHERE t.typname = 'raster'::name
AND a.attisdropped = false
AND a.atttypid = t.oid
Modified: branches/2.4/regress/estimatedextent.sql
===================================================================
--- branches/2.4/regress/estimatedextent.sql 2019-09-30 19:49:09 UTC (rev 17839)
+++ branches/2.4/regress/estimatedextent.sql 2019-09-30 20:54:38 UTC (rev 17840)
@@ -9,19 +9,19 @@
insert into t(g) values ('LINESTRING(-10 -50, 20 30)');
-- #877.3
-with e as ( select ST_EstimatedExtent('t','g') as e )
+with e as ( select ST_EstimatedExtent('t','g') as e offset 0)
select '#877.3', round(st_xmin(e.e)::numeric, 5), round(st_xmax(e.e)::numeric, 5),
round(st_ymin(e.e)::numeric, 5), round(st_ymax(e.e)::numeric, 5) from e;
-- #877.4
analyze t;
-with e as ( select ST_EstimatedExtent('t','g') as e )
+with e as ( select ST_EstimatedExtent('t','g') as e offset 0)
select '#877.4', round(st_xmin(e.e)::numeric, 5), round(st_xmax(e.e)::numeric, 5),
round(st_ymin(e.e)::numeric, 5), round(st_ymax(e.e)::numeric, 5) from e;
-- #877.5
truncate t;
-with e as ( select ST_EstimatedExtent('t','g') as e )
+with e as ( select ST_EstimatedExtent('t','g') as e offset 0)
select '#877.5', round(st_xmin(e.e)::numeric, 5), round(st_xmax(e.e)::numeric, 5),
round(st_ymin(e.e)::numeric, 5), round(st_ymax(e.e)::numeric, 5) from e;
drop table t;
@@ -39,17 +39,17 @@
-- #3391.1
-with e as ( select ST_EstimatedExtent('c1','g') as e )
+with e as ( select ST_EstimatedExtent('c1','g') as e offset 0)
select '#3391.1', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
-- #3391.2
-with e as ( select ST_EstimatedExtent('c2','g') as e )
+with e as ( select ST_EstimatedExtent('c2','g') as e offset 0)
select '#3391.2', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
-- #3391.3
-with e as ( select ST_EstimatedExtent('p','g') as e )
+with e as ( select ST_EstimatedExtent('p','g') as e offset 0)
select '#3391.3', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
@@ -62,17 +62,17 @@
analyze p;
-- #3391.4
-with e as ( select ST_EstimatedExtent('c1','g') as e )
+with e as ( select ST_EstimatedExtent('c1','g') as e offset 0)
select '#3391.4', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
-- #3391.5
-with e as ( select ST_EstimatedExtent('c2','g') as e )
+with e as ( select ST_EstimatedExtent('c2','g') as e offset 0)
select '#3391.5', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
-- #3391.6
-with e as ( select ST_EstimatedExtent('p','g') as e )
+with e as ( select ST_EstimatedExtent('p','g') as e offset 0)
select '#3391.6', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
@@ -85,17 +85,17 @@
analyze p;
-- #3391.7
-with e as ( select ST_EstimatedExtent('c1','g') as e )
+with e as ( select ST_EstimatedExtent('c1','g') as e offset 0)
select '#3391.7', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
-- #3391.8
-with e as ( select ST_EstimatedExtent('c2','g') as e )
+with e as ( select ST_EstimatedExtent('c2','g') as e offset 0)
select '#3391.8', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
-- #3391.9
-with e as ( select ST_EstimatedExtent('p','g') as e )
+with e as ( select ST_EstimatedExtent('p','g') as e offset 0)
select '#3391.9', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
@@ -108,17 +108,17 @@
analyze p;
-- #3391.10
-with e as ( select ST_EstimatedExtent('c1','g') as e )
+with e as ( select ST_EstimatedExtent('c1','g') as e offset 0)
select '#3391.10', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
-- #3391.11
-with e as ( select ST_EstimatedExtent('c2','g') as e )
+with e as ( select ST_EstimatedExtent('c2','g') as e offset 0)
select '#3391.11', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
-- #3391.12
-with e as ( select ST_EstimatedExtent('p','g') as e )
+with e as ( select ST_EstimatedExtent('p','g') as e offset 0)
select '#3391.12', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
@@ -138,22 +138,22 @@
-- #3391.13
-with e as ( select ST_EstimatedExtent('public','p','g','t') as e )
+with e as ( select ST_EstimatedExtent('public','p','g','t') as e offset 0)
select '#3391.13', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
-- #3391.14
-with e as ( select ST_EstimatedExtent('public','p','g','f') as e )
+with e as ( select ST_EstimatedExtent('public','p','g','f') as e offset 0)
select '#3391.14', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
-- #3391.15
-with e as ( select ST_EstimatedExtent('public','c1','g', 't') as e )
+with e as ( select ST_EstimatedExtent('public','c1','g', 't') as e offset 0)
select '#3391.15', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
-- #3391.16
-with e as ( select ST_EstimatedExtent('public','c1','g', 'f') as e )
+with e as ( select ST_EstimatedExtent('public','c1','g', 'f') as e offset 0)
select '#3391.16', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
@@ -166,22 +166,22 @@
analyze p;
-- #3391.17
-with e as ( select ST_EstimatedExtent('public','p','g','f') as e )
+with e as ( select ST_EstimatedExtent('public','p','g','f') as e offset 0)
select '#3391.17', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
-- #3391.18
-with e as ( select ST_EstimatedExtent('public','p','g','t') as e )
+with e as ( select ST_EstimatedExtent('public','p','g','t') as e offset 0)
select '#3391.18', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
-- #3391.19
-with e as ( select ST_EstimatedExtent('public','c1','g', 'f') as e )
+with e as ( select ST_EstimatedExtent('public','c1','g', 'f') as e offset 0)
select '#3391.19', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
-- #3391.20
-with e as ( select ST_EstimatedExtent('public','c1','g', 't') as e )
+with e as ( select ST_EstimatedExtent('public','c1','g', 't') as e offset 0)
select '#3391.20', round(st_xmin(e.e)::numeric, 2), round(st_xmax(e.e)::numeric, 2),
round(st_ymin(e.e)::numeric, 2), round(st_ymax(e.e)::numeric, 2) from e;
Modified: branches/2.4/regress/run_test.pl
===================================================================
--- branches/2.4/regress/run_test.pl 2019-09-30 19:49:09 UTC (rev 17839)
+++ branches/2.4/regress/run_test.pl 2019-09-30 20:54:38 UTC (rev 17840)
@@ -124,7 +124,10 @@
$ENV{"LANG"} = "C";
# Add locale info to the psql options
-my $PGOPTIONS = $ENV{"PGOPTIONS"} . " -c lc_messages=C -c client_min_messages=NOTICE";
+my $PGOPTIONS = $ENV{"PGOPTIONS"};
+$PGOPTIONS .= " -c lc_messages=C";
+$PGOPTIONS .= " -c client_min_messages=NOTICE";
+$PGOPTIONS .= " -c extra_float_digits=0";
$ENV{"PGOPTIONS"} = $PGOPTIONS;
# Bring the path info in
More information about the postgis-tickets
mailing list