désactiver les contraintes lors d'une restauration avec psql

bebert73 · 30/05/2011 20:54:55

Bonjour,

Peut-on désactiver les contraintes lors d'une restauration par psql ? (la sauvegarde ayant été faite via pg_dumpall)

J'ai une table avec des références circulaires, et ça plante lors de la restauration. J'ai vu dans la doc qu'on pouvait désactiver les triggers dans pg_dumpall, peut-on aussi désactiver les contraintes ?

gleu · 30/05/2011 21:37:07

Contrainte de quel type ? si c'est une clé étrangère, la désactivation des triggers désactive aussi les clés étrangères (car ils sont basés en interne sur des triggers... ça a ses avantages et ses inconvénients ).

bebert73 · 30/05/2011 21:45:16

non c'était pour des contraintes check

j'avais le cas suivant :

(à noter que j'ai résolu le problème en mettant un CASE dans mon check, mais ça nécessite de coder en dur la première valeur de la table...par curiosité je voulais savoir si on pouvait désactiver toutes les contraintes, ce qui aurait été plus simple - dans oracle on peut facilement désactiver toutes les contraintes, y compris les check, mais j'ai l'impression que ce n'est pas faisable dans postgresql)

Mon problème

Etape 1 :
create table toto (
titi INTEGER PRIMARY KEY,
tata INTEGER NOT NULL REFERENCES toto(titi),
valide BOOLEAN NOT NULL DEFAULT FALSE
);

Etape 2 :
INSERT INTO toto(titi, tata, valide) values (0, 0, TRUE);

Etape 3 :
CREATE FUNCTION est_valide (IN titi_in INTEGER) RETURNS BOOLEAN AS $$
SELECT EXISTS (SELECT titi
FROM toto
WHERE valide = TRUE
FOR SHARE);
$$ LANGUAGE SQL;

Etape 4 :
ALTER TABLE toto
ADD CONSTRAINT check_tata_valide CHECK (est_valide(tata));

Etape 5 :
INSERT INTO toto(titi, tata, valide) values (1, 0, FALSE);
INSERT INTO toto(titi, tata, valide) values (2, 0, FALSE);
INSERT INTO toto(titi, tata, valide) values (3, 0, TRUE);
INSERT INTO toto(titi, tata, valide) values (4, 3, FALSE);
INSERT INTO toto(titi, tata, valide) values (5, 3, FALSE);
INSERT INTO toto(titi, tata, valide) values (6, 0, FALSE);

Etape 6 : pg_dumpall

Etape 7 : psql pour restaurer

Message d'erreur :
La nouvelle ligne viole la contrainte de vérification "toto" de la relation "check_tata_valide"
CONTEXTE : COPY toto, ligne 1 : "0, 0, t"

Le message est logique, vu que j'avais inséré (0,0,true) avant de créer la contrainte. Mais comment faire pour qu'on puisse sauvegarder/restaurer proprement ?

bebert73 · 30/05/2011 22:31:37

à la réflexion, même en mettant des case dans les check ça ne résoud pas complètement le problème

si on ne peut pas désactiver le check, à priori la seule solution est de remplacer ces checks par des triggers, qui eux sont désactivables

SQLpro · 31/05/2011 15:43:26

Ordinairement lorsque l'on fait un DUMP on sépare les commandes en trois fichiers pour pouvoir reprendre facilement :
1) fichier contenant les CREATE basique des tables sans aucune contraintes (ni PK, ni FK, ni CHECK, ni UNIQUE, pas même NOT NULL et DEFAULT.....)
2) insertion basique des données (INSERT)
3) ALTER TABLE pour la pose de toutes les contraintes + CREATE INDEX.

C'est en outre ce qu'il y aura de plus rapide pour la reprise des données.

Il faudra bien entendu les lancer dans le même ordre :
1) CREATE
2) INSERT
3) ALTER + INDEX

C'est d'ailleurs ce que font les bons outils de modélisation comme Power AMC !
Vous pouvez l'utiliser pour ce faire sur la partie DDL en faisant une rétro ingéniérie de votre base.

A +

Dernière modification par SQLpro (31/05/2011 15:44:50)

bebert73 · 31/05/2011 19:12:03

