Postgresql 9.4 - prevent the application from always choosing the last updated lines

Question

I have an application (Ruby on Rails 4) and using a postgresql query that mainly looks at the Opportunity table, randomly search among rows where there is an “available = true” option and update the selected rows with “available = false” ". Each time the user clicks on the" try opportunity ", the application uses the request below.

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

I struggled for 5 days, but now I managed to understand how he behaves (badly): now I need to know how to change it.

Indeed, the application “gets a little crazy” and selects (and updates the row) the last updated one.

,

• 20 ()

• , id = 45 = 4. ( ) 4 = 45.

, , , : , , , ( prized_id = empty)...

: ( pgadmin), : 32, 45, 67, , , , , , : 67, line45, 32.. , ( , = true).

"pg_try_advisory_xact_lock (id)", , , .

, 20 , 4 , , ( pgadmin ... , backround, postgresql ?) , 4 , .

, , , (: 3, 4, 5 , pgadmin, ). , , ....

, .

: 100% , : , 4 , 2 , , , , ( ) , , ...

1

Opportunity (, , 21, = 3) = > ( ), , _ (.. pr_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- ( Randumb: github.com/spilliton/randumb)

2

: Postgresql 9.4 - FASTEST ( > 30M ) /

, , , RANDOM ARBITRARY.

Erwin NOWAIT, ?, , ( "" , , Postgresql, , , , ". postgresql " LIMIT 1 ", = , . , - Winning.

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;