[Qgis-user] Weird bug with view in postgresql

[Rémi Desgrange]

Hi,

We are using views to display data « nicely » to user in qgis. This views have INSERT/UPDATE/DELETE INSTEAD OF trigger ex: 

CREATE TABLE myCable(
cab_id uuid primary key default uuid_generate_v4(),
cab_usefull_column text,
the_geom geometry(LineString, 2154)
);

CREATE VIEW vw_myCable AS SELECT * FROM myCable;

CREATE FUNCTION f_insert_via_trig_mycable() RETURNS TRIGGER
LANGUAGE plpgsql AS $$
BEGIN 
	--useful stuff and finaly
	INSERT INTO myCable (cab_id, cab_usefull_column, the_geom) VALUES (NEW.cab_id, NEW.cab_usefull, NEW.the_geom);
END;
$$;

CREATE TRIGGER insert_mycable_trig INSTEAD OF INSERT ON myCable FOR EACH ROW EXECUTE PROCEDURE f_insert_via_trig_mycable();

Same stuff for update and delete.

Ok so pretty normal stuff. Now the weird part happens. Some person in my org (not always the same) tell me that, sometimes (that’s not good… regularly, I experienced it myself) when they create (INSERT) or modify (UPDATE) a geometry they can’t select it after creation/update. They have to restart Qgis, and this is not good ☹. I’m wondering why this stuff happen… Maybe it’s because inserting stuff takes long time (db speaking : >100ms, <200ms). They don’t have this kind of problem with non-view layer. I activated “log_statement=all” in postgresql to see what happen but I cannot activate it all day on production. Maybe this is a problem because 


If you have any insight on this… 

In advance thanks.

We are using postgresql 10, but this behavior happens in pg 9.4/9.5 and 9.6 too.


Sincelery, Rémi Desgrange