Put pg_try_advisory_xact_lock () in a nested subquery?

Question

In my Ruby on Rails 4 application, I have this query on a Postgres 9.4 database:

@chosen_opportunity = Opportunity.find_by_sql(
  " UPDATE \"opportunities\" s
    SET opportunity_available = false
    FROM (
          SELECT \"opportunities\".*
          FROM   \"opportunities\"
          WHERE  ( deal_id = #{@deal.id}
          AND    opportunity_available = true 
          AND    pg_try_advisory_xact_lock(id) )
          LIMIT  1
          FOR    UPDATE
          ) sub
    WHERE       s.id = sub.id
    RETURNING   sub.prize_id, sub.id"
)

Very inspired by this related answer to dba.SE .

But here ( Postgres pg_try_advisory_lock blocks all records ), they say, if I’m not mistaken, that I should not use it pg_try_advisory_lock()insideWHERE , because I would name it once per line in the whole set that is scanned (as part of the filtering that appears in the sentence where).

, (, LIMIT), available = true available = false, , , , .

pg_try_advisory_lock() WHERE? ?

Answer 1

.
Postgres 9.5 ( ) SKIP LOCKED :

• Postgres UPDATE... LIMIT 1

---

:

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;

• .
• option_available - , opportunity_available = true opportunity_available
• * , id .

, , . .

, CTE OFFSET 0 hack ( ), pg_try_advisory_xact_lock() :

UPDATE opportunities s
SET    opportunity_available = false
FROM (
   SELECT id
   FROM  ( 
      SELECT id
      FROM   opportunities
      WHERE  deal_id = #{@deal.id}
      AND    opportunity_available
      AND    pg_try_advisory_xact_lock(id)
      OFFSET 0
      ) sub1
   WHERE  pg_try_advisory_xact_lock(id)
   LIMIT  1
   FOR    UPDATE
   ) sub2
WHERE     s.id = sub.id
RETURNING s.prize_id, s.id;

, .

, ,

"" , , , :

CREATE INDEX opportunities_deal_id ON opportunities (deal_id)
WHERE opportunity_available;

EXPLAIN, , Postgres . , pg_try_advisory_xact_lock(id) , ( ) , . . .

, . , . , ? ?

, , .