Re: select from update from select?

Why not just store the work_order_id in temporary variable? You need to do
SELECT FOR UDPATE in this case, to avoid reserving the same work twice.
Written in plpgsql:

select into temp_queue_id work_queue_id from work_queue,
optimization_task_table
where reservation_time is null
and concordance is null
order by priority descending limit 1
for update;

if found then
update work_queue_table set worker_id = 5,
reservation_time = 'now()'
where work_queue_id = temp_queue_id;
end if;

While testing this, I discovered, that it doesn't work exactly as I
expected. Suppose one transaction locks row with work_queue_id = 1. Now
before the first transactions has finished, second comes in and tries to get
lock for the same row. SELECT FOR UPDATE in second transaction blocks as
expected. But when first transaction finishes, the SELECT in second
transaction returns 0 rows, not next row that satisfies the conditions. I'm
not sure if this should be considered bug or feature. But you have to test
if the query returned any rows anyway, because the same happens when there
are no unreserved works in queue.

If it is required, that calling this function always reserves one row, then
you should LOCK whole table before doing SELECT. You don't have to use FOR
UPDATE in this case.

lock work_queue_table share row exclusive;

select into temp_queue_id work_queue_id from work_queue,
optimization_task_table
where reservation_time is null
and concordance is null
order by priority descending limit 1;

if found then
update work_queue_table set worker_id = 5,
reservation_time = 'now()'
where work_queue_id = temp_queue_id;
end if;

Table level lock is released automatically when transaction ends. Of course
if you don't do concurrent access, then you can just leave out both LOCK and
FOR UPDATE.

Tambet

----- Original Message -----
From: "Dave Gomboc" <dave(at)boyne(dot)cs(dot)ualberta(dot)ca>
To: <pgsql-sql(at)postgresql(dot)org>
Sent: Sunday, February 16, 2003 2:16 PM
Subject: [SQL] select from update from select?

> I'm not sure if this is the correct place for this question. If it
> isn't, I'd appreciate a pointer to a better.
>
> Here is my pseudo-SQL (formatted for readability):
>
> select work_queue_id from
> (update work_queue_table set worker_id = 5,
> reservation_time = 'now()'
> where work_queue_id in
> (select work_queue_id from work_queue, optimization_task_table
> where reservation_time is null
> and concordance is null
> order by priority descending limit 1
> )
> )
>
> I am trying to do the following (using PostgreSQL 7.3.2):
>
> 1. reserve a row (which represents some work to be done) in my
> work_queue_table based on certain conditions (a: that work hasn't
> already been reserved; b: there is no work considered to be of higher
> priority than it available)
>
> 2. get the primary key (work_queue_id) of that reserved row.
>
> I'm wondering what changes I would need to make to my pseudo-SQL to
> make it legitimate. I'm trying to avoid a two-step process of
> attempting to reserve a row, then checking to see which -- if any --
> row was actually reserved.
>
> If it matters, I'm writing my code in C++, and using libpqxx (1.4.1)
> to interface to the database.
>
> Dave
>
> --
> Dave Gomboc
> M.Sc. Student 1-41 Athabasca Hall
> Department of Computing Science Edmonton, Alberta,
> University of Alberta Canada T6G 2E5
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>