Database Locks on specific workflows with GeoPackage

[widmerc]

Dear OpenGISch Team,

Thank you for the great plugin!

I am currently working on a newly created database based on a Interlis file (according to this guide: https://www.opengis.ch/de/2021/12/07/model-baker-6-7-noch-nie-wars-so-einfach/). I use the roads simple from https://www.interlis.ch/downloads/testdatensaetze. I've encountered a bit of a roadblock when it comes to adding points and geometries to an existing Interlis database.

I did not find any good guide how to add points / geometries in an existing interlis database. There seem to be different approaches how to do it:

1. Create a new line in the Layer 'StreetAxis' -> add a new reference object with the [+] -> fill out the form -> click OK -> klick OK
   But when I want to save the current layer, the following error occurs:

Konnte Änderungen am Layer StreetAxis nicht festschreiben
Fehler: FEHLER: 2 Objekte nicht hinzugefügt.
Datenanbieterfehler:
OGR-Fehler beim Erzeugen des Objekts -64: failed to execute insert : database is locked

2. Create an object Street -> fill out the form -> go to the tab streetaxis -> add an child object -> draw a new object -> click OK -> click OK.
   Now I am able to save the layers.

I'm wondering if approach number 2 is the recommended way to add points/geometries to an Interlis database. It worked for my current setup, but I'm not sure if it's suitable for more complex Interlis structures.

If this approach makes sense, I'd be happy to contribute to the Model Baker plugin by putting together a tutorial on modifying and creating loaded GeoPackage (gpkg) databases in Interlis format. It seems like there's a bit of a gap in documentation for these processes, and a tutorial could be really helpful for others.

Thanks for your time, and I hope to hear your thoughts soon.

[signedav]

Thanks for your input.

I agree that in this use case one can assume that people want to digitize first the streetaxis (geometrylayer) and then the street. I tested with QGIS 3.28 if I can not reproduce your problem with the database is locked (what QGIS version do you use). Still I can confirm that sometimes there are issues with GeoPackages. And locks should never happen, independent of the workflow you want to use.

Those issues are not directly connected with INTERLIS or Model Baker but with OGR or QGIS. In INTERLIS models and their physical implementations, there are just often lots of relations used, that could lead to issues. PostgreSQL is more stable.

An information maybe lacking in the documentation is the one with the transaction mode. This is automatically activated in PostgreSQL and means, that all the changes are commited in one transaction.

In GeoPackage this is not activated per default, because in the past it leaded to the issue you describe.

If it's not activated you have to save changes on every layer individually. But here you need to be aware that you have to save first the parent features (street) and then the children (steetaxis). Otherwise you get FK errors: OGR error committing transaction: sqlite3_exec(COMMIT) failed: FOREIGN KEY constraint failed

Then there is a third possiblity: Buffered Transaction Groups This is keeping all the changes in local buffer and then commits it in one command. This has been implemented especially for this issues with GeoPackage. But then had some bugs in it that became fixed later. We are currently testing it again #693 (ping @domi4484 fyi, maybe you can consider these test data in your research).

Maybe you can test those modes and give me feedback if there are still problems or not. And don't forget the QGIS version because this is relevant for us.

[signedav]

Maybe related #807

[widmerc]

Dear @signedav. Thank you for your help regarding this issue. I am working with QGIS 3.30.3. on WINDOWS.

Because I am still in education (Geography at UZH) I dont have the background to set up a PostgreSQL Database. I am sure, I can learn this in further semester ^^.

In this QGIS version, the Buffered Transaction Groups are the default option for any geopackages. If I change them to Automatic Transaction Groups, the FK errors don't occur, which is quite more userfriendly. Thanks for this tip.

Regarding the Database is locked - Error: When I tried to reproduce the error today, I was not able to do this. I know that it occurs, because I work with people that get this error from time to time, especially with more complex interlis models (e.g. from a komunale Nutzungsplanung - Interlis (e.g. Models GEO SZ).

I try to get a reproducable example that works for most machines. I will get in touch with you in the next days when I found a good way.

Thank you again for your fast answer. It is great to have people that take care of their community.