Re: select from update from select?

What's the problem in doing that in two step within a transaction?

I suppose, you could do it in one step if you create a rule on the table:

create rule reserve_id as on update to work_queue_table do select 
new.work_queue_id;

Then if you do
update work_queue_table set worker_id=5, reservation_time = now () where  ...

it will select and return the id(s) of rows that got updated...

But, I don't see any reason why you would want to do that, especially, 
in a procedural language (like C++), as opposed to sql.

Something like:

begin;
select work_queue_id from work_queue .... for update of work_queue_table;
update work_queue .... where work_queue_id=<what_was_just_selected>

commit;

... looks a lot more straightforward.

I hope, it helps...

Dima



Dave Gomboc wrote:
> 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
>