Discussion :

[devlop78]

Bonjour,

Je suis en train de voir l'ensemble des fonctionnalités des SGBDR, et je suis passé de Mysql à PostegreSQL en local pour pouvoir le faire (sauf assertions qui ne semblent pas possible).

Lors de la violation d'une contrainte CHECK, mon exécution retourne une erreur. De façon général, que ce soit en local directement sur phpPgAdmin, ou à travers un langage tel que PHP, savoir si une exécution a eu lieue ou non est primordiale.

Seulement, là j'ai créé un TRIGGER, et j'utilise une "fonction" (j'ai pas réussi autrement ^^). Je vais ensuite passer aux procédures stockées.

Donc ma question est : quelle va être la réaction face au ROLLBACK ? Si je lance une transaction dans ma procédure stockée et que le ROLLBACK est lancé, recevrais-je une erreur, ou dois-je me retourner en plus une valeur que je définirai comme telle ?

Et actuellement, ma fonction lancée par mon trigger BEFORE INSERT est :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
  BEGIN  
ROLLBACK;
  END;

Cela a bien été enregistré par PostegreSQL. Lorsque je tente d'ajouter une ligne (un tuple, c'est ça ?), il me retourne :

ERREUR:

SPI_execute_plan_with_paramlist failed executing query "ROLLBACK": SPI_ERROR_TRANSACTION
CONTEXT: PL/pgSQL function "gen_cle_client" line 4 at instruction SQL

Malheureusement, en voyant le message, je n'ai pas l'impression que c'est une bonne nouvelle ... Je crois surtout qu'il n'a pas aimé le ROLLBACK. Bien qu'en testant ROLLBACK directement en SQL, ça ne lance aucune erreur. Est-ce du à l'autocommit, je ne sais pas ... tout ce dont j'ai besoin, c'est de pouvoir arrêter une procédure stockée avec ou sans transaction (autre que autocommit) en emmettant un message d'erreur à l'utilisateur du SGBDR (le langage Php par exemple), et pareil pour le trigger. Ici j'ai mis brutalement un ROLLBACK, mais c'est dans l'objectif de refuser une entrée avec une analyse plus complète apparemment non possible avec CHECK(), et qui d'après moi serait plutot le rôle des assertions (par exemple, controler en fonction des autres lignes de la table, voire des autres tables comme l'exemple du client/prospect avec une unicité commune des clés primaires).

Je vais, en attendant, faire le tour du forum, mais j'espère avoir quelques pistes ;o

Merci, a+

[devlop78]

Au fait, j'ai vu le tuto sur les séquences ... mais auto increment n'existe pas ? Je ne le trouve pas dans phpPgAdmin et comme souvent depuis la transition, je viens d'essayer ALTER TABLE et il n'en veut pas ... il y a beaucoup trop de différence de syntaxe avec mySQL

[CinePhil]

Au fait, j'ai vu le tuto sur les séquences ... mais auto increment n'existe pas ?

Non. Pour incrémenter une colonne, il faut utiliser une séquence. À la création de la table, le pseudo-type serial s'occupe de créer la séquence et de l'associer à la table.

il y a beaucoup trop de différence de syntaxe avec mySQL

AUTO_INCREMENT est une instruction particulière de MySQL comme le pseudo-type serial est une particularité de Postgresql. Il y a encore d'autres instructions dans les autres SGBD.
La norme SQL ne prévoit pas d'instruction pour auto-incrémenter une colonne à ma connaissance.

[devlop78]

OK. La réponse est très claire. Malheureusement, je passe des fois (beaucoup) trop de temps sur des choses qui ne le devrait pas en demander. Par exemple, en formation .NET, le formateur avait parlé de procédures stockée pour éviter que entre son SELECT et son INSERT, un autre INSERT apparaisse. Evidemment, le cours n'était pas sur SQL, sinon pour cet exemple, la contrainte d'unicité était bien plus appropriée, mais surtout que j'ai regardé les procédures stockées sur Google, surtout pour Mysql, et ils parlaient énormément de l'avantage d'atomicité, mais toujours sans suggérer que la transaction faisait elle-même les verrouillages. J'ai même regarder à ce sujet, c'est toujours rester flou. Les discutions et tutoriels sur PostegreSQL sont déjà bien plus clair là dessus : les verrous faits "maison", c'est "à fuir". Et puis, de ce que j'ai pu comprendre, et c'est quasi sûr, sinon pourquoi permettre les verrous (serialize etc), s'il n'en mettait pas. Bref, c'est pas clair. Pareil pour les règles PostegreSQL, je me demande si ce n'est pas l'équivalent des assertions. A coté, on trouve facilement des tutoriels sur les contraintes. Heureusement ! Et là c'est un régal :p

