Error using Undo/Redo on layers with relations loaded from PostgreSQL/PostGIS database

[KulMaster]

I am working with layers loaded from PostgreSQL(11.7)/PostGIS database to QGIS 3.10.7. When I try to undo/redo changes made on the layer it fails, sometimes during the undo and basically every time when doing redo (I didn't notice any pattern in it). Apparently, there is some problem with the cursor handling in the PostgreSQL database, but I don't know why. The error:

Fetching from cursor qgis_398 failed Database error: ERROR: cursor "qgis_398" does not exist

After the fail, all changes made in edit session are lost. I suspect there is some problem when handling the relations, as when I create plain new database with simple table/layer, the problem does not occur.

1. Create PostgreSQL/PostGIS database that have three tables, two with geometry column and one that will serve as a junction table (without geometry)
2. For the junction table set two foreign keys that will point to the respective tables (id column) with geometry, both are initialized as 'DEFERRABLE INITIALLY DEFERRED' and they cascade on UPDATE and DELETE
3. Create connection to the db in QGIS and load the layers.
4. In Project properties -> 'Data sources' set 'Automatically create transaction groups where possible' and 'Evaluate default values on provider side'
5. In Project properties -> 'Relations' use 'Discover relations' and add two relatons that should be seen
6. In Properties of the respective layers with geometry, go to 'Attributes form' and set the right cardinality for the relation (It should point to other table(id)
7. Start editing of the layers 'Toggle editing', all layers now should be in edit mode
8. Now try to add some new features, my layers have geometry type multiline, so I added few lines
9. Try to 'Undo' changes, usually after few steps back or forward it fails with the above message

The steps for setting up the database and relations should be same as described in documentation.

Versions: Windows 10 Home 10.0.19041 QGIS 3.10.7 (tried it also in 3.14.15 with same result) PostgreSQL 11.7 PostGIS 2.5.3

'QGIS version 3.10.7-A Coruña QGIS code revision 7b4ca4c8d0 Compiled against Qt 5.11.2 Running against Qt 5.11.2 Compiled against GDAL/OGR 3.0.4 Running against GDAL/OGR 3.0.4 Compiled against GEOS 3.8.1-CAPI-1.13.3 Running against GEOS 3.8.1-CAPI-1.13.3 Compiled against SQLite 3.29.0 Running against SQLite 3.29.0 PostgreSQL Client Version 11.5 SpatiaLite Version 4.3.0 QWT Version 6.1.3 QScintilla2 Version 2.10.8 Compiled against PROJ 6.3.2 Running against PROJ Rel. 6.3.2, May 1st, 2020 OS Version Windows 10 (10.0) Active python plugins db_manager; processing'

I attach also a project file and sql dump of the db in the zip file rollback_error.zip

[WencelFrohlich]

Hi, is some progress in this bug?

Our organization need resolve this bug.

Thank you for your answer.

[gioman]

Our organization need resolve this bug.

@WencelFrohlich https://qgis.org/en/site/forusers/commercial_support.html

[KulMaster]

Over time I tried several versions of PostgreSQL (9, 11, 12) and QGIS (3.10, 3.14), the same problem persisted. After studying logs made by the database server I have found, that the QGIS is saving the state of the layers as a SAVEPOINT in database when I start editing. During the edits, QGIS is creating various cursors (with ST_AsBinary function). I am guessing that by doing this, the QGIS gets the actual data from the database. The problem occurs when the QGIS calls for the ROLLBACK (when I do undo in the QGIS). All the changes that was done after declaring the SAVEPOINT are lost, but QGIS then tries to FETCH the CURSOR that is lost thank to this. This causes the error and the transaction is aborted.

"DECLARE CURSOR",2020-11-24 15:51:20 CET,15/18,126847,LOG,00000,"duration: 0.473 ms statement: DECLARE qgis_305 BINARY CURSOR WITH HOLD FOR SELECT st_asbinary(""geometry"",'NDR'),""id"",""fk_MK"",""popisky_z""::text,""popisky_y""::text,""poznamka""::text,""typ_useku""::text,""umisteni""::text,""provoz""::text,""popisky_x""::text,array_out(""par_cis_ve"")::text,array_out(""par_cis_hl"")::text,""KU""::text,""stav_MK""::text,""cislo_kom""::text,""cislo_us""::text,""povrch""::text,""plocha""::text,""delka""::text,""sirka""::text FROM ""MK"".""usek"" WHERE ""geometry"" && st_makeenvelope(-533970.59677669638767838,-1124501.15351219591684639,-532212.18808578583411872,-1123600.86123963561840355,5514)",,,,,,,,,"QGIS"

"ROLLBACK",2020-11-24 15:51:20 CET,15/18,126847,LOG,00000,"duration: 0.065 ms statement: ROLLBACK TO SAVEPOINT ""qgis779d8f397d0d4932aa0a""",,,,,,,,,"QGIS"

"FETCH",2020-11-24 15:51:20 CET,15/18,126847,ERROR,34000,"cursor ""qgis_305"" does not exist",,,,,,"FETCH FORWARD 2000 FROM qgis_305",,,"QGIS"

"CLOSE CURSOR",2020-11-24 15:51:20 CET,15/18,126847,ERROR,25P02,"current transaction is aborted, commands ignored until end of transaction block",,,,,,"CLOSE qgis_305",,,"QGIS"

[Pedro-Murteira]

@KulMaster Hello, is this issue still valid on more recent releases?

[KulMaster]

@Pedro-Murteira Hi, I have quickly tried it and it seems that it persist. I've used QGIS 3.22.4.