Thread: Row locking inside a rule, is it possible?
Hi! I've got the followin problem: I have a view for which I want to create a rule for update, but in the rule I want to lock the updating row -i don't know if this is possible-, anyway I created the rule and had no problem: CREATE RULE mnt_order_submit_upd AS ON UPDATE TO mnt_order_submit DO INSTEAD [SELECT emission_date,emission_user,mnt_area,assign_date,technician FROM mnt_order WHERE mnt_order=OLD.mnt_order FOR UPDATE; UPDATE mnt_order SET emission_date=NEW.emission_date, emission_user=NEW.emission_user,mnt_area=NEW.mnt_area, assign_date=NEW.assing_date technician=NEW.technician WHERE mnt_order=OLD.mnt_order]; But when I try to update: UPDATE mnt_order_submit SET emission_date='2002-04-04',emission_user='1', mnt_area='2',assign_date='2002-04-04',technician='4' WHERE mnt_order='15'; I get the followin error: Internal Error: no jointree entry for rel *NEW* (3) Any idea of what i'm doing wrong or a better solution to the problem? I use Debian, Postgres 7.1 and I wrote the rule and the update from pgsql from an account with all privileges. Regards and thank you for your comments. Denis. -- GMX - Die Kommunikationsplattform im Internet. http://www.gmx.net
Denis Chavez <dnchavez@gmx.net> writes: > I get the followin error: > Internal Error: no jointree entry for rel *NEW* (3) Could we see a complete example that causes this? I'm too pressed for time to try to reverse-engineer your table declarations. regards, tom lane
Thanks. I kept trying and discovered that if I write the rule: CREATE RULE mnt_order_submit_upd AS ON UPDATE TO mnt_order_submit DO INSTEAD (SELECT * FROM mnt_order WHERE mnt_order=OLD.mnt_order FOR UPDATE OF mnt_order; UPDATE mnt_order SET emission_date=NEW.emission_date,emission_user=NEW.emission_user, mnt_area=NEW.mnt_area,assign_date=NEW.assign_date,technician=NEW.technician WHERE mnt_order=OLD.mnt_order); It works fine. My advice: when selecting for update inside a rule, we must especify the table twice?, that is: SELECT * FROM tablename WHERE ... FOR UPDATE OF tablename; :-) Regards, Denis > Denis Chavez <dnchavez@gmx.net> writes: > > I get the followin error: > > Internal Error: no jointree entry for rel *NEW* (3) > > Could we see a complete example that causes this? I'm too pressed for > time to try to reverse-engineer your table declarations. > > regards, tom lane > -- GMX - Die Kommunikationsplattform im Internet. http://www.gmx.net
Denis Chavez <dnchavez@gmx.net> writes: > ... My advice: when selecting for update inside a rule, we must > especify the table twice?, that is: SELECT * FROM tablename WHERE ... FOR > UPDATE OF tablename; :-) Hmm. Seems that the problem is this: the unadorned FOR UPDATE tries to apply FOR UPDATE to OLD and NEW as well as the regular tables mentioned in your SELECT. FOR UPDATE of NEW doesn't work ... and is fairly nonsensical anyway ... regards, tom lane