Unable to save changes when saving new WFS-T feature having a Relation Reference to a parent table #7158
Description
Hi QField team,
I have a geospatial infrastructure for testing WFS-T on the field using QField.
It can be summarized as follow:
(4) QFIELD consumes WFS through QGIS client projet & collects data (WFS-T) on the field
↑
(3) QGIS CLIENT consumes the WFS endpoints & collect data (WFS-T)
↑
(2) QGIS SERVER serves and accept data to/from the client through the WFS-T protocol
↑
(1) PG DATABASE stores data
Here's a sample database (1) structure:
CREATE TABLE IF NOT EXISTS public.family( -- parent table
id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
"name" VARCHAR(255) NOT NULL UNIQUE
);
CREATE TABLE IF NOT EXISTS public.flower( -- child table
id INT PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
species_id INT NOT NULL REFERENCES public.family(id) ON DELETE CASCADE,
geom GEOMETRY(POINT, 4326) NOT NULL
);QGIS Server (2) allows every CRUD operation in WFS/OAPIF parameters (publish, update, insert, delete). The project is valid.
QGIS Client (3) is where things start to be tricky because WFS does not forward information about the database constraints.
Therefore, we have to find a way to "redesign" those constraints in the client in order to avoid database errors when committing data.
So, in order to correctly fill the fk field in the child table, a solution can be to create a "relation" in the QGIS client:
This relation can be used to "resolve" the fk field in the child table (red dashed outline in the image below).
In addition one can fully set up the "Not NULL" constraints (both checkboxes must be checked as shown in the green outline) to actually match the underlying database constraint:
From now, one must consider the following data acquisition pattern:
- First, the user creates a new entry in the parent table (
family). This is entirely successfully as the new record is saved into the database:
LOG: execute addfeatures: INSERT INTO "public"."family"("name") VALUES ('New species') RETURNING "id"
- Second, the user creates a new entry in the child table (
flower) linking the previously created parent feature (<- this is the most important part!)
From this point, we can consider two major cases:
a) In the case the "Not NULL" constraints are both not set up in the form of the QGIS client, the database will naturally complains when submitting a new feature because the client let it through.
This is the log when trying to do so from QGIS desktop client (3):
LOG: execute addfeatures: INSERT INTO "public"."flower"("geom","species_id") VALUES (st_geomfromwkb($1::bytea,4326),NULL) RETURNING "id"
DETAIL: Parameters: $1 = '\x0101000000c7c1c5606e592240931895065e6b4740'
ERROR: null value in column "species_id" of relation "flower" violates not-null constraint
DETAIL: Failing row contains (8, null, 0101000020E6100000C7C1C5606E592240931895065E6B4740).
STATEMENT: INSERT INTO "public"."flower"("geom","species_id") VALUES (st_geomfromwkb($1::bytea,4326),NULL) RETURNING "id"
When trying to do the same from QField (4) using the same project file, it says:
🔴 Unable to save changes
and here is the error raised by the database:
LOG: execute addfeatures: INSERT INTO "public"."flower"("geom","species_id") VALUES (st_geomfromwkb($1::bytea,4326),0) RETURNING "id"
DETAIL: Parameters: $1 = '\x0101000000e8a6db282c59224048ddf9e9fa6a4740'
ERROR: insert or update on table "flower" violates foreign key constraint "flower_species_id_fkey"
DETAIL: Key (species_id)=(0) is not present in table "family".
STATEMENT: INSERT INTO "public"."flower"("geom","species_id") VALUES (st_geomfromwkb($1::bytea,4326),0) RETURNING "id"
I would have expected exactly the same log, but they are different.
b) with the "Not NULL" constraints correctly set up in the form of the QGIS client; the client itself complains about it when trying to submit a new child feature.
This is the behaviour from the QGIS dekstop client (3): it doesn't let the user submit the form; the "OK" button is grayed out and there is a little red cross near the fk field:
at this stage, everything is working "technically as expected" but from a user perspective, not being able to submit this new feature may seems confusing here because the new species is actually listed in the drop down...
And when trying to do the same from QField (4) using the same project file, when one click on the save button, it says:
🔴 Unable to save changes
and here is the error raised by the database at that time:
LOG: execute addfeatures: INSERT INTO "public"."flower"("geom","species_id") VALUES (st_geomfromwkb($1::bytea,4326),0) RETURNING "id"
DETAIL: Parameters: $1 = '\x0101000000e9b3fe3ff55a224000fc6dcecd6a4740'
ERROR: insert or update on table "flower" violates foreign key constraint "flower_species_id_fkey"
DETAIL: Key (species_id)=(0) is not present in table "family".
STATEMENT: INSERT INTO "public"."flower"("geom","species_id") VALUES (st_geomfromwkb($1::bytea,4326),0) RETURNING "id"
it's actually the same error as before when both "Not NULL" constraints were not set up in the QGIS form.
From my own understanding:
- the WFS layer is not refreshed once the new record in the parent (
family) table is saved. If I manually refresh the layer, the issue disappears. But this is more a QGIS issue and I opened an issue in QGIS repository to keep things clearly separated: Unable to save new WFS-T child feature having a Relation Reference to a parent table right after parent feature was successfully committed qgis/QGIS#65471 - in both a) and b) cases, QField tells us it cannot save the changes made, but it actually tried to commit those changes to the database with a wrong fk value set to 0, whereas in case a) it should insert a NULL value like QGIS does, and in case b) it should not even let the user press the save button.
Thank you for taking the time to read and understand this case from start to finish.
Steps to reproduce the issue
Everything is described above.
Version
4.0.6
Operating system name
Linux
Operating system version
Ubuntu 22.04.5 LTS
Reinstall QField
- I have a fresh install of the latest QField version, but the problem persists.
- Problem can be reliably reproduced, doesn't happen randomly.
- Problem happens with all files and projects, not only some files or projects.
Additional context
No response