Plusieurs questions techniques

guk92 · 10/10/2011 22:16:13

Bonsoir,

J'ai de nouveau plusieurs questions en tête, je les ai donc de mis de coté pour vous les poser ce soir si cela ne vous embête pas.
Les questions sont plutôt longues mais les réponses peuvent se résumer à un OUI ou NON (avec peut-être une phrase d'explication si le coeur y est ).

Mes premières questions portent sur les procédures, fonctions et requêtes type "INSERT / UPDATE / DELETE" :

1. Une procédure ou fonction sous PostgreSQL commence avec la syntaxe "CREATE FUNCTION", donc pour distinguer une procédure d'une fonction, une procédure est censée retourner un void (RETURNS void) et une fonction tout les autres types (RETURNS integer etc...), est-ce bien cela ?

2. J'ai remarqué que les FUNCTION contenant au moins un argument en mode OUT ne peuvent pas avoir de RETURNS void, mais peuvent contenir un RETURNS integer ! ... ou rien (ce que je trouve plus normal).
Normalement, un FUNCTION contenant un paramètre OUT doit être considéré comme une procédure et non une fonction, car d'après ce que j'ai vu sous SQL Server, seules les procédures peuvent contenir des arguments en mode OUT (appelé OUTPUT sous SQL Server) mais pas les fonctions.

Donc pourquoi une FUNCTION avec des arguments en mode OUT peut contenir un RETURNS integer ?
Pourquoi ne peut-t-il pas contenir de RETURN value dans le cas où l’on fait RETURNS integer ?
Est-ce un bug ?

Exemple de test :

CREATE FUNCTION test(OUT var1 integer, var2 integer, var3 integer)
/* Je suis obligé d'avoir un RETURNS type différent de void lorsque j'ai des arguments en mode OUT, 
mais je peux enlever le RETURNS value et cela fonctionne */
RETURNS integer -- Ou rien
AS $$
BEGIN

INSERT INTO  t1(date, vala, valb, valc) VALUES(NOW(), var1, var2, var3);

RETURN 1; -- Ceci génère une erreur lorsque j'ai un paramètre en mode OUT et RETURNS integer.
END;
$$ LANGUAGE plpgsql;

3. J'ai remarqué que les FUNCTION PostgreSQL retournant une valeur (donc une fonction et non une procédure) peuvent contenir des requêtes de type INSERT / UPDATE / DELETE, est-ce que c'est normal ? Pourquoi ?
Pour comparaison, sous SQL Server, seules les procédures (CREATE PROCEDURE) peuvent contenir ce type de requête (INSERT INTO / UPDATE / DELETE) et non les fonctions (CREATE FUNCTION).

Les trois questions qui vont suivre, portent sur les transactions :
4. J'ai créé une transaction sous Query de PostgreSQL :

BEGIN;
INSERT INTO t1(date, vala, valb, valc) VALUES(NOW(), 1, 1, 1);
UPDATE t1 SET date=NOW() WHERE id = 1;
COMMIT;

Ce bout de code fonctionne parfaitement, pourtant lorsque j'essaye de l'intégrer dans une procédure, cela ne fonctionne pas et j'ai une erreur SQL 42601 qui apparaît.
Ne peut-t-on pas faire de transaction dans des procédures sous PostgreSQL ? J'ai créé une procédure contenant une transaction sous SQL Server et cela fonctionne.

5. J'ai essayé de créer une transaction contenant une autre transaction (cela s'appelle nested transaction d'après ce que j'ai lu sur internet) en reprenant l'exemple précédent :

BEGIN;
INSERT INTO t1(date, vala, valb, valc) VALUES(NOW(), 1, 1, 1);
	BEGIN;
 	UPDATE t1 SET date=NOW() WHERE id = 1;
	COMMIT;
COMMIT;

J'ai ce message qui apparaît :

ATTENTION: une transaction est déjà en cours
ATTENTION: aucune transaction en cours
La requête a été exécutée avec succès en 15 ms, mais ne renvoie aucun résultat.

