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

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

Мой вариант использования у меня есть name: text колонна и value: bit(8) колонка. И я хотел создать ограничение, которое в основном говорит это:

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

Но это не работает, так как

operator &(bit,bit) не является членом семейства операторов "gist_bit_ops"

Я предполагаю, что это потому, что оператор bit_ops & не возвращает логическое значение. Но есть ли способ сделать то, что я пытаюсь сделать? Есть ли способ заставить operator & бросить его возвращаемое значение как логическое значение?

редактировать

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

Источник

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

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

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

Более сложное состояние

Пример триггера ловит только инверсию 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 Меньше на практике. Так что это все еще должно быть быстро.

Максимальная производительность INSERT

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

Источник
Другие вопросы по тегам