[postgis-tickets] r16267 - Add check for PostgreSQL version compatibility in postgis_full_version

[Sandro Santilli]

Author: strk
Date: 2018-01-12 01:38:17 -0800 (Fri, 12 Jan 2018)
New Revision: 16267

Modified:
   branches/2.3/NEWS
   branches/2.3/postgis/postgis.sql.in
   branches/2.3/postgis/sqldefines.h.in
Log:
Add check for PostgreSQL version compatibility in postgis_full_version

Adds _postgis_scripts_pgsql_version() and _postgis_pgsql_version()
internal functions, each returning an encoded version number,
and has postgis_full_version() compare running vs. build-time used

Always show PGSQL version scripts were built against

References #3822 for 2.3 branch

Modified: branches/2.3/NEWS
===================================================================
--- branches/2.3/NEWS	2018-01-12 08:59:47 UTC (rev 16266)
+++ branches/2.3/NEWS	2018-01-12 09:38:17 UTC (rev 16267)
@@ -4,6 +4,8 @@
   * Bug Fixes and Enhancements
 
   - #3713, Support encodings that happen to output a '\' character
+  - #3822, Have postgis_full_version() also show and check version of
+           PostgreSQL the scripts were built against (Sandro Santilli)
   - #3965, ST_ClusterKMeans used to lose some clusters on initialization
            (Darafei Praliaskouski)
   - #3956, Brin opclass object does not upgrade properly (Sandro Santilli)

Modified: branches/2.3/postgis/postgis.sql.in
===================================================================
--- branches/2.3/postgis/postgis.sql.in	2018-01-12 08:59:47 UTC (rev 16266)
+++ branches/2.3/postgis/postgis.sql.in	2018-01-12 09:38:17 UTC (rev 16267)
@@ -2842,6 +2842,16 @@
 	AS 'MODULE_PATHNAME'
 	LANGUAGE 'c' IMMUTABLE;
 
+CREATE OR REPLACE FUNCTION _postgis_scripts_pgsql_version() RETURNS text
+	AS _POSTGIS_SQL_SELECT_POSTGIS_PGSQL_VERSION
+	LANGUAGE 'sql' IMMUTABLE;
+
+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;
+$$ LANGUAGE 'sql' STABLE;
+
 CREATE OR REPLACE FUNCTION postgis_full_version() RETURNS text
 AS $$
 DECLARE
@@ -2863,11 +2873,15 @@
 	json_lib_ver text;
 	sfcgal_lib_ver text;
 	sfcgal_scr_ver text;
+	pgsql_scr_ver text;
+	pgsql_ver text;
 BEGIN
 	SELECT postgis_lib_version() INTO libver;
 	SELECT postgis_proj_version() INTO projver;
 	SELECT postgis_geos_version() INTO geosver;
 	SELECT postgis_libjson_version() INTO json_lib_ver;
+	SELECT _postgis_scripts_pgsql_version() INTO pgsql_scr_ver;
+	SELECT _postgis_pgsql_version() INTO pgsql_ver;
 	BEGIN
 		SELECT postgis_gdal_version() INTO gdalver;
 	EXCEPTION
@@ -2932,6 +2946,10 @@
 		fullver = fullver || ' (liblwgeom version mismatch: "' || liblwgeomver || '")';
 	END IF;
 
+	fullver = fullver || ' PGSQL="' || pgsql_scr_ver || '"';
+	IF pgsql_scr_ver != pgsql_ver THEN
+		fullver = fullver || ' (procs need upgrade for use with "' || pgsql_ver || '")';
+	END IF;
 
 	IF  geosver IS NOT NULL THEN
 		fullver = fullver || ' GEOS="' || geosver || '"';
@@ -2964,6 +2982,10 @@
 		fullver = fullver || ' (core procs from "' || dbproc || '" need upgrade)';
 	END IF;
 
+	IF pgsql_scr_ver != pgsql_ver THEN
+		fullver = fullver || ' (procs built against PostgreSQL "' || pgsql_scr_ver || '" need upgrade)';
+	END IF;
+
 	IF topo_scr_ver IS NOT NULL THEN
 		fullver = fullver || ' TOPOLOGY';
 		IF topo_scr_ver != relproc THEN

Modified: branches/2.3/postgis/sqldefines.h.in
===================================================================
--- branches/2.3/postgis/sqldefines.h.in	2018-01-12 08:59:47 UTC (rev 16266)
+++ branches/2.3/postgis/sqldefines.h.in	2018-01-12 09:38:17 UTC (rev 16267)
@@ -26,6 +26,7 @@
  */
 #define _POSTGIS_SQL_SELECT_POSTGIS_VERSION 'SELECT ''@POSTGIS_VERSION@''::text AS version'
 #define _POSTGIS_SQL_SELECT_POSTGIS_BUILD_DATE 'SELECT ''@POSTGIS_BUILD_DATE@''::text AS version'
+#define _POSTGIS_SQL_SELECT_POSTGIS_PGSQL_VERSION 'SELECT ''@POSTGIS_PGSQL_VERSION@''::text AS version'
 
 #if POSTGIS_SVN_REVISION
 #define _POSTGIS_SQL_SELECT_POSTGIS_SCRIPTS_VERSION $$ SELECT '@POSTGIS_SCRIPTS_VERSION@'::text || ' r' || POSTGIS_SVN_REVISION::text AS version $$