Обсуждение: 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