SHARE锁定记录的行为，为什么聚合功能不可能？ [英] Behavior of SHARE locked records, why are aggregation functions impossible?

问题描述

在先前的问题，我询问如何从 SELECT 中排除被 UPDATE 锁定。

On the previous question, I asked about how can I exclude records from SELECT that are locked with UPDATE lock.

对我来说，不是很明显，用 SHARE LOCK 锁定的记录无法进行汇总功能。

It is not so obvious for me that on records that are locked with SHARE LOCK is not possible to make aggregation functions.

为什么？我该如何绕过这个问题？

Why is that, and how can I bypass this problem?

我正在执行的过程：

1. 使用 UPDATE锁定队列中要删除的记录锁定


2. 要为用户排除该记录，请在 SELECT上设置与之冲突的共享锁定执行 UPDATE锁定，并过滤记录


3. 对所选记录进行分页（ COUNT（）正在使用）

1. Lock records that are in the queue for delete with UPDATE lock
2. To exclude that records for user, on SELECT set SHARE lock that will have a conflict with execute UPDATE lock, and filter that records
3. Paginate that selected records (COUNT() is being used)

我唯一的选择是以dB / Redis为单位设置标志进行过滤记录？

Is my only option to set flag in dB/Redis to filter that records?

错误：

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;

原因是锁定行的查询必须能够识别出

The reason for this is that the query that locks the rows must be able to identify the row that is to be locked from the result.

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.
 */

所以可以说这是实现它所必需的。

So one could say that this is required by the way it is implemented.

这篇关于SHARE锁定记录的行为，为什么聚合功能不可能？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！