From: Dima Tkach <dmitry@openratings.com>
To: Dave Gomboc <dave@boyne.cs.ualberta.ca>
Subject: Re: select from update from select?
Date: Sun, 16 Feb 2003 12:07:41 -0500
Message-ID: <3E4FC55D.5060903@openratings.com> (raw)
In-Reply-To: <b2nvfo$9ro$1@pulp.srv.ualberta.ca>
References: <b2nvfo$9ro$1@pulp.srv.ualberta.ca>
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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: dmitry@openratings.com, dave@boyne.cs.ualberta.ca
Subject: Re: select from update from select?
In-Reply-To: <3E4FC55D.5060903@openratings.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox