[QGIS-Developer] Reduced compatibility with old PosgreSQL/PostGIS databases in v. 4.0.0

Wed Apr 1 09:50:21 PDT 2026

I just added

CREATE OR REPLACE FUNCTION pg_is_in_recovery()
RETURNS boolean
LANGUAGE sql
AS $$
  SELECT false;
$$;

to the 8.4 database (it will never be in recovery anyway ;.)).
I think there are other quirks, but I can at least display the map.
Thanks for the hint provided.

On Wed, Apr 1, 2026 at 5:54 PM Carlo A. Bertelli (Charta s.r.l.) <
carlo.bertelli at gmail.com> wrote:

> Thanks for the explanation. Maybe I could create a function on the
> database just to send something sufficiently appeasing to QGIS.
> Buona Pasqua!
> c
>
> On Wed, Apr 1, 2026 at 1:52 PM Régis Haubourg <regis.haubourg at gmail.com>
> wrote:
>
>> Hi,
>>
>> This is pretty simple,
>>
>> QGIS queries a bunch of things against the catalog system table to
>> understand the capabilities of the server, tables, access privileges and so
>> on. This query fails because pg_is_in_recovery() function does not exist in
>> postgres 8.4. on QGIS side, it hasn't changed since 2 years:
>> https://github.com/qgis/QGIS/commit/d3474e4e2f258676c75bba874485b816b262acc2
>>
>>
>> This has nothing to do with the URI.
>>
>> GDAL does not need as many system queries because it is not a GUI tool,
>> so there is less chances it breaks.
>>
>> This has changed between 3.34 and 3.40, to fix a crash
>> https://github.com/qgis/QGIS/pull/57810
>>
>> So, it is probable we don't add a If statement to handle PG 8.4. This
>> function landed in 9.0 in postgres.
>>
>>
>> Cheers
>> Régis Haubourg
>>
>> On 31/03/2026 22:00, Carlo A. Bertelli (Charta s.r.l.) wrote:
>>
>> Thanks to everyone for replying so quickly to this problem.
>> I totally agree with you about the necessity to upgrade PostgreSQL and I
>> think it's not a good idea to risk adding bugs to a foundation library as
>> GDAL.
>> Anyway, I tried version 3.12.3 and it supports reading from a table and
>> writing to it without problems. But something changed at least on version
>> 4.0.0 for MacOS.
>> While the DBManager recognized the table structure, the datasource
>> browser doesn't and complains:
>>
>>> Error retrieving fields information for uri: bname='mydb' host=d
>>> b.myclientshost.com port=5432 user='mypooruser' sslmode=disable
>>> checkPrimaryKeyUnicity='O' table="oneschema". "mypolytable"
>>
>> when browsing fields.
>> Maybe it has something to do with *uri: *bname which obviously means *uri:
>> dbname* but the typo would break something if it is used in the code.
>> This is the PostGIS error I get when retrieving this layer:
>>
>> 2026-03-31T21:39:29     WARNING    Erroneous query: SELECT
>>> has_table_privilege('"oneschema".
>>> "mypolytable"','SELECT'),pg_is_in_recovery(),current_schema()
>>> ,has_any_column_privilege('"oneschema".
>>> "mypolytable"','INSERT'),has_table_privilege('"oneschema".
>>> "mypolytable"','DELETE'),has_any_column_privilege('"oneschema".
>>> "mypolytable"','UPDATE'),'f' returned 7 [ERROR: function
>>> pg_is_in_recovery() does not exist
>>>              LINE 1: ...vilege('"oneschema".
>>> "mypolytable"','SELECT'),pg_is_in_r...
>>>               ^
>>>              HINT: No function matches the given name and argument
>>> types. You might need to add explicit type casts.
>>>              ]
>>> 2026-03-31T21:39:29     WARNING    Unable to determine table access
>>> privileges for the "oneschema". "mypolytable" relation.
>>>              The error message from the database was:
>>>              ERROR: function pg_is_in_recovery() does not exist
>>>              LINE 1: ...vilege('"oneschema".
>>> "mypolytable"','SELECT'),pg_is_in_r...
>>>               ^
>>>              HINT: No function matches the given name and argument
>>> types. You might need to add explicit type casts.
>>>              .
>>>              SQL: SELECT has_table_privilege('"oneschema".
>>> "mypolytable"','SELECT'),pg_is_in_recovery(),current_schema()
>>> ,has_any_column_privilege('"oneschema".
>>> "mypolytable"','INSERT'),has_table_privilege('"oneschema".
>>> "mypolytable"','DELETE'),has_any_column_privilege('"oneschema".
>>> "mypolytable"','UPDATE'),'f'
>>> 2026-03-31T21:39:29     WARNING    Erroneous query: SELECT
>>> has_table_privilege('"oneschema".
>>> "mypolytable"','SELECT'),pg_is_in_recovery(),current_schema()
>>> ,has_any_column_privilege('"oneschema".
>>> "mypolytable"','INSERT'),has_table_privilege('"oneschema".
>>> "mypolytable"','DELETE'),has_any_column_privilege('"oneschema".
>>> "mypolytable"','UPDATE'),'f' returned 7 [ERROR: function
>>> pg_is_in_recovery() does not exist
>>>              LINE 1: ...vilege('"oneschema".
>>> "mypolytable"','SELECT'),pg_is_in_r...
>>>               ^
>>>              HINT: No function matches the given name and argument
>>> types. You might need to add explicit type casts.
>>>              ]
>>> 2026-03-31T21:39:29     WARNING    Unable to determine table access
>>> privileges for the "oneschema". "mypolytable" relation.
>>>              The error message from the database was:
>>>              ERROR: function pg_is_in_recovery() does not exist
>>>              LINE 1: ...vilege('"oneschema".
>>> "mypolytable"','SELECT'),pg_is_in_r...
>>>               ^
>>>              HINT: No function matches the given name and argument
>>> types. You might need to add explicit type casts.
>>>              .
>>>              SQL: SELECT has_table_privilege('"oneschema".
>>> "mypolytable"','SELECT'),pg_is_in_recovery(),current_schema()
>>> ,has_any_column_privilege('"oneschema".
>>> "mypolytable"','INSERT'),has_table_privilege('"oneschema".
>>> "mypolytable"','DELETE'),has_any_column_privilege('"oneschema".
>>> "mypolytable"','UPDATE'),'f'
>>> 2026-03-31T21:46:13     WARNING    Erroneous query: SELECT
>>> has_table_privilege('"oneschema".
>>> "mypolytable"','SELECT'),pg_is_in_recovery(),current_schema()
>>> ,has_any_column_privilege('"oneschema".
>>> "mypolytable"','INSERT'),has_table_privilege('"oneschema".
>>> "mypolytable"','DELETE'),has_any_column_privilege('"oneschema".
>>> "mypolytable"','UPDATE'),has_column_privilege('"oneschema".
>>> "mypolytable"','GEOMETRY','UPDATE') returned 7 [ERROR: function
>>> pg_is_in_recovery() does not exist
>>>              LINE 1: ...vilege('"oneschema".
>>> "mypolytable"','SELECT'),pg_is_in_r...
>>>               ^
>>>              HINT: No function matches the given name and argument
>>> types. You might need to add explicit type casts.
>>>              ]
>>> 2026-03-31T21:46:13     WARNING    Unable to determine table access
>>> privileges for the "oneschema". "mypolytable" relation.
>>>              The error message from the database was:
>>>              ERROR: function pg_is_in_recovery() does not exist
>>>              LINE 1: ...vilege('"oneschema".
>>> "mypolytable"','SELECT'),pg_is_in_r...
>>>               ^
>>>              HINT: No function matches the given name and argument
>>> types. You might need to add explicit type casts.
>>>              .
>>>              SQL: SELECT has_table_privilege('"oneschema".
>>> "mypolytable"','SELECT'),pg_is_in_recovery(),current_schema()
>>> ,has_any_column_privilege('"oneschema".
>>> "mypolytable"','INSERT'),has_table_privilege('"oneschema".
>>> "mypolytable"','DELETE'),has_any_column_privilege('"oneschema".
>>> "mypolytable"','UPDATE'),has_column_privilege('"oneschema".
>>> "mypolytable"','GEOMETRY','UPDATE')
>>
>>
>> while getting the same data by gdalinfo does not need any special
>> privilege. I should be able to understand what this repeated error means on
>> the SQL side, but it seems a complete nonsense to me. I'm sure someone
>> would detect the reason for it.
>> Is there a way to mitigate it? I was trying to use a virtual ogr file to
>> handle misbehaving columns; I could convert some specific table to
>> Spatialite, but I think solving my problem could be useful to someone else.
>> Thanks in advance for any hint provided.
>> c
>>
>> On Tue, Mar 31, 2026 at 8:26 PM Régis Haubourg via QGIS-Developer <
>> qgis-developer at lists.osgeo.org> wrote:
>>
>>> 9.1 has reached end of life 10 years ago!
>>> I'm with Even here. A Postgres server out of maintenance period must be
>>> considered insecure and outdated and must totally be upgraded anyway.
>>>
>>> Cheer, Régis
>>>
>>> On 31/03/2026 18:14, Stefanos Natsis via QGIS-Developer wrote:
>>> > One breaking change I'm aware of in QGIS 4 is that we've used the
>>> > `CREATE TABLE IF NOT EXISTS` idiom for the `qgis_projects` table (for
>>> > storing projects in the database) which was introduced in PostgreSQL
>>> 9.1
>>> _______________________________________________
>>> QGIS-Developer mailing list
>>> QGIS-Developer at lists.osgeo.org
>>> List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>>> Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
>>>
>>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20260401/46d6ea7c/attachment-0001.htm>