使用数据库进行排队操作？ [英] using database for queuing operations?

问题描述

我想尝试在Postgresql之上构建一个排队机制。


想象一个应用程序，其中大量进程生成图像

和排队缩略图请求。较少数量的进程（在一组专用计算机上运行
）会为这些映像生成缩略图。


从多个进程向队列添加条目很容易，通过执行

语句，例如：


插入nameq（action，name）值（''add''，''foo''）;


现在来了我不确定的部分。我可以很容易地编写一个前面的b $ b结束程序来选择最低的序列号


select * from nameq其中serial =（从nameq选择min（serial）） ;


，然后是用于生成缩略图的子流程的包裹。

如果我能在没有前端的情况下处理这个问题真的很棒
程序，以便多个程序可以执行以下操作：

选择要处理的下一个图像（使用上面的选择逻辑）

处理图像

删除该图像的行


我认为我可以使用select for update获得一个写锁（这样我就可以在完成时安全地删除该行），但我不确定是否可以

来避免比赛条件，其中两个进程将获得相同的行。


任何建议，评论等，赞赏！

Mark


------------------------------------------------ ---------

mh =＃\d nameq

表" public.nameq"

列|输入|修饰符

--------- + ----------------------------- + - -------------------------------------------------- -

行动|文字|不是null

name |文字|不是null

serial | bigint | default nextval（''nameq_seq'':: text）

addtime |没有时区的时间戳|默认（''now'':: text）:: timestamp（6）带时区

索引：

" nameq_addtime" btree（addtime）

" nameq_ser" btree（序列号）

mh =＃select * from nameq;

action |名字|串口| addtime

-------- + ------ + -------- + ---------------- ------------

add |吧| 11 | 2004-09-20 11：50：19.756182

del |吧| 13 | 2004-09-20 11：50：25.080124

add | foo | 14 | 2004-09-20 11：50：28.536398

-

Mark Harrison

皮克斯动画工作室


---------------------------（广播结束）--------------- ------------

提示1：订阅和取消订阅命令转到 ma*******@postgresql.org

I would like to try and build a queuing mechanism on top of Postgresql.

Imagine an application where a large number of processes generate images
and queue up thumbnail requests. A smaller number of processes (running
on a dedicated set of machines) generate thumbnails for those images.

Adding entries to the queue from multiple processes is easy, by executing
statements such as:

insert into nameq(action,name) values(''add'',''foo'');

Now comes the part I''m not sure about. I can easily write a front
end program that selects the lowest sequence number

select * from nameq where serial = (select min(serial) from nameq);

and then parcels that out to a subprocess for thumbnail generation.
It would be really great if I could handle this without the front end
program, so that multiple programs could do something like the following:
select next image to be processed (with above select logic)
process the image
delete the row for that image

I think that I can use "select for update" to obtain a write lock (so that
I can safely delete the row when finished), but I''m unsure if it''s possible
to avoid the race condition where two processes would get the same row.

Any advice, comments, etc, appreciated!
Mark

---------------------------------------------------------
mh=# \d nameq
Table "public.nameq"
Column | Type | Modifiers
---------+-----------------------------+----------------------------------------------------
action | text | not null
name | text | not null
serial | bigint | default nextval(''nameq_seq''::text)
addtime | timestamp without time zone | default (''now''::text)::timestamp(6) with time zone
Indexes:
"nameq_addtime" btree (addtime)
"nameq_ser" btree (serial)
mh=# select * from nameq;
action | name | serial | addtime
--------+------+--------+----------------------------
add | bar | 11 | 2004-09-20 11:50:19.756182
del | bar | 13 | 2004-09-20 11:50:25.080124
add | foo | 14 | 2004-09-20 11:50:28.536398
--
Mark Harrison
Pixar Animation Studios

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

推荐答案

在nameq表中添加一列，指定图像的状态。

然后你的逻辑变为select * from nameq where serial =（select name />
min（serial）from nameq）and state = QUOT未处理的" （或其他）

所以你选择更新，改变状态，然后处理

图像....然后删除。

Viola ！


Mark Harrison写道：

Add a column to the nameq table designating the ''state'' of the image.
Then your logic changes to "select * from nameq where serial = (select
min(serial) from nameq) and state="UNPROCESSED" (or whatever)
So you select for update, change the state, then process the
image....then delete.
Viola!

Mark Harrison wrote:

我想尝试在Postgresql之上构建一个排队机制。

