Nicely catch DB errors and forward it to the user

[haubourg]

Ex: replaying an insert event on a table with a primary key will fail. currently pg_history_viewer raises a python exception, when it should just nicely warn replay failed and why.

Une erreur est survenue lors de l'éxécution du code Python: 

IntegrityError: duplicate key value violates unique constraint "ouvrepere_pkey" DETAIL:  Key (id)=(00f0d8f3-1d38-4c27-b735-f9732e59b645) already exists. CONTEXT:  SQL statement "insert into ouvrage.ouvrepere (id, type_ouvrepere) values (new_id, 'TRONCON')" PL/pgSQL function api_maj.creer_troncon(hstore,real) line 10 at SQL statement SQL statement "SELECT api_maj.creer_troncon(hstore(NEW))" PL/pgSQL function maj_plan.tf_troncon() line 7 at PERFORM SQL statement "INSERT INTO maj_plan.troncon (id,egout,pente,numero,hauteur,largeur,fonction,longueur,code_type,geometrie,est_regule,tr_type_id,date_constat,code_fonction,noeud_aval_id,noeud_amont_id,tr_fonction_id,code_saturation,code_type_ovoide,code_type_radier,cote_radier_aval,frequence_visite,largeur_cunnette,note_degradation,tr_saturation_id,arrondissement_id,code_gestionnaire,cote_radier_amont,tr_type_ovoide_id,tr_type_radier_id,circonscription_id,code_risque_impact,code_type_effluent,numero_galerie_edp,securite_personnel,tr_gestionnaire_id,code_arrondissement,code_forme_cunnette,code_maitre_ouvrage,derniere_visite_itv,derniere_visite_tsp,profondeur_cunnette,responsable_constat,tr_risque_impact_id,tr_type_effluent_id,code_circonscription,sous_zone_regulee_id,tr_forme_cunnette_id,tr_maitre_ouvrage_id,code_etat_hydraulique,code_risque_geologique,risque_impact_dominant,tr_etat_hydraulique_id,tr_risque_geologique_id,code_etat_rehabilitation,code_lateralite_cunnette,frequence_visite_proposee,tr_etat_general_visite_id,tr_etat_rehabilitation_id,tr_lateralite_cunnette_id,qualite_cote_radier_aval_id,qualite_cote_radier_amont_id,description_risque_geologique) VALUES ('00f0d8f3-1d38-4c27-b735-f9732e59b645',null,null,null,null,null,null,null,null,'01020000206A08000003000000FC797A9F44E823410E180DF51E2A5A41341A66DC76E82341D0593E8B1F2A5A41839F4F4284E82341398A11801D2A5A41','f','01596845-4e12-48de-abb5-71f7853b4c80','2016-12-07 17:18:30+01',null,null,null,null,null,null,null,null,null,null,null,null,'05c170fa-6358-42c4-a6a2-650431be4801',null,null,null,null,'4de00519-bd73-4f82-ba3d-f653640ef3ba',null,null,null,null,null,null,null,null,null,null,null,'digiTigre',null,null,null,null,null,null,null,null,null,null,'2a6ec5f5-f289-41a0-840f-db8d2b9424f1',null,null,null,null,'5387fff3-6063-406a-bfc8-4c595b8fe3ee',null,null,null,null)" PL/pgSQL function audit.replay_event(integer) line 33 at EXECUTE 
Traceback (most recent call last):
  File "/home/regis/OSLANDIA/projets_locaux/TIGRE_STEA/GITLAB_specifique_qgis/1507_05_stea_tigre7/qgis/dot_qgis2/python/plugins/pg-history-viewer/event_dialog.py", line 414, in onReplayEvent
    cur.execute("SELECT {}({})".format(self.replay_function, event_id))
