Re: FOR UPDATE lock problem ?

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