pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedselect from update from select?
6+ messages / 5 participants
[nested] [flat]
* select from update from select?
@ 2003-02-16 12:16 Dave Gomboc <dave@boyne.cs.ualberta.ca>
0 siblings, 2 replies; 6+ messages in thread
From: Dave Gomboc @ 2003-02-16 12:16 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: select from update from select?
@ 2003-02-16 17:07 Dima Tkach <dmitry@openratings.com>
parent: Dave Gomboc <dave@boyne.cs.ualberta.ca>
1 sibling, 0 replies; 6+ messages in thread
From: Dima Tkach @ 2003-02-16 17:07 UTC (permalink / raw)
To: Dave Gomboc <dave@boyne.cs.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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: select from update from select?
@ 2003-02-17 21:22 Tambet Matiisen <tambet.matiisen@mail.ee>
parent: Dave Gomboc <dave@boyne.cs.ualberta.ca>
1 sibling, 1 reply; 6+ messages in thread
From: Tambet Matiisen @ 2003-02-17 21:22 UTC (permalink / raw)
To: Dave Gomboc <dave@boyne.cs.ualberta.ca>; pgsql-sql
Why not just store the work_order_id in temporary variable? You need to do
SELECT FOR UDPATE in this case, to avoid reserving the same work twice.
Written in plpgsql:
select into temp_queue_id work_queue_id from work_queue,
optimization_task_table
where reservation_time is null
and concordance is null
order by priority descending limit 1
for update;
if found then
update work_queue_table set worker_id = 5,
reservation_time = 'now()'
where work_queue_id = temp_queue_id;
end if;
While testing this, I discovered, that it doesn't work exactly as I
expected. Suppose one transaction locks row with work_queue_id = 1. Now
before the first transactions has finished, second comes in and tries to get
lock for the same row. SELECT FOR UPDATE in second transaction blocks as
expected. But when first transaction finishes, the SELECT in second
transaction returns 0 rows, not next row that satisfies the conditions. I'm
not sure if this should be considered bug or feature. But you have to test
if the query returned any rows anyway, because the same happens when there
are no unreserved works in queue.
If it is required, that calling this function always reserves one row, then
you should LOCK whole table before doing SELECT. You don't have to use FOR
UPDATE in this case.
lock work_queue_table share row exclusive;
select into temp_queue_id work_queue_id from work_queue,
optimization_task_table
where reservation_time is null
and concordance is null
order by priority descending limit 1;
if found then
update work_queue_table set worker_id = 5,
reservation_time = 'now()'
where work_queue_id = temp_queue_id;
end if;
Table level lock is released automatically when transaction ends. Of course
if you don't do concurrent access, then you can just leave out both LOCK and
FOR UPDATE.
Tambet
----- Original Message -----
From: "Dave Gomboc" <dave@boyne.cs.ualberta.ca>
To: <pgsql-sql@postgresql.org>
Sent: Sunday, February 16, 2003 2:16 PM
Subject: [SQL] select from update from select?
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* select from update from select?
@ 2003-02-18 08:57 Dave Gomboc <dave@cs.ualberta.ca>
parent: Tambet Matiisen <tambet.matiisen@mail.ee>
0 siblings, 1 reply; 6+ messages in thread
From: Dave Gomboc @ 2003-02-18 08:57 UTC (permalink / raw)
To: Tambet Matiisen <tambet.matiisen@mail.ee>; +Cc: pgsql-sql
On Mon, 17 Feb 2003, Tambet Matiisen wrote:
> Why not just store the work_order_id in temporary variable? You need to do
> SELECT FOR UDPATE in this case, to avoid reserving the same work twice.
This doesn't work -- for the reason you give:
> While testing this, I discovered, that it doesn't work exactly as I
> expected. Suppose one transaction locks row with work_queue_id = 1. Now
> before the first transactions has finished, second comes in and tries to
> get lock for the same row. SELECT FOR UPDATE in second transaction
> blocks as expected. But when first transaction finishes, the SELECT in
> second transaction returns 0 rows, not next row that satisfies the
> conditions. I'm not sure if this should be considered bug or feature.
> But you have to test if the query returned any rows anyway, because the
> same happens when there are no unreserved works in queue.
It's definitely a bug to me. The purpose of the work_queue is hold the
pool of tasks to be done, and to allow different machines to indicate that
they will do a certain task without any master program to tell machines
what work to do. With the above process, concurrent attempts block
(except for one), then they don't even get a row! I definitely want
concurrent requests to not haggle over the same row, there'd be way too
much contention between machines, and it defeats the whole purpose of more
than one job being available simultaneously. If PostgreSQL had a SKIP
LOCKED feature, I'd be in business, but (AFAIK) it doesn't? Using NOWAIT
and doing a large number of retries using some random back-off scheme
seems like a big hack rather than something that would actually work
acceptably.
> If it is required, that calling this function always reserves one row, then
> you should LOCK whole table before doing SELECT. You don't have to use FOR
> UPDATE in this case.
>
> lock work_queue_table share row exclusive;
>
> select into temp_queue_id work_queue_id from work_queue,
> optimization_task_table
> where reservation_time is null
> and concordance is null
> order by priority descending limit 1;
>
> if found then
> update work_queue_table set worker_id = 5,
> reservation_time = 'now()'
> where work_queue_id = temp_queue_id;
> end if;
>
> Table level lock is released automatically when transaction ends. Of course
> if you don't do concurrent access, then you can just leave out both LOCK and
> FOR UPDATE.
Hmm. I didn't consider locking the entire table. I think this would be a
better way to go than the NOWAIT and retry-like-crazy? At least this way
each concurrent request will grab unique work until such time as there is
no work left. It's too bad that everything will stall, because the
actions don't need to be serialized, but I guess you can't have
everything.
Dave
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: select from update from select?
@ 2003-02-19 12:49 Richard Huxton <dev@archonet.com>
parent: Dave Gomboc <dave@cs.ualberta.ca>
0 siblings, 1 reply; 6+ messages in thread
From: Richard Huxton @ 2003-02-19 12:49 UTC (permalink / raw)
To: Dave Gomboc <dave@cs.ualberta.ca>; Tambet Matiisen <tambet.matiisen@mail.ee>; +Cc: pgsql-sql
On Tuesday 18 Feb 2003 8:57 am, Dave Gomboc wrote:
> On Mon, 17 Feb 2003, Tambet Matiisen wrote:
> > Why not just store the work_order_id in temporary variable? You need to
> > do SELECT FOR UDPATE in this case, to avoid reserving the same work
> > twice.
>
> This doesn't work -- for the reason you give:
> > While testing this, I discovered, that it doesn't work exactly as I
> > expected. Suppose one transaction locks row with work_queue_id = 1. Now
> > before the first transactions has finished, second comes in and tries to
> > get lock for the same row. SELECT FOR UPDATE in second transaction
> > blocks as expected. But when first transaction finishes, the SELECT in
> > second transaction returns 0 rows, not next row that satisfies the
> > conditions. I'm not sure if this should be considered bug or feature.
> > But you have to test if the query returned any rows anyway, because the
> > same happens when there are no unreserved works in queue.
>
> It's definitely a bug to me. The purpose of the work_queue is hold the
> pool of tasks to be done, and to allow different machines to indicate that
> they will do a certain task without any master program to tell machines
> what work to do. With the above process, concurrent attempts block
> (except for one), then they don't even get a row! I definitely want
> concurrent requests to not haggle over the same row, there'd be way too
> much contention between machines, and it defeats the whole purpose of more
> than one job being available simultaneously. If PostgreSQL had a SKIP
> LOCKED feature, I'd be in business, but (AFAIK) it doesn't? Using NOWAIT
> and doing a large number of retries using some random back-off scheme
> seems like a big hack rather than something that would actually work
> acceptably.
This has come up before - check the archives for details.
One solution is to have a "processed" flag with values:
U - unprocessed
X - executing
D - done
You claim a job by changing from U to X - this takes very little time, so your
worker processes can simply sit in a loop trying. When work is complete the
flag is marked D.
This has the added advantage that you can spot rows where processing started
but didn't complete due to a crash etc.
Another solution if you know how many workers you have is to have a manager
process which tags each job with a worker ID, but this does tend to make the
manager a point of failure.
--
Richard Huxton
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: select from update from select?
@ 2003-02-20 01:46 Dave Gomboc <dave@cs.ualberta.ca>
parent: Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 6+ messages in thread
From: Dave Gomboc @ 2003-02-20 01:46 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Tambet Matiisen <tambet.matiisen@mail.ee>; pgsql-sql
> This has come up before - check the archives for details.
>
> One solution is to have a "processed" flag with values:
>
> U - unprocessed
> X - executing
> D - done
>
> You claim a job by changing from U to X - this takes very little time, so your
> worker processes can simply sit in a loop trying. When work is complete the
> flag is marked D.
> This has the added advantage that you can spot rows where processing started
> but didn't complete due to a crash etc.
>
> Another solution if you know how many workers you have is to have a manager
> process which tags each job with a worker ID, but this does tend to make the
> manager a point of failure.
In my system, when reservation_time is not null, the state is not U, and
when the result field (not described in my email) is not null, the state
is neither U nor X. I do use reservation_time (and 'now()') to reclaim
tasks that have not completed after a certain (dynamic) length of time
(e.g. due to crash). So I think I'm attempting to implement essentially
what you describe. ;-)
I guess what you're saying is that in the big picture, the period of
contention for jobs between, say, 60 concurrent clients will really be
quite small, so don't worry about eliminating it?
Dave
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-02-20 01:46 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-16 12:16 select from update from select? Dave Gomboc <dave@boyne.cs.ualberta.ca>
2003-02-16 17:07 ` Dima Tkach <dmitry@openratings.com>
2003-02-17 21:22 ` Tambet Matiisen <tambet.matiisen@mail.ee>
2003-02-18 08:57 ` Dave Gomboc <dave@cs.ualberta.ca>
2003-02-19 12:49 ` Richard Huxton <dev@archonet.com>
2003-02-20 01:46 ` Dave Gomboc <dave@cs.ualberta.ca>
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