Re: Bug #514: Backend crashes periodically

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
>
> 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.

Yes it seems a pretty serious problem.
The problem is that session 2 sees a not yet deleted( by session 1)
session row and an already updated( by session 1) sis_user row at
the same time. There's no such snapshot that could see both rows.

How do you think about the following query ?

  update sis_user set last_visit = session.last_access_time
  where sis_user_id = session.sis_user_id
  and session.session_key = 'zzz';

UPDATE acquires row level locks on the target sis_user rows
but doesn't acuiqre any row level lock on the related session
rows. Could it guarantee the consistency of the query ?

regards,
Hiroshi Inoue