Re: Bug #514: Backend crashes periodically

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)jp>
Cc: Warren Volz <wrv(at)po(dot)cwru(dot)edu>, pgsql-bugs(at)postgresql(dot)org
Subject: Re: Bug #514: Backend crashes periodically
Date: 2001-11-14 03:03:16
Message-ID: 4695.1005706996@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hiroshi Inoue <Inoue(at)tpf(dot)co(dot)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

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Justin Clift 2001-11-14 05:46:27 Problem with 7.1.3 and -DUSE_AUSTRALIAN_RULES
Previous Message Hiroshi Inoue 2001-11-14 00:14:02 Re: Bug #514: Backend crashes periodically