Postgresql: empty SAVEPOINTS

[qgib]

Author Name: Hugo Mercier (@mhugo) Original Redmine Issue: 17535 Affected QGIS version: master Redmine category:data_provider/postgis Assignee: Vincent Mora

---

When using layers from a PostgreSQL in transaction mode, some strange things happen sometimes.

In particular, an error related to SAVEPOINT happens some times upon modifications on layers.

ERROR:  SAVEPOINT can only be used in transaction blocks

.. quickly followed by

[ERROR:  zero-length delimited identifier at or near """"
LIGNE 1 : SAVEPOINT "" 

... and sometimes by a segmentation fault

The problem happens on https://github.com/qgis/QGIS/blob/master/src/core/qgsvectorlayerundopassthroughcommand.cpp#L77 when mSavePointId.isEmpty() AND savePointId.isEmpty()

I can't find yet a minimal reproducible scenario to reproduce, sorry.

---

Related issue(s): #25074 (relates) Redmine related issue(s): 17175

---

[qgib]

Author Name: Matthias Kuhn (@m-kuhn)

---

I noticed the same in some tests.

Probably the transaction is not started, might be the begin transaction command is sent in a context where it's not expected.

---

• assigned_to_id was changed from Jürgen Fischer to Vincent Mora

[qgib]

Author Name: Vincent Mora (@vmora)

---

might be the begin transaction command is sent in a context where it's not expected

I don't get it. How can an edit command be issued while the transaction has not begun. I'll try and see if I can understand what's going on, but I'd really, really appreciate some pointers to reproduce the bug.

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

• priority_id was changed from Normal to High

[qgib]

Author Name: Vincent Mora (@vmora)

---

Hugo, is there by any chance a redo command issued (or an edit command) ? Can you describe, at least rougthly what you are (or your script is) trying to do ?

[qgib]

Author Name: Vincent Mora (@vmora)

---

Hugo, can you try checking the error msg from mTransaction->begin in QgsTransactionGroup::onEditingStarted() ?

Also, adding a Q_ASSERT(!mSavePointId.empty()) in QgsVectorLayerUndoPassthroughCommand::QgsVectorLayerUndoPassthroughCommand would help diagnose the problem at it's root.

For the record, the transaction should begin on signal beforeEditingStarted from vector layer, connected in QgsTransactionGroup:

QgsVectorLayer::startEditing emit beforeEditingStarted which is connected to QgsTransactionGroup::onEditingStarted which loops over layers and call QgsVectorLayer::startEditing which returns before emitting if editing is already underway.

[qgib]

Author Name: Matthias Kuhn (@m-kuhn)

---

I just found a way to reproduce this behavior reliably.

Setup database and project

1. Install and enable the QgsProjectGenerator plugin
2. Database -> Project Generator -> Generate
3. Source: Interlis (Use PostGIS)
4. Models: KbS_LV95_V1_3
5. CRS: EPGS:2056
6. PostgreSQL: Choose any databaseserver near you
7. Save the project, so next time there's no reason to repeat these steps ;)

Reproduce problem:

1. Toggle editing
2. Add a feature on "belasteter standort"
3. Go to tab "egrid_"
4. Click child feature
5. Enter any number in value
6. Accept
7. Debug ;)

I have isolated the reason for the crash, but not for the missing transaction block

[qgib]

Author Name: Matthias Kuhn (@m-kuhn)

---

It looks like what happens is the following

• A transaction is created
• At some point a command fails (in the example above it's a syntax error because it's trying to use a "default value definition" instead of an evaluatated default value)
• The transaction is aborted (silently)
• The user wants to commit something on the (aborted) transaction
• The system tries to create a snapshot (and fails because the transaction is aborted)

What could be done?

• Can we run commands in a sandbox (as in "if command fails, rollback to last savepoint instead of killing the whole transaction")?
• Can we run commands outside the transaction (i.e. can we run r/o select statements after a ROLLBACK TO SAVEPOINT or some other form of checkout in a different transaction)?
• Can we at least detect when a transaction fails and report this to the user and also repaint the map (because all the work in the current transaction is gone)?

[qgib]

Author Name: Vincent Mora (@vmora)

---

Matthias, thanks for the update, but there is something I don't get: why should a syntax error abort the transaction ? It's the primary cause of the issue apparently, and normally a failed instruction could be rolled back, or am I mistaken ?

I'm pretty sure the undo/redo code checks for errors, but it may not take the right course of action when error occur (like issuing a rollback).

[qgib]

Author Name: Regis Haubourg (@haubourg)

---

related to #25074

[qgib]

Author Name: Regis Haubourg (@haubourg)

---

transaction abort on syntax error in PostgreSQL seems to be the normal behavior. If we want to avoid that, the idea of a sandbox sounds good. I found that strategy for psql client : https://www.endpoint.com/blog/2015/02/24/postgres-onerrorrollback-explained

It relies on creating a savepoint and rollback to it when using the psql specific '\set ON_ERROR_ROLLBACK' session setting.

[qgib]

Author Name: Jürgen Fischer (@jef-n)

---

• relates was configured as #25074

[qgib]

Author Name: Vincent Mora (@vmora)

---

Regis, as far as I understand the article, creating savepoint before each command is roughly what we do to implement the undo/redo.

Postgres doc on transactions (https://www.postgresql.org/docs/8.3/static/tutorial-transactions.html) states: "ROLLBACK TO is the only way to regain control of a transaction block that was put in aborted state by the system due to an error"

So we may not need a sandbox, but simply to "ROLLBACK TO" when an error occurs. The last example the the aforementioned doc points in this direction too.

[qgib]

Author Name: Regis Haubourg (@haubourg)

---

You're right Vincent. Anyway, the ROLLBACK TO will just get back to the previous SAVEPOINT without aborting the transaction, but the error will reoccur just when the user will try again the same action. I think we should adress all the SQL syntax errors we have first.

The one that I isolated in #25074 is that when QGIS manipulates uuid fields and happen to emit a

WHERE my_uuid_field = 'NULL'

should be changed by a

WHERE my_uuid_field IS NULL

Matthias, Hugo, did you isolate other causes ?

[qgib]

Author Name: Vincent Mora (@vmora)

---

Matthias, I've tried your procedure (project generator) and couldn't reproduce the problem with today's master.

I've also tried to reproduce the syntax error using #25074, but again couldn't.

With the article Regis mentioned and according to the postgres doc, we should be able to "ROLLBACK TO transaction_savepoint" if a commands causes the transaction to abort, which is what is done in the postgres provider. In this case the last undo/redo savepoint (which is just before the rolled back transaction_savepoint) is not dirtied which is what we want... or maybe it should be released instead ?

I have also tried to introduce a syntax error in the "DELETE" of the postgres provider, the error is caught, user notified, roll back occurs and I can create features after that (the transaction is restored and undo/redo works as expected).

Matthias, I'm wondering if by "silently" aborted you mean the transaction is aborted with no PGException raised.

[qgib]

Author Name: Giovanni Manghi (@gioman)

---

Please try on QGIS 3.4.1, if the issue is still valid change the affected version, thanks.

---

• status_id was changed from Open to Feedback

[qgib]

Author Name: Vincent Mora (@vmora)

---

Régis, Hugo, have you got a test that reproduces the issue ?

[qgib]

Author Name: Hugo Mercier (@mhugo)

---

The last issues with a similar behavior that I've encountered were due to concurrency problems, which should be fixed now (with https://github.com/qgis/QGIS/pull/8850)

Closing

---

• status_id was changed from Feedback to Closed

[hachete01]

I recently encountered the same issue (postgres savepoint error) on Qgis v. 3.22.14 LTR (Bialowieza) when trying to write to postgresql database. After reading the issue, I noticed a reference to a project generator plugin as origin of the project used to recreate the issue, which resonated with the problem I encountered, being that I used the Model Baker plugin to generate it. 

I created a new project and manually added the same layers/tables, and somehow writing to the postgres/postgis database worked. The project is also saved in the database. 

I am not able to confirm if the use of a project generator could indeed be the culprit, but felt the detail could still be relevant in case someone else encounters the issue. 

[m-kuhn]

@hachete01 can you provide steps to reproduce and check if this issue is also present on 3.28 or 3.30? Thank you

[hachete01]

The steps to set up the project are as following: 1) new untitled project, 2) generate from existing remote postgis database (a free instance with postgis enabled offered by bit.io) using Model Baker plugin; 3) connection to database is a service connection; 5) projection is CRS: EPGS:32619; 5) save project in database.

Reproduce problem:

1) Toggle editing 2) Add a feature on a polygon layer with autogenerated sequential PK id 3) Fill form, all fields are checked green (however, the PK is empty, where it should denote "nextval('table_name_id_seq'::regclass)" 4) Save form

After this, the feature does not appear on screen (nothing happens), and the error referencing the savepoint issue is logged.

[m-kuhn]

Thanks. Can you share a dump or sql to setup that specific demo database?

[hachete01]

Here is a sql... ld_dll.txt

[jgrocha]

Hi @hachete01 I've tried to reproduce the issue. I've added items to parcela and to mejora_edification tables. Both have autogenerated sequential primary keys.

No error adding items and they were saved on the DB. Should I try to insert child items on these tables?

I'm using PostgreSQL 15 on Ubuntu. I used:

psql -h localhost -p 5434 -U postgres qgis25432 -f ld_dll.txt

To load your data. Some minor issues were reported:

...
CREATE TABLE
CREATE TABLE
psql:ld_dll.txt:1316: ERROR:  syntax error at or near "MATCH"
LINE 11: ...ribuyente(id) ON DELETE CASCADE ON UPDATE CASCADE MATCH FULL...
                                                              ^
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
CREATE TABLE
psql:ld_dll.txt:1532: ERROR:  relation "ld.persona_fisica" does not exist
CREATE TABLE
CREATE TABLE
psql:ld_dll.txt:1592: ERROR:  relation "ld.persona_fisica" does not exist
CREATE TABLE
CREATE TABLE
psql:ld_dll.txt:1650: ERROR:  relation "ld.oficial_publico" does not exist
CREATE TABLE
CREATE TABLE
...

[hachete01]

Thanks for the feedback. The tests I ran were limited to new features in "mejora_edificacion" table. No child table. After bumping into the issue page and noticing the use of the plugin to generate the project, it ocurred to me to redo the project manually. After I did that, the problem went away. It is possible I did something else in the steps up to the issue, or that the root is something else... Do let me know if there is more info I could look for or provide that would be helpful. Here is a few screenshots in sequential order, showing the form after digitizing, before saving, then the logs after hitting save.

[m-kuhn]

The plugin model baker (formerly project generator) also sets transaction mode. Maybe it's other specifics as well.

If you could store your project as a file and share it here, that would help.

[hachete01]

ld_test.zip

[jgrocha]

Hi @hachete01

You project uses a service based connection postgresatbitio, but also layers directly from db.bit.io.

You project has many relations. Toggle editing for mejora_edification switches many other layers into edit mode too. Why?

Why do you have layers from two different databases mixed? mejora_edificacion comes from one database, parcela comes from another database, for example.

Such a complicated project can raise QGIS issues, but it is quite difficult to identify such issues in this project. If you can create a very small project that fails, it would be great. To debug such a complicated and slow project is not easy.

Maybe the problem is on the project itself. Maybe on the plugin used to create the project. Try to isolate the problem, if you can.

[hachete01]

Hello,

I get the complexity issue... In reality all tables/layers are in the same schema within the same database. At least that is how they are stored and should have been loaded.

I was not able to use Model Baker with basic auth configuration (got an error that db or schema did not exist), which is the reason I used a service connection to generate the project. After this was done, and after encountering editing issues, I switched to basic configuration, thinking that the service connection might have been the cause (the first editing error I got was permission related). For some reason some layers in the Browser show as source the service connection, while others show the auth configuration, but all sources are in the same database.

Here is a copy of the project that I created by manually adding of tables/layers from the same database. This project does not have the editing issue (savepoint), which resolves my initial problem. ld_test2.zip

[vmora]

Hi @hachete01,

I just looked at the two projects (the xml) and the first one (ld_tes.zip, that does trigger the issue) sets autotransactions whereas the second one (ld_test2.zip) doesn't. Also, there are relations defined in the former, not in the later.

As pointed out by @jgrocha , I had to move MATCH FULL before ON DELETE to load your .sql, I'm curious to know how you obtained this file, other from that line everything is fine.

I'll try to find some time to look into the issue, because I'd be delighted to crush that one little bugger.

If the project, however complex, allows to consistently reproduce it, it's a real gift, thank you for sharing.

[hachete01]

Hi @vmora,

The relations were generated by Qgis automatically. I did not work on them. For the second project, I did not work much on it after creation. I had just created it following clues after reading this issue page.

The .sql file was automatically generated using a postgresql client (Dbeaver 22.2.3). I did it using UI commands, from the relevant schema. I am very happy to be of help.