At Oslandia, we use a lot the transaction groups option of QGIS. It was an experimental feature in QGIS 2.X allowing to open only one common Postgres transaction for all layers sharing the same connection string.
When activated, that option will bring many killer features:
- Users can switch all the layers in edit mode at once. A real time saver.
- Every INSERT, UPDATE or DELETE is forwarded immediately to the database, which is nice for:
- Evaluating on the fly if database constraints are satisfied or not. Without transaction groups this is only done when saving the edits and this can be frustrating to create dozens of features and having one of them rejected because of a foreign key constraint…
- Having triggers evaluated on the fly. QGIS is so powerful when dealing with « thick database » concepts that I would never go back to a pure GIS ignoring how powerful databases can be !
- Playing with QgsTransaction.ExecuteSQL allows to trigger stored procedures in PostgreSQL in a beautiful API style interface. Something like
- However, the implementation was flagged « experimental » because some caveats where still causing issues:
- Committing on the fly was breaking the logic of the undo/redo stack. So there was no way to do a local edit. No Ctrl+Z! The only way to rollback was to stop the edit session and loose all the work. Ouch.. Bad!
- Playing with ExecuteSQL did not dirty the QGIS edit buffer. So, if during an edit session no edit action was made using QGIS native tools, there was no clean way to activate the « save edits » icon.
- When having some failures in the triggers, QGIS may loose DB connection and thus create a silent ROLLBACK.
We decided to try to restore the undo/redo stack by saving the history edits in PostgreSQL SAVEPOINTS and see if we could restore the original feature in QGIS.
And.. it worked!
Let’s see that in action:
Potential caveats ?
At start, we worried about how heavy all those savepoints would be for the database. It turns out that maybe for really massive geometries, and heavy editing sessions, this could start to weight a bit, but honestly far away from PostgreSQL capabilities.
Up to now, we didn’t really find any issue with that..
And we didn’t address the silent ROLLBACK that occurs sometimes, because it is generated by buggy stored procedures, easy to solve.
Some new ideas came to us when working in that area. For instance, if a transaction locks a feature, QGIS just… wait for the lock to be released. I think we should find a way to advertise those locks to the users, that would be great! If you’re interested in making that happen, please contact us.
More to come soon, stay tuned!