postgresql 9.4 – 阻止应用程序始终选择最新更新的行

我有一个(Ruby on Rails 4)应用程序,并使用postgreSQL查询,它基本上查看Opportunity表,在机会为’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行,其中prize_id = 45.

现在一个用户运行应用程序并且如上所述,应用程序不会随机选择一行,而是始终通过最新更新开始：它似乎带有奖品的一行,然后带有奖品的另一行,然后另一个带有奖品没有奖品的人(prized_id =空)……

现在我做了一些手动实验：我尝试手动更改我的表上的值(通过pgadmin),最奇怪的事情发生：如果我修改第32行,然后第45行,然后第67行,当用户再次尝试播放时,猜猜是什么,随机选取的线正是我按相反顺序更新的线：它将选择线67然后线45然后线32 ..
它甚至不会考虑可以选择的其他行(所有其他可用的机会= true).

我也尝试过不用于更新或’pg_try_advisory_xact_lock(id)’行,看起来它仍然有同样的问题.

作为管理员,我首先创建了总共20行,然后是4个获胜行,它们是最后一个被更新的行(即使在我的pgadmin屏幕上它们保持在同一行…也许在后台,postgresql将它们作为最后一次更新的顺序排序并选择它们？)这就是为什么只要选择4个这些获胜行中的一个,然后所有其他行都会跟随.

为了清楚起见,我可以通过逐行选择每个可用的机会(例如：第3行,然后第4行,然后按照我在pgadmin上看到的第5行,因为行已经完全随机地归结了奖品).问题是它没有这样做,它经常连续取得所有获胜的行….

我无言以对如何打破这种模式毫无头绪.

注意：这种模式不会在100％的情况下连续发生,但经常发生：例如,如果我有超过4个获胜行,如果我继续点击作为用户,它表现如此处所说,那就是2分钟,然后停止似乎(或者我可能是错的)表现正常然后再次&分钟将再次只选择获胜的行…

编辑1

以下是如何在商机表中注入奖品(例如,我创建奖品,其ID为21,奖品数量为3)=>它随机发送(据我所知)但只有在没有prize_id的情况下(即如果机会有prize_id =空,它可以把它放在那里)

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)

编辑2

我转到另一个更精确的问题：Postgresql 9.4 – FASTEST query to select and update on large dataset (>30M rows) with heavy writes/reads and locks

事实上,我认为问题实际上是我需要一个真正的RANDOM选择并远离ARBITRARY选秀权.

Erwin在Advisory locks or NOWAIT to avoid waiting for locked rows?已经说过了,但现在我明白了他的意思(“任意”也很重要,因为它暗示Postgresql通常会为同一个查询任意选择相同的行,这使得锁争用比真正的随机选择更大的问题可以.“postgresql可以自由选择输出’LIMIT 1’的最快方式,并且它总是选择相同的行=那些已被提升的最后一行.但我不能这样做,因为最新的更新都是Winning机会.

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

原文链接：https://www.f2er.com/postgresql/192801.html

postgresql 9.4 – 阻止应用程序始终选择最新更新的行

猜你在找的Postgre SQL相关文章