arrays - ¿Puede PostgreSQL tener una restricción de unicidad en los elementos de la matriz?

database-design ldap

Question

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

Una cosa que me gustaría evitar al mover estos datos a un RDBMS es la capacidad de establecer una restricción de unicidad 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 pudieran tener un nombre, pero como pueden tener más de uno, es más complicado.

Me doy cuenta de que la forma completamente normal de hacerlo sería tener una tabla de nombres de host con una clave externa que apunte a la tabla de hosts, pero me gustaría evitar que todo el mundo tenga que hacer uniones, 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 que las consultas simples sean simples:

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

Sin embargo, cuando establezco una restricción de unicidad 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 una manera de hacer que cada nombre sea único en cada fila?

Si no es así, ¿alguien sabe de otro enfoque de modelado de datos que tenga más sentido?

Answer 1

El camino recto

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" . Crear una vista 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
);

El hostname_id clave primaria sustituta es opcional . Prefiero tener uno. En su caso, el hostname podría ser la clave principal. Pero muchas operaciones son más rápidas con una simple y pequeña clave de integer . Cree una restricción de clave externa para vincular al host la 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+

A partir de 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 de GROUP BY en la lista de destino de la consulta cuando la clave principal se especifica en la cláusula GROUP BY

Las consultas pueden utilizar la vista como una tabla. Buscar un nombre de host será mucho más rápido 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) , este debería ser el caso, como debería ser la clave principal. Además, la restricción UNIQUE en el hostname(hostname) de hostname(hostname) implementa automáticamente el otro índice necesario.}

En Postgres 9.2+, un índice de varias columnas sería aún mejor si pudiera obtener un escaneo de solo índice :

CREATE INDEX hn_multi_idx ON hostname (hostname, host_id);

A partir de Postgres 9.3 , puede usar una MATERIALIZED VIEW , si las circunstancias lo permiten. Especialmente si lees mucho más a menudo que lo que escribes en la tabla.

El lado oscuro (lo que realmente pediste)

Si no puedo convencerte del camino recto, también te ayudaré en el lado oscuro. Soy flexible. :)

Aquí hay una demostración de cómo hacer valer la unicidad de los nombres de host. Utilizo un hostname de hostname tabla para recopilar nombres de host y un desencadenante en el host tabla para mantenerlo actualizado. Las violaciones únicas generan una excepción 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 = 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;

Desencadenar:

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

Fiddle SQL con prueba de ejecución.

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

• ¿Por qué no se está utilizando mi índice de matriz PostgreSQL (Rails 4)?
• Compruebe si alguno de los valores de una matriz dada está presente en una matriz de Postgres

Answer 2

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

Registrado en Postgres 9.5 en Windows.

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

PS En general, estoy de acuerdo con la respuesta anterior, pero este enfoque es solo una buena opción cuando no tiene que preocuparse realmente por el rendimiento y esas cosas.