Exclusion constraint on a bitstring column with bitwise AND operator

Question

I read about Exclusion Constraints in PostgreSQL but can't seem to find a way to use bitwise operators on bitstrings.

I have two columns (name text, value bit(8)). And I want to create a constraint that basically says this:

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

But this doesn't work since:

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

I assume this is because the bit_ops operator & doesn't return 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?

Currently using Postgres 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.

Answer 1

You installed the extension btree_gist. Without it, the example would already fail at name WITH =.

CREATE EXTENSION 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.

Alternative solution

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

In Postgres 10 or older use instead:

...
EXECUTE PROCEDURE trg_t_name_value_inversion_prohibited();

See:

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

~ is the inversion operator.

The extension btree_gist is not required in this scenario.

I restricted the trigger to INSERT OR UPDATE OF relevant columns for efficiency.

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

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

Bold emphasis mine.

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.

This solution is not as bullet-proof 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.

More complex condition

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 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 use for it anyway. Or, more efficiently, a simple index on name:

CREATE INDEX t_name_idx ON t (name);

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

Ultimate INSERT performance

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;

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);

Then your INSERT could look like this:

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.

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