Pourquoi un message d'avertissement apparaît ? N'a-t-on pas le droit de faire cela en temps normal ?

6. J'ai vu des exemples sur internet avec un bloc de transaction contenant une seule requête seulement.
Est-ce qu'il peut être utile d'avoir un bloc de transaction ne contenant qu'une seule requête seulement ?
Si oui pourquoi ?
Est-ce que c'est parce ce qu'une requête peut prendre du temps à s'exécuter et qu'entre temps on ne veut pas qu'il y ait un changement pouvant être provoqué par un autre utilisateur ?

Ma dernière question est plus lié à la norme SQL :
7. Est-ce que les jointures avec la clause USING font parti de la norme SQL ?

 -- Forme 1, tel qu’on la connaît
SELECT champs
FROM table1 
JOIN table2 
ON table1.id = table2.id

-- Équivalent forme 2 avec USING
SELECT champs
FROM table1 
JOIN table2 
USING(id)

Je vous remercie pour vos réponses

Dernière modification par guk92 (10/10/2011 22:17:35)

gleu · 10/10/2011 23:11:15

1. Oui

2.
a. Elles ne peuvent pas avoir de RETURNS, y compris un RETURNS integer. PostgreSQL ne dit rien pour votre fonction car vous dites deux fois la même chose : je renvoie un entier (RETURNS integer), et je renvoie un entier grâce au paramètre var1 (hé oui, pas de OUT pour les autres, donc ils sont en IN par défaut). Par contre, essayer de mettre OUT aux autres et vous le verrez se plaindre.

b. Parce que vous êtes sensé renvoyer les infos par le paramètre var1.

c. Non. Très sincèrement, c'est très difficile de trouver un bug dans PostgreSQL.

3. Oui, c'est normal. Il n'y a pas vraiment de réponses à "pourquoi" en dehors du fait qu'il n'y a aucune raison à l'empêcher.

4. PostgreSQL ne gère pas les sous-transactions et une fonction est en elle-même une transaction. Donc, non, pas possible de faire de COMMIT dans une fonction.

5. Le premier message est pour le deuxième BEGIN. Il vous avertit seulement que vous êtes déjà dans une transacton. Le deuxième message est pour le deuxième COMMIT car à ce moment-là, vous n'êtes plus dans une transaction. Comme dit juste au-dessus, PostgreSQL ne gère pas les transactions imbriquées/sous-transactions/nested transactions.

6. Euh... non. Je ne vois aucune raison à cela. Un requête sans BEGIN auparavant est une transaction en elle-même. Donc aucun intérêt.

7. Il me semble que oui mais il me semble aussi que je ne sois pas la meilleure personne pour répondre à ça.

J'en profite pour vous poser moi-aussi une question, cette fois sur SQL Server. Est-il possible d'exécuter des CREATE TABLE, CREATE INDEX et autres dans une transaction avec SQL Server ?

rjuju · 10/10/2011 23:16:57

guk92 a écrit :

5. J'ai essayé de créer une transaction contenant une autre transaction (cela s'appelle nested transaction d'après ce que j'ai lu sur internet) en reprenant l'exemple précédent :
BEGIN;
INSERT INTO t1(date, vala, valb, valc) VALUES(NOW(), 1, 1, 1);
	BEGIN;
 	UPDATE t1 SET date=NOW() WHERE id = 1;
	COMMIT;
COMMIT;
J'ai ce message qui apparaît :
ATTENTION: une transaction est déjà en cours
ATTENTION: aucune transaction en cours
La requête a été exécutée avec succès en 15 ms, mais ne renvoie aucun résultat.
Pourquoi un message d'avertissement apparaît ? N'a-t-on pas le droit de faire cela en temps normal ?

Si c'est pour pouvoir annuler une partie des requêtes en cours de traitement, vous pouvez mettre des points de sauvegarde à l'intérieur d'une transaction et ensuite choisir d'annuler ce qui a été fait depuis un des points de sauvegarde.

