PostgreSQL实现优先按指定条件更新，无匹配则回退原有选择逻辑

我们可以通过在子查询中增加优先级排序的方式，单次查询完成「优先匹配csv=true任务，无结果时自动回退原有逻辑」的需求，不需要执行多次UPDATE，仅在无csv=true符合条件任务时才会扫描其他任务：

UPDATE "job" 
SET pending=true, completor='{client.uuid}' 
WHERE "number" IN 
    (
     SELECT "number" FROM "job" 
     WHERE pending=false AND closed=false AND gpu=false
     -- 优先按csv倒序，csv=true的任务会排在最前面
     ORDER BY csv DESC, RANDOM() LIMIT 1
     FOR UPDATE SKIP LOCKED
    )
  AND pending=false AND closed=false AND gpu=false
;

逻辑说明 #

• 排序规则csv DESC保证所有符合基础条件的csv=true任务优先级高于其他任务，只要存在符合条件的csv=true任务，一定会优先被选中
• 同优先级内用RANDOM()保持原有的随机选取逻辑
• 保留了原查询外层的状态校验逻辑，避免子查询取数到UPDATE执行的间隙，其他事务修改任务状态导致的脏更新
• PostgreSQL的查询优化器会在排序取到第一条符合条件的csv=true任务后停止扫描，不会额外扫描非csv=true的任务，符合「仅必要时执行第二逻辑」的要求

如果你需要更严格的拆分两个查询的执行逻辑（确保csv=true的查询完全执行无结果后，才触发原逻辑查询），可以使用CTE的方式实现：

WITH csv_job AS (
    SELECT "number" FROM "job" 
    WHERE pending=false AND closed=false AND gpu=false AND csv=true
    ORDER BY RANDOM() LIMIT 1
    FOR UPDATE SKIP LOCKED
),
fallback_job AS (
    SELECT "number" FROM "job" 
    WHERE pending=false AND closed=false AND gpu=false
    -- 仅当csv_job无结果时才会执行该子查询
    WHERE NOT EXISTS (SELECT 1 FROM csv_job)
    ORDER BY RANDOM() LIMIT 1
    FOR UPDATE SKIP LOCKED
)
UPDATE "job" 
SET pending=true, completor='{client.uuid}' 
WHERE "number" IN (SELECT "number" FROM csv_job UNION ALL SELECT "number" FROM fallback_job)
  AND pending=false AND closed=false AND gpu=false
;

两种方案对比 #

• 单排序方案性能更优，仅需一次表扫描即可完成逻辑
• CTE拆分方案逻辑更直观，可分别针对两个查询加独立的过滤条件、索引优化，适合后续逻辑需要单独调整的场景

内容的提问来源于stack exchange，提问作者Theo Coombes