en effet ça paraît logique, mais pg_dump et/ou pg_dumpall permettent de faire cela ?

je n'ai vu aucune option pour sauvegarder les CREATE des tables sans les contraintes (dans pg_dumpall il y a bien une option --disable-triggers, mais ça ne concerne que les triggers, pas les autres contraintes)

gleu · 31/05/2011 20:14:02

Non, pg_dump et pg_dumpall ne le permettent pas. Vous pouvez sauvegarder la structure séparément des données mais dans ce cas, la structure contient les tables, les index, les contraintes, etc.

bebert73 · 31/05/2011 21:25:30

ok merci, c'est bien ce qu'il me semblait...dommage quand même, ça accelererait les restaurations (je crois que l'outil dump d'oracle désactive les contraintes par défaut)

gleu · 31/05/2011 21:54:39

C'est pourquoi les contraintes sont ajoutées à la fin, pour qu'elles prennent le moins de temps possibles. Maintenant, si vous sauvegardez la structure puis les données et que vous les restaurez aussi de cette façon, vous allez perdre en temps de restauration, c'est clair. Dans ce cas, il vous faut modifier la sauvegarde avec les contraintes pour la diviser en deux fichiers. Ça doit être assez simple à coder en fait : tous les CREATE TABLE dans un fichier, tout le reste dans un autre.

bebert73 · 01/06/2011 02:39:45

justement non, toutes les contraintes ne sont pas ajoutées à la fin. Quand on ouvre un fichier dump on voit bien que les contraintes CHECK sont créées en même temps que les tables. Toutes les autres contraintes (unique, primary key, foreign key) sont en effet ajoutées à la fin, mais PAS la contrainte CHECK.

du coup, quand on se retrouve dans un cas comme le mien, avec des contraintes CHECK circulaires, la restauration plante.

deux solutions à cela :

- soit il faut modifier le fichier dump à la main, et déplacer soi-même en fin de fichier la création de la ou des contraintes qui posent problème (c'est fastidieux, ou alors il faut faire un script qui modifie le fichier dump pour automatiser ces modifications)

- soit il faut remplacer ces contraintes check qui posent problème par autre chose, par exemple des triggers. Mais c'est dommage, car si vous vous référez à l'autre post que j'ai ouvert, d'après les informations de SQLpro une contrainte par trigger peut être jusqu'à 95% moins performante qu'une contrainte par un check qui appelle une fonction (ces tests ont certes été effectués sur une base autre que PostgreSQL)

A priori je vais opter pour la 1ère solution, mais ça créé un travail supplémentaire (script de modification de la sauvegarde à écrire) qui aurait pu être évité si pg_dump déplaçait toutes les contraintes check en fin de fichier

Je me demande quand même quelle est la raison pour laquelle les concepteurs de PostgreSQL on choisi de créer les contraintes check en même temps que les tables dans leurs outils de sauvegarde/restauration...

Dernière modification par bebert73 (01/06/2011 02:42:51)

Marc Cousin · 01/06/2011 07:31:42

Pour le second point (performance du trigger), faites le test. L'argumentation de sqlpro sur ce point est assez sujette à caution, pour ne pas dire pire…

postgresql n'a pas grand chose à voir avec ingres, malgré ce qu'il affirme: aucune ligne de code en commun, des algorithmes en commun mais qui ont été réécrits plusieurs fois (et commentés depuis, ce n'était pas franchement le cas avec le code initial ), et 17 ans de développement depuis le départ de Michael Stonebraker, qui font que le projet a évolué de façon radicalement différente depuis. Sans mentionner qu'à l'époque du développement à Berkeley, il n'y avait qu'une poignée de développeurs, alors qu'ils sont environ 300 aujourd'hui. On peut donc penser qu'il y a largement plus de travail qui a été effectué sur ces 17 dernières années que sur les 8 premières. D'ailleurs, le chapitre history de la doc trace quelques améliorations de performance majeures qui sont apparues depuis que le projet n'est plus sous le contrôle de Berkeley…

Vous aurez probablement une dégradation de performance avec le trigger, mais surtout parce que vous devrez l'écrire en PL/PgSQL, et non plus en SQL pur (pour pouvoir déclencher des exceptions). Mais mesurez en l'impact vous même.

Bref, faites un simple test: écrivez le en tant que check et en tant que trigger, et mesurez le temps pour insérer une grande quantité d'enregistrements. Vous serez fixé. Ça vaut mieux que de spéculer sur d'hypothétiques ressemblances du moteur.

Par contre, le point sur les contraintes check est intéressant à savoir. Effectivement, c'est un problème, et une idée de patch pour pg_dump (un --defer-check-constraints ? )

gleu · 01/06/2011 08:27:28

Oups, je pensais que toutes les contraintes étaient ajoutées après. C'est vraiment étonnant que celles-ci soient créées avant.

Marc Cousin · 01/06/2011 08:39:36

Comme quoi la discussion sur un forum amène des connaissances nouvelles

http://archives.postgresql.org/pgsql-ge … g01091.php ou http://postgresql.1045698.n5.nabble.com … 24348.html

=> Ça ne sera pas ajouté tant que les contraintes CHECK ne sont pas améliorées pour pouvoir exécuter des sous-requêtes (et personne ne travaille dessus aux dernières nouvelles). Il ne faut pas utiliser des contraintes CHECK pour ça pour le moment, uniquement pour des règles vérifiant les données d'un enregistrement lui-même. Je disais donc une bêtise, il faut le faire avec des triggers. J'ai complètement loupé ces mails à l'époque…

bebert73 · 01/06/2011 10:57:17

en effet, je crois qu'on a définitivement la réponse là

Sauf que dans votre réponse, Marc, j'enlèverai la phrase "pour le moment" : d'après ce que j'ai compris dans le mail de Tom Lane, CHECK n'est ni pour le moment ni ne sera jamais prévu pour supporter des sous-requêtes. Il conclut en effet en disant

"There is no point in changing the behavior of pg_dump until and unless we improve the handling of CHECK constraints to support subqueries which is more or less the same thing as supporting database-wide ASSERTIONs, and I don't know of anybody even thinking of working on that."

Autrement dit les sous-requêtes ne seront jamais supportées, et la seule façon propre de faire des contraintes vers d'autres tables c'est les triggers, au moins jusqu'au jour où PG supportera les assertions (ça rejoint une réponse de SQLPro, qui disait que les assertions étaient la façon la plus propre de faire ça, sauf que encore peu supporté car surcharge et ralentit la base)

bref, la phrase de Tom Lane résume toute notre discussion : "A CHECK constraint that depends on anything more than the content of the row being checked is simply wrong."

....que c'est bien dit !

Dernière modification par bebert73 (01/06/2011 10:58:46)

Marc Cousin · 01/06/2011 11:00:54

Non, autrement dit: «je ne connais personne qui pense à travailler là dessus». Ça date d'il y a 5 ans, il y a eu un peu de travail sur les contraintes dernièrement (comme des contraintes deferrable supplémentaires en 9.0).

Ça ne veut pas dire que si quelqu'un arrive avec un patch super bien foutu qui gère cela, il sera refusé. Ça veut juste dire que ce n'est une priorité pour aucun des développeurs qu'il connait

bebert73 · 01/06/2011 11:04:29

ah ok ok j'avais pas vu que ça date d'il y a 5 ans

discussion intéressante en tout cas, on aura tous appris quelque chose

bebert73 · 01/06/2011 11:07:30

par contre je pense que ça n'a pas tellement évolué depuis 5 ans, car le 2ème lien que vous avez mis, toujours vers Tom Lane, date de 2009, et il répète encore

"A check constraint is only expected to examine the current row of its table. "

Marc Cousin · 01/06/2011 11:09:57

Oui, ce point n'a pas changé. Je parle du 'ça n'intéresse personne en ce moment'. Ça je ne sais pas si c'est toujours d'actualité.

bebert73 · 01/06/2011 14:39:46

ah ok...

en tout cas merci, vous avez encore trouvé la bonne réponse à cette question...pas si raté que ça, le clone de Tom Lane

Marc Cousin · 01/06/2011 14:41:31

L'original est vraiment extrêmement brillant…

SQLpro · 01/06/2011 19:36:14

Marc Cousin a écrit :

Pour le second point (performance du trigger), faites le test. L'argumentation de sqlpro sur ce point est assez sujette à caution, pour ne pas dire pire…

merci pour le commentaire !!! ;-)

Sérieusement, le problème des triggers PG c'est qu'il ne sont pas ensemblistes, il opèrent uniquement ligne à ligne (FORE EACH ROW) donc beaucoup moins rapide encore que ce que j'ai testé dans SQL Server....

Dès que j'ai du temps je ferais le test avec PG pour comparer ces mêmes contraintes en version PG !

A +

Dernière modification par SQLpro (01/06/2011 19:36:25)

gleu · 01/06/2011 20:30:09

le problème des triggers PG c'est qu'il ne sont pas ensemblistes, il opèrent uniquement ligne à ligne (FORE EACH ROW)

"FOR EACH STATEMENT" est là depuis si longtemps que même la 7.4 l'avait déjà (http://docs.postgresql.fr/7.4/sql-createtrigger.html).

Marc Cousin · 03/06/2011 11:48:06

Oui, mais dans les triggers for each statement, on n'a pas accès aux données modifiées, sous PostgreSQL (par old et new). C'est une discussion (et une question) qui revient régulièrement. La dernière en date à ma connaissance étant http://archives.postgresql.org/pgsql-sq … g00035.php …

Bon, c'est écrit dans la doc, mais c'est vrai que c'est quelquefois gênant. Ça sera surement implémenté un de ces jours, surtout qu'à mon avis il y a une bonne partie de l'infrastructure pour le faire qui a été apportée par les wCTEs (le nouveau nœud d'exécution pourra certainement être recyclé pour ça aussi).

Mais comme d'habitude, on s'éloigne du sujet, qui était une question pratique, pas un comparatif entre les différents SGBD.

#1 30/05/2011 20:54:55

désactiver les contraintes lors d'une restauration avec psql

#2 30/05/2011 21:37:07

Re : désactiver les contraintes lors d'une restauration avec psql

#3 30/05/2011 21:45:16

Re : désactiver les contraintes lors d'une restauration avec psql

#4 30/05/2011 22:31:37

Re : désactiver les contraintes lors d'une restauration avec psql

#5 31/05/2011 15:43:26

Re : désactiver les contraintes lors d'une restauration avec psql

#6 31/05/2011 19:12:03

Re : désactiver les contraintes lors d'une restauration avec psql

#7 31/05/2011 20:14:02

Re : désactiver les contraintes lors d'une restauration avec psql

#8 31/05/2011 21:25:30

Re : désactiver les contraintes lors d'une restauration avec psql

#9 31/05/2011 21:54:39

Re : désactiver les contraintes lors d'une restauration avec psql

#10 01/06/2011 02:39:45

Re : désactiver les contraintes lors d'une restauration avec psql

#11 01/06/2011 07:31:42

Re : désactiver les contraintes lors d'une restauration avec psql

#12 01/06/2011 08:27:28

Re : désactiver les contraintes lors d'une restauration avec psql

#13 01/06/2011 08:39:36

Re : désactiver les contraintes lors d'une restauration avec psql

#14 01/06/2011 10:57:17

Re : désactiver les contraintes lors d'une restauration avec psql

#15 01/06/2011 11:00:54

Re : désactiver les contraintes lors d'une restauration avec psql

#16 01/06/2011 11:04:29

Re : désactiver les contraintes lors d'une restauration avec psql

#17 01/06/2011 11:07:30

Re : désactiver les contraintes lors d'une restauration avec psql

#18 01/06/2011 11:09:57

Re : désactiver les contraintes lors d'une restauration avec psql

#19 01/06/2011 14:39:46

Re : désactiver les contraintes lors d'une restauration avec psql

#20 01/06/2011 14:41:31

Re : désactiver les contraintes lors d'une restauration avec psql

#21 01/06/2011 19:36:14

Re : désactiver les contraintes lors d'une restauration avec psql

#22 01/06/2011 20:30:09

Re : désactiver les contraintes lors d'une restauration avec psql

#23 03/06/2011 11:48:06

Re : désactiver les contraintes lors d'une restauration avec psql

Pied de page des forums

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux