Re: Select for update with offset interferes with concurrent transactions - Mailing list pgsql-general

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@squeakycode.net>
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@yahoo.com>
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@opera.com
Opera Software ASA                   http://www.opera.com/
Phone:  +47 23 69 32 60              Fax:    +47 23 69 24 01
********************************************************************