[postgis-tickets] [SCM] PostGIS branch stable-3.1 updated. 3.1.4-26-ge3921ac43
git at osgeo.org
git at osgeo.org
Mon Jan 31 16:58:47 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, stable-3.1 has been updated
via e3921ac43be7beb497dd44be910c16687e84481b (commit)
from 2a5f869a6a1da681fafc0eda30c1c7a7adb953e6 (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 e3921ac43be7beb497dd44be910c16687e84481b
Author: Regina Obe <lr at pcorp.us>
Date: Mon Jan 31 19:58:20 2022 -0500
search_path vulnerability during install. References #5069 for PostGIS 3.1.5
diff --git a/NEWS b/NEWS
index df990e692..d7566a751 100644
--- a/NEWS
+++ b/NEWS
@@ -1,5 +1,5 @@
PostGIS 3.1.5
-2021/XX/XX
+2022/02/XX
* Bug Fixes
- #4994, Random missing INSERT in shp2pgsql (Sandro Santilli)
@@ -10,6 +10,8 @@ PostGIS 3.1.5
- #5033, #5035, allow upgrades in presence of views using deprecated
functions (Sandro Santilli)
- #5046, Fix upgrades in absence of old library (Sandro Santilli)
+ - #5069, search_path vulnerabilty during install/upgrade
+ (Regina Obe)
PostGIS 3.1.4
2021/09/04
diff --git a/extensions/extensions_unpackage.sh b/extensions/extensions_unpackage.sh
index 44a6384ca..af3c4be97 100755
--- a/extensions/extensions_unpackage.sh
+++ b/extensions/extensions_unpackage.sh
@@ -24,7 +24,7 @@ SELECT 'ALTER EXTENSION ' || extname || ' DROP ' ||
E'\\\\1 using \\\\2'
) || ';' AS sqladd
FROM pg_catalog.pg_depend AS d
-INNER JOIN pg_extension AS e ON (d.refobjid = e.oid)
+INNER JOIN pg_catalog.pg_extension AS e ON (d.refobjid = e.oid)
WHERE d.refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass
AND deptype = 'e' AND e.extname = '${ext}' ORDER BY sqladd;
diff --git a/extensions/postgis/unpackage_raster_if_needed.sql b/extensions/postgis/unpackage_raster_if_needed.sql
index 9d48244f1..bee2a83f9 100644
--- a/extensions/postgis/unpackage_raster_if_needed.sql
+++ b/extensions/postgis/unpackage_raster_if_needed.sql
@@ -1,10 +1,10 @@
DO $unpackage_if_needed$
BEGIN
IF EXISTS (
- select t.typname from pg_depend d, pg_extension e, pg_type t where
+ select t.typname from pg_catalog.pg_depend d, pg_catalog.pg_extension e, pg_catalog.pg_type t where
e.extname = 'postgis' and d.refclassid =
'pg_catalog.pg_extension'::pg_catalog.regclass and d.refobjid = e.oid
- and d.classid = 'pg_type'::regclass and d.objid = t.oid
+ and d.classid = 'pg_catalog.pg_type'::pg_catalog.regclass and d.objid = t.oid
and t.typname = 'raster'
) THEN
diff --git a/extensions/postgis_extension_helper.sql b/extensions/postgis_extension_helper.sql
index bc1e9f92d..35cc7723f 100644
--- a/extensions/postgis_extension_helper.sql
+++ b/extensions/postgis_extension_helper.sql
@@ -28,7 +28,7 @@ DECLARE
var_class text := '';
var_is_aggregate boolean := false;
var_sql_list text := '';
- var_pgsql_version integer := current_setting('server_version_num');
+ var_pgsql_version integer := pg_catalog.current_setting('server_version_num');
BEGIN
var_class := CASE WHEN lower(param_type) = 'function' OR lower(param_type) = 'aggregate' THEN 'pg_proc' ELSE '' END;
var_is_aggregate := CASE WHEN lower(param_type) = 'aggregate' THEN true ELSE false END;
@@ -36,34 +36,34 @@ BEGIN
IF var_pgsql_version < 110000 THEN
var_sql_list := $sql$SELECT 'ALTER EXTENSION ' || e.extname || ' DROP ' || $3 || ' ' || COALESCE(proc.proname || '(' || oidvectortypes(proc.proargtypes) || ')' ,typ.typname, cd.relname, op.oprname,
cs.typname || ' AS ' || ct.typname || ') ', opcname, opfname) || ';' AS remove_command
- FROM pg_depend As d INNER JOIN pg_extension As e
- ON d.refobjid = e.oid INNER JOIN pg_class As c ON
+ FROM pg_catalog.pg_depend As d INNER JOIN pg_catalog.pg_extension As e
+ ON d.refobjid = e.oid INNER JOIN pg_catalog.pg_class As c ON
c.oid = d.classid
- LEFT JOIN pg_proc AS proc ON proc.oid = d.objid
- LEFT JOIN pg_type AS typ ON typ.oid = d.objid
- LEFT JOIN pg_class As cd ON cd.oid = d.objid
- LEFT JOIN pg_operator As op ON op.oid = d.objid
- LEFT JOIN pg_cast AS ca ON ca.oid = d.objid
- LEFT JOIN pg_type AS cs ON ca.castsource = cs.oid
- LEFT JOIN pg_type AS ct ON ca.casttarget = ct.oid
- LEFT JOIN pg_opclass As oc ON oc.oid = d.objid
- LEFT JOIN pg_opfamily As ofa ON ofa.oid = d.objid
+ LEFT JOIN pg_catalog.pg_proc AS proc ON proc.oid = d.objid
+ LEFT JOIN pg_catalog.pg_type AS typ ON typ.oid = d.objid
+ LEFT JOIN pg_catalog.pg_class As cd ON cd.oid = d.objid
+ LEFT JOIN pg_catalog.pg_operator As op ON op.oid = d.objid
+ LEFT JOIN pg_catalog.pg_cast AS ca ON ca.oid = d.objid
+ LEFT JOIN pg_catalog.pg_type AS cs ON ca.castsource = cs.oid
+ LEFT JOIN pg_catalog.pg_type AS ct ON ca.casttarget = ct.oid
+ LEFT JOIN pg_catalog.pg_opclass As oc ON oc.oid = d.objid
+ LEFT JOIN pg_catalog.pg_opfamily As ofa ON ofa.oid = d.objid
WHERE d.deptype = 'e' and e.extname = $1 and c.relname = $2 AND COALESCE(proc.proisagg, false) = $4;$sql$;
ELSE -- for PostgreSQL 11 and above, they removed proc.proisagg among others and replaced with some func type thing
var_sql_list := $sql$SELECT 'ALTER EXTENSION ' || e.extname || ' DROP ' || $3 || ' ' || COALESCE(proc.proname || '(' || oidvectortypes(proc.proargtypes) || ')' ,typ.typname, cd.relname, op.oprname,
cs.typname || ' AS ' || ct.typname || ') ', opcname, opfname) || ';' AS remove_command
- FROM pg_depend As d INNER JOIN pg_extension As e
- ON d.refobjid = e.oid INNER JOIN pg_class As c ON
+ FROM pg_catalog.pg_depend As d INNER JOIN pg_catalog.pg_extension As e
+ ON d.refobjid = e.oid INNER JOIN pg_catalog.pg_class As c ON
c.oid = d.classid
- LEFT JOIN pg_proc AS proc ON proc.oid = d.objid
- LEFT JOIN pg_type AS typ ON typ.oid = d.objid
- LEFT JOIN pg_class As cd ON cd.oid = d.objid
- LEFT JOIN pg_operator As op ON op.oid = d.objid
- LEFT JOIN pg_cast AS ca ON ca.oid = d.objid
- LEFT JOIN pg_type AS cs ON ca.castsource = cs.oid
- LEFT JOIN pg_type AS ct ON ca.casttarget = ct.oid
- LEFT JOIN pg_opclass As oc ON oc.oid = d.objid
- LEFT JOIN pg_opfamily As ofa ON ofa.oid = d.objid
+ LEFT JOIN pg_catalog.pg_proc AS proc ON proc.oid = d.objid
+ LEFT JOIN pg_catalog.pg_type AS typ ON typ.oid = d.objid
+ LEFT JOIN pg_catalog.pg_class As cd ON cd.oid = d.objid
+ LEFT JOIN pg_catalog.pg_operator As op ON op.oid = d.objid
+ LEFT JOIN pg_catalog.pg_cast AS ca ON ca.oid = d.objid
+ LEFT JOIN pg_catalog.pg_type AS cs ON ca.castsource = cs.oid
+ LEFT JOIN pg_catalog.pg_type AS ct ON ca.casttarget = ct.oid
+ LEFT JOIN pg_catalog.pg_opclass As oc ON oc.oid = d.objid
+ LEFT JOIN pg_catalog.pg_opfamily As ofa ON ofa.oid = d.objid
WHERE d.deptype = 'e' and e.extname = $1 and c.relname = $2 AND (proc.prokind = 'a') = $4;$sql$;
END IF;
@@ -85,7 +85,7 @@ CREATE OR REPLACE FUNCTION postgis_extension_drop_if_exists(param_extension text
RETURNS boolean AS
$$
DECLARE
- var_sql_ext text := 'ALTER EXTENSION ' || quote_ident(param_extension) || ' ' || replace(param_statement, 'IF EXISTS', '');
+ var_sql_ext text := 'ALTER EXTENSION ' || pg_catalog.quote_ident(param_extension) || ' ' || pg_catalog.replace(param_statement, 'IF EXISTS', '');
var_result boolean := false;
BEGIN
BEGIN
@@ -109,13 +109,13 @@ DECLARE
var_result text;
var_cur_search_path text;
BEGIN
- SELECT reset_val INTO var_cur_search_path FROM pg_settings WHERE name = 'search_path';
- IF var_cur_search_path LIKE '%' || quote_ident(a_schema_name) || '%' THEN
+ SELECT reset_val INTO var_cur_search_path FROM pg_catalog.pg_settings WHERE name = 'search_path';
+ IF var_cur_search_path LIKE '%' || pg_catalog.quote_ident(a_schema_name) || '%' THEN
var_result := a_schema_name || ' already in database search_path';
ELSE
var_cur_search_path := var_cur_search_path || ', '
- || quote_ident(a_schema_name);
- EXECUTE 'ALTER DATABASE ' || quote_ident(current_database())
+ || pg_catalog.quote_ident(a_schema_name);
+ EXECUTE 'ALTER DATABASE ' || pg_catalog.quote_ident(current_database())
|| ' SET search_path = ' || var_cur_search_path;
var_result := a_schema_name || ' has been added to end of database search_path ';
END IF;
diff --git a/extras/tiger_geocoder/utility/set_search_path.sql b/extras/tiger_geocoder/utility/set_search_path.sql
index c8e8595bb..19d2f9523 100644
--- a/extras/tiger_geocoder/utility/set_search_path.sql
+++ b/extras/tiger_geocoder/utility/set_search_path.sql
@@ -13,9 +13,9 @@ DECLARE
var_result text;
var_cur_search_path text;
BEGIN
- SELECT reset_val INTO var_cur_search_path FROM pg_settings WHERE name = 'search_path';
+ SELECT reset_val INTO var_cur_search_path FROM pg_catalog.pg_settings WHERE name = 'search_path';
- EXECUTE 'SET search_path = ' || quote_ident(a_schema_name) || ', ' || var_cur_search_path;
+ EXECUTE 'SET search_path = ' || pg_catalog.quote_ident(a_schema_name) || ', ' || var_cur_search_path;
var_result := a_schema_name || ' has been made primary for install ';
RETURN var_result;
END
diff --git a/loader/postgis.pl b/loader/postgis.pl
index 5ef24574d..a6858d963 100644
--- a/loader/postgis.pl
+++ b/loader/postgis.pl
@@ -63,7 +63,7 @@ sub status
{
my $sql = "
SELECT n.nspname
- FROM pg_namespace n, pg_proc p
+ FROM pg_catalog.pg_namespace n, pg_catalog.pg_proc p
WHERE n.oid = p.pronamespace
AND p.proname = 'postgis_full_version'
";
diff --git a/postgis/postgis.sql.in b/postgis/postgis.sql.in
index 75ccc135b..9d235f0a0 100644
--- a/postgis/postgis.sql.in
+++ b/postgis/postgis.sql.in
@@ -29,15 +29,15 @@ DO $$
DECLARE
pgver text;
BEGIN
- SELECT substring(version(), 'PostgreSQL ([0-9\.]+)') INTO pgver;
+ SELECT pg_catalog.substring(version(), 'PostgreSQL ([0-9\.]+)') INTO pgver;
IF POSTGIS_PGSQL_VERSION::text != ( SELECT CASE
- WHEN split_part(s,'.',1)::integer > 9
- THEN split_part(s,'.',1) || '0'
+ WHEN pg_catalog.split_part(s,'.',1)::integer > 9
+ THEN pg_catalog.split_part(s,'.',1) || '0'
ELSE
- split_part(s,'.', 1) || split_part(s,'.', 2)
+ pg_catalog.split_part(s,'.', 1) || pg_catalog.split_part(s,'.', 2)
END
- FROM substring(version(), 'PostgreSQL ([0-9\.]+)') AS s )
+ FROM pg_catalog.substring(version(), 'PostgreSQL ([0-9\.]+)') AS s )
THEN
RAISE EXCEPTION 'PostGIS built for PostgreSQL % cannot be loaded in PostgreSQL %',
POSTGIS_PGSQL_HR_VERSION, pgver;
@@ -52,7 +52,7 @@ DECLARE
rec RECORD;
BEGIN
FOR rec IN
- SELECT n.nspname, p.proname FROM pg_proc p, pg_namespace n
+ SELECT n.nspname, p.proname FROM pg_catalog.pg_proc p, pg_catalog.pg_namespace n
WHERE p.proname = 'postgis_version'
AND p.pronamespace = n.oid
LOOP
@@ -73,9 +73,9 @@ BEGIN
-- a WARNING if in a previous version (only up to minor version checked)
--
curver_text := POSTGIS_LIB_VERSION;
- IF split_part(curver_text,'.',1)::int > split_part(version,'.',1)::int OR
- ( split_part(curver_text,'.',1) = split_part(version,'.',1) AND
- split_part(curver_text,'.',2) != split_part(version,'.',2) )
+ IF pg_catalog.split_part(curver_text,'.',1)::int > pg_catalog.split_part(version,'.',1)::int OR
+ ( pg_catalog.split_part(curver_text,'.',1) = pg_catalog.split_part(version,'.',1) AND
+ pg_catalog.split_part(curver_text,'.',2) != pg_catalog.split_part(version,'.',2) )
THEN
RAISE WARNING '% signature was deprecated in %. Please use %', oldname, version, newname;
ELSE
@@ -2016,7 +2016,7 @@ BEGIN
FROM pg_class c,
pg_attribute a,
pg_type t,
- pg_namespace n
+ pg_catalog.pg_namespace n
WHERE c.relkind IN('r','v','f', 'p')
AND t.typname = 'geometry'
AND a.attisdropped = false
@@ -2033,7 +2033,7 @@ BEGIN
FROM pg_class c,
pg_attribute a,
pg_type t,
- pg_namespace n
+ pg_catalog.pg_namespace n
WHERE c.relkind IN( 'r', 'f', 'p')
AND t.typname = 'geometry'
AND a.attisdropped = false
@@ -2104,7 +2104,7 @@ BEGIN
FROM pg_class c,
pg_attribute a,
pg_type t,
- pg_namespace n
+ pg_catalog.pg_namespace n
WHERE c.relkind IN('r', 'f', 'p')
AND t.typname = 'geometry'
AND a.attisdropped = false
@@ -2287,7 +2287,7 @@ BEGIN
-- Verify schema
IF ( schema_name IS NOT NULL AND schema_name != '' ) THEN
- sql := 'SELECT nspname FROM pg_namespace ' ||
+ sql := 'SELECT nspname FROM pg_catalog.pg_namespace ' ||
'WHERE text(nspname) = ' || quote_literal(schema_name) ||
'LIMIT 1';
RAISE DEBUG '%', sql;
@@ -2883,8 +2883,8 @@ CREATE OR REPLACE FUNCTION _postgis_scripts_pgsql_version() RETURNS text
CREATE OR REPLACE FUNCTION _postgis_pgsql_version() RETURNS text
AS $$
- SELECT CASE WHEN split_part(s,'.',1)::integer > 9 THEN split_part(s,'.',1) || '0' ELSE split_part(s,'.', 1) || split_part(s,'.', 2) END AS v
- FROM substring(version(), 'PostgreSQL ([0-9\.]+)') AS s;
+ SELECT CASE WHEN pg_catalog.split_part(s,'.',1)::integer > 9 THEN pg_catalog.split_part(s,'.',1) || '0' ELSE pg_catalog.split_part(s,'.', 1) || pg_catalog.split_part(s,'.', 2) END AS v
+ FROM pg_catalog.substring(version(), 'PostgreSQL ([0-9\.]+)') AS s;
$$ LANGUAGE 'sql' STABLE;
-- Availability: 2.5.0
@@ -2893,9 +2893,9 @@ $$ LANGUAGE 'sql' STABLE;
CREATE OR REPLACE FUNCTION postgis_extensions_upgrade() RETURNS text
AS $$
DECLARE
- rec record;
- sql text;
- var_schema text;
+ rec pg_catalog.record;
+ sql pg_catalog.text;
+ var_schema pg_catalog.text;
BEGIN
FOR rec IN
@@ -2943,18 +2943,18 @@ BEGIN
THEN
-- Force install in same schema as postgis
SELECT INTO var_schema n.nspname
- FROM pg_namespace n, pg_proc p
+ FROM pg_catalog.pg_namespace n, pg_catalog.pg_proc p
WHERE p.proname = 'postgis_full_version'
AND n.oid = p.pronamespace
LIMIT 1;
IF rec.name NOT IN('postgis_topology', 'postgis_tiger_geocoder')
THEN
- sql := format(
+ sql := pg_catalog.format(
'CREATE EXTENSION %1$I SCHEMA %2$I VERSION unpackaged;'
'ALTER EXTENSION %1$I UPDATE TO %3$I',
rec.name, var_schema, rec.default_version);
ELSE
- sql := format(
+ sql := pg_catalog.format(
'CREATE EXTENSION %1$I VERSION unpackaged;'
'ALTER EXTENSION %1$I UPDATE TO %2$I',
rec.name, rec.default_version);
@@ -2968,7 +2968,7 @@ BEGIN
ELSIF rec.default_version != rec.installed_version
THEN
sql = 'ALTER EXTENSION ' || rec.name || ' UPDATE TO ' ||
- quote_ident(rec.default_version) || ';';
+ pg_catalog.quote_ident(rec.default_version) || ';';
RAISE NOTICE 'Updating extension % from % to %',
rec.name, rec.installed_version, rec.default_version;
RAISE DEBUG '%', sql;
@@ -2980,11 +2980,11 @@ BEGIN
RAISE NOTICE 'Updating extension % %',
rec.name, rec.installed_version;
sql = 'ALTER EXTENSION ' || rec.name || ' UPDATE TO ' ||
- quote_ident(rec.default_version || 'next') || ';';
+ pg_catalog.quote_ident(rec.default_version || 'next') || ';';
RAISE DEBUG '%', sql;
EXECUTE sql;
sql = 'ALTER EXTENSION ' || rec.name || ' UPDATE TO ' ||
- quote_ident(rec.default_version) || ';';
+ pg_catalog.quote_ident(rec.default_version) || ';';
RAISE DEBUG '%', sql;
EXECUTE sql;
END IF;
@@ -3000,28 +3000,28 @@ $$ LANGUAGE plpgsql VOLATILE;
CREATE OR REPLACE FUNCTION postgis_full_version() RETURNS text
AS $$
DECLARE
- libver text;
- librev text;
- projver text;
- geosver text;
- sfcgalver text;
- gdalver text := NULL;
- libxmlver text;
- liblwgeomver text;
- dbproc text;
- relproc text;
- fullver text;
- rast_lib_ver text := NULL;
- rast_scr_ver text := NULL;
- topo_scr_ver text := NULL;
- json_lib_ver text;
- protobuf_lib_ver text;
- wagyu_lib_ver text;
- sfcgal_lib_ver text;
- sfcgal_scr_ver text;
- pgsql_scr_ver text;
- pgsql_ver text;
- core_is_extension bool;
+ libver pg_catalog.text;
+ librev pg_catalog.text;
+ projver pg_catalog.text;
+ geosver pg_catalog.text;
+ sfcgalver pg_catalog.text;
+ gdalver pg_catalog.text := NULL;
+ libxmlver pg_catalog.text;
+ liblwgeomver pg_catalog.text;
+ dbproc pg_catalog.text;
+ relproc pg_catalog.text;
+ fullver pg_catalog.text;
+ rast_lib_ver pg_catalog.text := NULL;
+ rast_scr_ver pg_catalog.text := NULL;
+ topo_scr_ver pg_catalog.text := NULL;
+ json_lib_ver pg_catalog.text;
+ protobuf_lib_ver pg_catalog.text;
+ wagyu_lib_ver pg_catalog.text;
+ sfcgal_lib_ver pg_catalog.text;
+ sfcgal_scr_ver pg_catalog.text;
+ pgsql_scr_ver pg_catalog.text;
+ pgsql_ver pg_catalog.text;
+ core_is_extension pg_catalog.bool;
BEGIN
SELECT @extschema at .postgis_lib_version() INTO libver;
SELECT @extschema at .postgis_proj_version() INTO projver;
@@ -3066,7 +3066,7 @@ BEGIN
END;
BEGIN
- SELECT postgis_raster_scripts_installed() INTO rast_scr_ver;
+ SELECT @extschema at .postgis_raster_scripts_installed() INTO rast_scr_ver;
EXCEPTION
WHEN undefined_function THEN
RAISE DEBUG 'Function postgis_raster_scripts_installed() not found. Is raster support enabled and rtpostgis.sql installed?';
@@ -5964,7 +5964,7 @@ LANGUAGE 'sql' STABLE STRICT PARALLEL SAFE _COST_MEDIUM;
-- TODO: Can't deprecate this because UpdateGeometrySRID still uses them
CREATE OR REPLACE FUNCTION postgis_constraint_dims(geomschema text, geomtable text, geomcolumn text) RETURNS integer AS
$$
-SELECT replace(split_part(s.consrc, ' = ', 2), ')', '')::integer
+SELECT replace(pg_catalog.split_part(s.consrc, ' = ', 2), ')', '')::integer
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
@@ -5985,7 +5985,7 @@ LANGUAGE 'sql' STABLE STRICT PARALLEL SAFE _COST_MEDIUM;
-- TODO: Can't deprecate this because UpdateGeometrySRID still uses them
CREATE OR REPLACE FUNCTION postgis_constraint_type(geomschema text, geomtable text, geomcolumn text) RETURNS varchar AS
$$
-SELECT replace(split_part(s.consrc, '''', 2), ')', '')::varchar
+SELECT replace(pg_catalog.split_part(s.consrc, '''', 2), ')', '')::varchar
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
@@ -6013,14 +6013,14 @@ CREATE OR REPLACE VIEW geometry_columns AS
a.attname AS f_geometry_column,
COALESCE(postgis_typmod_dims(a.atttypmod), sn.ndims, 2) AS coord_dimension,
COALESCE(NULLIF(postgis_typmod_srid(a.atttypmod), 0), sr.srid, 0) AS srid,
- replace(replace(COALESCE(NULLIF(upper(postgis_typmod_type(a.atttypmod)), 'GEOMETRY'::text), st.type, 'GEOMETRY'::text), 'ZM'::text, ''::text), 'Z'::text, ''::text)::character varying(30) AS type
- FROM pg_class c
- JOIN pg_attribute a ON a.attrelid = c.oid AND NOT a.attisdropped
- JOIN pg_namespace n ON c.relnamespace = n.oid
- JOIN pg_type t ON a.atttypid = t.oid
+ pg_catalog.replace(pg_catalog.replace(COALESCE(NULLIF(upper(postgis_typmod_type(a.atttypmod)), 'GEOMETRY'::text), st.type, 'GEOMETRY'::text), 'ZM'::text, ''::text), 'Z'::text, ''::text)::character varying(30) AS type
+ FROM pg_catalog.pg_class c
+ JOIN pg_catalog.pg_attribute a ON a.attrelid = c.oid AND NOT a.attisdropped
+ JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
+ JOIN pg_catalog.pg_type t ON a.atttypid = t.oid
LEFT JOIN ( SELECT s.connamespace,
s.conrelid,
- s.conkey, replace(split_part(s.consrc, ''''::text, 2), ')'::text, ''::text) As type
+ s.conkey, pg_catalog.replace(pg_catalog.split_part(s.consrc, ''''::text, 2), ')'::text, ''::text) As type
FROM (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
FROM pg_constraint) AS s
WHERE s.consrc ~~* '%geometrytype(% = %'::text
@@ -6028,7 +6028,7 @@ CREATE OR REPLACE VIEW geometry_columns AS
) 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
+ s.conkey, pg_catalog.replace(pg_catalog.split_part(s.consrc, ' = '::text, 2), ')'::text, ''::text)::integer As ndims
FROM (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
FROM pg_constraint) AS s
WHERE s.consrc ~~* '%ndims(% = %'::text
@@ -6036,7 +6036,7 @@ CREATE OR REPLACE VIEW geometry_columns AS
) 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
+ s.conkey, pg_catalog.replace(pg_catalog.replace(pg_catalog.split_part(s.consrc, ' = '::text, 2), ')'::text, ''::text), '('::text, ''::text)::integer As srid
FROM (SELECT connamespace, conrelid, conkey, pg_get_constraintdef(oid) As consrc
FROM pg_constraint) AS s
WHERE s.consrc ~~* '%srid(% = %'::text
diff --git a/postgis/postgis_after_upgrade.sql b/postgis/postgis_after_upgrade.sql
index b7a464f66..6807d6e88 100644
--- a/postgis/postgis_after_upgrade.sql
+++ b/postgis/postgis_after_upgrade.sql
@@ -231,7 +231,7 @@ DROP FUNCTION IF EXISTS st_distance_sphere(geometry, geometry);
DROP FUNCTION IF EXISTS pgis_geometry_union_transfn(internal, geometry);
-- #4394
-update pg_operator set oprcanhash = true, oprcanmerge = true where oprname = '=' and oprcode = 'geometry_eq'::regproc;
+update pg_catalog.pg_operator set oprcanhash = true, oprcanmerge = true where oprname = '=' and oprcode = 'geometry_eq'::pg_catalog.regproc;
DO language 'plpgsql'
@@ -240,7 +240,7 @@ BEGIN
IF _postgis_scripts_pgsql_version()::integer >= 96 THEN
-- mark ST_Union agg as parallel safe if it is not already
BEGIN
- UPDATE pg_proc SET proparallel = 's'
+ UPDATE pg_catalog.pg_proc SET proparallel = 's'
WHERE oid = 'st_union(geometry)'::regprocedure AND proparallel = 'u';
EXCEPTION WHEN OTHERS THEN
RAISE DEBUG 'Could not update st_union(geometry): %', SQLERRM;
diff --git a/postgis/postgis_before_upgrade.sql b/postgis/postgis_before_upgrade.sql
index 7a9323b0c..a9a61c785 100644
--- a/postgis/postgis_before_upgrade.sql
+++ b/postgis/postgis_before_upgrade.sql
@@ -39,7 +39,7 @@ CREATE OR REPLACE FUNCTION _postgis_drop_function_if_needed(
DECLARE
sql_drop text;
postgis_namespace OID;
- matching_function REGPROCEDURE;
+ matching_function pg_catalog.REGPROCEDURE;
BEGIN
-- Fetch install namespace for PostGIS
@@ -53,9 +53,9 @@ BEGIN
SELECT oid
FROM pg_catalog.pg_proc p
WHERE pronamespace = postgis_namespace
- AND LOWER(p.proname) = LOWER(function_name)
+ AND pg_catalog.LOWER(p.proname) = pg_catalog.LOWER(function_name)
AND pg_catalog.pg_function_is_visible(p.oid)
- AND LOWER(pg_catalog.pg_get_function_identity_arguments(p.oid)) ~ LOWER(function_arguments)
+ AND pg_catalog.LOWER(pg_catalog.pg_get_function_identity_arguments(p.oid)) ~ pg_catalog.LOWER(function_arguments)
INTO matching_function;
IF matching_function IS NOT NULL THEN
@@ -108,24 +108,24 @@ SELECT _postgis_drop_function_if_needed
DO language 'plpgsql' $$
BEGIN
-- fix geometry ops --
- IF EXISTS(SELECT oprname from pg_operator where oprname = '&&' AND oprrest::text = 'geometry_gist_sel_2d') THEN
+ IF EXISTS(SELECT oprname from pg_catalog.pg_operator where oprname = '&&' AND oprrest::text = 'geometry_gist_sel_2d') THEN
--it is bound to old name, drop new, rename old to new, install will fix body of code
DROP FUNCTION IF EXISTS gserialized_gist_sel_2d(internal, oid, internal, int4) ;
ALTER FUNCTION geometry_gist_sel_2d(internal, oid, internal, int4) RENAME TO gserialized_gist_sel_2d;
END IF;
- IF EXISTS(SELECT oprname from pg_operator where oprname = '&&' AND oprjoin::text = 'geometry_gist_joinsel_2d') THEN
+ IF EXISTS(SELECT oprname from pg_catalog.pg_operator where oprname = '&&' AND oprjoin::text = 'geometry_gist_joinsel_2d') THEN
--it is bound to old name, drop new, rename old to new, install will fix body of code
DROP FUNCTION IF EXISTS gserialized_gist_joinsel_2d(internal, oid, internal, smallint) ;
ALTER FUNCTION geometry_gist_joinsel_2d(internal, oid, internal, smallint) RENAME TO gserialized_gist_joinsel_2d;
END IF;
-- fix geography ops --
- IF EXISTS(SELECT oprname from pg_operator where oprname = '&&' AND oprrest::text = 'geography_gist_selectivity') THEN
+ IF EXISTS(SELECT oprname from pg_catalog.pg_operator where oprname = '&&' AND oprrest::text = 'geography_gist_selectivity') THEN
--it is bound to old name, drop new, rename old to new, install will fix body of code
DROP FUNCTION IF EXISTS gserialized_gist_sel_nd(internal, oid, internal, int4) ;
ALTER FUNCTION geography_gist_selectivity(internal, oid, internal, int4) RENAME TO gserialized_gist_sel_nd;
END IF;
- IF EXISTS(SELECT oprname from pg_operator where oprname = '&&' AND oprjoin::text = 'geography_gist_join_selectivity') THEN
+ IF EXISTS(SELECT oprname from pg_catalog.pg_operator where oprname = '&&' AND oprjoin::text = 'geography_gist_join_selectivity') THEN
--it is bound to old name, drop new, rename old to new, install will fix body of code
DROP FUNCTION IF EXISTS gserialized_gist_joinsel_nd(internal, oid, internal, smallint) ;
ALTER FUNCTION geography_gist_join_selectivity(internal, oid, internal, smallint) RENAME TO gserialized_gist_joinsel_nd;
diff --git a/raster/rt_pg/rtpostgis.sql.in b/raster/rt_pg/rtpostgis.sql.in
index 9adeb6152..1acfd2911 100644
--- a/raster/rt_pg/rtpostgis.sql.in
+++ b/raster/rt_pg/rtpostgis.sql.in
@@ -47,7 +47,7 @@ DECLARE
rec RECORD;
BEGIN
FOR rec IN
- SELECT n.nspname, p.proname FROM pg_proc p, pg_namespace n
+ SELECT n.nspname, p.proname FROM pg_catalog.pg_proc p, pg_catalog.pg_namespace n
WHERE p.proname = 'postgis_raster_lib_version'
AND p.pronamespace = n.oid
LOOP
@@ -63,7 +63,7 @@ DECLARE
nsp TEXT;
BEGIN
SELECT n.nspname
- INTO nsp FROM pg_proc p, pg_namespace n
+ INTO nsp FROM pg_catalog.pg_proc p, pg_catalog.pg_namespace n
WHERE p.proname = 'postgis_lib_version'
AND p.pronamespace = n.oid;
@@ -8298,8 +8298,8 @@ CREATE OR REPLACE VIEW raster_overviews AS
current_database() AS r_table_catalog,
split_part(split_part(s.consrc, '''::name', 1), '''', 2)::name AS r_table_schema,
split_part(split_part(s.consrc, '''::name', 2), '''', 2)::name AS r_table_name,
- split_part(split_part(s.consrc, '''::name', 3), '''', 2)::name AS r_raster_column,
- trim(both from split_part(s.consrc, ',', 2))::integer AS overview_factor
+ pg_catalog.split_part(pg_catalog.split_part(s.consrc, '''::name', 3), '''', 2)::name AS r_raster_column,
+ trim(both from pg_catalog.split_part(s.consrc, ',', 2))::integer AS overview_factor
FROM
pg_class c,
pg_attribute a,
diff --git a/raster/rt_pg/rtpostgis_upgrade_cleanup.sql.in b/raster/rt_pg/rtpostgis_upgrade_cleanup.sql.in
index 0d6f3ba00..08e787699 100644
--- a/raster/rt_pg/rtpostgis_upgrade_cleanup.sql.in
+++ b/raster/rt_pg/rtpostgis_upgrade_cleanup.sql.in
@@ -95,10 +95,10 @@ BEGIN
SELECT
proname
FROM pg_proc f
- JOIN pg_type r
+ JOIN pg_catalog.pg_type r
ON r.typname = 'raster'
AND (f.proargtypes::oid[])[0] = r.oid
- JOIN pg_type g
+ JOIN pg_catalog.pg_type g
ON g.typname = 'geometry'
AND (f.proargtypes::oid[])[1] = g.oid
WHERE proname = 'raster_contained_by_geometry'
@@ -118,10 +118,10 @@ BEGIN
SELECT
proname
FROM pg_proc f
- JOIN pg_type r
+ JOIN pg_catalog.pg_type r
ON r.typname = 'raster'
AND (f.proargtypes::oid[])[1] = r.oid
- JOIN pg_type g
+ JOIN pg_catalog.pg_type g
ON g.typname = 'geometry'
AND (f.proargtypes::oid[])[0] = g.oid
WHERE proname = 'geometry_contained_by_raster'
@@ -362,8 +362,8 @@ CREATE OR REPLACE FUNCTION _rename_raster_tables()
cnt int;
BEGIN
SELECT count(*) INTO cnt
- FROM pg_class c
- JOIN pg_namespace n
+ FROM pg_catalog.pg_class c
+ JOIN pg_catalog.pg_namespace n
ON c.relnamespace = n.oid
WHERE c.relname = 'raster_columns'
AND c.relkind = 'r'::char
@@ -374,8 +374,8 @@ CREATE OR REPLACE FUNCTION _rename_raster_tables()
END IF;
SELECT count(*) INTO cnt
- FROM pg_class c
- JOIN pg_namespace n
+ FROM pg_catalog.pg_class c
+ JOIN pg_catalog.pg_namespace n
ON c.relnamespace = n.oid
WHERE c.relname = 'raster_overviews'
AND c.relkind = 'r'::char
@@ -587,7 +587,7 @@ BEGIN
count(*)
INTO cnt
FROM pg_proc f
- JOIN pg_type t
+ JOIN pg_catalog.pg_type t
ON f.prorettype = t.oid
WHERE lower(f.proname) = '_raster_constraint_nodata_values'
AND f.pronargs = 1
diff --git a/topology/sql/manage/ManageHelper.sql.in b/topology/sql/manage/ManageHelper.sql.in
index 31b70756b..6fdcae2ae 100644
--- a/topology/sql/manage/ManageHelper.sql.in
+++ b/topology/sql/manage/ManageHelper.sql.in
@@ -25,7 +25,7 @@ DECLARE
var_result text;
var_cur_search_path text;
BEGIN
- SELECT reset_val INTO var_cur_search_path FROM pg_settings WHERE name = 'search_path';
+ SELECT reset_val INTO var_cur_search_path FROM pg_catalog.pg_settings WHERE name = 'search_path';
IF var_cur_search_path LIKE '%' || quote_ident(a_schema_name) || '%' THEN
var_result := a_schema_name || ' already in database search_path';
ELSE
diff --git a/utils/create_undef.pl b/utils/create_undef.pl
index d98ccce6d..3c3bc9bb4 100755
--- a/utils/create_undef.pl
+++ b/utils/create_undef.pl
@@ -261,10 +261,10 @@ DECLARE
BEGIN
FOR rec IN
SELECT n.nspname, c.relname, a.attname, t.typname
- FROM pg_attribute a
- JOIN pg_class c ON a.attrelid = c.oid
- JOIN pg_namespace n ON c.relnamespace = n.oid
- JOIN pg_type t ON a.atttypid = t.oid
+ FROM pg_catalog.pg_attribute a
+ JOIN pg_catalog.pg_class c ON a.attrelid = c.oid
+ JOIN pg_catalog.pg_namespace n ON c.relnamespace = n.oid
+ JOIN pg_catalog.pg_type t ON a.atttypid = t.oid
WHERE t.typname = '$type'
AND NOT (
-- we exclude complexes defined as types
@@ -362,15 +362,15 @@ DECLARE
var_result text;
var_search_path text;
BEGIN
- SELECT reset_val INTO var_search_path FROM pg_settings WHERE name = 'search_path';
- IF var_search_path NOT LIKE '%' || quote_ident(a_schema_name) || '%' THEN
+ SELECT reset_val INTO var_search_path FROM pg_catalog.pg_settings WHERE name = 'search_path';
+ IF var_search_path NOT LIKE '%' || pg_catalog.quote_ident(a_schema_name) || '%' THEN
var_result := a_schema_name || ' not in database search_path';
ELSE
- var_search_path := btrim( regexp_replace(
- replace(var_search_path, a_schema_name, ''), ', *,', ','),
+ var_search_path := pg_catalog.btrim( pg_catalog.regexp_replace(
+ pg_catalog.replace(var_search_path, a_schema_name, ''), ', *,', ','),
', ');
RAISE NOTICE 'New search_path: %', var_search_path;
- EXECUTE 'ALTER DATABASE ' || quote_ident(current_database()) || ' SET search_path = ' || var_search_path;
+ EXECUTE 'ALTER DATABASE ' || pg_catalog.quote_ident(pg_catalog.current_database()) || ' SET search_path = ' || var_search_path;
var_result := a_schema_name || ' has been stripped off database search_path ';
END IF;
diff --git a/utils/postgis_proc_upgrade.pl b/utils/postgis_proc_upgrade.pl
index 7f135f40c..d0047fdb9 100755
--- a/utils/postgis_proc_upgrade.pl
+++ b/utils/postgis_proc_upgrade.pl
@@ -249,11 +249,11 @@ BEGIN
WHERE
d.refclassid = 'pg_catalog.pg_extension'::pg_catalog.regclass AND
d.refobjid = e.oid AND
- d.classid = 'pg_proc'::regclass AND
- d.objid = replaced_proc::oid
+ d.classid = 'pg_catalog.pg_proc'::pg_catalog.regclass AND
+ d.objid = replaced_proc::pg_catalog.oid
LOOP
RAISE DEBUG 'Unpackaging ${renamed} from extension %', rec.extname;
- sql := format('ALTER EXTENSION %I DROP FUNCTION ${renamed}(${args})', rec.extname);
+ sql := pg_catalog.format('ALTER EXTENSION %I DROP FUNCTION ${renamed}(${args})', rec.extname);
EXECUTE sql;
END LOOP;
@@ -611,7 +611,7 @@ DECLARE
BEGIN
-- Fetch a list of deprecated functions
- SELECT array_agg(oid::regprocedure)
+ SELECT array_agg(oid::pg_catalog.regprocedure)
FROM pg_catalog.pg_proc
WHERE proname = ANY ('${deprecated_names}'::name[])
INTO deprecated_functions;
@@ -622,22 +622,22 @@ BEGIN
-- FOR rec IN
-- SELECT n.nspname AS schemaname,
-- c.relname AS viewname,
--- pg_get_userbyid(c.relowner) AS viewowner,
--- pg_get_viewdef(c.oid) AS definition,
+-- pg_catalog.pg_get_userbyid(c.relowner) AS viewowner,
+-- pg_catalog.pg_get_viewdef(c.oid) AS definition,
-- CASE
-- WHEN 'check_option=cascaded' = ANY (c.reloptions) THEN 'WITH CASCADED CHECK OPTION'
-- WHEN 'check_option=local' = ANY (c.reloptions) THEN 'WITH LOCAL CHECK OPTION'
-- ELSE ''
-- END::text AS check_option
--- FROM pg_class c
--- LEFT JOIN pg_namespace n ON n.oid = c.relnamespace
+-- FROM pg_catalog.pg_class c
+-- LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
-- WHERE c.relkind = 'v'
--- AND pg_get_viewdef(c.oid) ~ 'deprecated_by_postgis'
+-- AND pg_catalog.pg_get_viewdef(c.oid) ~ 'deprecated_by_postgis'
-- LOOP
--- sql := format('CREATE OR REPLACE VIEW %I.%I AS %s %s',
+-- sql := pg_catalog.format('CREATE OR REPLACE VIEW %I.%I AS %s %s',
-- rec.schemaname,
-- rec.viewname,
--- regexp_replace(rec.definition, '_deprecated_by_postgis_[^(]*', '', 'g'),
+-- pg_catalog.regexp_replace(rec.definition, '_deprecated_by_postgis_[^(]*', '', 'g'),
-- rec.check_option
-- );
-- RAISE NOTICE 'Updating view % to not use deprecated signatures', rec.viewname;
@@ -647,17 +647,17 @@ BEGIN
-- WHEN OTHERS THEN
-- GET STACKED DIAGNOSTICS detail := PG_EXCEPTION_DETAIL;
-- RAISE WARNING 'Could not rewrite view % using deprecated functions', rec.viewname
--- USING DETAIL = format('%s: %s', SQLERRM, detail);
+-- USING DETAIL = pg_catalog.format('%s: %s', SQLERRM, detail);
-- END;
-- END LOOP;
-- Try to drop all deprecated functions, raising a warning
-- for each one which cannot be drop
- FOR rec IN SELECT unnest(deprecated_functions) as proc
+ FOR rec IN SELECT pg_catalog.unnest(deprecated_functions) as proc
LOOP --{
- sql := format('DROP FUNCTION %s', rec.proc);
+ sql := pg_catalog.format('DROP FUNCTION %s', rec.proc);
--RAISE DEBUG 'SQL: %', sql;
BEGIN
EXECUTE sql;
@@ -666,10 +666,10 @@ BEGIN
hint = 'Resolve the issue';
GET STACKED DIAGNOSTICS detail := PG_EXCEPTION_DETAIL;
IF detail LIKE '%view % depends%' THEN
- hint = format(
+ hint = pg_catalog.format(
'Replace the view changing all occurrences of %s in its definition with %s',
rec.proc,
- regexp_replace(rec.proc::text, '_deprecated_by_postgis[^(]*', '')
+ pg_catalog.regexp_replace(rec.proc::text, '_deprecated_by_postgis[^(]*', '')
);
END IF;
hint = hint || ' and upgrade again';
@@ -717,8 +717,8 @@ BEGIN
SELECT into old_scripts MODULE_scripts_installed();
END;
SELECT into new_scripts 'NEWVERSION';
- SELECT into old_maj substring(old_scripts from 1 for 1);
- SELECT into new_maj substring(new_scripts from 1 for 1);
+ SELECT into old_maj pg_catalog.substring(old_scripts, 1, 1);
+ SELECT into new_maj pg_catalog.substring(new_scripts, 1, 1);
-- 2.x to 3.x was upgrade-compatible, see
-- https://trac.osgeo.org/postgis/ticket/4170#comment:1
@@ -739,11 +739,11 @@ CREATE TEMPORARY TABLE _postgis_upgrade_info AS WITH versions AS (
) SELECT
upgraded as scripts_upgraded,
installed as scripts_installed,
- substring(upgraded from '([0-9]*)\.')::int * 100 +
- substring(upgraded from '[0-9]*\.([0-9]*)\.')::int
+ pg_catalog.substring( upgraded , '([0-9]*)\.' )::int * 100 +
+ pg_catalog.substring( upgraded , '[0-9]*\.([0-9]*)\.' )::int
as version_to_num,
- substring(installed from '([0-9]*)\.')::int * 100 +
- substring(installed from '[0-9]*\.([0-9]*)\.')::int
+ pg_catalog.substring( installed , '([0-9]*)\.' )::int * 100 +
+ pg_catalog.substring( installed , '[0-9]*\.([0-9]*)\.' )::int
as version_from_num,
installed ~ 'dev|alpha|beta'
as version_from_isdev
-----------------------------------------------------------------------
Summary of changes:
NEWS | 4 +-
extensions/extensions_unpackage.sh | 2 +-
extensions/postgis/unpackage_raster_if_needed.sql | 4 +-
extensions/postgis_extension_helper.sql | 56 +++++------
extras/tiger_geocoder/utility/set_search_path.sql | 4 +-
loader/postgis.pl | 2 +-
postgis/postgis.sql.in | 114 +++++++++++-----------
postgis/postgis_after_upgrade.sql | 4 +-
postgis/postgis_before_upgrade.sql | 14 +--
raster/rt_pg/rtpostgis.sql.in | 8 +-
raster/rt_pg/rtpostgis_upgrade_cleanup.sql.in | 18 ++--
topology/sql/manage/ManageHelper.sql.in | 2 +-
utils/create_undef.pl | 18 ++--
utils/postgis_proc_upgrade.pl | 44 ++++-----
14 files changed, 148 insertions(+), 146 deletions(-)
hooks/post-receive
--
PostGIS
More information about the postgis-tickets
mailing list