Forums PostgreSQL.fr

ramirez22

Membre

[PG9.6]Validation d'une requete au sein d'une transaction

Bonjour,

Peut-être ce sujet a t'il déjà été évoqué, mais je n'ai pas trouvé de solution.
Ma base inclut une table dans laquelle j'enregistre tous les évènements liée à mon application.

Or, cette application nécessite la mise en œuvre de transactions en fonction du résultat de certaines actions parallèles à l'entrée de données

BEGIN
PostgreSQL : ajout d'un enregistrement dans la table 1, récupèration de l'ID de l'enregistrement 
Application : déplace un fichier en le renommant avec l'ID récupéré. Si erreur, génération d'un message de log à enregistrer dans la table 2.
Si pas d'erreur de transfert fichier : COMMIT sinon ROLLBACK

Comme j'utilise la base pour enregistrer toutes les actions (traçabilité des modifs), en cas de ROLLBACK, je perds la totalité des infos générées dans la table 2 . Du coup, difficile de débogguer.

Est-il possible de faire de sorte que l'enregistrement dans la table 2 se fasse, quel que soit le résultat final (COMIT ou ROLLBACK) ?

Merci de votre aide.

rjuju

Administrateur

Re : [PG9.6]Validation d'une requete au sein d'une transaction

Vous pouvez utiliser des SAVEPOINT / ROLLBACK TO pour annuler l'insertion dans la table 1 en cas de problème, avant d'écrire dans la table 2.  Sinon, la seule alternative serait d'utiliser une autre connexion (soit depuis l'application, soit depuis postgres via dblink par exemple).

---

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

ramirez22

Membre

Re : [PG9.6]Validation d'une requete au sein d'une transaction

Bonjour,

