[HACKERS] FOR UPDATE lock problem ?
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 time zone | 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=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)
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 [EMAIL PROTECTED]
Elma
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Function call with offset and limit
Hi
I've just tried it, and it works. So it's a good work-around.
Though, is it a wanted feature to have a function being performed on
each row before the offset ?
Le mercredi 21 décembre 2005 à 13:41 -0600, Jim C. Nasby a écrit :
Have you tried
SELECT *, test_func(idkeyword)
FROM (SELECT * FROM tag OFFSET 5000 LIMIT 1)
;
?
This should probably have been on -general, btw.
On Wed, Dec 21, 2005 at 06:44:33PM +0100, REYNAUD Jean-Samuel wrote:
Hi all,
We need to find a solution for a strange problem.
We have a plpgsql FUNCTION which performs an heavy job (named
test_func).
CREATE or replace function test_func(z int) returns integer as $$
declare
tst integer;
begin
--
-- Large jobs with z
--
tst := nextval('test_truc');
return tst;
end;
$$ LANGUAGE plpgsql;
So I made this test:
test=# select setval('test_truc',1);
setval
1
(1 row)
test=# select currval('test_truc') ;
currval
-
1
(1 row)
test=# select *,test_func(idkeyword) from tag offset 5000 limit 1;
idkeyword | test_func
---+-
5001 | 5002
(1 row)
test=# select currval('test_truc') ;
currval
-
5002
(1 row)
This demonstrates that the function is called 5001 times though only one
row is returned. Problem is that this heavy job is performed much, much
more than needed.
But, If I do:
test=# select *,(select test_func(1)) from tag offset 5000 limit 1;
My function is called only once.
Is there any work around ?
Thanks
--
REYNAUD Jean-Samuel [EMAIL PROTECTED]
Elma
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
--
REYNAUD Jean-Samuel [EMAIL PROTECTED]
Elma
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
[HACKERS] Function call with offset and limit
Hi all,
We need to find a solution for a strange problem.
We have a plpgsql FUNCTION which performs an heavy job (named
test_func).
CREATE or replace function test_func(z int) returns integer as $$
declare
tst integer;
begin
--
-- Large jobs with z
--
tst := nextval('test_truc');
return tst;
end;
$$ LANGUAGE plpgsql;
So I made this test:
test=# select setval('test_truc',1);
setval
1
(1 row)
test=# select currval('test_truc') ;
currval
-
1
(1 row)
test=# select *,test_func(idkeyword) from tag offset 5000 limit 1;
idkeyword | test_func
---+-
5001 | 5002
(1 row)
test=# select currval('test_truc') ;
currval
-
5002
(1 row)
This demonstrates that the function is called 5001 times though only one
row is returned. Problem is that this heavy job is performed much, much
more than needed.
But, If I do:
test=# select *,(select test_func(1)) from tag offset 5000 limit 1;
My function is called only once.
Is there any work around ?
Thanks
--
REYNAUD Jean-Samuel [EMAIL PROTECTED]
Elma
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
