postgresql - 使用按位 AND 运算符对位串列的排除约束

Question

我读到了 Exclusion Constraints在 PostgreSQL 中，但似乎找不到在位串上使用按位运算符的方法。

我有两列(名称文本，值位(8))。我想创建一个约束，基本上是这样说的:

ADD CONSTRAINT route_method_overlap
EXCLUDE USING gist(name WITH =, value WITH &)

但这不起作用，因为:

operator &(bit,bit) is not a member of operator family "gist_bit_ops"

我认为这是因为 bit_ops 运算符 & 没有返回 boolean。但是有没有办法做我想做的事情？有没有办法强制 operator & 将其返回值转换为 bool 值？

目前使用安装了“btree_gist”扩展的 Postgres 9.1.4，全部来自 Ubuntu 12.04 存储库。但是版本无关紧要。如果上游有修复/更新，我可以从存储库安装。我仍处于设计阶段。

Answer 1

您安装了扩展 btree_gist .没有它，示例将在 name WITH = 处失败。

CREATE EXTENSION btree_gist;

btree_gist 安装的运算符类涵盖了很多运算符。不幸的是，& 运算符不在其中。显然，因为它不返回一个 boolean，而这将是一个运算符所期望的。

替代方案

我会结合使用 b 树多列索引(为了提高速度)和触发器。考虑这个在 PostgreSQL 9.1 上测试的演示:

CREATE TABLE t (
  name text 
, value bit(8)
);

INSERT INTO t VALUES ('a', B'10101010'); 

CREATE INDEX t_name_value_idx ON t (name, value);

CREATE OR REPLACE FUNCTION trg_t_name_value_inversion_prohibited()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   IF EXISTS (
      SELECT FROM t
      WHERE (name, value) = (NEW.name, ~ NEW.value)  -- example: exclude inversion
      ) THEN

       RAISE EXCEPTION 'Your text here!';
   END IF;

   RETURN NEW;
END
$func$;

CREATE TRIGGER insup_bef_t_name_value_inversion_prohibited
BEFORE INSERT OR UPDATE OF name, value  -- only involved columns relevant!
ON t
FOR EACH ROW
EXECUTE FUNCTION trg_t_name_value_inversion_prohibited();

INSERT INTO t VALUES ('a', ~ B'10101010');  -- fails with your error msg.

在 Postgres 10 或更早版本中使用:

...
EXECUTE PROCEDURE trg_t_name_value_inversion_prohibited();

参见:

• Trigger function does not exist, but I am pretty sure it does

~ is the inversion operator .

在这种情况下不需要扩展btree_gist。

我限制了 trigger to INSERT OR UPDATE OF relevant columns为了效率。

检查约束 不起作用。我引用 the manual on CREATE TABLE :

Currently, CHECK expressions cannot contain subqueries nor refer tovariables other than columns of the current row.

大胆强调我的。

应该表现很好，实际上比排除约束更好，因为维护 b-tree 索引比 GiST 索引便宜。使用基本 = 运算符的查找应该比使用 & 运算符的假设查找更快。

此解决方案不像排除约束那样万无一失，因为可以更轻松地规避触发器 - 例如，在同一事件的后续触发器中，或者如果触发器被暂时禁用。如果适用，请准备好对整个表进行额外检查。

更复杂的条件

示例触发器仅捕获 value 的反转。正如您在评论中澄清的那样，您实际上需要这样的条件:

IF EXISTS (
      SELECT FROM t
      WHERE  name = NEW.name
      AND    value & NEW.value <> B'00000000'::bit(8)
      ) THEN

这个条件稍微贵一点，但是还是可以用索引的。上面的多列索引可以工作——如果你用它的话。或者，更有效地，一个简单的名称索引:

CREATE INDEX t_name_idx ON t (name);

您评论说每个 name 最多只能有 8 个不同的行，实际上更少。所以这应该还是很快的。

终极 INSERT 性能

如果 INSERT 性能是最重要的，特别是如果许多尝试的 INSERT 失败条件，您可以做更多:创建一个物化 View ，每个 预先聚合 value姓名:

CREATE TABLE mv_t AS 
SELECT name, bit_or(value) AS value
FROM   t
GROUP  BY 1
ORDER  BY 1;

name 保证在这里是唯一的。我会在 name 上使用 PRIMARY KEY 来提供我们要查找的索引:

ALTER TABLE mv_t SET (FILLFACTOR=90);

ALTER TABLE mv_t
ADD CONSTRAINT mv_t_pkey PRIMARY KEY(name);

那么您的INSERT 可能如下所示:

WITH i(n,v) AS (SELECT 'a'::text, B'10101010'::bit(8)) 
INSERT INTO t (name, value)
SELECT n, v
FROM   i
LEFT   JOIN mv_t m ON m.name = i.n
                  AND m.value & i.v <> B'00000000'::bit(8)
WHERE  m.n IS NULL;          -- alternative syntax for EXISTS (...)

fillfactor仅当您的表获得大量更新时才有用。

在 TRIGGER AFTER INSERT OR UPDATE OF name, value OR DELETE 中更新实体化 View 中的行以使其保持最新。必须仔细权衡额外对象的成本和 yield 。很大程度上取决于您的典型负载。