Merci pour l'info. Je suis en train de tester dblink_exec sans connexion permanente (paramètres de connexion directement dans la commande) et pour l'instant, j'ai réussi à écrire dans ma table.
Il a fallu trouver le bon formalisme (notamment en ce qui concerne le doublage des ' ) mais j'y suis arrivé.

Reste à tester au milieu d'une transaction et je détaillerai la méthode pour les autres personnes qui se poseraient la même question que moi.

Cdt,

ramirez22

Membre

Re : [PG9.6]Validation d'une requete au sein d'une transaction

Bonsoir,
Bon ben y'a sans doute un problème, je n'y arrive pas...

Voici ce que je fais:

Suppression d'un enregistrement de la table 1. Obligation de réaliser un bloc transaction car je dois au préalable effacer une clef étrangère dans une table (2).

[SQL] BEGIN;
[SQL] DELETE FROM table_2 WHERE id_enregistrement = {valeur quelconque};
[Application] Si erreurSQL alors AfficheMessageErreur puis AjoutLog(message) puis [SQL]ROLLBACK; puis quitte
[Application] Sinon, 
[SQL] DELETE FROM table_1 WHERE id_enregistrement = {valeur quelconque};
[Application] Si erreurSQL alors AfficheMessageErreur puis AjoutLog(message) puis [SQL]ROLLBACK; puis quitte
[Application] Sinon,
[SQL] COMMIT;
[Application] AjoutLog("Enregistrement effacé")
...etc...

[Procédure AjoutLog]
[SQL] SELECT dblink_exec (
[SQL] $$host=localhost port=5432 dbname=database user=administrateur password=blablabla,
[SQL] $$INSERT INTO logs (date_log,heure_log,utilisateur,type_log,titre_log,message_log)
[SQL] VALUES ('%1','%2','%3','%4','%5','%6')$$
[SQL] );
[Application] Si erreurSQL Alors AfficheMessageErreur 
(forcément, inutile de tenter un nouveau message de log, le premier à planté et la procédure s’appellerait sans arrêt ...)

Il s'agit évidemment d'une transcription allégée ...
Les %1, %2 ... sont des variables que l'application remplace au moment d'envoyer la requête.

Tout se passe correctement s'il n'y a pas d'erreur (c'est une lapalissade, je vous l'accorde). La procédure fonctionne parfaitement, le message "Enregistrement effacé" est bien écrit dans ma table 'logs' (avec tous les champs dûment renseignés).

Puis j'ai volontairement créé une erreur dans le nom de la table_2 de la première requête.
J'ai bien une erreur qui est retourné par PostgreSQL (premier AfficheMessageErreur):

EIT_BASEMESSAGE <ERREUR : la relation "table2" n'existe pas at character 20>
Numéro d'erreur : <42P01> (correspond bien à 'undefined_table')

Puis la procédure AjoutLog est appelée et me génère également une erreur:

Numéro d'erreur : <25P02>
EIT_BASEMESSAGE <ERREUR: la transaction est annulée, les commandes sont ignorées jusqu'à la fin du bloc de la transaction>

Le code d'erreur correspond à in_failed_sql_transaction
Du coup, j'ai l'impression que ma transaction bloque quand même ma requête dblink_exec

J'ai essayé de saisir quelques lignes de code directement sous PGAdmin histoire de voir ce qu'il se passe.

BEGIN;
DELETE FROM table_1 WHERE id = 2;
	SELECT dblink_exec (
	$$host=localhost port=5432 dbname=database user=administrateur password=blablabla$$,
	$$INSERT INTO logs (date_log,heure_log,utilisateur,type_log,titre_log,message_log)
	VALUES ('09-10-2019','21:06:01.23','user','SUPPRESSION','Suppression','Suppression table_1 id ''2''')$$
	);
DELETE FROM table_2 WHERE id_palier = 2;
	SELECT dblink_exec (
	$$host=localhost port=5432 dbname=database user=administrateur password=blablabla$$,
	$$INSERT INTO logs (date_log,heure_log,utilisateur,type_log,titre_log,message_log)
	VALUES ('09-10-2019','21:06:01.23','user','SUPPRESSION','Suppression','Suppression table_2 id ''3''')$$
	);
COMMIT;

Dans ce cas, tout roule. La requête bdlink_exec s'exécute bien au milieu d'une transaction (qui se termine par COMMIT)
J'ai tenté un ROLLBACK à la fin pour être sûr. La requête dblink_exec s'exécute bien, les données propres au log sont bien enregistrées mais celles des autres tables n'ont pas bougées. Au poil.

J'ai tenté de faire une erreur volontaire de saisie dans la première ligne, mais l'erreur arrête immédiatement l'exécution des requêtes.
Du coup, il n'y a pas de ROLLBACK exécuté, du coup la transaction est toujours ouverte. La requête dblink_exec n'est même pas appelée (normal en PGAdmin).
Sauf que si je tente quoique ce soit par la suite, j'ai toujours l'erreur 25P02 (La transaction est annulée, les commandes sont ignorées jusqu'à la fin du bloc) qui s'affiche.
J'ai essayé (dans le même onglet) de tout virer et de faire un ROLLBACK : même erreur.
J'en déduit que la transaction reste ouverte et qu'elle est plantée.

Mais là, je sèche.

Une piste à me suggérer ?

PS : Au fait, j'espère que les termes que j'emploie sont compréhensible et corrects : je suis autodidacte et fais ce que je peut

Dernière modification par ramirez22 (09/10/2019 21:33:23)

rjuju

Administrateur

Re : [PG9.6]Validation d'une requete au sein d'une transaction

Oui, à partir du moment où une erreur est levée sans être gérée, les seules commandes que vous pourrez effectuer son ROLLBACK et COMMIT (qui effectuera un rollback).  Vous pouvez soit utiliser un savepoint comme indiqué précédemment, soit utiliser un bloc BEGIN / EXCEPTION si vous utilisez une procédure stockée.

---

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

dverite

Membre

Re : [PG9.6]Validation d'une requete au sein d'une transaction

La méthode du dblink est utilisable dans le cas où il appelé dans une clause EXCEPTION d'un bloc plpgsql. Un tel bloc est soit dans un bloc de code anonyme (DO...<code> LANGUAGE plpgsql) soit dans une fonction.

Ici il semble que ce ne soit pas le cas, c'est-à-dire que les instructions DELETE sont envoyés individuellement par le client et que la logique IF erreur SQL est dans le client. Vous ne pouvez pas utiliser le SELECT dblink_exect() dans cette situation.

Mais vous pouvez (devez en fait) dès qu'il y a une erreur SQL, lancer un ROLLBACK pour annuler définitivement la transaction en cours et de ce fait vous n'aurez plus ces erreurs 25P02.

Une fois ceci fait, il est possible d'insérer l'entrée de log avec un simple INSERT sans besoin d'un dblink. Le point crucial est que ça ne se fait pas dans la même transaction que celle qui était en échec et que vous terminez en exécutant un ROLLBACK explicite.

---

@DanielVerite
http://blog-postgresql.verite.pro/

ramirez22

Membre

Re : [PG9.6]Validation d'une requete au sein d'une transaction

Bonjour,

Désolé de cette absence de réponse, appelé sur des projets plus urgents...
Merci de votre aide.
Si je comprends bien, je n'ai pas le choix : il faut que je mémorise tous les évènements pour les inscrire à la fin de la transaction, car sinon ils seront effacés par le ROLLBAK (le cas échéant).
Dommage, j'espérais bien pouvoir faire une exception sur une table.

Mais je voudrais creuser un peu.
D'après vos commentaires, si je crée les fonctions qui vont bien sur le serveur, je devrait pouvoir intégrer une clause EXCEPTION qui me permettrait, en cas d'erreur, de remplir ma table LOG même en cas de ROLLBACK ?
Actuellement, une grosse partie de mes transactions se font en requête préparée. J'imagine que cela n'est pas la même chose.

Requête préparée = PREPARE blablabla;
Fonction = DECLARE etc ...

Cela me fait quelques modifs majeur de mon application...
Otez-moi d'un doute : les requêtes préparées sont valides uniquement le temps de la connexion qui les crée (actuellement, c'est comme ça que je fonctionne : à chaque connexion, je crée les fonctions préparées de l'utilisateur. Si x utilisateurs se connectent, j'aurai x requêtes préparées, chacune isolées des autre. Dès qu'une connexion disparait, ses requêtes préparées sont effacées).
Quid des fonctions ? Je les crées une fois pour toute sur mon serveur ?

Cdt

dverite

Membre

Re : [PG9.6]Validation d'une requete au sein d'une transaction

Si je comprends bien, je n'ai pas le choix : il faut que je mémorise tous les évènements pour les inscrire à la fin de la transaction, car sinon ils seront effacés par le ROLLBAK (le cas échéant).

Vous avez le choix d'écrire les évènements via une connexion dédiée, par le client ou par le serveur via un dblink.

Le fait d'avoir des clauses EXCEPTION qui capturent toutes les erreurs SQL permettent effectivement de consigner l'erreur dans une table, mais fait aussi que tout ce qui précède dans la transaction n'est plus annulé. Donc ça change radicalement le résultat en base en cas d'erreur. Par ailleurs le fait d'enregistrer l'erreur peut aussi provoquer une erreur.

Otez-moi d'un doute : les requêtes préparées sont valides uniquement le temps de la connexion qui les crée

Oui

Quid des fonctions ? Je les crées une fois pour toute sur mon serveur ?

Oui. CREATE FUNCTION namespace.nomfonction a un effet permanent, sauf si namespace=pg_temp.

---

@DanielVerite
http://blog-postgresql.verite.pro/

ramirez22

Membre

Re : [PG9.6]Validation d'une requete au sein d'une transaction

Merci dverite, mais je ne comprends pas (désolé, cerveau de plus de 40 ans...)

Ici il semble que ce ne soit pas le cas, c'est-à-dire que les instructions DELETE sont envoyés individuellement par le client et que la logique IF erreur SQL est dans le client. Vous ne pouvez pas utiliser le SELECT dblink_exect() dans cette situation.

Vous avez le choix d'écrire les évènements via une connexion dédiée, par le client ou par le serveur via un dblink.

Ou alors est-ce qu'il y a une différence entre bdlink et dblink_exec ?
J'ai crus que dblink_exec correspondait en gros à dblink_connect puis dblink en enfin dblink_disconnect. Peut-être me suis-je fourvoyé.
Si je continue sur cette voie, cela signifierait qu'il faille, dès la connexion de mon appli à la BDD créer un deuxième "tunnel" via dblink_connect et si mon appli reçoit une erreur SQL, utiliser cette connexion pour exécuter un dblink ?

Je suis pas certain d'avoir bien compris, un petit éclaircissement serait le bienvenu.

Cdt,
Ramirez22

[Edit]Je reviens sur ce que j'ai dit, en relisant je crois apercevoir un début de commencement de compréhension.

La méthode du dblink est utilisable dans le cas où il appelé dans une clause EXCEPTION d'un bloc plpgsql

Je ne peux donc pas utiliser dblink tel quel.
Si je comprends bien, il faudrait que j'utilise un bloc code anonyme en gros comme ça ?

DO <code de la modification à effectuer sur mes tables
DECLARE;
BEGIN
code SQL commençant par BEGIN; pour ouvrir une transaction
END>
EXCEPTION
dblink

LANGUAGE plpgsql;

Ou est-ce que je suis en train de complètement me planter ?

Dernière modification par ramirez22 (18/10/2019 14:24:20)

dverite

Membre

Re : [PG9.6]Validation d'une requete au sein d'une transaction

J'ai crus que dblink_exec correspondait en gros à dblink_connect puis dblink en enfin dblink_disconnect. Peut-être me suis-je fourvoyé.

Non c'est bien ça

Il y a deux situations très différentes suivant qu'on place le code de gestion d'erreur côté client ou côté serveur ou un mix des deux.

Côté client, on suppose que vous pouvez ouvrir et maintenir deux connexions en permanence. Par exemple si votre appli est en php, vous allez avoir
  $db1 = pg_connect($connect_string);
et
  $db2 = pg_connect($connect_string);

Les traitements sont lancés avec pg_exec($db1, "BEGIN"); pg_exec($db1, "INSERT.. etc..");
et les logs avec pg_exec($db2, "INSERT INTO log... etc..");

Le fait que la session $db1 soit dans une transaction en échec n'empêchera pas du tout d'insérer via $db2.
Ca suppose juste d'avoir une interface client PG qui soit capable d'avoir plusieurs connexions simultanément; la plupart permettent ça.

L'exemple du premier message de la discussion laisse supposer qu'il y a un besoin d'annuler une transaction suite à l'impossibilité de déplacer un fichier côté client:

BEGIN
PostgreSQL : ajout d'un enregistrement dans la table 1, récupèration de l'ID de l'enregistrement
Application : déplace un fichier en le renommant avec l'ID récupéré. Si erreur, génération d'un message de log à enregistrer dans la table 2.
Si pas d'erreur de transfert fichier : COMMIT sinon ROLLBACK

Pour ce besoin, le module dblink n'est pas nécessaire. dblink devient nécessaire si vous *voulez* logger les erreurs dans une table dans du code serveur des clauses EXCEPTION en plpgsql. Pour cette stratégie il y a encore deux sous-choix possibles:
1. ne pas remonter l'erreur au client et considérer qu'elle est "gérée"  après l'avoir loggé l'erreur avec dblink (mauvaise idée  (*) mais certains préfèrent procéder comme ça).
2. garder la transaction en échec, et remonter l'erreur au client (=faire un RAISE en plpgsql depuis le bloc d'exception, cf https://www.postgresql.org/docs/current … ages.html)

(*) je n'ai pas de lien Postgres sous la main mais le problème est similaire avec Oracle et il y a de bonnes explications sur le mésusage de la clause WHEN OTHERS avec Oracle ici: http://www.orafaq.com/wiki/WHEN_OTHERS

---

@DanielVerite
http://blog-postgresql.verite.pro/

ramirez22

Membre

Re : [PG9.6]Validation d'une requete au sein d'une transaction

Côté client, on suppose que vous pouvez ouvrir et maintenir deux connexions en permanence.

Il me semble que gérer 2 connexions simultanées sous Windev soit impossible. Je vais quand même m'en assurer.

L'exemple du premier message de la discussion laisse supposer qu'il y a un besoin d'annuler une transaction suite à l'impossibilité de déplacer un fichier côté client

Exact. Cela va même un peu plus loin : Il y a plusieurs fichiers (même nom, extension différente). Je contrôle d'abord si des métadonnées contenue dans le titre du fichier existent dans d'autres tables (T1, T2 et T3 par exemple) de ma base. Si oui, je les verrouille (BEGIN; SELECT FOR UPDATE ... pour chaque table) pour ne pas risquer qu'un autre utilisateur efface les données entre le contrôle et l'insertion (+LOG en fin de verrouillage). Puis j'insère les infos de ce fichier dans une table (T4) en récupérant l'ID de la ligne ajoutée (+LOG) + quelques clés étrangères (T1, T2 et T3). Je fais la copie des fichiers sur un emplacement réseau en les renommant avec le n° ID de T4. Si le transfert s'est bien passé, je COMMIT, je LOG que l'opération a été un franc succès et enfin j'efface les fichiers dans le répertoire d'import (si problème à ce moment, pas trop grave puisque les fichiers sont au bon emplacement et la base est à jour). S'il y a eu des problèmes, tout va dépendre de où cela s'est produit :

- Si c'est au moment de la recherche des meta données sur T1, T2 ou T3, il me faut faire un LOG puis ROLLBACK;. Il est nécessaire que j'ai l'info de la table en DF, mais au pire, si je perds le LOG de la première opération c'est pas trop grave (le message de log de l'erreur me permettra de retrouver facilement la source du problème).

- Si c'est au moment de l'INSERT, là encore, je peux faire ROLLBACK puis faire mon LOG. Je perds cependant 2 LOGs "process", là encore je peux m'en passer.

- Si c'est lors de la copie des fichiers, j'ai déjà réalisé un LOG indiquant la création dans la table de l'enregistrement. De plus, je LOG le transfert des fichiers 1 par 1. Comme ça, je peux savoir si c'est un fichier spécifique qui pose problème. Du coup, Si je fais un ROLLBACK, je perds ces LOG. Si je fais un COMMIT, je valide ma transaction alors que les fichiers ne sont pas déplacés.

Et c'est au niveau de cette opération que cela pose soucis. Ce n'est qu'un extrait de mon application, j'ai d'autres endroits avec les opérations multiples sur tables que je dois tracer (pour que le LOG serve à quelque chose en cas de défaut), mais qu'il ne faut pas que je conserve s'il y a eu un problème (les modif de tables hein, pas le LOG )

Donc, faire la gestion de l'erreur côté client risque d'être difficile puisque assujetti à la possibilité de connexion multiples simultanées.
Maintenant, si je veux faire du LOG côté serveur (et là encore si j'ai bien compris), il faut que j'utilise des clause exception, donc que je dialogue avec mon serveur soit avec des fonctions anonymes, soit que je crée une fois pour toute des fonctions sur mon serveur qui gère ces exceptions. C'est bien cela ?

ramirez22

Membre

Re : [PG9.6]Validation d'une requete au sein d'une transaction

Bonjour,

Je viens d'essayer d'ouvrir 2 connexions avec Windev, puis de les appeler en fonction du contexte :

connexion_log, connexion_normale sont des entiers
connexion_log = SQLConnecte(AdrServ, Login, Mdp, Base, "POSTGRESQL")
<Gestion de l'erreur de connexion>

connexion_normale = SQLConnectte(AdrServ, Login, Mdp, Base, "POSTGRESQL")
<Gestion de l'erreur de connexion>
SQLChangeConnexion(connexion_normale)


... Traitement du programme ...
<En cas d'erreur:>
AjoutLog("ERREUR", Utilisateur, Message_erreur)


<Procédure globale de traitement des messages de défaut, appelée à chaque message à enregistrer dans la base>
PROCEDURE AjoutLog(sType est chaine, sUtilisateur est chaine, sMessage est chaine)
<Traitement pour vérifier les données transmises, éviter les injection SQL etc...>
     SQLChangeConnexion(connexion_log)
requete = "INSERT INTO logs (date, heure, utilisateur, type, message) VALUES (CURRENT_DATE, LOCALTIME(2),'%1','%2','%3');"
<Traitement des données variables (%1, %2...)
SQLExec(requete, resultat_requete)
<Traitement de l'erreur>
     SQLChangeConnexion(connexion_normale)
<Fin de procedure>

Et apparement ça marche : j'ai volontairement créé une erreur dans une requête dans le corps du programme, et j'ai bien eu le log, malgré un ROLLBACK sur cette première requête. De plus, plus d'erreur bloquante.

Bref, content

Merci dverite et rjuju pour votre aide, cela m'a permis d'approfondir mes connaissances en Windev aussi