J'ai une table qui décrit les versions logicielles installées sur une machine à différents moments:
machine_id::integer, version::text, datefrom::timestamp, dateto::timestamp
6 Réponses :
Voulez-vous vraiment un chèque costraint, comme mentionné dans le titre? Cela n'est pas possible, car les contraintes de contrôle ne peuvent travailler qu'une ligne à la fois. Il pourrait y avoir un moyen de faire cela en utilisant des déclencheurs, bien que ...
Toute façon de contraindre les données sera suffisante. Je viens (à tort!) Supposé que ce serait un chèque ...
dans PostgreSQL 8.4 Cela ne peut être résolu que de déclencheurs. La gâchette devra vérifier l'insertion / la mise à jour qu'aucune rangée en conflit n'existe. Étant donné que la série de transactions ne met pas en œuvre le verrouillage du prédicat, vous devez effectuer le verrouillage nécessaire par vous-même. Pour faire ce dans PostgreSQL 9.0 Il y aura une meilleure solution à cela, appelée contraintes d'exclusion (quelque peu documenté sous Créer une table ). Cela vous permettra de spécifier une contrainte que les gammes de date ne doivent pas se chevaucher. Jeff Davis, l'auteur de cette fonctionnalité dispose d'une radiation en deux parties sur ceci: Partie 1 , Partie 2 . Depesz a également un peu Exemples décrivant la fonctionnalité . Sélectionner pour la mise à jour la ligne du tableau des machines de sorte qu'aucune autre transaction ne puisse insérer simultanément des données pouvant être conflictuelles.
-- Implementation of a CONSTRAINT on non-overlapping datetime ranges
-- , using the Postgres rulesystem.
-- This mechanism should work for 8.4, without needing triggers.(tested on 9.0)
-- We need a shadow-table for the rangesonly to avoid recursion in the rulesystem.
-- This shadow table has a canary variable with a CONSTRAINT (value=0) on it
-- , and on changes to the basetable (that overlap with an existing interval)
-- an attempt is made to modify this variable. (which of course fails)
-- CREATE SCHEMA tmp;
DROP table tmp.dates_shadow CASCADE;
CREATE table tmp.dates_shadow
( time_begin timestamp with time zone
, time_end timestamp with time zone
, overlap_canary INTEGER NOT NULL DEFAULT '0' CHECK (overlap_canary=0)
);
ALTER table tmp.dates_shadow
ADD PRIMARY KEY (time_begin,time_end)
;
DROP table tmp.dates CASCADE;
CREATE table tmp.dates
( time_begin timestamp with time zone
, time_end timestamp with time zone
, payload varchar
);
ALTER table tmp.dates
ADD PRIMARY KEY (time_begin,time_end)
;
CREATE RULE dates_i AS
ON INSERT TO tmp.dates
DO ALSO (
-- verify shadow
UPDATE tmp.dates_shadow ds
SET overlap_canary= 1
WHERE (ds.time_begin, ds.time_end)
OVERLAPS ( NEW.time_begin, NEW.time_end)
;
-- insert shadow
INSERT INTO tmp.dates_shadow (time_begin,time_end)
VALUES (NEW.time_begin, NEW.time_end)
;
);
CREATE RULE dates_d AS
ON DELETE TO tmp.dates
DO ALSO (
DELETE FROM tmp.dates_shadow ds
WHERE ds.time_begin = OLD.time_begin
AND ds.time_end = OLD.time_end
;
);
CREATE RULE dates_u AS
ON UPDATE TO tmp.dates
WHERE NEW.time_begin <> OLD.time_begin
AND NEW.time_end <> OLD.time_end
DO ALSO (
-- delete shadow
DELETE FROM tmp.dates_shadow ds
WHERE ds.time_begin = OLD.time_begin
AND ds.time_end = OLD.time_end
;
-- verify shadow
UPDATE tmp.dates_shadow ds
SET overlap_canary= 1
WHERE (ds.time_begin, ds.time_end)
OVERLAPS ( NEW.time_begin, NEW.time_end)
;
-- insert shadow
INSERT INTO tmp.dates_shadow (time_begin,time_end)
VALUES (NEW.time_begin, NEW.time_end)
;
);
INSERT INTO tmp.dates(time_begin,time_end) VALUES
('2011-09-01', '2011-09-10')
, ('2011-09-10', '2011-09-20')
, ('2011-09-20', '2011-09-30')
;
SELECT * FROM tmp.dates;
EXPLAIN ANALYZE
INSERT INTO tmp.dates(time_begin,time_end) VALUES ('2011-09-30', '2011-10-04')
;
INSERT INTO tmp.dates(time_begin,time_end) VALUES ('2011-09-02', '2011-09-04')
;
SELECT * FROM tmp.dates;
SELECT * FROM tmp.dates_shadow;
Entre-temps (depuis la version 9.2 Si je lisais correctement le manuel) PostgreSQL a ajouté la prise en charge de RangeyTypes .
Avec ces rangeypes, le problème devient soudainement très simple (exemple copié du manuel): p> et c'est tout. Test (également copié du manuel): p> INSERT INTO reservation VALUES
('[2010-01-01 14:45, 2010-01-01 15:45)');
Et comment allez-vous avoir cette déclaration d'exclusion pour des rangées uniques avec un FK, c'est-à-dire que nous avons une réservation de table crée (Geartheid Enterger non NULL, pendant Tsrange, exclure à l'aide de GIST (pendant &&));
@mikejames: Comme ceci: exclude à l'aide de gist (GISTID avec =, pendant &&)
Pour utiliser Exclure avec des valeurs scalaires Activer: Créer une extension Btree_gist; Ensuite, excluez l'utilisation de GIST (GestionID avec =, pendant &&)
Si pour une raison quelconque ou une autre, vous ne pouvez pas modifier le schéma de la table et que vous devez conserver deux lignes de temps, vous pouvez créer la plage dans la contrainte, par exemple:
CREATE TABLE reservations (
datefrom timestamp,
dateto timestamp,
EXCLUDE USING gist (tsrange(datefrom, dateto) WITH &&)
);
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE machines(
machine_id integer,
version text,
during tsrange,
EXCLUDE USING gist ( machine_id with =, during with &&)
);
machines with same id in the table will not overlap.
Je reçois une erreur sur Machin_id disant Integer a besoin d'une classe d'opérateur ... sqlalchemy.exc.programmingError: (psycopg2.exe.undefinedObject) Type de données INTEGER n'a pas de classe d'opérateur par défaut pour la méthode d'accès "GIST" indice: vous devez spécifier une classe d'opérateur pour l'index ou définir une classe d'opérateur par défaut pour le type de données.