[devlop78]

Re:

http://www.postgresql.org/docs/9.0/s...l-trigger.html

Je cite

IF NEW.empname IS NULL THEN
RAISE EXCEPTION 'empname cannot be null';

J'essaie de mon coté hors procédure et fonction RAISE EXCEPTION, et il n'en veut pas. L'équivalent de throw new Exception serait effectivement super.

[devlop78]

Ca y est ! Ouf !

j'ai trouvé seulement après la valeur de retour VOID, donc j'ai mis INTEGER puisque NULL ne passait pas ...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
BEGIN
RAISE EXCEPTION 'CECI EST UNE ERREUR';
RETURN 25;
END;
 
SELECT somefunc();

-- RESULTAT

Erreur SQL :

ERREUR: CECI EST UNE ERREUR


J'imagine donc que l'exception + un rollback (voir l'exception seule il me semble l'avoir vu) permettent de gérer cette ensemble dissociable arret transaction (si existe) + erreur

J'attends vos com :p

[estofilo]

J'imagine donc que l'exception + un rollback (voir l'exception seule il me semble l'avoir vu) permettent de gérer cette ensemble dissociable arret transaction (si existe) + erreur

Le fonctionnement de postgresql par rapport à ça, c'est que lorsqu'une exception se produit dans une transaction, grosso modo tout ordre SQL autre que ROLLBACK se soldera par une erreur indiquant que la transaction est annulée. Il est donc indispensable d'exécuter un rollback pour continuer.

Autre chose notable par rapport à d'autres SGBDs: il n'est pas possible de faire commit ou rollback à l'intérieur d'une fonction.

En revanche si besoin est de traiter localement une erreur isolée du reste de la transaction, y compris dans une fonction, c'est possible en utilisant un SAVEPOINT.

[devlop78]

Je comprends pas trop ... On ne peut pas faire de transaction dans la procédure stockée ? Il faut la démarrer et la valider/annuler avant et après son appel ?

Par ailleurs, si la transaction est possible dans la procédure, comment savoir si une requête lancée dans cette procédure échoue ou réussie (contraintes ou autres), afin de pouvoir annuler la transaction ?

L'idée c'était de créer une petite application qui permette de se passer de langages tels que Php. Donc par exemple, créer un résident et sa résidence (deux tables) ==> Op, une procédure / fonction qui traite le tout dans une transaction lancée par eux mêmes afin d'éviter l'oubli par l'utilisateur ^^. GrossoModo ne permettre à l'utilisateur de n'utiliser que les procédures stockées en partant du principe que c'est un idiot ^^.

Quoique je fasse je galère vraiment à découvrir seul PostgreSQL et les procédures, ça m'envoie des erreurs partout et je suis un peu perdu. Mais rien que la possibilité de CHECK-er les valeurs, de créer des domaines, c'est vraiment bête que MySQL ne le prenne pas en charge.

[devlop78]

Le fonctionnement de postgresql par rapport à ça, c'est que lorsqu'une exception se produit dans une transaction, grosso modo tout ordre SQL autre que ROLLBACK se soldera par une erreur indiquant que la transaction est annulée. Il est donc indispensable d'exécuter un rollback pour continuer.

J'imagine que c'est comparable à :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
 
try {
$c->exec('machin');
$c->exec('truc');
} catch (Exception $e) {
$c->exec('ROLLBACK;');
}

Pourquoi lancer "truc" lorsque "machin" a échoué. Le problème, c'est comme je l'ai expliqué, l'idée est de créer une abstraction absolue entre le langage et la bdd. Là où ça se complique c'est que

