¿Puede PostgreSQL tener una restricción de exclusividad en los elementos de la matriz?

Estoy tratando de crear un esquema de PostgreSQL para los datos de host que se encuentran actualmente en una tienda LDAP. Parte de esa información es la lista de nombres de host que puede tener una máquina, y ese atributo generalmente es la clave que la mayoría de la gente usa para encontrar los registros de host.

Una cosa que me gustaría obtener al mover estos datos a un RDBMS es la capacidad de establecer una restricción de exclusividad en la columna de nombre de host para que no se puedan asignar nombres de host duplicados. Esto sería fácil si los hosts solo tuvieran un nombre, pero dado que pueden tener más de uno, es más complicado.

Me doy cuenta de que la forma completamente normalizada de hacer esto sería tener una tabla de nombres de host con una clave externa que señale la tabla de hosts, pero me gustaría evitar que todos tengan que hacer combinaciones incluso para la consulta más simple:

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

Pensé que el uso de matrices PostgreSQL podría funcionar para esto, y ciertamente hacen simples las consultas simples:

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

Sin embargo, cuando establezco una restricción de exclusividad en el atributo de nombres de host, por supuesto trata la lista completa de nombres como el valor único en lugar de cada nombre. ¿Hay alguna manera de hacer que cada nombre sea único en cada fila?

Si no, ¿alguien sabe de otro enfoque de modelado de datos que tendría más sentido?

El camino correcto

Es posible que desee reconsiderar la normalización de su esquema. No es necesario que todos se “unan incluso para la consulta más simple”. Crea una VIEW para eso.

La tabla podría verse así:

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

La clave primaria sustituta hostname_id es opcional . Yo prefiero tener uno. En su caso, el hostname podría ser la clave principal. Pero muchas operaciones son más rápidas con una clave integer simple y pequeña. Cree una restricción de clave externa para vincular al host tabla.
Crea una vista como esta:

 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+ 

Comenzando con la página 9.1 , la clave principal en GROUP BY cubre todas las columnas de esa tabla en la lista SELECT . Las notas de la versión para la versión 9.1 :

Permitir columnas que no sean GROUP BY en la lista de destino de la consulta cuando se especifica la clave primaria en la cláusula GROUP BY

Las consultas pueden usar la vista como una tabla. La búsqueda de un nombre de host será mucho más rápida de esta manera:

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

Siempre que tenga un índice en el host(host_id) , que debería ser el caso, ya que debería ser la clave principal. Además, la restricción UNIQUE en el hostname(hostname) de hostname(hostname) implementa el otro índice necesario automáticamente.

En Postgres 9.2+, un índice de columnas múltiples sería aún mejor si puede obtener un escaneo index-only fuera de él:

 CREATE INDEX hn_multi_idx ON hostname (hostname, host_id) 

Comenzando con Postgres 9.3 , podría usar una MATERIALIZED VIEW , si las circunstancias lo permiten. Especialmente si lees mucho más a menudo de lo que escribes en la mesa.

El lado oscuro (lo que realmente pediste)

Si no puedo convencerte del camino correcto, también te ayudaré en el lado oscuro. Soy flexible. 🙂

Aquí hay una demostración de cómo hacer que los nombres de host sean únicos. Utilizo un hostname de hostname tabla para recostackr nombres de host y un desencadenador en el host tabla para mantenerlo actualizado. Las violaciones únicas provocan un error y abortan la operación.

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

Función de disparo

 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 = dx; 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; 

Desencadenar:

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

SQL Fiddle con ejecución de prueba.

Use un índice GIN en la columna de matriz host.hostnames y operadores de matriz para trabajar con él:

  • ¿Por qué no se usa mi índice de matriz PostgreSQL (Rails 4)?
  • Verifique si alguna de una matriz dada de valores está presente en una matriz de Postgres

En caso de que alguien todavía necesite lo que estaba en la pregunta original:

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

y esto te daría:

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

Marcado en Postgres 9.5 en Windows.

Tenga en cuenta que esto crearía un índice usando el operador && . Por lo tanto, cuando trabaje con testtable , sería más rápido comprobar ARRAY[x] && refs que x = ANY( refs ) debido a la indexación interna de Postgres.

PD En general, estoy de acuerdo con la respuesta anterior, pero este enfoque es solo una buena opción cuando no tienes que preocuparte por el rendimiento y demás.