http://docs.postgresqlfr.org/9.0/sql-savepoint.html

Dernière modification par rjuju (10/10/2011 23:18:28)

SQLpro · 11/10/2011 00:54:47

gleu a écrit :

J'en profite pour vous poser moi-aussi une question, cette fois sur SQL Server. Est-il possible d'exécuter des CREATE TABLE, CREATE INDEX et autres dans une transaction avec SQL Server ?

Oui. D'après la norme SQL les transcation portent sur tout l'étendue des ordres SSL à l’exception de la gestion des privilèges, qui pour des raison de sécurité doit être immédiat et non "révocable" par la transaction.
Donc les ordre DDL CREATE, ALTER, DROP... doivent être transactionné, ce que SQL Server gère depuis l'origine. En revanche Oracle ne sait pas faire cela...

A +

Dernière modification par SQLpro (11/10/2011 00:55:26)

gleu · 11/10/2011 06:52:27

OK, merci pour l'info.

guk92 · 11/10/2011 13:59:25

Tout d'abord je tiens à vous remercier pour vos réponses et je vais commencer par répondre à ta question Gleu.

Il est en effet possible de faire des CREATE TABLE dans une transaction sous SQL Server, voici la preuve en image d’une procédure contenant une transaction (contenant elle-même une création de table) :

PS: j'ai remarqué que j'ai inséré un entier dans une colonne de type char, mais ça fonctionne ( ).

Par rapport à ta réponse n°4 :

Gleu a écrit :

Une fonction est en elle-même une transaction

Si c'est vrai pour PostgreSQL c'est génial, ainsi pas de problème entre différents utilisateurs qui lancent en même temps la fonction (ou procédure).
Par contre je ne sais pas s'il est considéré de même pour une procédure sous SQL Server, car il m'a déjà semblé exécuter une procédure (sans transactions et contenant plusieurs requêtes d'insertion) sur deux clients en même temps, et les données que j'ai inséré dans une même table étaient mélangés dans le temps (un coup un bloc de données insérées provenant du client 1, un coup un bloc de données insérées provenant du client 2 et ainsi de suite).
La preuve en image :

(Les données des utilisateurs 1 et 2 sont mélangés => on n'a pas une suite de 1, puis une suite de 2).
Bien sur ces tests étaient sous SQL Server 2005 sans mises à jour, je ne sais pas si cela a changé depuis.

Mais sinon il est aussi possible de créer des tables dans des transactions sous PostgreSQL :

BEGIN;
CREATE TABLE tableTest1(colonne1 integer);
INSERT INTO tableTest1(colonne1) VALUES(134);
COMMIT;

SELECT * FROM tableTest1;

Par contre, pour les INDEX sous SQL Server je ne sais pas du tout (pas encore pratiqué).

Rjuju,
Il est aussi possible de faire des SAVEPOINT sous SQL Server, sauf que le mot-clé est SAVE, il faut lui ajouter impérativement TRANSACTION et un nom de point de sauvegarde :

-- PostgreSQL :
SAVEPOINT pointSauvegarde ;

-- SQL Server :
SAVE TRANSACTION pointSauvegarde ;

Autrement, d'après un exemple officiel SQL Server, il est possible de créer des transactions dans des transactions :

BEGIN TRAN T1;
UPDATE table1 ...;
     BEGIN TRAN M2 WITH MARK;
     UPDATE table2 ...;
     SELECT * from table1;
     COMMIT TRAN M2;
UPDATE table3 ...;
COMMIT TRAN T1;

Gleu,
Pour ma question n°2, me conseillez-vous de ne pas utiliser de RETURNS type lorsque j'utilise un paramètre en mode OUT ?
Car à vrai dire, je ne vois pas l'utilité de mettre un RETURNS type si cela fonctionne sans

Merci

Dernière modification par guk92 (11/10/2011 14:03:06)

SQLpro · 11/10/2011 16:04:59

Plusieurs choses à dire :

1) la démo de transaction DDL est mauvaise car sans la transaction ça marche aussi. Elle aurait été plus probante si vous aviez fait un ROLLBACK et montré que la table avait disparue.

2) le fait qu'une fonction soit en elle même une transaction est un choix plus que discutable. En effet que se passe t-il si vous avez lancé une transaction qui lance une procédure ? (transactions imbriquées)
Le cas des transactions imbriquées est toujours complexe, mais sans méthode de résolution, il est difficile de savoir ce qui se passe.
En sus ce n'est pas parce que il y a une erreur que l'on doit considérer automatiquement que la transaction doit être annulé. C'est en principe l'utilisateur et lui seul qui devrait décider.
Considérant par exemple, une gestion de stocke, on peut essayer de déstocker du matériel et partir en erreur dans une table car on dépasse le seuil. Mais on peut alors décider en cas d'erreur de lancer une commande. Ce que visiblement PG ne sais pas faire au sein d'une même fonction.
Dans ce sens, PG n'est pas conforme à la norme SQL qui précise que les transactions survivent au niveau de la session.

