Hiroshi Inoue <Inoue@tpf.co.jp> writes:
> What I meant is
> Session 2 invokes the session_del rule and really
> updates a sis_user row by the rule though it deletes
> no session row.
Hmmm ... that's an ugly thought, isn't it? And I'm not sure there's
anything we can do to defend against it. If both sessions are executing
the UPDATE at the same time, then neither can possibly know that the
other is about to do a DELETE. So the UPDATE will happen twice, which
is harmless in the given scenario but would be decidedly not so if the
UPDATE were changing some sort of total or balance.
Perhaps a safer way to write such a rule would be
... ON DELETE DO
SELECT * FROM target_table WHERE key = OLD.key FOR UPDATE;
UPDATE other_table SET balance = balance + OLD.quantity
WHERE something-or-other;
I haven't experimented with this but it seems that the FOR UPDATE
ought to provide the necessary interlock to ensure that only one
transaction does the UPDATE for a particular target row.
If this does work, is there a way to make the FOR UPDATE lock happen
implicitly in the rule mechanism? Should we even try? It's a lot
of overhead that may not always be needed.
regards, tom lane