Re: Bug #514: Backend crashes periodically

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

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