关于上一个问题,我询问如何从SELECT
锁定中UPDATE
锁定的记录中排除记录。
对于我来说,并不是很明显,在锁定了的记录上SHARE LOCK
不可能进行聚合功能。
为什么会这样,我如何绕过这个问题?
我正在做的过程:
UPDATE
锁定进行删除SELECT set SHARE lock
,将与execute冲突UPDATE lock
,并过滤该记录COUNT()
正在使用)我唯一的选择是以dB / Redis设置标志以过滤该记录吗?
我得到的错误:
PG::FeatureNotSupported: ERROR: FOR SHARE is not allowed with aggregate functions
: SELECT COUNT(*) FROM "groups" WHERE (groups.deleted_at IS NULL) FOR SHARE OF groups SKIP LOCKED
子选择可以帮助您:
SELECT COUNT(*)
FROM (SELECT 1
FROM groups
WHERE groups.deleted_at IS NULL
FOR SHARE SKIP LOCKED) AS q;
原因是锁定行的查询必须能够从结果中识别要锁定的行。
src/backend/optimizer/plan/planner.c
有此代码注释:
/*
* We've got trouble if FOR [KEY] UPDATE/SHARE appears inside
* grouping, since grouping renders a reference to individual tuple
* CTIDs invalid. This is also checked at parse time, but that's
* insufficient because of rule substitution, query pullup, etc.
*/
因此,可以说这是实现它所必需的。
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句