通过执行
语句，可以轻松地从多个进程向队列中添加条目例如：

插入nameq（动作，名称）值（''添加''，''foo''）;

现在来了我的部分不确定。我可以轻松编写一个前端程序，选择最低序列号

select * from nameq where serial =（select name（serial）from nameq）;
然后打包到缩略图生成的子流程。
如果我能在没有前端程序的情况下处理它，那将是非常好的，因此多个程序可以执行如下操作：

选择要处理的下一个图像（用上面的选择逻辑）
处理图像
删除该图像的行

我认为我可以使用& ;选择更新获得一个写锁（所以
我可以安全地删除该行），但我不确定是否可以
避免比赛条件，其中两个进程将获得相同的行。

I would like to try and build a queuing mechanism on top of Postgresql.

Imagine an application where a large number of processes generate images
and queue up thumbnail requests. A smaller number of processes (running
on a dedicated set of machines) generate thumbnails for those images.

Adding entries to the queue from multiple processes is easy, by executing
statements such as:

insert into nameq(action,name) values(''add'',''foo'');

Now comes the part I''m not sure about. I can easily write a front
end program that selects the lowest sequence number

select * from nameq where serial = (select min(serial) from nameq);

and then parcels that out to a subprocess for thumbnail generation.
It would be really great if I could handle this without the front end
program, so that multiple programs could do something like the following:
select next image to be processed (with above select logic)
process the image
delete the row for that image

I think that I can use "select for update" to obtain a write lock (so
that
I can safely delete the row when finished), but I''m unsure if it''s
possible
to avoid the race condition where two processes would get the same row.

---------------------------（广播结束）---------------------------

提示3：如果通过Usenet发布/阅读，请发送适当的

subscribe-nomail命令到 ma ******* @ postgresql.org 这样你的

消息可以干净地通过邮件列表

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Jeff Amiel写道：

Jeff Amiel wrote:

在nameq表中添加一个列，指定图像的状态。
然后你的逻辑变为select * from nameq where serial =（select
min（serial）来自nameq）和state =" UNPROCESSED" （或其他）
所以你选择更新，改变状态，然后处理
图像....然后删除。

Add a column to the nameq table designating the ''state'' of the image.
Then your logic changes to "select * from nameq where serial = (select
min(serial) from nameq) and state="UNPROCESSED" (or whatever)
So you select for update, change the state, then process the
image....then delete.

谢谢杰夫，我认为这对我来说非常有用！


干杯，

马克


-

Mark Harrison

皮克斯动画工作室


------------------- --------（广播结束）---------------------------

提示6 ：您是否搜索了我们的列表档案？

http：//archives.postgresql。 org

Thanks Jeff, I think that will work perfectly for me!

Cheers,
Mark

--
Mark Harrison
Pixar Animation Studios

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

2004年9月20日星期一03:08:29 -0500，Jeff Amiel写道：

On Mon, Sep 20, 2004 at 03:08:29PM -0500, Jeff Amiel wrote:

在nameq表中添加一个列，指定图像的''state'。
然后你的逻辑变为'select * from nameq where serial =（select name /> min（serial）from nameq ）和state =UNPROCESSED （或其他）
所以你选择更新，改变状态，然后处理
图像......然后删除。
Viola！

Add a column to the nameq table designating the ''state'' of the image.
Then your logic changes to "select * from nameq where serial = (select
min(serial) from nameq) and state="UNPROCESSED" (or whatever)
So you select for update, change the state, then process the
image....then delete.
Viola!

您还应该考虑如果转换程序无法将b $ b更新为由于某种原因处理的状态会发生什么。例如，pgsql可能会意外地关闭
，或者转换过程可以。

-

Jim C. Nasby，数据库顾问 de ***** @ decibel.org

给你的电脑一些大脑糖果！ www.distributed.net 团队＃1828


Windows：你今天想去哪里？

Linux：明天你想去哪里？

FreeBSD：你呢？伙计们来了，还是什么？"


---------------------------（...结束广播）---------------------------

提示2：你可以一次性使用取消注册命令

（发送取消注册YourEmailAddressHere到 ma ******* @ postgresql。组织）

You should also consider what happens if the conversion program can''t
update the state to processed for some reason. For example, pgsql might
get shutdown unexpectedly, or the conversion process could.
--
Jim C. Nasby, Database Consultant de*****@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

这篇关于使用数据库进行排队操作？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！