Hello all,
Just a quick update of how it went.
I ended up using code similar to a combination of Andy Colson's and David
Johnston's suggestions below, and performance is back at what is was
before. Thanks for the suggestions
BTW: AFAICT I never got a response from Tom Lane about whether it was the
intention with the new FOR UPDATE locking policy to effectively lock the
entire table for all other clients using the exact same Select but with a
different and non-overlapping offset/limit for update query. IMO
continuing to lock unselected rows after the selection have completed is a
significant performance regression.
Also, an off-topic BTW: I have noticed that autovacuum of a table seems to
block ANALYZE of the same table, because the autovacuum do not release its
lock on the table.
On Tue, 01 Feb 2011 16:19:17 +0100, Andy Colson <andy@xxxxxxxxxxxxxxx>
wrote:
On 2/1/2011 6:32 AM, Yngve Nysaeter Pettersen wrote:
Hello all,
I am in the process of migrating a system from Postgresql 8.3 to 9.0,
and have run into a problem with the task queue systems I am using.
The task queue controls the allocation of tasks between about 1000
processes working in parallel, and is essentially a table of
<snip>
So, if I understand correctly, you:
q = SELECT record_id FROM queue
WHERE project_id = my_project AND state = idle
LIMIT n OFFSET i FOR UPDATE
while not q.eof
update queue set state = started where record_id = x;
process record_id
update queue set state = finsihed where record_id = x;
q.next;
Might I suggest and alternative:
q = update queue set state = started
WHERE project_id = my_project AND state = idle
LIMIT n OFFSET i
RETURNING project_id;
idlist = @q;
commit;
foreach x in idlist
process record_id
begin
update queue set state = finsihed where record_id = x;
commit;
On Wed, 02 Feb 2011 01:36:15 +0100, David Johnston <polobo@xxxxxxxxx>
wrote:
If random sampling is desirable would the following construct limit
locking
only to the sampled rows?
SELECT id
FROM tasktable
WHERE id IN (SELECT random_id_sample())
FOR UPDATE
--
Sincerely,
Yngve N. Pettersen
********************************************************************
Senior Developer Email: yngve@xxxxxxxxx
Opera Software ASA http://www.opera.com/
Phone: +47 23 69 32 60 Fax: +47 23 69 24 01
********************************************************************
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
