我想要一个数据库查询来改变备用状态 – JUST ONE – 并返回要使用的服务器IP.选择可以是任意的：由于服务器的状态随查询而变化,因此选择哪个备用数据库无关紧要.
是否可以将我的查询限制为仅一次更新？
这是我到目前为止：
UPDATE server_info SET status = 'active' WHERE status = 'standby' [[LIMIT 1???]] RETURNING server_ip;
Postgres不喜欢这个.我能做些什么不同的事情？
在CTE中实现选择并在UPDATE的FROM子句中连接到它.
WITH cte AS ( SELECT server_ip -- pk column or any (set of) unique column(s) FROM server_info WHERE status = 'standby' LIMIT 1 -- arbitrary pick (cheapest) ) UPDATE server_info s SET status = 'active' FROM cte WHERE s.server_ip = cte.server_ip RETURNING server_ip;
我最初在这里有一个普通的子查询,但是可以回避某些查询计划的LIMIT,因为Feike指出：
The planner may choose to generate a plan that executes a nested loop over the
LIMITingsubquery,causing moreUPDATEsthanLIMIT,e.g.:
06001
07002
The way to fix the above was to wrap the
LIMITsubquery in its own CTE,as the CTE is materialized it will not return different results on different iterations of the nested loop.
或者使用LIMIT 1的简单情况使用低相关子查询.更简单,更快：
UPDATE server_info
SET status = 'active'
WHERE server_ip = (
SELECT server_ip
FROM server_info
WHERE status = 'standby'
LIMIT 1
)
RETURNING server_ip;
具有并发性
在并发写入加载下,添加FOR UPDATE SKIP LOCKED以锁定行以避免竞争条件. Postgres 9.5中添加了SKIP LOCKED,旧版本见下文. The manual:
With
SKIP LOCKED,any selected rows that cannot be immediately locked
are skipped. Skipping locked rows provides an inconsistent view of the
data,so this is not suitable for general purpose work,but can be
used to avoid lock contention with multiple consumers accessing a
queue-like table.
UPDATE server_info
SET status = 'active'
WHERE server_ip = (
SELECT server_ip
FROM server_info
WHERE status = 'standby'
LIMIT 1
FOR UPDATE SKIP LOCKED
)
RETURNING server_ip;
如果没有符合条件的解锁行,则此查询中没有任何反应,并且您得到一个空结果.对于不加批判的操作,这意味着你已经完成了.
但是,对于关键操作,确保进行最终检查：
SELECT NOT EXISTS ( SELECT 1 FROM server_info WHERE status = 'standby' );
如果Wile不返回true,则仍在处理一行或多行,并且仍可以回滚事务.稍等一下,然后循环两个步骤：UPDATE直到你没有返回行,SELECT …直到你得到TRUE.
有关：
> Atomic UPDATE .. SELECT in Postgres
在Postgresql 9.4或更早版本中没有SKIP LOCKED
UPDATE server_info
SET status = 'active'
WHERE server_ip = (
SELECT server_ip
FROM server_info
WHERE status = 'standby'
LIMIT 1
FOR UPDATE
)
RETURNING server_ip;
尝试锁定同一行的并发事务将被阻止,直到第一个释放其锁定.
如果第一个被回滚,则下一个事务将获取锁定并正常进行;队列中的其他人继续等待.
如果第一次提交,则重新评估WHERE条件,如果它不再为TRUE(状态已更改),则CTE(有点令人惊讶地)不返回任何行.什么都没发生.当所有事务都想要更新同一行时,这是所需的行为.
但不是每个事务都想要更新下一行.而且由于我们只想更新任意(或随机)行,所以没有必要等待.
我们可以在advisory locks的帮助下解除这种情况：
UPDATE server_info
SET status = 'active'
WHERE server_ip = (
SELECT server_ip
FROM server_info
WHERE status = 'standby'
AND pg_try_advisory_xact_lock(id)
LIMIT 1
FOR UPDATE
)
RETURNING server_ip;
这样,未更新的下一行将被更新.每个事务都有一个新的行可以使用.我从Czech Postgres Wiki获得了这个技巧的帮助.
id是任何唯一的bigint列(或任何具有隐式转换的类型,如int4或int2).
如果同时对数据库中的多个表使用建议锁,请使用pg_try_advisory_xact_lock(tableoid :: int,id)消除歧义 – 此处的id是唯一的整数.
由于tableoid是一个很大的数量,它理论上可以溢出整数.如果你足够偏执,请使用(tableoid :: bigint％2147483648):: int – 为真正的偏执者留下理论上的“哈希冲突”……
此外,Postgres可以任意顺序测试WHERE条件.它可以测试pg_try_advisory_xact_lock()并在status =’standby’之前获取锁定,这可能导致对不相关的行进行额外的建议锁定,其中status =’standby’不为true.关于SO的相关问题：
> Postgres pg_try_advisory_lock blocks all records
通常,您可以忽略这一点.为了保证只有符合条件的行被锁定,您可以将谓词嵌套在上面的CTE中,或者将子查询嵌套到OFFSET 0 hack (prevents inlining)中.示例：
> Put pg_try_advisory_xact_lock() in a nested subquery?
或者(顺序扫描更便宜)将条件嵌套在CASE语句中,如：
WHERE CASE WHEN status = 'standby' THEN pg_try_advisory_xact_lock(id) END
然而,CASE技巧也会使Postgres不使用状态索引.如果这样的索引可用,则不需要额外的嵌套开始：只有符合条件的行才会在索引扫描中被锁定.
由于您无法确定每次调用都使用了索引,因此您可以：
WHERE status = 'standby' AND CASE WHEN status = 'standby' THEN pg_try_advisory_xact_lock(id) END
CASE在逻辑上是多余的,但它服务于所讨论的目的.
如果该命令是长事务的一部分,请考虑可以(并且必须)手动释放的会话级锁.因此,您可以在完成锁定行后立即解锁：pg_try_advisory_lock() and pg_advisory_unlock().The manual:
Once acquired at session level,an advisory lock is held until
explicitly released or the session ends.
有关：