Le langage lance une procédure stockée. Il doit pouvoir avoir message confirmation ou erreur.

La procédure lance des requêtes et traîte des données, avec une ou plusieurs transactions (c'est là où elle remplace PHP sur la plupart des cas). Elle doit pouvoir s'arrêter, lancer une erreur pour le langage, etc.

Durant ses requêtes, les triggers peuvent intervenir, et eux mêmes lancés des exceptions.

Durant les requêtes des triggers ou des procédures (les triggers utilisent des procédures c'est vrai), les contraintes peuvent émettre des erreurs.

Bref, c'est imbriqué de partout, et j'ai vraiment du mal. Je crois que je vais me contenter des contraintes et triggers à terme (qui n'ont pas à être remplacés par le langage de programmation), et à faire les procédures et transactions par le langage (sachant, en avouant tout, que pour le moment une appli = un langage). Mais j'aurais voulu pouvoir baigner là dedans quelques instants ...

[SQLpro]

La norme SQL ne prévoit pas d'instruction pour auto-incrémenter une colonne à ma connaissance.

Si :
1) IDENTITY
2) CREATE SEQUENCE
Font tous deux parties intégrantes de la norme SQL !
Voir mon bouquin pour de plus amples informations...

A +

[SQLpro]

... la transaction faisait elle-même les verrouillages. J'ai même regarder à ce sujet, c'est toujours rester flou.

Lisez ce que j'ai écrit sur le sujet...
http://sqlpro.developpez.com/isolation-transaction/

A +

[SQLpro]

L'idée c'était de créer une petite application qui permette de se passer de langages tels que Php. Donc par exemple, créer un résident et sa résidence (deux tables) ==> Op, une procédure / fonction qui traite le tout dans une transaction lancée par eux mêmes afin d'éviter l'oubli par l'utilisateur ^^. GrossoModo ne permettre à l'utilisateur de n'utiliser que les procédures stockées en partant du principe que c'est un idiot ^^.

C'est une façon absolument parfaite de développer. J'ai pas le temps ni PG sous la main mais voyez s'il est possible de greffer des triggers INSTEAD OF sur des vues PG...

Comme ce que j'indique ici :
http://blog.developpez.com/sqlpro/p9...pping-ro-dire/

[estofilo]

Je comprends pas trop ... On ne peut pas faire de transaction dans la procédure stockée ? Il faut la démarrer et la valider/annuler avant et après son appel ?

Oui. Sauf que ce n'est pas indispensable si l'appel de la fonction est la seule instruction de la transaction car toute instruction SQL isolée est exécutée dans une transaction implicite. Au passage, il n'y a pas de procédure stockée dans postgresql, il n'y a que des fonctions.

Par ailleurs, si la transaction est possible dans la procédure, comment savoir si une requête lancée dans cette procédure échoue ou réussie (contraintes ou autres), afin de pouvoir annuler la transaction ?

Pareil que si ce n'était pas une procédure. Quand on fait un UPDATE ou INSERT par exemple, comment sait-on s'il a réussi ou pas? Et bien s'il a échoué, une exception est générée par le SGBD, et le programme client peut capter cette exception, et d'ailleurs doit le faire s'il est écrit de manière propre. Pour un appel de fonction, c'est le même mécanisme.

L'idée c'était de créer une petite application qui permette de se passer de langages tels que Php. Donc par exemple, créer un résident et sa résidence (deux tables) ==> Op, une procédure / fonction qui traite le tout dans une transaction lancée par eux mêmes afin d'éviter l'oubli par l'utilisateur ^^. GrossoModo ne permettre à l'utilisateur de n'utiliser que les procédures stockées en partant du principe que c'est un idiot ^^.

C'est tout à fait faisable et même courant. Mais il n'y a aucune gestion de transaction à faire dans les fonctions. Eventuellement on peut capter les erreurs SQL avec des blocs BEGIN/END/EXCEPTION et les gérer dans la fonction mais déjà il faut une raison bien spécifique pour faire ça, dans le cas général c'est inutile.

Quoique je fasse je galère vraiment à découvrir seul PostgreSQL et les procédures, ça m'envoie des erreurs partout et je suis un peu perdu.

Tu peux toujours poster sur ce forum les bouts de code et les erreurs en question.

[devlop78]

Table Residents

id SERIAL
genre ENUM ('M.','Mme','Mlle')
nom VARCHAR (50)
prenom VARCHAR (50)
CONSTRAINT id PRIMARY KEY
CONSTRAINT UNIQUE (nom, prenom)
CONSTRAINT nom, prenom, genre NOT NULL

Table Résidences

id SERIAL
adresse VARCHAR (150)
codeVille INTEGER
mcarre INTEGER
resident INTEGER
CONSTRAINT codeVille NOT NULL FOREIGN KEY Residents.id
CONSTRAINT id PRIMARY KEY
CONSTRAINT mcarre, adresse NOT NULL
CONSTRAINT resident NOT NULL FOREIGN KEY Villes.id


Table Villes

id SERIAL
nom VARCHAR (50)
codePostal (5)
impots_mcarre NUMBER(4,2)
CONSTRAINT id PRIMARY KEY
CONSTRAINT codePostal, nom, impots_mcarre NOT NULL
CONSTRAINT UNIQUE (codePostal, nom)

Vue Apercu (Pour comptable par exemple) (rem: code SQL modifié par phpMyAdmin )

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
select concat(`u`.`genre`,' ',`u`.`prenom`,' ',ucase(`u`.`nom`)) AS `utilisateur`,concat(`r`.`adresse`,' - ',`v`.`nom`,' (',`v`.`codePostal`,')') AS `residence`,(`r`.`mcarre` * `v`.`impots_mcarre`) AS `impots` from ((`utilisateurs` `u` left join `residences` `r` on((`r`.`resident` = `u`.`id`))) left join `villes` `v` on((`r`.`codeVille` = `v`.`id`))) order by `u`.`nom`;

Exemple résultat :

utilisateur residence impots
M. Marc LEGRAND 25 rue de charte - Versailles (78000) 727.90

Fonction Creer_ville

=> Ajouter Ville (si erreur, il faut en emettre une si ce n'est pas le cas)


Fonction Creer_resident

=> Ajouter Utilisateur (si erreur, ...)

Fonction Creer_residence

=> Ajouter Ville (si erreur, ...)

Fonction Creer_resident_ville

=> Début transaction
=> Vérifier si résident existe
=> Si oui, passer étape suivante
=> Ajouter résident
=> Ajouter résidence (Si erreur - résidence existante ou ville enexistante, emettre erreur si ce n'est pas le cas)
=> Fin transaction - COMMIT

ou (si gestion des erreurs)

=> Début transaction
=> Ajouter résident
=> Si erreur, la capturer, vérifier son type (Si type != résident existant, alors émettre erreur)
=> Ajouter résidence (Si erreur - résidence existante ou ville enexistante, emettre erreur si ce n'est pas le cas)
=> Fin transaction - COMMIT

Alors, si, par exemple, j'appelle Creer_ville, la requête INSERT retourne une erreur, celle-ci sera t-elle envoyée à l'utilisateur de la fonction ? Je pense que oui.

Dans Creer_resident_ville, peut-on capturer une erreur (cas n°2) et la gérer du coup ? Si (cas n°1), une erreur est jettée, la transaction se ROLLBACK toute seule, et la fonction s'arrête ?

Donc, en dehors de ces questions, comment exécuter des commandes SQL dans le code PL/SQL (enfin, l'équivalent PostgreSQL) ?

J'essayerai une fonction si j'ai le temps demain (Dimanche), là il est tard et mes tables sont toujours sur MySQL.

Il faut aussi que je rajoute des CHECK pour codePostal, genre (je ne veux pas de valeur '' (item 0)), etc.

Attention, il ne s'agit pas ici de critiquer la modélisation et la logique, mais de voir comment utiliser des fonctions, triggers, etc pour effectuer des tâches.

Autre question : ces fonctions sont-elles disponibles si j'interdis l'accès direct aux tables à l'utilisateur mais permet l'accès aux fonctions ?

[devlop78]

Lisez ce que j'ai écrit sur le sujet...
http://sqlpro.developpez.com/isolation-transaction/

A +

Oui, j'ai pu lire des articles, et tu en as fait pas mal. J'ai notamment imprimé "Niveau d'isolation et anomalies transactionnelles" de Frédéric Brouard (différents modes de verrouillage avec tableau de comparaison), j'ai lu un article sur les "verrous mortels" (de mémoire, mais je ne sais plus dans lequel), j'ai imprimé "Bases de donneés relationnelles et contraintes SQL" de SQLpro", et j'ai pu retenir que l'on a des contraintes de lignes/table (CHECK, UNIQUE, etc), des contraintes de base (assertions), et après triggers qui sont des procédures stockées déclenchées, qui sont d'après moi des "évenements" au regard d'un langage événementiel. J'ai imprimé "Limiter la complexité du code applicatif grâce au SGBD" de Alain Defrance en cherchant une liste exhaustive et clair des contraintes (et la différence entre tous ces termes pas évidents au début), et qui m'a conforté dans la vision de division des responsabilités, vision que je développe de plus en plus avec l'orienté objet (abstraction/délégation). j'ai aussi imprimé une petite page ... voilà, par exemple "Les contraintes de table" : Elles valident la ligne d'une table. Tout est dit en une phrase, là où des fascicules entier sur le net échouent. Enfin, merci à vous ;o

[devlop78]

When the PREPARE statement is executed, the specified statement is parsed, rewritten, and planned. When an EXECUTE command is subsequently issued, the prepared statement need only be executed.

Dois-je comprendre que les fonctions stockées ne sont pas compilées ? D'un point de vue performances, elles n'offrent donc pas de réels avantages, contrairement aux requêtes préparées ?

[devlop78]

By default, any error occurring in a PL/pgSQL function aborts execution of the function, and indeed of the surrounding transaction as well. You can trap errors and recover from them by using a BEGIN block with an EXCEPTION clause

Ok, la gestion des erreurs est donc possible Ouf !

Je galère toujours là pour retourner un jeu d'enregistrement ... j'ai mis SET OF RECORD en sortie, et

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SELECT INTO toReturn id FROM test WHERE chiffre > 21;
RETURN NEXT toReturn;

Mais lorsque je fais SELECT * FROM essai2() il n'en veut pas ...

une liste de définition de colonnes est requise pour les fonctions renvoyant
un « record »

[estofilo]

Dois-je comprendre que les fonctions stockées ne sont pas compilées ? D'un point de vue performances, elles n'offrent donc pas de réels avantages, contrairement aux requêtes préparées ?

Les fonctions s'exécutant dans le contexte du serveur évitent des transferts de données de serveur à client qui peuvent être coûteux, ça dépend de ce qu'elles font.

[estofilo]

Ok, la gestion des erreurs est donc possible Ouf !

Je galère toujours là pour retourner un jeu d'enregistrement ... j'ai mis SET OF RECORD en sortie, et

SELECT INTO toReturn id FROM test WHERE chiffre > 21;
RETURN NEXT toReturn;

Mais lorsque je fais SELECT * FROM essai2() il n'en veut pas ...

En SQL on ne peut pas faire référence à des résultats dont les noms et les types de colonnes ne seraient connus qu'après l'exécution, puisque justement on en a besoin pour calculer le plan d'exécution.

Si la fonction renvoie des lignes de la table test, il est préférable de le déclarer explicitement comme type de retour plutôt que SET OF RECORD qui est difficilement exploitable par la suite.

[devlop78]

Au fait, pgAdmin III, ou phppgAdmin ? Perso, j'ai une préférence pour pgAdmin III ...

estofilo, l'idée serait par exemple d'avoir une fonction stockée de pagination de la vue "aperçu", avec un LIMIT sur le résident (pour éviter de couper puisque resident-residence est à 1-n).

La requête SQL, pas de problème, mais il faudrait en entrée un INTEGER pour l'OFFSET, et en sortie un ensemble de lignes comportant plusieurs colonnes. Comment, à partir d'une requete, on peut faire ça ? Avec un FOR statement IN ? Oui, mais comment le sortir ?