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

达达广告

我有一个（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）生成的。

解决方案:


            只是一个想法：与其调用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;

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

达达广告 LV1