Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-bugs by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group