qwat/QWAT

Deleting data use case issues #7

haubourg opened this issue · 7 comments

haubourg commented

In the idea of finalizing the demo data sample to v1.2.1 model version, I continued the work started here #2

Removing meters via vw_element_meter

delete from qwat_od.vw_element_meter a where NOT a.geometry && (st_makebox2d(st_makePoint(559144, 143502), st_makepoint(561817, 140796)))

will throw fk constraint errors on meter_reference table

delete from qwat_od.vw_element_meter a where NOT a.geometry && (st_makebox2d(st_makePoint(559144, 143502), st_makepoint(561817, 140796)))
ft_element_meter_delete seem to miss the cascading delete for meter_reference table

haubourg commented

adding
DELETE FROM qwat_od.meter_reference WHERE fk_meter = OLD.id; in ft_element_meter_delete() does the trick.

haubourg commented

but.. that trigger function is generated by the meta-projects recursive scripts..

haubourg commented

SOoo, if I get it well, the solution would be to add a trigger on qwat_od.meter to delete meter_reference on delete. @3nids Can you confirm that I get it well ?

haubourg commented

Same problem occurs with deleting from vw_element_subscriber

**ERROR:  update or delete on table "subscriber" violates foreign key constraint "subscriber_reference_fk_subscriber" on table "subscriber_reference"
DETAIL:  Key (id)=(38641) is still referenced from table "subscriber_reference".
CONTEXT:  SQL statement "DELETE FROM qwat_od.subscriber WHERE id = OLD.id"
PL/pgSQL function qwat_od.ft_element_subscriber_delete() line 3 at SQL statement
********** Erreur **********

ERROR: update or delete on table "subscriber" violates foreign key constraint "subscriber_reference_fk_subscriber" on table "subscriber_reference"
État SQL :23503
Détail :Key (id)=(38641) is still referenced from table "subscriber_reference".
Contexte : SQL statement "DELETE FROM qwat_od.subscriber WHERE id = OLD.id"
PL/pgSQL function qwat_od.ft_element_subscriber_delete() line 3 at SQL statement
**

I guess that we have a general issues with reference_* logic and trigger generation process

3nids commented

Yep exactly.
A delete cascade rule is the way to go.

haubourg commented

Another one on pipes:
ERROR: update or delete on table "pipe" violates foreign key constraint "pipe_fk_parent" on table "pipe" DETAIL: Key (id)=(1106) is still referenced from table "pipe".

Deleting a pipe means first removing only fk_renference to it on other pipes, and then deleting it. Ther is no yet a delete trigger for pipes, should we add one?