为什么下面的查询返回超过limit条记录？大约有272条记录存在，但当提供的限制小于该限制时，它总是更新并返回所有272条记录。
UPDATE thing
SET status = 'status_2'
WHERE thing.thing_id in (
SELECT thing.thing_id
FROM thing
WHERE thing.status = ('status_1') AND
thing.c_id = ('c_id___1') AND
thing.name LIKE ('protocol://some/url/location/%')
ORDER BY thing.status_1_date LIMIT (150) FOR UPDATE
) AND
thing.c_id = ('c_id___1') AND
thing.name LIKE ('protocol://some/url/location/%')
RETURNING *用替代尝试观察到相同的结果：
UPDATE thing
SET status = 'status_2'
FROM (
SELECT thing.thing_id
FROM thing
WHERE thing.status = ('status_1') AND
thing.c_id = ('c_id___1') AND
thing.name LIKE ('protocol://some/url/location/%')
ORDER BY thing.status_1_date LIMIT (150) FOR UPDATE
) AS temp
WHERE thing.thing_id = temp.thing_id AND
thing.c_id = ('c_id___1') AND
thing.name LIKE ('protocol://some/url/location/%')
RETURNING *问题概述：我需要选择最多limit thing_ids，并更新所有包含thing_ids子集的记录。对于我正在测试的这组记录，没有重复的thing_id，但对于其他记录，可能会有。
更新：为了证明没有重复，我运行了以下查询：
SELECT COUNT(thing.thing_id)
FROM thing
WHERE thing.c_id = ('c_id___1') AND
thing.name LIKE ('protocol://some/url/location/%'))
GROUP BY thing.thing_id```272.第272章我的世界
更新：我能够推导出一个工作解决方案，但我将保留这个问题，因为我想了解前两个实现的问题。
新实现：
UPDATE thing
SET status = 'status_2'
WHERE thing.thing_id in (
SELECT a.thing_id
FROM thing a INNER
JOIN thing b on b.thing_id = a.thing_id
WHERE a.status = ('status_1') AND a.c_id = ('c_id___1') AND a.name LIKE ('protocol://some/url/location/%')
ORDER BY a.status_1_date, a.name LIMIT (150) FOR UPDATE
) RETURNING *更新：事实证明，连接queyr也不是我所需要的。它只会在unique thing.thing_ids的数量等于行数时更新LIMIT行。如果有重复的thing_ids，它将排除thing_ids，以便更新的行数小于limit。
这是查询的联接版本的查询计划：查询计划
Update on thing (cost=31977.16..47319.15 rows=1 width=764)
" -> Hash Semi Join (cost=31977.16..47319.15 rows=1 width=764)"
" Hash Cond: ((thing.thing_id)::text = (""ANY_subquery"".thing_id)::text)"
" -> Seq Scan on thing (cost=0.00..14776.23 rows=215523 width=190)"
" -> Hash (cost=31977.15..31977.15 rows=1 width=92)"
" -> Subquery Scan on ""ANY_subquery"" (cost=31977.13..31977.15 rows=1 width=92)"
" -> Limit (cost=31977.13..31977.14 rows=1 width=134)"
" -> LockRows (cost=31977.13..31977.14 rows=1 width=134)"
" -> Sort (cost=31977.13..31977.13 rows=1 width=134)"
" Sort Key: a.status_1_date, a.name"
" -> Hash Join (cost=16392.67..31977.12 rows=1 width=134)"
" Hash Cond: ((b.thing_id)::text = (a.thing_id)::text)"
" -> Seq Scan on thing b (cost=0.00..14776.23 rows=215523 width=40)"
" -> Hash (cost=16392.65..16392.65 rows=1 width=128)"
" -> Seq Scan on thing a (cost=0.00..16392.65 rows=1 width=128)"
" Filter: (((name)::text ~~ 'protocol://path/to/thing/%'::text) AND ((c_id)::text = 'c_id_1'::text) AND ((status)::text = 'status_2'::text))"查询表创建：
CREATE TABLE IF NOT EXISTS thing (
name VARCHAR(255) PRIMARY KEY,
thing_id VARCHAR(255),
c_id VARCHAR(255),
status VARCHAR(255),
etag VARCHAR(255),
last_modified VARCHAR(255),
size VARCHAR(255),
status_1_date DATE
);插入示例数据的查询：
INSERT INTO thing (name,thing_id, c_id,status, etag, last_modified, size, status_1_date)
values
('protocol://full/path/to/thing/thing_1.file1', 'thing_1','c_id', 'status_1', 'etag', '1111', 200, '2023-09-29 09:00:01'),
('protocol://full/path/to/thing/thing_1.file2', 'thing_1','c_id', 'status_1', 'etag', '1111', 200, '2023-09-29 09:00:02'),
('protocol://full/path/to/thing/thing_2.file1', 'thing_2','c_id', 'status_1', 'etag', '1111', 200, '2023-09-29 09:00:03'),
('protocol://full/path/to/thing/thing_2.file2', 'thing_2','c_id', 'status_1', 'etag', '1111', 200, '2023-09-29 09:00:04'),
('protocol://full/path/to/thing/thing_2.file3', 'thing_2','c_id', 'status_1', 'etag', '1111', 200, '2023-09-29 09:00:05'),
('protocol://full/path/to/thing/thing_3.file1', 'thing_3','c_id', 'status_1', 'etag', '1111', 200, '2023-09-29 09:00:06');对于LIMIT 2，要返回的期望结果如下：
问题1：
('protocol://full/path/to/thing/thing_1.file1', 'thing_1','c_id', 'status_2', 'etag', '1111', 200, '2023-09-29 09:00:01'),
('protocol://full/path/to/thing/thing_1.file2', 'thing_1','c_id', 'status_2', 'etag', '1111', 200, '2023-09-29 09:00:02'),
('protocol://full/path/to/thing/thing_2.file1', 'thing_2','c_id', 'status_2', 'etag', '1111', 200, '2023-09-29 09:00:03'),
('protocol://full/path/to/thing/thing_2.file2', 'thing_2','c_id', 'status_2', 'etag', '1111', 200, '2023-09-29 09:00:04'),
('protocol://full/path/to/thing/thing_2.file3', 'thing_2','c_id', 'status_2', 'etag', '1111', 200, '2023-09-29 09:00:05'),问题2：
('protocol://full/path/to/thing/thing_3.file1', 'thing_3','c_id', 'status_2', 'etag', '1111', 200, '2023-09-29 09:00:06')
1条答案
按热度按时间slmsl1lt1#
它是子查询中的
select for update。A thread on pgsql-bugs解释了.这里的问题是，原则上，子SELECT会为外部查询的每一行重新执行，并且您不能保证每次执行都会返回 * 相同 * 的单行。普通的查询执行将提供这样的保证，但是您正在对一个正在被并发修改（包括由该查询本身修改）的表使用FOR UPDATE，因此保证消失了。
你可能会认为，由于子SELECT与外部查询不相关，因此不需要多次执行它。但这是一种优化，不是保证语义的一部分。如果在您的系统上看不到这个查询的EXPLAIN，我们就无法知道它是否是这样做的（尽管您抱怨的事实表明它不是这样做的）。
我们可以从
explain中看到子查询位于嵌套循环中。later in the thread有更详细的解释。
seqscan+sort将查找并返回在查询开始时满足“过滤”条件 * 的所有行。Locktool节点将获取其中的第一个并锁定它，这将包括查找和锁定由于并发更新而存在的行的任何更新版本。如果有更新的版本，它会重新检查该版本是否仍然满足过滤条件（通过一些我们不需要在这里讨论的魔术）。如果是，它将该行返回到LIMIT节点，LIMIT节点将该行返回，然后声明它完成了，所以我们只找到并锁定了一行。但是，如果第一行已经更新为 doesn't 满足筛选条件的状态，则Locktool节点将前进到seqscan+sort输出的下一行，并锁定并重新测试那一行。重复此操作，直到找到仍满足锁定后筛选条件的行。
因此，很容易看出并发更新是如何导致该查询锁定N行的，其中N大于1。但这本身并不能很好地解释您所说的查询锁定 * 所有 * 行的情况。所有这些事务都必须同时更新为不再满足过滤条件的状态，而这似乎不太可能因为其他几个事务单独执行相同类型的查询而发生。
他们的建议是用CTE代替它。一般来说，使用CTE而不是子查询FROM是个好建议，即使这样更容易阅读。
注意，如果
thing被赋予主键，查询将更快更简单。然后您就可以识别单独的行，而不需要重复where子句。Demonstration。