Czy PostgreSQL może mieć ograniczenie unikalności elementów tablicy?

Question

Czy PostgreSQL może mieć ograniczenie unikalności elementów tablicy?

Próbuję wymyślić schemat PostgreSQL dla danych hosta, który jest obecnie w sklepie LDAP. Częścią tych danych jest lista nazw hostów, które może mieć maszyna, a ten atrybut jest zazwyczaj kluczem, którego większość ludzi używa do znalezienia rekordów hosta.

Jedną z rzeczy, które chciałbym uzyskać z przenoszenia tych danych do RDBMS, jest możliwość ustawienia ograniczenia unikalności w kolumnie Nazwa hosta, aby nie można było przypisać zduplikowanych nazw hostów. Byłoby to łatwe, gdyby hosty mogły mieć tylko jedną nazwę, ale ponieważ mogą mieć więcej niż jeden, jest to bardziej skomplikowane.

Zdaję sobie sprawę, że w pełni znormalizowanym sposobem na to byłoby posiadanie tabeli nazw hostów z obcym kluczem skierowanym z powrotem do tabeli hostów, ale chciałbym uniknąć konieczności wykonywania złączeń dla najprostszych zapytań:

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

Pomyślałem, że użycie tablic PostgreSQL może do tego zadziałać i na pewno ułatwią proste zapytania:

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

Kiedy ustawiam ograniczenie unikalności na nazwach hostów atrybut, choć, oczywiście traktuje całą listę nazw jako unikalną wartość zamiast każdej nazwy. Czy istnieje sposób, aby każda nazwa była unikalna w każdym rzędzie?

Jeśli nie, czy ktoś zna inne podejście do modelowania danych, które miałoby większy sens?

16

database-design arrays postgresql unique-constraint ldap

Author: Erwin Brandstetter, 2011-11-05

Source

2 answers

Na wypadek, gdyby ktoś jeszcze potrzebował tego, co było w pierwotnym pytaniu:

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

A to daje:

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

Sprawdzone w Postgres 9.5 na Windows.

Zauważ, że spowoduje to utworzenie indeksu za pomocą operatora &&. Więc kiedy pracujesz z testtable, sprawdzanie ARRAY[x] && refs byłoby o wiele szybsze niż x = ANY( refs ) ze względu na wewnętrzne indeksowanie Postgres.

P. S. generalnie zgadzam się z powyższą odpowiedzią, ale takie podejście jest po prostu fajną opcją, gdy nie trzeba naprawdę troska o wydajność i takie tam.

5

Author: volvpavl,
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-02-06 20:18:09

score 24 · Accepted Answer

The righteous path

Powinieneś rozważyć normalizację twojego schematu. Nie jest konieczne, aby każdy "przyłączył się do nawet najprostszego zapytania". Create a VIEW za to.

Tabela może wyglądać tak:

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

Zastępczy klucz podstawowy hostname_id jest opcjonalny . Wolę mieć. W Twoim przypadku hostname może być kluczem głównym. Ale wiele operacji jest szybszych dzięki prostemu, niewielkiemu kluczowi integer. Tworzenie klucza obcego ograniczenie do linku do tabeli host.
Utwórz taki widok:

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+

Zaczynając od pg 9.1, klucz podstawowy w GROUP BY obejmuje wszystkie kolumny tej tabeli w liście SELECT. uwagi do wydania wersji 9.1:

Zezwalaj na kolumny nie-GROUP BY na liście docelowej zapytania, gdy główny klucz jest określony w klauzuli GROUP BY

Zapytania mogą używać widoku jak tabeli. Szukanie nazwy hosta będzie much faster this way:

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

^{pod warunkiem, że masz indeks host(host_id), który powinien być tak, jak powinien być kluczem głównym. Dodatkowo, ograniczenie UNIQUE na hostname(hostname) implementuje drugi potrzebny indeks automatycznie.}

W Postgres 9.2+ indeks wielokolumnowy byłby jeszcze lepszy, jeśli można uzyskać skanowanie tylko indeksowe out of it:

CREATE INDEX hn_multi_idx ON hostname (hostname, host_id)

Zaczynając od Postgres 9.3, przydałby ci się MATERIALIZED VIEW, okoliczności na to pozwalają. Zwłaszcza, jeśli czytasz znacznie częściej niż piszesz do tabeli.

The dark side (what you actually asked)

Jeśli nie przekonam Cię o słusznej ścieżce, pomogę również ciemnej stronie. Jestem elastyczny. :)

Oto demo, jak wymusić wyjątkowość nazw hostów. Używam tabeli hostname do zbierania nazw hostów i wyzwalacza na stole host, aby być na bieżąco. Unikalne naruszenia powodują błąd i przerwać operację.

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

Funkcja wyzwalania

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;

Trigger:

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

SQL Fiddle z uruchomieniem próbnym.

Użyj indeksu GIN w kolumnie tablicy host.hostnames I operatory tablicy do pracy z nim: