Ограничение исключения для столбца цепочки битов с побитовым оператором AND

Так что я просто читал оОграничения исключения в PostgreSQL я не смог найти способ использовать побитовые операторы для цепочек битов, и мне было интересно, возможно ли это.

Мой вариант использования у меня естьname: text колонна иvalue: bit(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 & amp; Оператор не возвращает логическое значение. Но есть ли способ сделать то, что я пытаюсь сделать? Есть ли способ заставитьoperator & бросить его возвращаемое значение как логическое значение?

Edit

Забыли номер версии. Это на 9.1.4 с "btree_gist" расширение установлено, все из репозиториев Ubuntu 12.04. Но версия не имеет значения. Если есть исправления / обновления, я могу установить их из репозиториев. Я все еще на стадии разработки этого.

 Erwin Brandstetter20 июн. 2012 г., 22:03
Важно предоставить свой номер версии с дополнительными вопросами, как этот.

Ответы на вопрос(1)

Решение Вопроса

Как пояснили ваши правки, вы установили расширениеbtree_gist, Без этого пример уже потерпел бы неудачу вname WITH =.

CREATE EXTENSION btree_gist;

Классы операторов, установленныеbtree_gist охватывают много операторов. К сожалению,& Оператор не входит в их число. Очевидно, потому что это не возвращаетboolean что можно ожидать от оператора для квалификации.

Alternative solution

Я бы использовал комбинацию б-дереваmulti-column index (для скорости) иtrigger вместо. Рассмотрим это демо, протестированное на PostgreSQL9.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.

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:

Должен работать очень хорошо, на самом деле лучше, чем ограничение исключения, потому что поддержание индекса b-дерева дешевле, чем индекса GiST. И поиск с основными= операторы должны быть быстрее, чем гипотетические поиски с& оператор.

Это решение не так безопасно, как ограничение исключения, поскольку триггеры легче обойти - например, в последующем триггере для того же события или если триггер временно отключен. Будьте готовы выполнить дополнительные проверки всей таблицы, если такие условия применяются.

More complex condition

Пример триггера ловит только инверсиюvalue, Как вы пояснили в своем комментарии, вам на самом деле нужно следующее условие:

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

Это условие немного дороже, но все же можно использовать индекс. Многостолбцовый индекс сверху сработал бы - если вам все равно это нужно. Или, чуть более эффективный, простой индекс по имени:

CREATE INDEX t_name_idx ON t (name);

Как вы прокомментировали, может быть максимум 8 отдельных строк наnameМеньше на практике. Так что это все еще должно быть быстро.

Ultimate INSERT performance

ЕслиINSERT производительность имеет первостепенное значение, особенно если многие попытки INSERT не соответствуют условию, вы можете сделать больше: создать материализованное представление, которое предварительно агрегируетсяvalue вname:

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

name гарантированно будет уникальным здесь. Я бы использовалPRIMARY KEY наname чтобы предоставить индекс, который мы ищем после:

ALTER TABLE mv_t SET (fillfactor=90);

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

Тогда ваш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 чтобы держать это в курсе. Стоимость дополнительных объектов должна быть сопоставлена с прибылью. Во многом зависит от вашей типичной нагрузки.

 Falmarri21 июн. 2012 г., 22:53
Очень подробный ответ, спасибо. Я думаю, что более сложный триггер будет работать отлично, так как вставки должны быть «относительно» редко по сравнению с выбирает.
 Falmarri21 июн. 2012 г., 00:26
Хм, интересно. Используется ли здесь триггер для скорости? В отличие от ограничения CHECK? Что я хочу предотвратить, так это частичное совпадение. Так что для любых 2 столбцов с равнымиname поля, val1 & amp; val2 должен =B'00000000', Вот почему я полагал, что ограничение исключения будет работать здесь. Поэтому я не думаю, что индексирование~ value будет конструктивным. Кроме того, с этим условием, имейте в виду, что может быть только 8 столбцов с соответствующимиname поля.
 21 июн. 2012 г., 03:31
@Falmarri: я исправил свой ответ с вашими разъяснениями, рассмотрением проверочных ограничений и добавлением других.

Ваш ответ на вопрос