[QGIS-Developer] Reduced compatibility with old PosgreSQL/PostGIS databases in v. 4.0.0
Régis Haubourg
regis.haubourg at gmail.com
Wed Apr 1 04:52:42 PDT 2026
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=db.myclientshost.com <http://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/80e74a27/attachment-0001.htm>
More information about the QGIS-Developer
mailing list