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
>