Have postgis_full_version() also denote which version of PostgreSQL the scripts were built against

[robe]

Right now our postgis_full_version() will only warn if the scripts are older PostGIS micro from PostGIS binary.

This makes it possible for example for someone to do a

soft upgrade from 2.2 to 2.3 on PostgreSQL 9.3

and then a pg_upgrade to PostgreSQL 9.5 and not know that they are missing the new BRIN operators or KNN true dist operators

SELECT postgis_full_version();

should warn and tell them to run something like

ALTER EXTENSION postgis UPDATE postgis-2.3.4next;
ALTER EXTENSION postgis UPDATE postgis-2.3.4;

to fix the issue.

[strk]

In 15602:

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

Fixes #3822 (untested)

[robe]

cough cough this needs a NEWS entry and a document update

[strk]

In 15604:

Make postgis_pgsql version functions "internal"

… they are really only meant to be used by postgis_full_version()

Closes #3822 again (as internal functions don't need documentation)

[robe]

strk I think this compare needs some work.

I just did an

ALTER EXTENSION postgis UPDATE;

using winnie's built postgis 2.4.0beta4 tar ball for PostgreSQL 10.

when I ran postgis_full_version();

I got this:

POSTGIS="2.4.0beta1 r15614" PGSQL="100" (procs need upgrade for use with "PostgreSQL 10beta3 on x86_64-w64-mingw32, compiled by gcc.exe (x86_64-win32-seh-rev1, Built by MinGW-W64 project) 4.8.3, 64-bit") GEOS="3.7.0dev-CAPI-1.11.0 1618346" PROJ="Rel. 4.9.1, 04 March 2015" GDAL="GDAL 2.2.1, released 2017/06/23" LIBXML="2.7.8" LIBJSON="0.12" LIBPROTOBUF="1.2.1" RASTER

and I notice:

SELECT _postgis_pgsql_version();

returns

PostgreSQL 10beta3 on x86_64-w64-mingw32, compiled by gcc.exe (x86_64-win32-seh-rev1, Built by MinGW-W64 project) 4.8.3, 64-bit

In my opinion it should be returning 100 and I shouldn't be getting a warning about a mismatch.

[robe]

as per strk's comment on IRC I'll replicate regexp and I'm taking this over since I'm in best position to test.

14:39:45	robe2:	strk you want me to attempt to fix this one - agree with my proposed change? https://trac.osgeo.org/postgis/ticket/3822
14:39:46	sigq:	Title: #3822 (Have postgis_full_version() also denote which version of PostgreSQL the scripts were built against) – PostGIS (at trac.osgeo.org)
14:40:50	robe2:	Let's not scare people for no reason. Also I think it might break my pgsql version check as I thought that would just return the minor for < 10 and major for >= 10
14:41:30	strk:	robe2: sure, you're in the best position to test it there (I don't have 10 installed)
14:41:54	strk:	we just need to replicate the regexp used by configure.in
14:42:07	robe2:	yah that was what I was thinking

[robe]

forogto to write closed on r15628 commit notes

[strk]

In 16267:

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

[strk]

Oops I just realized this was only added in 2.4.0 so probably should have not been backported. Should I revert r16267 ?