[QGIS-Developer] Request for comments: saving features with GENERATED columns on PostgreSQL 12+

[José de Paula Rodrigues]

Hello all,

I've just submitted a PR (https://github.com/qgis/QGIS/pull/35995) that
allows QGIS to manipulate features from PostgreSQL tables with GENERATED
columns. Those are a new feature introduced by PostgreSQL 12 (
https://www.postgresql.org/docs/12/ddl-generated-columns.html ), useful for
common vector operations such as storing areas or centroids of polygons
without having to resort to triggers - which have a performance impact on
their own.

Since GENERATED columns only work with PostgreSQL versions equal or newer
than 12, I've changed the script that populates the database with test data
(tests/testdata/provider/testdata_pg.sh) to check for the database version,
and only load 12+ data if the test server is running that version.

For the Python tests themselves, I've also add a query to check the server
version, and if it is older than 12, the tests are not run.

Since Travis currently uses PostgreSQL 11, those tests won't be run on the
CI environment, but they did pass on my local environment (PostgreSQL
12+Postgis 3.0.0+Python 3.8.2).

The changes in the source code themselves are actually simple, they follow
the same line as the handling of ordinary DEFAULT values, with the
difference that GENERATED columns cannot be overriden by the user as per
the PostgreSQL docs, so we enforce the use of the DEFAULT clause when QGIS
writes to the database.

Any thoughts? Reviewers?

Thank you all.

-- 
... et cognoscetis veritatem et veritas liberabit vos.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20200504/ad68d61a/attachment.html>