From: Dave Gomboc <dave@boyne.cs.ualberta.ca>
To: pgsql-sql@postgresql.org
Subject: select from update from select?
Date: 16 Feb 2003 12:16:56 GMT
Message-ID: <b2nvfo$9ro$1@pulp.srv.ualberta.ca> (raw)
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
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: dave@boyne.cs.ualberta.ca
Subject: Re: select from update from select?
In-Reply-To: <b2nvfo$9ro$1@pulp.srv.ualberta.ca>
* 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