2

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"
14
  • We use PostgreSQL with PostGIS in QGIS heavily, and do not have any issues like this at all. What kind of changes you do? Any plugins involved? – Miro Jul 22 '20 at 9:38
  • Also as far as I am aware, QGIS does not save any changes to the database in editing mode until you hit save or quit editing with save. So do you save changes and then try to undo/redo it? – Miro Jul 22 '20 at 9:44
  • @Miro We do not use any plugins. I can understand now that QGIS is keeping all changes on my side. What is puzzling to me is, why the redo failes with the above message. Database should not be involved if the changes are only in QGIS. – PavelP Jul 22 '20 at 10:40
  • Ok. Does your feature class (table) in PostGIS has unique key defined like gid serial primary key? – Miro Jul 22 '20 at 11:03
  • @Miro Table has id column which is defined as unique as well asi primary key. It is not explicitly declared as serial, instead the sequence is defined by it's default value. DEFAULT nextval('"MK".usek_id_seq'::regclass) – PavelP Jul 22 '20 at 12:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.