Re: implicit lock in RULE ?

Fritz Lehmann-Grube <lehmannf@math.TU-Berlin.DE> writes:
> I tried

> CREATE RULE new_vc_thread AS ON INSERT TO images
> WHERE new.vc_thread = 0
> DO
> (
> BEGIN;
> LOCK vc_threads;
> INSERT INTO vc_threads(name) VALUES(new.name);
> UPDATE images SET vc_thread = currval('vc_threads_id_seq') WHERE vc_thread=0;
> COMMIT;
> );

> but got a syntax error - OK.

> But how can I work around it ?

Drop the BEGIN, the LOCK, and the COMMIT.  Read the discussion of
sequence functions at
http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/functions-sequence.html
to see why you don't need any LOCK to protect the currval() value.

A more serious problem with the above is that it will fail to do what
you want for multiple-row insertion commands --- the INSERT will
correctly insert multiple rows into vc_threads, but when control comes
to the UPDATE, all of the freshly added images rows will be updated to
link to the last of those vc_threads rows, because you only have one
currval() value to work with.

You'd be better off doing this as a trigger, not a rule.  The syntax
hurdle is a bit higher (you need to learn a little bit of pl/pgsql)
but the mental model of what's going on is far simpler.  Triggers
work on one row at a time --- rules don't.

            regards, tom lane