QGEP
error in vw_qgep_maintenance delete trigger function
I tried to delete a maintenance event from the view and got that message:
ERROR: case not found HINT: CASE statement is missing ELSE part. CONTEXT: PL/pgSQL function qgep_od.ft_vw_qgep_maintenance_delete() line 3 at CASE SQL state: 20000
I downloaded the datamodel this monday and used it with PUM to update the datamodel.
My other databases are in version 1.5.3 and in them, the 'ELSE' isn't missing from the trigger.
By the way, where in the datamodel folder can I find this trigger?
!! 1.5.5 is not released yet !! Latest official released version is 1.5.4
https://github.com/QGEP/datamodel/releases/tag/1.5.4
This is probably linked to @olivierdalang 's fix not merged yet:
https://github.com/QGEP/datamodel/pull/169
That's strange, I don't see ft_vw_qgep_maintenance_delete() anywhere in the QGEP codebase...
Could it be a leftover from old pre-pum times ? But it's not in the demo production dataset either...
So, yes it is linked to that incoming fix about function inconsistencies, but it would probably have slipped through the suggested fix and required some manual fixing. That's slightly scary as I'm not sure how many other different stray functions there may be on other user's datamodels ?
pum check should warn about these differences (work partially done here but not merged yet : https://github.com/opengisch/pum/pull/97).
@varrieta Can you share a dump of your database (in private of not publicly shareable) ?
@olivierdalang Ok I'll send you one by mail.
Is it normal that when downloading the datamodel we can get one that hasen't been released yet (here 1.5.5 instead of 1.4.4)? (althouhg I should have checked it my self, my bad!!)
You need to download from the release tab : https://github.com/QGEP/datamodel/releases
If you download using git, or using "download code" from github, by default you will have the master branch, which is not yet released, which could lead to issues (e.g. deltas that are modified afterwards).
It's an easy mistake though :-/
Documentation of install and update is right by the way ;)
https://github.com/QGEP/datamodel/releases/latest
which target is https://github.com/QGEP/datamodel/releases/tag/1.5.4 actually.
For me yes because I'll get back to my old version of the database.
@olivierdalang can we close or is it a remaining issue?
Yes I'll close it, the remaining issue is on Pum side (https://github.com/opengisch/pum/pull/97)
Reopening: I have the same issue in my other (production) databases. They are all in version 1.5.4
Update: I installed a new qgep database from file qgep_1.5.4_structure_with_value_lists.sql and I have the same issue
Looked into that again. Actually I was wrong, ft_vw_qgep_maintenance_delete() is indeed normal, and from your dump it seems you have the correct one.
The issue arises because when there is no examination row related to the maintenance, the maintenance_event_type will be set to a generic maintenance_event, which it seems the trigger does not support.
Here I'm able to reproduce the issue if I first delete the row in the examination, and then try to delete the row in vw_qgep_maintenance. But if I directly delete the row in vw_qgep_maintenance, it works.
Not clear to me if this is expected (e.g. QGEP only supports maintenance_events that actually are examinations) or if this should be considered a bug.
Can you expand a little bit on your use case ? (do you have other type of maintenance events, etc.)
But how did you do to insert an examination from w_qgep_maintenance ?
I had to modify the form (picture below) so I can add examination. Without this modification and even if I fill the row related to the examination, the maintenance_event_type is always 'maintenance_event'
But whatever type it is, when I try to delete from the vw_qgep_maintenance_event, I always get the message error saying the CASE statement is missing ELSE.