Impossibility to edit layers from Lizmap with PSQL

[TecnostudiAmbiente]

What is the bug?

We can't modify the layers in Lizmap if we use a Postgres DB (this works on shape files).
In Lizmap we are using an admin account and in PSQL we have read and write permissions (we also tried to enable all permission on the DB side), however when we click on the modify icon in the popup or on the same icon in the attributes table a popup error message appears saying:

The layer "name_of_the_layer" has no primary keys. The edition tool needs a primary key on the table to be defined.

And of course, we have the primary key inside the DB.

It seems like an old bug you solved #765 but we are using the latest stable version of Lizmap.

Could you help us with this?
Thanks

Steps to reproduce the issue

1. Go to table attributes
2. Click on modify icon (pencil)
3. See the popup error

Expected behavior

We are expecting to be able to modify the layers from Lizmap.

Screenshots and logs

Here the error message:

No error logs are shown in Lizmap:

Environment

• Lizmap version: 3.3.12
• QGIS Server FULL version: 3.10.12
• QGIS Desktop FULL version: 3.10.12-A Coruna
• OS (Windows, Linux, MacOS, Android…): Ubuntu 20.04
• Browser (Firefox, Chrome…): Chrome, Firefox, Safari.
• Lizmap admin information (users, logs…) stored into Sqlite (default)? PostgreSQL? MySQL? PSQL 11.9 PostGIS 2.4.6

[Gustry]

How is your settings in Lizmap plugin about this edition layer?

[TecnostudiAmbiente]

Here our configuration (please note that we want only to be able to modify the attributes, not the geometry, also we tried to modify those options, but nothing changes):

Thanks for the quick reply : )

[ygorigor]

@TecnostudiAmbiente your table has a constraint?

[TecnostudiAmbiente]

No, none of the tables we use have constraints.

[ygorigor]

@TecnostudiAmbiente this could be the reason that you can not edit PG layer. In my case, it was.

[mdouchin]

You should not need additionnal constraint. The primary key should be enough.

• Can you pass the SQL definition of your table ?
• Have you unchecked the WFS checkboxes for some fields in the Field tab of the layer properties dialog ?

[TecnostudiAmbiente]

Thanks @ygorigor & @mdouchin , here following the table we are using:

CREATE TABLE table."xxxx"
(
    id integer NOT NULL DEFAULT nextval('table."xxxx_id_seq"'::regclass),
    geom geometry(Point,4326),
    xxxx bigint,
    xxxx bigint,
    xxxx bigint,
    xxxx character varying(254) COLLATE pg_catalog."default",
    xxxx bigint,
    xxxx character varying(254) COLLATE pg_catalog."default",
    xxxx character varying(254) COLLATE pg_catalog."default",
    xxxx character varying(254) COLLATE pg_catalog."default",
    xxxx character varying(254) COLLATE pg_catalog."default",
    xxxx character varying(254) COLLATE pg_catalog."default",
    xxxx character varying(254) COLLATE pg_catalog."default",
    xxxx character varying(254) COLLATE pg_catalog."default",
    xxxx character varying(254) COLLATE pg_catalog."default",
    xxxx character varying(254) COLLATE pg_catalog."default",
    xxxx character varying(254) COLLATE pg_catalog."default",
    xxxx character varying(254) COLLATE pg_catalog."default",
    xxxx character varying(254) COLLATE pg_catalog."default",
    xxxx character varying(254) COLLATE pg_catalog."default",
    xxxx double precision,
    xxxx double precision,
    xxxx date,
    xxxx date,
    xxxx character varying(254) COLLATE pg_catalog."default",
    xxxx character varying(254) COLLATE pg_catalog."default",
    xxxx character varying(254) COLLATE pg_catalog."default",
    xxxx character varying(254) COLLATE pg_catalog."default",
    xxxx double precision,
    xxxx double precision,
    xxxx double precision,
    xxxx double precision,
    xxxx character varying(50) COLLATE pg_catalog."default",
    xxxx character varying(250) COLLATE pg_catalog."default",
    xxxx character varying(50) COLLATE pg_catalog."default",
    xxxx character varying(50) COLLATE pg_catalog."default",
    xxxx character varying(50) COLLATE pg_catalog."default",
    xxxx character varying(50) COLLATE pg_catalog."default",
    xxxx character varying(50) COLLATE pg_catalog."default",
    xxxx character varying(50) COLLATE pg_catalog."default",
    xxxx character varying(150) COLLATE pg_catalog."default",
    xxxx character varying(50) COLLATE pg_catalog."default",
    xxxx character varying(50) COLLATE pg_catalog."default",
    xxxx character varying(50) COLLATE pg_catalog."default",
    xxxx integer,
    xxxx integer,
    xxxx double precision,
    xxxx integer,
    CONSTRAINT "xxxx_pkey" PRIMARY KEY (id)
)
WITH (
    OIDS = FALSE
)
TABLESPACE pg_default;

ALTER TABLE table."xxxx"
    OWNER to admin;

GRANT ALL ON TABLE table."xxxx" TO admin;

GRANT SELECT ON TABLE table."xxxx" TO "xxxx-xxxx";

GRANT SELECT ON TABLE table."xxxx" TO "xxxx-xxxx";

CREATE INDEX "sidx_xxx_geom"
    ON table."xxxx" USING gist
    (geom)
    TABLESPACE pg_default;

ALTER TABLE table."xxxx"
    CLUSTER ON "sidx_xxxx_geom";

Nb: Every xxxx values is a unique value but I couldn't report them here

I also tried to uncheck the WFS boxes on the project but it didn't work.

Thanks

[mdouchin]

You must never uncheck WFS in the Fields tab of the layer properties dialog, since WFS is used by Lizmap (+ a PostgreSQL request) to gather the fields and their properties. The primary key field must obviously also been checked

[TecnostudiAmbiente]

Good morning @mdouchin, initially the WFS boxes were checked, included the primary key field, I tried to uncheck them because of your previous comment, I thought you were suggesting to uncheck them.

Anyway I reverted them back to checked as they were initially and I can confirm that it doesn’t work both ways.
Thanks

[ygorigor]

@TecnostudiAmbiente did you try to edit this table from QGIS Desktop?

[TecnostudiAmbiente]

@ygorigor yes, of course, everything works!

[ocelot73]

Try to copy all styles from layer, remove It, then re-add the PG layer to Qgis project and paste styles to It. Set again qgis server properties in project file, set lizmap parameters needed and re upload qgis project and lizmap .cfg file to server. Many times i've solved weird lizmap behaviours with PG layers this way. Hope this can help.

[TecnostudiAmbiente]

Hi @ocelot73, yes, we did try that, although it didn't work.

Should we upgrade to another version, or do we risk having the same issue?

We also noticed that this problem is present also in SQLite :(

[Gustry]

Is this issue still valid @tethysgeco ?

[tethysgeco]

Hi @Gustry, do you need any feedback from us? Probably the only similar case we found in our experience was solved exposing the pkey field as wfs but setting the field hidden in the editing module, so that the sequence (xxxx_id_seq in this case?) could work properly. I hope to remember correctly, a long time is passed..

[Gustry]

our experience was solved exposing the pkey field as wfs but setting the field hidden in the editing module

This is indeed very important. Do not hide from WFS important fields, as then Lizmap Web Client will not be aware about this field. Lizmap is downstream compare to QGIS server. It's on the checklist already : 3liz/lizmap-plugin#389

So I'm closing this issue, thanks for the feedback.