Re: FOR UPDATE lock problem ?

[Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>]

REYNAUD Jean-Samuel <reynaud(at)elma(dot)fr> writes:
> test=# explain select s.id_session from users u, sessions s where
> to_date IS NULL and u.id_user = s.id_user;
>                                        QUERY PLAN
> -----------------------------------------------------------------------------------------
>  Nested Loop  (cost=0.00..6.85 rows=1 width=4)
>    ->  Index Scan using idx_session_null on sessions s  (cost=0.00..1.01
> rows=1 width=8)
>    ->  Index Scan using users_pkey on users u  (cost=0.00..5.82 rows=1
> width=4)
>          Index Cond: (u.id_user = "outer".id_user)
> (4 rows)

> If I remove the idx_session_null index the problem disappears.

Interesting example.  The planner is assuming that it need not
explicitly check the to_date IS NULL condition as a plan filter
condition since it is using a partial index, but apparently in the case
of SELECT FOR UPDATE queries we need to check anyway so that
EvalPlanQual will work properly.  Or maybe partial-index predicates
ought to be added to the EvalPlanQual mechanism.

			regards, tom lane