IntegrityError: duplicate key value violates unique constraint "ouvrepere_pkey"
DETAIL:  Key (id)=(00f0d8f3-1d38-4c27-b735-f9732e59b645) already exists.
CONTEXT:  SQL statement "insert into ouvrage.ouvrepere (id, type_ouvrepere) values (new_id, 'TRONCON')"
PL/pgSQL function api_maj.creer_troncon(hstore,real) line 10 at SQL statement
SQL statement "SELECT api_maj.creer_troncon(hstore(NEW))"
PL/pgSQL function maj_plan.tf_troncon() line 7 at PERFORM
SQL statement "INSERT INTO maj_plan.troncon (id,egout,pente,numero,hauteur,largeur,fonction,longueur,code_type,geometrie,est_regule,tr_type_id,date_constat,code_fonction,noeud_aval_id,noeud_amont_id,tr_fonction_id,code_saturation,code_type_ovoide,code_type_radier,cote_radier_aval,frequence_visite,largeur_cunnette,note_degradation,tr_saturation_id,arrondissement_id,code_gestionnaire,cote_radier_amont,tr_type_ovoide_id,tr_type_radier_id,circonscription_id,code_risque_impact,code_type_effluent,numero_galerie_edp,securite_personnel,tr_gestionnaire_id,code_arrondissement,code_forme_cunnette,code_maitre_ouvrage,derniere_visite_itv,derniere_visite_tsp,profondeur_cunnette,responsable_constat,tr_risque_impact_id,tr_type_effluent_id,code_circonscription,sous_zone_regulee_id,tr_forme_cunnette_id,tr_maitre_ouvrage_id,code_etat_hydraulique,code_risque_geologique,risque_impact_dominant,tr_etat_hydraulique_id,tr_risque_geologique_id,code_etat_rehabilitation,code_lateralite_cunnette,frequence_visite_proposee,tr_etat_general_visite_id,tr_etat_rehabilitation_id,tr_lateralite_cunnette_id,qualite_cote_radier_aval_id,qualite_cote_radier_amont_id,description_risque_geologique) VALUES ('00f0d8f3-1d38-4c27-b735-f9732e59b645',null,null,null,null,null,null,null,null,'01020000206A08000003000000FC797A9F44E823410E180DF51E2A5A41341A66DC76E82341D0593E8B1F2A5A41839F4F4284E82341398A11801D2A5A41','f','01596845-4e12-48de-abb5-71f7853b4c80','2016-12-07 17:18:30+01',null,null,null,null,null,null,null,null,null,null,null,null,'05c170fa-6358-42c4-a6a2-650431be4801',null,null,null,null,'4de00519-bd73-4f82-ba3d-f653640ef3ba',null,null,null,null,null,null,null,null,null,null,null,'digiTigre',null,null,null,null,null,null,null,null,null,null,'2a6ec5f5-f289-41a0-840f-db8d2b9424f1',null,null,null,null,'5387fff3-6063-406a-bfc8-4c595b8fe3ee',null,null,null,null)"
PL/pgSQL function audit.replay_event(integer) line 33 at EXECUTE

Version de Python : 2.7.12 (default, Nov 19 2016, 06:48:10) [GCC 5.4.0 20160609] 
Version de QGIS : 2.18.1 Las Palmas, b7fe4bb2 

[mhugo]

The error is here IntegrityError: duplicate key value violates unique constraint "ouvrepere_pkey" DETAIL: Key (id)=(00f0d8f3-1d38-4c27-b735-f9732e59b645) already exists So you mean get rid of the stacktrace ?

[haubourg]

The idea is to expose the PG stacktrace, but not in a python red stacktrace (this is perceived as a plugin error, not a database error)

[haubourg]

Good to me too. Just wondering if replaying should be available for DELETE. If a delete replay succeeds, it means an object have caught the same id as the deleted one. This should not happen for databases with serials or uuid, but for other codes, it could happen, and that could trigger data deletion where it's not advisable to.

Inserting twice is less problematic, it's not destroying anyhing.

@3nids any opinion ?

[lbartoletti]

https://github.com/qwat/pg-history-viewer/pull/15