Может ли PostgreSQL иметь ограничение уникальности для элементов массива?

Я пытаюсь придумать схему PostgreSQL для данных хоста, которая в данный момент находится в хранилище LDAP. Часть этих данных - это список имен хостов, которые может иметь машина, и этот атрибут обычно является ключом, который большинство людей используют для поиска записей хоста.

Одна вещь, которую я хотел бы извлечь из перемещения этих данных в СУБД, - это возможность установить ограничение уникальности для столбца имени хоста, чтобы дублирующие имена хостов не могли быть назначены. Это было бы легко, если бы хосты могли иметь только одно имя, но, поскольку они могут иметь более одного имени, это более сложно.

Я понимаю, что полностью нормализованный способ сделать это состоит в том, чтобы иметь таблицу имен хостов с внешним ключом, указывающим обратно на таблицу хостов, но я бы хотел, чтобы всем не нужно было выполнять объединения даже для самого простого запроса:

select hostnames.name,hosts.*
  from hostnames,hosts
 where hostnames.name = 'foobar'
   and hostnames.host_id = hosts.id;

Я подумал, что для этого могут работать массивы PostgreSQL, и они, безусловно, упрощают простые запросы:

select * from hosts where names @> '{foobar}';

Однако, когда я устанавливаю ограничение уникальности для атрибута hostnames, он, конечно, обрабатывает весь список имен как уникальное значение вместо каждого имени. Есть ли способ сделать каждое имя уникальным в каждой строке?

Если нет, знает ли кто-нибудь другой подход к моделированию данных, который был бы более логичным?

Источник

2 ответа

Решение

Праведный путь

Возможно, вы захотите пересмотреть нормализацию вашей схемы. Не обязательно для всех "присоединяться даже к самому простому запросу". Создать VIEWдля этого.

Таблица может выглядеть так:

CREATE TABLE hostname (
 hostname_id serial PRIMARY KEY
,host_id     int    REFERENCES host(host_id) ON UPDATE CASCADE ON DELETE CASCADE
,hostname    text   UNIQUE
);

Суррогатный первичный ключhostname_idне являетсяобязательным. Я предпочитаю иметь один. В твоем случаеhostnameможет быть первичным ключом. Но многие операции выполняются быстрее с помощью простого, небольшого integer ключ. Создать ограничение внешнего ключа для ссылки на таблицу host,
Создайте вид как это:

CREATE VIEW v_host AS
SELECT h.*
      ,array_agg(hn.hostname) AS hostnames
--    ,string_agg(hn.hostname, ', ') AS hostnames  -- text instead of array
FROM   host h
JOIN   hostname hn USING (host_id)
GROUP  BY h.host_id;   -- works in v9.1+

Начиная с стр.9.1,первичный ключ вGROUP BYохватывает все столбцы этой таблицы вSELECTсписок. Примечания к выпуску для версии 9.1:

Разрешить неGROUP BYстолбцы в списке целей запроса, когда первичный ключ указан вGROUP BYпункт

Запросы могут использовать представление как таблицу. Поиск имени хоста будет гораздо быстрее:

SELECT *
FROM   host h
JOIN   hostname hn USING (host_id)
WHERE  hn.hostname = 'foobar';

Если у вас есть индекс наhost(host_id), что должно быть так, как это должно быть первичным ключом.Плюс UNIQUE ограничение наhostname(hostname)реализует другой необходимый индекс автоматически.

В Postgres9.2+ многоколонный индекс был бы еще лучше, если бы из него можно было получить сканирование только по индексу:

CREATE INDEX hn_multi_idx ON hostname (hostname, host_id)

Начиная с Postgres9.3, вы можете использоватьMATERIALIZED VIEW, обстоятельства позволяют. Особенно, если вы читаете намного чаще, чем пишете в таблицу.

Темная сторона (что вы на самом деле спросили)

Если я не смогу убедить вас в праведном пути, я тоже помогу темной стороне. Я гибкий.:)

Вот демонстрация того, как обеспечить уникальность имен хостов. Я использую стол hostname собирать имена хостов и триггер на столе host чтобы держать это в курсе. Уникальные нарушения приводят к ошибке и отмене операции.

CREATE TABLE host(hostnames text[]);
CREATE TABLE hostname(hostname text PRIMARY KEY);  --  pk enforces uniqueness

Функция запуска

CREATE OR REPLACE FUNCTION trg_host_insupdelbef()
  RETURNS trigger AS
$func$
BEGIN
-- split UPDATE into DELETE & INSERT
IF TG_OP = 'UPDATE' THEN
   IF OLD.hostnames IS DISTINCT FROM NEW.hostnames THEN  -- keep going
   ELSE RETURN NEW;  -- exit, nothing to do
   END IF;
END IF;

IF TG_OP IN ('DELETE', 'UPDATE') THEN
   DELETE FROM hostname h
   USING  unnest(OLD.hostnames) d(x)
   WHERE  h.hostname = d.x;

   IF TG_OP = 'DELETE' THEN RETURN OLD;  -- exit, we are done
   END IF;
END IF;

-- control only reaches here for INSERT or UPDATE (with actual changes)
INSERT INTO hostname(hostname)
SELECT h
FROM   unnest(NEW.hostnames) h;

RETURN NEW;
END
$func$ LANGUAGE plpgsql;

Спусковой крючок:

CREATE TRIGGER host_insupdelbef
BEFORE INSERT OR DELETE OR UPDATE OF hostnames ON host
FOR EACH ROW EXECUTE PROCEDURE trg_host_insupdelbef();

SQL Fiddle с тестовым прогоном.

Используйте индекс GIN для столбца массива host.hostnames и операторы массива для работы с ним:

Источник

Если кому-то все еще нужно то, что было в первоначальном вопросе:

CREATE TABLE testtable(
    id serial PRIMARY KEY,
    refs integer[],
    EXCLUDE USING gist( refs WITH && )
);

INSERT INTO testtable( refs ) VALUES( ARRAY[100,200] );
INSERT INTO testtable( refs ) VALUES( ARRAY[200,300] );

и это даст вам:

ERROR:  conflicting key value violates exclusion constraint "testtable_refs_excl"
DETAIL:  Key (refs)=({200,300}) conflicts with existing key (refs)=({100,200}).

Проверено в Postgres 9.5 на Windows.

Обратите внимание, что это создаст индекс с помощью оператора &&, Поэтому, когда вы работаете с testtable было бы в разы быстрее проверить ARRAY[x] && refs чем x = ANY( refs ) из-за индексации внутренних органов Postgres.

PS В целом я согласен с приведенным выше ответом, но такой подход является хорошим вариантом, когда вам не нужно беспокоиться о производительности и прочем.

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