Improve error messsage when due to a SQL error

[mathieubossaert]

good morning, during our training this week we face this problem.

On the database side, we create triggers to gets data from views and insert it into another table (GIS Table for colleagues). We manually submit collected data (we do not have wireless connection) When PostgreSQL throw an error, due to our triggers, Aggregate prints an error about XML file parse error. It would be great to catch and print the PostgreSQL error and print it instead of an "xml error"

[ggalmazor]

Hi, @mathieubossaert!

I wanted to ask you about those triggers you're talking about. It would be good to have an example of those documented in this issue so that it's ready for anyone wanting to work on it.

I know we will be working on logging and error reporting after v2.0 launches and we could consider working on this one if it's ready.

[mathieubossaert]

HI @ggalmazor, Sorry for the delay. So we are using ODK to collect located species data. When my colleagues are back to office the sent finalized form to Aggregate in order to use their data in our office app (web app and QGIS). I use a trigger to push Aggregate data in our own database. A view is used to format odk data to our database schema and for each new row in Aggregate form "core" table a trigger insert any new data (uuid comparison) in our database. The error was thrown because of "foreign key violation". A form value was not implement in our database. So I have to be aware of that "problem" when I implement new value in form choices but a explicit error message would be nice too :-)

Here is the formatting view of odk collected data and the trigger.

Thanks a lot.

/* this view makes data formatting for new collected data (_URI not present in id_waypoint field) */
CREATE OR REPLACE VIEW odk_prod.donnees_mise_en_forme_serveur_distant AS 
 WITH observateurs_numerisateur AS (
         SELECT string_agg(btrim(split_part("EXO_MOBILE_8_SAISIE_OBSERVATEUR"."SEARCH_OBSERVATEUR"::text, '{'::text, 2), '}'::text), '&'::text ORDER BY "EXO_MOBILE_8_SAISIE_OBSERVATEUR"."_ORDINAL_NUMBER") AS observateurs,
            "EXO_MOBILE_8_SAISIE_OBSERVATEUR"."_PARENT_AURI"
           FROM odk_prod."EXO_MOBILE_8_SAISIE_OBSERVATEUR"
          GROUP BY "EXO_MOBILE_8_SAISIE_OBSERVATEUR"."_PARENT_AURI"
        )
 SELECT core."SAISIE_DATE_DATE_OBS"::date AS date_obs,
    taxref.regne,
    taxref.nom_scientifique,
    btrim(split_part(obs."LB_CD_NOM_LATIN"::text, '{'::text, 2), '}'::text) AS cd_nom,
        CASE
            WHEN taxref.regne = 'Animalia'::text THEN obs."CARAC_OBSERVATION_FAUNE_FAUNE_EFFECTIF"
            ELSE obs."CARAC_OBSERVATION_FLORE_FLORE_EFFECTIF"
        END AS effectif,
    obs."CARAC_OBSERVATION_FLORE_EFFECTIF_TEXTUEL" AS effectif_text,
    obs."CARAC_OBSERVATION_FAUNE_AGE" AS age,
    obs."CARAC_OBSERVATION_FAUNE_SEXE" AS sexe,
    obs."CARAC_OBSERVATION_FLORE_PHENOLOGIE" AS phenologie,
    obs."CARAC_OBSERVATION_FLORE_STRATE" AS starte,
    obs."STATUT_REPRO" AS statut_repro,
    obs."_URI" AS id_waypoint,
    COALESCE(core."OBS_GEOPOINT_LNG"::double precision, st_x(st_transform(st_centroid(geom.geometrie), 4326))) AS longitude,
    COALESCE(core."OBS_GEOPOINT_LAT"::double precision, st_y(st_transform(st_centroid(geom.geometrie), 4326))) AS latitude,
    core."LOCAL_RQS" AS remarque_localisation,
    observateurs_numerisateur.observateurs,
    '2'::text AS structure,
    concat('id sicen_mobil ', obs."_URI",
        CASE
            WHEN core."OPTION_LOCALISATION"::text = '"gps_auto"'::text THEN concat('. Précision GPS : ', core."OBS_GEOPOINT_ACC", '. ')
            ELSE NULL::text
        END, obs."REMARQUES_OBS_OBS_RQS") AS remarque_obs,
    'GPS'::text AS "precision",
    'à valider'::text AS statut_validation,
    btrim(split_part(core."RELEVE_ID_ETUDE"::text, '{'::text, 2), '}'::text)::integer AS id_etude,
    btrim(split_part(core."RELEVE_ID_PROTOCOLE"::text, '{'::text, 2), '}'::text)::integer AS id_protocole,
    st_transform(odk_prod.geom_from_odk_to_postgis(LONG, LAT, GEOTRACE, GEOSHAPE) ,2154) AS geometrie
   FROM odk_prod."EXO_MOBILE_8_CORE" core
     JOIN observateurs_numerisateur ON observateurs_numerisateur."_PARENT_AURI"::text = core."_URI"::text
     JOIN odk_prod."EXO_MOBILE_8_SAISIE_OBSERVATION" obs ON obs."_PARENT_AURI"::text = core."_URI"::text
     JOIN geom ON geom."_URI"::text = core."_URI"::text
     JOIN inpn.taxref ON taxref.cd_nom = btrim(split_part(obs."LB_CD_NOM_LATIN"::text, '{'::text, 2), '}'::text)
     LEFT JOIN fdw.saisie_observation_cenlr saisie_observation ON saisie_observation.id_waypoint::text = obs."_URI"::text
  WHERE saisie_observation.id_waypoint IS NULL
  ORDER BY obs."_ORDINAL_NUMBER";

CREATE OR REPLACE FUNCTION odk_prod.alimente_saisie_observation_serveur_distant()
  RETURNS trigger AS
$BODY$ declare
BEGIN
INSERT INTO fdw.saisie_observation_cenlr(
            date_obs, regne, nom_scientifique, cd_nom, effectif, 
            effectif_text, age, sexe, phenologie, starte, statut_repro, id_waypoint, 
            longitude, latitude, remarque_localisation, observateurs, structure, 
            remarque_obs, "precision", statut_validation, id_etude, id_protocole, 
            geometrie)
SELECT date_obs, regne, nom_scientifique, cd_nom, effectif, effectif_text, 
       age, sexe, phenologie, starte, statut_repro, id_waypoint, longitude, 
       latitude, remarque_localisation, observateurs, structure, remarque_obs, 
       "precision", statut_validation, id_etude, id_protocole, geometrie
  FROM odk_prod.donnees_mise_en_forme_serveur_distant;
RETURN NULL; 
END; $BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

CREATE TRIGGER alimente_saisie_obs_sevreur_distant
AFTER INSERT
ON odk_prod."EXO_MOBILE_8_CORE"
FOR EACH STATEMENT
EXECUTE PROCEDURE odk_prod.alimente_saisie_observation_serveur_distant();

[ggalmazor]

Thanks, @mathieubossaert!

Just to complete the information about this, could you copy an example of the error Aggregate produces on the logs when this happens?