PostgreSQL:如何像队列一样从表中选择和更新 [英] PostgreSQL: How to select and update from a table as if it were a queue

问题描述

我希望从一个表中提取多个进程，就好像它是一个队列一样.要求:

I'd like several processes to pull from a table as if it were a queue. The requirements:

1. 项目的顺序很重要，必须在查询中指定
2. 每个进程(其中数量未知)一次可以获取 N 个项目的块
3. 每件商品只能处理一次
4. 我不想锁定表(出于性能原因)

我有一个可行的解决方案，但想要一些其他的意见.

I have a working solution, but would like some other opinions.

第一次尝试:

UPDATE foo
SET should_select=0
FROM
        (SELECT
                *
        FROM foo
        WHERE
                should_select=1
        ORDER BY id ASC
        LIMIT $N FOR UPDATE) as to_select
WHERE
    foo.id = to_select.id
RETURNING to_select.*;

这有效，但不会按顺序返回 $N 结果，因为返回"不保证顺序.我可以在客户端进行排序，但这需要将所有结果加载到内存中，如果 $N 很大，则效率不高.

This works, but does not return the $N results in order, as "RETURNING" does not guarantee order. I could sort on client side, but that will require loading all of the results into memory, and if $N is large this is not efficient.

第二次尝试:

我可以使用咨询锁，以便一次只能从队列中抓取 1 个进程，然后执行两个查询:

I can use an advisory lock so only 1 process can grab from the queue at a time, and then do two queries:

SELECT pg_advisory_lock( 123456 );

SELECT
        *
FROM foo
WHERE
        should_select=1
ORDER BY id ASC
LIMIT $N;

UPDATE foo
SET should_select=0
WHERE id IN (<list of $N ids...>);

SELECT pg_advisory_unlock( 123456 );

然而，我不喜欢 IN 子句.这对性能来说似乎很糟糕.

However, I'm not a fan of the IN clause.. this seems very bad for performance.

我使用的是 9.3 版.

I'm using version 9.3.

想法?

推荐答案

我环顾四周.这里有一些关于控制退货顺序的讨论:退货订单.这是一个较旧的问题(可能是 9.1 之前的)，但有对 CTE 的引用.那是票吧?这是我最喜欢的关于你应该在 postgres(包括 CTE)中使用的东西的页面:最好的 Postgres 功能.......因此，使用 with 子句我可以强制返回 * 的顺序，例如:

I looked around a bit. There is some talk about controlling the returning order here : returning order. This was an older question (pre 9.1 maybe), but there was a reference to the CTE. That's the ticket, right? Here is my favorite page about stuff you should be using in postgres (including CTEs): The best Postgres Features .... So, using a with clause I could coerce the order of a returning * like:

WITH FOOUP as (
UPDATE foo
SET should_select=0
FROM
    (SELECT
            *
    FROM foo
    WHERE
            should_select=1
    ORDER BY id ASC
    LIMIT $N FOR UPDATE) as to_select
WHERE
    foo.id = to_select.id
RETURNING to_select.*)
select * from FOOUP order by id asc;

这看起来应该是非常高效的，你应该按照正确的顺序恢复元组.也许你可以不使用 FOR UPDATE?

That looks like it should be super efficient, and you should get the tuples back in the correct order. Maybe you could leave off the FOR UPDATE?

-g

这篇关于PostgreSQL:如何像队列一样从表中选择和更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！