FOR UPDATE lock problem ? - Mailing list pgsql-hackers

Hi all,

I had a lock problem on my database.
When I use a "select for update" request whitch uses an index, the
locking system is inconsistant. 
Take this example:

test=# \d users                           Table "public.users"Column  |  Type   |                        Modifiers
---------+---------+---------------------------------------------------------id_user | integer | not null default
nextval('users_id_user_seq'::regclass)name    | text    |
Indexes:   "users_pkey" PRIMARY KEY, btree (id_user)

test=# \d sessions                                        Table "public.sessions"  Column   |            Type
 |                    Modifiers
 
------------+-----------------------------+---------------------------------------------------------------id_session |
integer                    | not null default
 
nextval('sessions_id_session_seq'::regclass)id_user    | integer                     |from_date  | timestamp without
timezone | default now()to_date    | timestamp without time zone |
 
Indexes:   "sessions_pkey" PRIMARY KEY, btree (id_session)   "idx_session_null" btree (id_session) WHERE to_date IS
NULL
Foreign-key constraints:   "sessions_id_user_fkey" FOREIGN KEY (id_user) REFERENCES
users(id_user)


test =# INSERT INTO users (name) values ('bob');
test =# INSERT INTO users (name) values ('brad');

test =# INSERT INTO Sessions (id_user,to_date) select id_user,now() from
users;
x 200 times (for example)
test =# INSERT INTO Sessions (id_user) select id_user from users;

test =# ANALYSE Sessions;
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=1width=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)


Then the problem with two backends:
bk1:
test=# begin;
test=# select s.id_session from users u, sessions s       where to_date IS NULL and u.id_user = s.id_user for
update;id_session
------------       403       404
(2 rows)

bk2:
test=# begin;
test=# select s.id_session from users u, sessions s       where to_date IS NULL and u.id_user = s.id_user for update;
=> ... Waiting

bk1:
test=# UPDATE sessions set to_date = now() where to_date is null;
UPDATE 2
test=# commit;

Then finaly on bk2:id_session
------------       403       404
(2 rows)

=> But the rows were updated by the other backend so to_date field is
not null for these tuples...However these tuples are in the result
produced by the backend #2...

If I remove the idx_session_null index the problem disappears.


-- 
REYNAUD Jean-Samuel <reynaud@elma.fr>
Elma