TransWikia.com

QGIS: Error when using Undo/Redo on layers loaded from PostgreSQL database

Geographic Information Systems Asked on May 30, 2021

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 in the PostgreSQL database, but I don’t know why.

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

I thought that QGIS is able to handle in the background all the functionality for undo/redo on database layers (in newer versions at least).

UPDATE: It looks like when the layers are in editing mode, changes (creating new features at least) are happening on the QGIS side and are not propagated to the database. When I do "redo" QGIS is trying to obtain the data from database via cursor, but the cursor was not set beforehand. Probably because the changes were not pushed to database?

UPDATE 2: 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 transactio 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"

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP