Forums PostgreSQL.fr

TheMatrix

Membre

Eviter un ROLLBACK lors du chargement des données rejetées via COPY

Bonjour à tous,

Je voulais poser une question concernant l'utilisation de la commande COPY (à partir d'une session psql).

Environnement:

OS: Windows 10 (64 bits)
PostgreSQL: 10

J'utilise cette commande afin de charger des fichier CSV très volumineux dans un certain nombre de tables dans ma base. Le problème c'est que depuis plusieurs jours, apparemment il y a des données erronées qui sont rejetées par les contraintes d'intégrité définies sur les tables et dès le premier rejet, PostgreSQL fait un ROLLBACK complet. C'est-ce qu'il est d'ailleurs écrit dans la documentation en ligne:
https://www.postgresql.org/docs/10/sql-copy.html

COPY stops operation at the first error. This should not lead to problems in the event of a COPY TO, but the target table will already have received earlier rows in a COPY FROM. These rows will not be visible or accessible, but they still occupy disk space. This might amount to a considerable amount of wasted disk space if the failure happened well into a large copy operation. You might wish to invoke VACUUM to recover the wasted space.

Je n'ai pas la possibilité de nettoyer les fichiers avant le chargement dans PostgreSQL et d'ailleurs ce n'est pas le but. Au contraire, nous souhaitons pouvoir rassembler les lignes rejetées en un seul fichier et l'envoyer à l'équipe de développement, qui est responsable de la production de ces fichiers, afin de leur montrer le problème et donc faciliter/accélérer le travail d'analyse de ces anomalies.

Avec Oracle, je n'aurai aucune difficulté, le programme SQL*Loader permet de récupérer facilement les lignes rejetées dans un fichier, tout en continuant à charger les données ayant le bon format dans les tables. Comment je peux faire pour avoir le même résultat sur PostgreSQL? C'est-à-dire au lieu d'un ROLLBACK, écrire les données rejetées dans un fichier? Le problème me paraît un grand classique notamment dans le cadre des missions de la reprise de données où parfois on trouve un peu tout dans les fichiers. Je serai vraiment étonné si une solution similaire/équivalent à SQL*Loader n'existe pas pour régler le même problème sur PostgreSQL.

Merci d'avance,

pifor

Membre

Re : Eviter un ROLLBACK lors du chargement des données rejetées via COPY

Une solution c'est pgloader.

---

Pierre

TheMatrix

Membre

Re : Eviter un ROLLBACK lors du chargement des données rejetées via COPY

Une solution c'est pgloader.

Il me semble que c'est un outil pour les environnements Linux/Unix, non? Je suis sous Windows 10.

pifor

Membre

Re : Eviter un ROLLBACK lors du chargement des données rejetées via COPY

Sur la page GitHub, certains semblent être arrivés à l'installer sur Windows soit directement (mais avec difficultés) soit en installant le package apt dans le système Linux de Windows:
https://github.com/dimitri/pgloader/issues/652

Sinon il existe toujours la possibilité d'avoir une machine virtuelle Linux sur Windows et d'y installer pgloader.

---

Pierre

rjuju

Administrateur

Re : Eviter un ROLLBACK lors du chargement des données rejetées via COPY

Ou d'utiliser un autre ETL?  Il me semble que kettle gère ce cas par exemple, et l'outil est écrit en java il devrait donc tourner facilement sur windows.

---

Julien.
https://rjuju.github.io/

TheMatrix

Membre

Re : Eviter un ROLLBACK lors du chargement des données rejetées via COPY

Merci beaucoup pour votre aide. Je vais d'abord faire un test sur une machine virtuel Linux (virtualbox) sur mon ordinateur pour faire un premier test avec pgloader. J'écrirai le résultat.

TheMatrix

Membre

Re : Eviter un ROLLBACK lors du chargement des données rejetées via COPY

Finalement, je n'ai pas réussi à exécuter pgloader, il y avait plusieurs messages d'erreur pour lesquels mes recherches sur Google n'ont pas abouti. Ensuite j'ai testé l'ETL Talend, mais vu la volumétrie très importante des données à charger, le temps d'exécution n'était pas du tout raisonnable et en plus une bonne partie de mes données ayant le bon format et respectant les contraintes d'intégrité, n'étaient même pas chargées dans la base (pourquoi? aucune idée).

J'ai fini donc par développer en Python ma propre solution en passant par psycopg2. J'utilise la méthode copy_from qui ne fait qu'appeler la commande COPY de PostgreSQL et c'est très intéressant car cela veut dire que l'on bénéficie de la performance de la vraie commande COPY. Sauf que, dans le code que j'ai développé, je fait ça dans un bloc try ... catch ... et s'il y a une exception ( = échec du chargement), à ce moment-là dans une boucle while, je découpe le fichier par paquets (= un fichier intermédiaire/temporaire que le programme crée pendant l'exécution) de 100 000 ligne et je continue le chargement en récupérant à chaque fois les numéros de lignes rejetées (cette information se trouve dans le message d'exception lancée par COPY) et en sautant ces lignes problématiques dans ces fichiers temporaires/intermédiaires, j'arrive à charger le reste de mes données (d'ailleurs il me semble que c'est un peu le même principe avec pgloader). Pendant l'exécution du programme, les numéro de lignes rejetées sont sauvegardés dans une liste ce qui me permet à la fin, de faire un export de l'ensemble des lignes problématique dans un fichier.

Voilà, c'était juste pour confirmer, dans le cas où ça pourrait intéresser ceux et celles qui ont eu mon problème, qu'effectivement Psycopg2 avec un peu de développement est en effet une solution (parmi d'autres).

Je remercie encore une fois pour les commentaires et suggestions.

gleu

Administrateur

Re : Eviter un ROLLBACK lors du chargement des données rejetées via COPY

C'est en effet le principe de pgloader. Dommage que vous ayez eu à développer ça vous-même.

---

Guillaume.