postgresql 9.4-阻止应用始终选择最新更新的行

Question

我有一个（Ruby on Rails 4）应用程序，并使用了一个PostgreSQL查询，该查询基本上查看机会表，在机会为’available =true’的行中随机搜索，并用’available = false’更新这些选定的行。每次用户单击“尝试机会”时，应用程序都会使用以下查询。

UPDATE opportunities s
SET    opportunity_available = false
FROM  (
   SELECT id
   FROM   opportunities
   WHERE  deal_id = #{@deal.id}
   AND    opportunity_available
   AND    pg_try_advisory_xact_lock(id)
   LIMIT  1
   FOR    UPDATE
   ) sub
WHERE     s.id = sub.id
RETURNING s.prize_id, s.id;
// inspired by https://stackoverflow.com/questions/33128531/put-pg-try-advisory-xact-lock-in-a-nested-subquery

我已经苦苦挣扎了5天，但现在我已经设法大致了解了它的表现（严重）：现在我需要知道如何对其进行更改。

实际上，该应用程序“变得有点疯狂”，并完全按照我更新的最新版本进行选择（并更新该行）。

让我清楚我如何创造这些机会的过程

• 我以管理员身份通过我的应用创建了20个机会（行）

• 然后在管理面板中创建一个奖品，奖品的数量，比如说奖品id = 45，数量=4。该应用程序将进入“机会”表，并随机填充（这部分工作正常），其中有4行有priest_id = 45。

现在，用户运行该应用程序，并且如上所述，该应用程序将不会随机选择一行，而是始终以最新的更新开始：似乎需要一行中有一个奖品，然后是另一个中有奖品，然后又是另一个，从不那些没有奖品的人（primed_id
=空）…

现在，我进行了一些手动实验：我尝试手动更改表上的值（通过pgadmin），这是最奇怪的事情：如果我修改了第32行，然后是第45行，然后是第67行，当用户再次尝试播放时，猜测一下，则随机选择的行正是我按照相反顺序更新的行：它将依次选择第67行，第45行和第32行。
= true）。

我也尝试不用于更新或’pg_try_advisory_xact_lock（id）’行，看来它仍然有相同的问题。

作为管理员，我首先创建总共20行，然后创建4个获胜行，它们是最后要删除的行（即使在我的pgadmin屏幕上它们仍然位于同一行…也许在背景中，postgresql是将它们作为最后一次连续更新并选择它们吗？），这就是为什么一旦从这4个获胜行中选择了一个，然后所有其他行都跟随着的原因。

需要明确的是，我会逐行选择每个可用的机会（例如：按照我在pgadmin上看到的，第3行，然后第4行，然后第5行，因为行已经完全随机地分配了奖金）。问题是它没有这样做，而是经常连续接获所有获奖行…。

我无语，也不知道如何打破这种模式。

注意：这种模式并不是100％的时间连续发生，而是经常发生：例如，如果我有4个以上的获胜行，那么如果我持续按用户单击2分钟，它就会像此处所说的那样运行，然后停止并似乎（或者我可能是错的）表现正常，然后＆分钟之后再次只会选择获胜的行…

编辑1

这是将奖品注入到“机会”表中的方式（例如，我创建了一个id为21且该奖品数量为3的奖品）=>它将随机发送给他们（据我所知），但仅限于尚未有award_id的地方（即，如果机会有priest_id
= empty，则可以将其放置在那里）

  SQL (2.4ms)  
  UPDATE "opportunities"
  SET "prize_id" = 21
  WHERE "opportunities"."id" 
  IN (
    SELECT "opportunities"."id"
    FROM "opportunities"
    WHERE (deal_id = 341 AND prize_id IS NULL)
    ORDER BY RANDOM()
    LIMIT 3) //
   (0.9ms)  COMMIT

这个SQL查询是由Rails
gem（称为Randumb：github.com/spilliton/randumb）生成的。

Answer 1

只是一个想法：与其调用random（）而不是将其用作列的默认值（可以建立索引），一种类似的方法可以使用增量约为0.7 * INT_MAX的序列。

\i tmp.sql

CREATE TABLE opportunities
    ( id SERIAL NOT NULL PRIMARY KEY
    , deal_id INTEGER NOT NULL DEFAULT 0
    , prize_id INTEGER
    , opportunity_available boolean NOT NULL DEFAULT False
            -- ----------------------------------------
            -- precomputed random() , (could be indexed)
    , magic DOUBLE precision NOT NULL default RANDOM()
    );

INSERT INTO opportunities(deal_id)
SELECT 341
FROM generate_series(1,20) gs
    ;
VACUUM ANALYZE opportunities;

PREPARE add_three (integer) AS (
WITH zzz AS (
  UPDATE opportunities
  SET prize_id = 21
    , opportunity_available = True
    -- updating magic is not *really* needed here ...
    , magic = random()
  WHERE opportunities.id
  IN (
    SELECT opportunities.id
    FROM opportunities
    WHERE (deal_id = $1 AND prize_id IS NULL)
    -- ORDER BY RANDOM()
    ORDER BY magic
    LIMIT 3)
RETURNING id, magic
    ) -- 
SELECT * FROM zzz
    );

PREPARE draw_one (integer) AS (
  WITH upd AS (
  UPDATE opportunities s
  SET    opportunity_available = false
  FROM  (
     SELECT id
     FROM   opportunities
     WHERE  deal_id = $1
     AND    opportunity_available
     AND    pg_try_advisory_xact_lock(id)
     ORDER BY magic
     LIMIT  1

     FOR    UPDATE
     ) sub
  WHERE     s.id = sub.id
  RETURNING s.prize_id, s.id, magic
    )
SELECT * FROM upd
    );

SELECT * FROM opportunities;

\echo add3
EXECUTE add_three(341);
SELECT * FROM opportunities;

\echo add3 more
EXECUTE add_three(341);
SELECT * FROM opportunities;

\echo draw1
EXECUTE draw_one(341);
SELECT * FROM opportunities;

\echo draw2
EXECUTE draw_one(341);
SELECT * FROM opportunities;

VACUUM ANALYZE opportunities;

\echo draw3
EXECUTE draw_one(341);
SELECT * FROM opportunities;

\echo draw4
EXECUTE draw_one(341);
SELECT * FROM opportunities;