与位AND运算符位串列排除约束 [英] Exclusion constraint on a bitstring column with bitwise AND operator

问题描述

所以我只是读有关排除约束 PostgreSQL中，我似乎无法找到一种方法使用的位串位运算符，我想知道，如果它是可能的。

So I was just reading about Exclusion Constraints in PostgreSQL and I couldn't seem to find a way to use bitwise operators on bitstrings, and I was wondering if it was possible.

我用例是我有一个名称：文本列和值：位（8）列。我想创建一个约束，基本上这样说：

My use case is I have a name: text column and a value: bit(8) column. And I wanted to create a constraint that basically says this:

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

但是，这因为

运营商及（比特，比特）不是经营者的家人gist_bit_ops成员

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

我想这是因为bit_ops＆放大器;运营商不返回一个布尔值。但是，有没有办法做我想要做什么？有没有办法强迫运营商的放大器; 施展它的返回值作为一个布尔

I assume this is because the bit_ops & operator doesn't return a boolean. But is there a way to do what I'm trying to do? Is there a way to coerce operator & to cast its return value as a boolean?

忘记版本号。这是9.1.4的btree_gist扩展安装，所有从Ubuntu 12.04回购。但版本并不重要。如果上游有补丁/更新，我可以从回购安装。我仍然在这个设计阶段。

Forgot the version number. This is on 9.1.4 with the "btree_gist" extension installed, all from the Ubuntu 12.04 repos. But the version doesn't matter. If there's fixes/updates upstream, I can install from the repos. I'm still in the design phase of this.

推荐答案

作为你的编辑澄清，你安装了扩展的 btree_gist 。没有它，例子已经在名称失败= 。

As your edit clarified, you installed the extension btree_gist. Without it, the example would already fail at name WITH =.

CREATE EXTENSION btree_gist;

按 btree_gist 安装操作类涵盖众多运营商。不幸的是，＆安培; 运营商不在其中。很明显，因为它不返回布尔这将可以预期操作的参赛资格。

The operator classes installed by btree_gist cover many operators. Unfortunately, the & operator is not among them. Obviously because it does not return a boolean which would be expected of an operator to qualify.

我会用B树的多列索引（速度）和触发的组合来代替。考虑这个演示中，在PostgreSQL上的 9.1 测试：

I would use a combination of a b-tree multi-column index (for speed) and a trigger instead. Consider this demo, tested on 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 AS
$func$
BEGIN
IF EXISTS (
     SELECT 1 FROM t
     WHERE (name, value) = (NEW.name, ~ NEW.value)  -- example: exclude inversion
     ) THEN

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

RETURN NEW;
END
$func$ LANGUAGE plpgsql;

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 PROCEDURE trg_t_name_value_inversion_prohibited();

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

• 〜是反转运营商。

  • ~ is the inversion operator.

    扩展 btree_gist 是的不的在此方案所需。

    The extension btree_gist is not required in this scenario.

    我限制了触发插入或相关列的UPDATE 的效率。

    A 检查约束是行不通的。我引用说明书上 CREATE TABLE ：

    A check constraint wouldn't work. I quote the manual on CREATE TABLE:

    目前，检查前pressions不能包含子查询也不能引用
      比在当前行的列的其他变量

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

    粗体重点煤矿：

    应该执行得非常好，实际上比排斥约束比较好，因为B树索引维护比GiST索引便宜。而查询基本 = 运营商应该比假设查找UPS与＆放快; 运营商

    Should perform very well, actually better than the exclusion constraint, because maintenance of a b-tree index is cheaper than a GiST index. And the look-up with basic = operators should be faster than hypothetical look-ups with the & operator.

    此解决方案不作为排除约束如安全的，因为触发器可以更容易地被规避 - 在随后的触发器上，例如，或者如果触发器是暂时禁用相同的事件。被prepared如果这些条件适用于整个表上运行额外的检查。

    This solution is not as safe as an exclusion constraint, because triggers can more easily be circumvented - in a subsequent trigger on the same event for instance, or if the trigger is disabled temporarily. Be prepared to run extra checks on the whole table if such conditions apply.

    这个例子只触发捕获值的反转。当您在您的评论澄清，你确实需要这样，而不是条件：

    The example trigger only catches the inversion of value. As you clarified in your comment, you actually need a condition like this instead:

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

    这条件是稍贵，但依然可以使用索引。从上面的多列索引将工作 - 如果你有需要也无妨。或者，会更有效，在名称的简单指标：

    This condition is slightly more expensive, but can still use an index. The multi-column index from above would work - if you have need for it anyway. Or, slightly more efficient, a simple index on name:

    CREATE INDEX t_name_idx ON t (name);
    

    当你评论，只能有每个名称最多8个不同的行，在实践中较少。因此，这应该还是快的。

    As you commented, there can only be a maximum of 8 distinct rows per name, fewer in practice. So this should still be fast.

    如果插入的性能是至关重要的，尤其是当许多试图插入操作失败的情况下，你可以做更多：建立一个物化视图pre-聚集值每名称：

    If INSERT performance is paramount, especially if many attempted INSERTs fail the condition, you could do more: create a materialized view that pre-aggregated value per name:

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

    名称保证是唯一在这里。我会使用一个 PRIMARY KEY 在名称来提供我们之后是指数：

    name is guaranteed to be unique here. I'd use a PRIMARY KEY on name to provide the index we're after:

    ALTER TABLE mv_t SET (fillfactor=90);
    
    ALTER TABLE mv_t
    ADD CONSTRAINT mv_t_pkey PRIMARY KEY(name) WITH (fillfactor=90);
    

    那么你的插入可能看起来像这样：

    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 (...)
    

    填充因子如果你的表得到了很多的更新是唯一有用的。

    The fillfactor is only useful if your table gets a lot of updates.

    在一个 TRIGGER物化视图更新行插入或名称，值或删除更新后来保持它的电流。该附加对象的成本应具有对增益进行权衡。在很大程度上取决于你的典型负载。

    Update rows in the materialized view in a TRIGGER AFTER INSERT OR UPDATE OF name, value OR DELETE to keep it current. The cost of the additional objects has to be weighed against the gain. Largely depends on your typical load.

    这篇关于与位AND运算符位串列排除约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！