3) "mélange"
Dans une base de données, par nature ensembliste il n'y a pas d'ordre de données. pas plus que dans un sac. A lire : "les données des bases sont des ensembles"

A +

Dernière modification par SQLpro (11/10/2011 16:06:20)

rjuju · 11/10/2011 16:20:45

guk92 a écrit :

(Les données des utilisateurs 1 et 2 sont mélangés => on n'a pas une suite de 1, puis une suite de 2).
Bien sur ces tests étaient sous SQL Server 2005 sans mises à jour, je ne sais pas si cela a changé depuis.

L'ordre de visualisation des lignes correspond à l'ordre d'utilisation du compteur associé lors de l'insertion. Sur postgresql, le résultat sera équivalent car les séquences n'ont aucune contrainte de transaction pour assurer leur unicité, et peuvent ainsi se "mélanger" lors de plusieurs appels concurrents.

SQLpro · 11/10/2011 16:50:50

rjuju a écrit :

L'ordre de visualisation des lignes correspond à l'ordre d'utilisation du compteur associé lors de l'insertion. Sur postgresql, le résultat sera équivalent car les séquences n'ont aucune contrainte de transaction pour assurer leur unicité, et peuvent ainsi se "mélanger" lors de plusieurs appels concurrents.

Dernière modification par SQLpro (11/10/2011 16:51:31)

gleu · 11/10/2011 18:22:40

En effet que se passe t-il si vous avez lancé une transaction qui lance une procédure ? (transactions imbriquées)

Pas dans PostgreSQL. La procédure n'est considéré comme une transaction qu'à partir du moment où il n'y a pas de transaction explicite.

Ce que visiblement PG ne sais pas faire au sein d'une même fonction.

Dans une fonction, une erreur lève une exception qu'il est possible de récupérer et donc de continuer la fonction.

Pour ma question n°2, me conseillez-vous de ne pas utiliser de RETURNS type lorsque j'utilise un paramètre en mode OUT ?

Je préfère la méthode RETURNS type s'il n'y a qu'une valeur à renvoyer, mais d'autres préféreront autre chose. PostgreSQL propose les deux pour mieux s'adapter.

#1 10/10/2011 22:16:13

Plusieurs questions techniques

#2 10/10/2011 23:11:15

Re : Plusieurs questions techniques

#3 10/10/2011 23:16:57

Re : Plusieurs questions techniques

#4 11/10/2011 00:54:47

Re : Plusieurs questions techniques

#5 11/10/2011 06:52:27

Re : Plusieurs questions techniques

#6 11/10/2011 13:59:25

Re : Plusieurs questions techniques

#7 11/10/2011 16:04:59

Re : Plusieurs questions techniques

#8 11/10/2011 16:20:45

Re : Plusieurs questions techniques

#9 11/10/2011 16:50:50

Re : Plusieurs questions techniques

#10 11/10/2011 18:22:40

Re : Plusieurs questions techniques

Pied de page des forums

PostgreSQL.fr

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux