From: Dima Tkach <dmitry@openratings.com>
Subject: Re: select from update from select?
Date: Sun, 16 Feb 2003 12:07:41 -0500
Organization: Hub.Org Networking Services (http://www.hub.org)
Lines: 69
Message-ID: <3E4FC55D.5060903@openratings.com>
References: <b2nvfo$9ro$1@pulp.srv.ualberta.ca>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
To: Dave Gomboc <dave@boyne.cs.ualberta.ca>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.2.1) Gecko/20021130
In-Reply-To: <b2nvfo$9ro$1@pulp.srv.ualberta.ca>
To: pgsql-sql@postgresql.org

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
>