[Bug]: Unable to edit a PostgreSQL layer with generated columns

Mavialle commented 1 week ago

What is the bug? (in English)

Generated column is a new feature of PostgreSQL 12 : https://www.postgresql.org/docs/current/ddl-generated-columns.html

I created a layer with generated columns, but when I edit it in Lizmap, it doesn't work : the edit panel loads infinitely and never opens. When I replace generated columns by normal columns, everything works normally again

Steps to reproduce the issue

In PostgreSQL, create a table with a generated column
Create a Lizmap project with this table and enable editing on it
In Lizmap, try to edit the table created in step 1

Versions, safeguards, check summary etc

Versions :

Lizmap Web Client : 3.7.5
Lizmap plugin : 4.3.0
QGIS Desktop : 3.28.15
QGIS Server : 3.28.15
Py-QGIS-Server : not used
QGIS Server plugin atlasprint : 3.3.2
QGIS Server plugin cadastre : 1.19.1
QGIS Server plugin lizmap_server : 2.8.6
QGIS Server plugin wfsOutputExtension : 1.8.0

List of safeguards :

* Mode : safe * Allow parent folder : no * Prevent other drive : yes * Prevent PG service : yes * Prevent PG Auth DB : yes * Force PG user&pass : yes * Prevent ECW : yes

Check Lizmap plugin

[X] I have done the step just before in the Lizmap QGIS desktop plugin before opening this ticket. Otherwise, my ticket is not considered valid and might get closed.

Operating system

Windows 8

Browsers

Chrome

Browsers version

Version 125.0.6422.142

Relevant log output

No response

gioman commented 1 week ago

but when I edit it in Lizmap, it doesn't work

@Mavialle does it works in qgis?

Gustry commented 5 days ago

And please upgrade your Lizmap Web Client version, the loading bar has been improved recently related to errors while loading the form.

And do the same for the Lizmap plugin, your version is old.

Mavialle commented 4 days ago

@gioman yes, with the same project, editing works in QGIS but not in Lizmap.

@Gustry, I upgraded the Lizmap plugin but I get a python error message when I click the OK button. Should I make another ticket ? I will upgrade the Lizmap web client as soon as possible, we have many maps with many js in use on the server, so we can't do it quickly (we need to test first if everything is OK)

Gustry commented 4 days ago

Yes, open a ticket on https://github.com/3liz/lizmap-plugin/ thanks

Mavialle commented 4 days ago

Okay, I did it.

I'll try editing the table with a generated column when I can use the upgraded plugin again.

I'll keep you informed. Thanks !

Mavialle commented 16 hours ago

I have successfully updated the QGIS Lizmap extension. So I deleted the cfg file and recreated it with version 4.3.18, but it still doesn't work.

I will try to update Lizmap Web Client as soon as possible, but it may take a few months.

I have nothing in the Lizmap or QGIS server logs, but in the PostgreSQL logs I have the message:

ERROR: column "geom" does not exist at character 18 STATEMENT: SELECT st_length(geom) AS v;

SELECT st_length(geom) is the expression used in my generated column. I checked the "Evaluate vendor-side defaults" option in the Project Properties > Data Sources dialog, but nothing changed. So PostgreSQL and Lizmap Web Client don't communicate well?

If anyone wants to test it faster than me with an up to date version of Lizmapwebclient, here is the SQL code to create the very simple table I used named test in a schema named suivis

CREATE TABLE IF NOT EXISTS suivis.test
(
    gid serial NOT NULL,
    longueur integer GENERATED ALWAYS AS (st_length(geom)) STORED,
    geom geometry(LineString,2154) NOT NULL,
    commentaire character(100),
    CONSTRAINT test_gid_pkey PRIMARY KEY (gid)
)

I will come back to you as soon as I have anything new

Gustry commented 16 hours ago

I agree it would be nice to have generated column supported. But in the meantime, you can use a trigger for this use case. Execute the trigger when the geom is updated or created.

Mavialle commented 14 hours ago

Yes, and I do. But it's so much simpler to use the generated column : a row to create the generated column replaces a function trigger + a trigger (sometimes several). I find this to be a great innovation since PostgreSQL 12, and it's a shame that it is not compatible with using the table in Lizmap.

Gustry commented 14 hours ago

Feel free to contact us to improve this in Lizmap Web Client 👍

Mavialle commented 13 hours ago

At 3Liz? I just need to explain the context and ask for a quote ?

Gustry commented 13 hours ago

Yes, generated columns have never been implemented, it's a new feature request, which needs some dev.

Mavialle commented 13 hours ago

OK, I will do it as soon as possible. In the meantime, do I leave the ticket open?

3liz / lizmap-web-client