Refuse to import if PostGIS's core procedures aren't updated.

[mboeringa]

I noticed osm2pgsql currently happily starts an import session with outdated PostGIS core procedures. While this may not be directly critical, and somewhat out of scope of the responsibility of osm2pgsql, I still think it is good practice to refuse import in such a configuration.

I run some own software written in Python, and have been bitten by this issue in my own code, and have since adjusted my own tool to issue a warning if outdated PostGIS core procedures are detected upon start up, and block processing if so.

I find this really helpful to not forget to update the core procedures after a PostGIS update. Especially novice users are usually unaware of the need to do so, and may run into issues.

This situation can be easily detected by parsing the string returned by SELECT PostGIS_Full_Version();, which often is already a requirement in software development related to PostGIS to detect other (version) incompatibilities.

[ImreSamu]

This situation can be easily detected by parsing the string returned by SELECT PostGIS_Full_Version();

I do not recommend parsing the result of 'SELECT PostGIS_Full_Version();' as the text can change at any time, or there could be a localized (non-English) version installed, which would then not work.

If the warning is still important, I would rather suggest a comparison based on the PostgreSQL meta tables, although this may yield a different result than the PostGIS_Full_Version();

SELECT
  ae.name,
  installed_version,
  default_version,
  case when installed_version <> default_version 
    THEN 'need_to_upgrade'
    ELSE 'up_to_date'
  END AS upgrade_status
FROM pg_extension e
JOIN pg_available_extensions ae on extname = ae.name
WHERE extname='postgis'
;

  name   | installed_version | default_version | upgrade_status  
---------+-------------------+-----------------+-----------------
 postgis | 3.1.7             | 3.2.5           | need_to_upgrade
(1 row)

vs.

SELECT PostGIS_Full_Version();

                                           postgis_full_version
---------------------------------------------------------------------------------------------------
 POSTGIS="3.1.9 3.1.9" [EXTENSION] PGSQL="120" (procs need upgrade for use with PostgreSQL "140") 
 GEOS="3.5.0-CAPI-1.9.0 4392" PROJ="Rel. 6.3.1, February 10th, 2020" LIBXML="2.9.10" 
 LIBJSON="0.17" LIBPROTOBUF="1.4.1" (core procs from "3.1.7 3.1.7" need upgrade)
 

[rustprooflabs]

refuse import in such a configuration.

Blocking an import because an upgrade step was missed doesn't seem appropriate, unless the current version relies on specific functionality. I've seen that step missed quite a few times, but it's rarely been an actual problem that has manifested into an error.

[joto]

I don't think checks like this are something we would want to have in osm2pgsql itself. But I have a (currently work-in-progress) Python script which bundles some convenient "database helper" functions. One of the things it offers is a "database check" function that will check a few things in your database related to osm2pgsql, like whether PostGIS is enabled and whether you use UTF-8 encoding. A check like this might fit in there.

[mboeringa]

@joto That would be fine, but additionally, you might consider to simply print out the result of SELECT PostGIS_Full_Version(); as one of the first steps in the processing.

osm2pgsql already prints out a few lines regarding versions of PostgreSQL and PostGIS, which is useful, but not the full result of this command. Maybe replacing these loglines by the PostGIS_Full_Version command's result, would at least give users a chance to catch such an issue early on, and potentially cancel a very large strenuous import, without requiring to run additional scripts to access convenience functions.

[pnorman]

We currently check PostGIS versions by querying the PostgreSQL extension table. I wonder if it would be better to query PostGIS_Lib_Version() instead. As a bonus, this would cause an early failure if PostGIS functions do not execute successfully.