Error editing 'editable' view in QGIS

Question

Taking some code from this post, I've been trying to create an 'editable' view in PostGIS.

I'd like to store label placement properties in a separate table, build a view of the data I want to use for my map, and use QGIS to move the labels. The idea is when the labels are updated, the placement coordinate would go into the LABEL_X and LABEL_Y fields - the geometry of the underlying points and several other attributes (in the view, coming from the source tables) would remain unchanged.

However, when all is in place, QGIS throws the following error when trying to save the edits to the view (the edit session can start, labels moved, but cannot save):

Could not commit changes to layer schools_district_map

Errors: ERROR: 1 attribute value change(s) not applied.

Provider errors: PostGIS error while changing attributes:   

ERROR:  infinite recursion detected in rules for relation "schools_district_map"

Here is the definition of the view:

CREATE OR REPLACE VIEW public.schools_district_map AS 
 SELECT sch.schnum,
    sch.oid,
    sch.abbreviation,
    sch.school_level,
    sch.geom,
    l.label_x,
    l.label_y
   FROM temp_schools_label sch
     LEFT JOIN district_map_labels l ON sch.schnum = l.schnum;

And here are the rules I've applied to make the view 'editable':

--delete rule
create or replace rule "delete_label" as 
on delete to schools_district_map do instead
delete from schools_district_map 
where oid = old.oid;

--insert rule

create or replace rule "insert_label" as 
on insert to schools_district_map do instead
insert into schools_district_map (label_x, label_y)
values (new.label_x, new.label_y);


--update rule
create or replace rule "labels_update" as 
on UPDATE TO schools_district_map do instead 
update schools_district_map set 
label_x = new.label_x
, label_y = new.label_y
where oid = new.oid;

QGIS is then set to display the labels using the label_x and label_y field.

If anyone can spot where I might have left something out, or if there is a glaring oversight on my part, let me know.

Answer 1

Ran into this myself as well, recently.

There's a small mistake in the rules, as you are trying to edit the view itself, instead of the underlying table. This then triggers the rule again and so starts an infinite loop. So your rules should be something like

--delete rule
create or replace rule "delete_label" as 
on delete to schools_district_map do instead
delete from temp_schools_label 
where oid = old.oid;

--insert rule
create or replace rule "insert_label" as 
on insert to schools_district_map do instead
insert into district_map_labels (label_x, label_y)
values (new.label_x, new.label_y);

--update rule
create or replace rule "labels_update" as 
on UPDATE TO schools_district_map do instead 
update district_map_labels set 
label_x = new.label_x,
label_y = new.label_y
where oid = new.oid;

Furthermore, if you're using postgis 9.1, please look into using triggers instead of rules. A good explanation can be found here: https://vibhorkumar.wordpress.com/2011/10/28/instead-of-trigger/