PostGreSQL est-il un vrai SGBD relationnel ?

PostGreSQL est il un vrai SGBD Relationnel ? Au regard des règles de Codd et des réflexions de Chris Date, il semblerait que non.
Voici quelques éléments qui vous permettrons d’apprécier les limites de PostGreSQl et de trouver certains moyens de contournement.

En octobre 1985, Franck Edgard Codd, père fondateur des bases de données relationnelles édictait, à travers deux articles
de vulgarisation du magazine Computerworld (Is your DBMS really relational ? et Does your DBMS run by the rules ?), douze règles
formalisent la notion de SGBD relationnel.
Ces règles de (numérotés de 0 à 12, donc en fait treize…) formalisent la notion de SGBD relationnel et gravent ainsi dans le
marbre les 13 commandements de la Loi pour tout SGBD se prétendant relationnel.
A lire : les règles de Codd pour les SGBD Relationnels

D’où la question : PostGreSQL est-il un vrai SGBD Relationnel ?

NOTA : test effectués avec la version : « PostgreSQL 9.1.3, compiled by Visual C++ build 1500, 32-bit »

1 Рpremier probl̬me

viol de la règle de CODD n°7 – Insertion, suppression et modification ensemblistes :
 »
Le SGBDR retourne un ensemble d’éléments en réponse aux requêtes qui lui sont
soumises. Il doit pouvoir mettre à jour un ensemble d’éléments en exécutant une seule
requête.
« 


CREATE TABLE T_UNIK (C INT CONSTRAINT PK PRIMARY KEY);
 
INSERT INTO T_UNIK VALUES (1), (2), (3);
 
SELECT * FROM T_UNIK;
 
UPDATE T_UNIK
SET C = C + 1;
 
********** Erreur **********
 
ERREUR: la valeur d'une clé dupliquée rompt la contrainte unique « t_unik_pkey »
État SQL :23505
Détail :La clé « (c)=(2) » existe déjà.

Cette requête passe sur Oracle, MS SQL Server, IBM DB2, etc…

En fait PostGreSQL ne sait pas faire des mises à jour ensembliste et agit itérativement.
Cependant, PostGreSQL fournit une rustine pour ce faire, en permettant de vérifier la contrainte en fin de transaction…
Mais il faut reconstruire la table (pas de ALTER TABLE … ALTER CONSTRAINT … DEFERRABLE INITIALLY DEFERRED);
Voici la solution de contournement :


DROP TABLE T_UNIK;
 
CREATE TABLE T_UNIK (C INT CONSTRAINT PK PRIMARY KEY DEFERRABLE INITIALLY DEFERRED);
 
INSERT INTO T_UNIK VALUES (1), (2), (3);
 
UPDATE T_UNIK
SET C = C + 1;
 
SELECT * FROM T_UNIK
 
C
-----------
2
3
4

Le problème principal est que PostGreSQL n’est pas capable de faire des requêtes portant directement sur des ensembles de données et fonctionne de manière itérative, c’est à dire, ligne à ligne et vérifie les contraintes à chaque UPDATE monoligne, contrairement à ses grand ancêtres, qui eux fonctionnent proprement !
Cette limitation est inhérente à la conception technique de PostGreSQL qui n’est pas capable de faire des requêtes mutilthreadées, c’est à dire de lancer plusieurs processus pour traiter une seule et même requête, en d’autre termes, exécuter chacune des tâches du plan de requête de manière parallèle (ce que font Oracle ou SQL Server de manière naturelle).
Mais cette solution de contournement est en contradiction apparente avec une autre règle de Codd :

RÈGLE 12 – Non subversion :
 »
Il ne doit pas être possible de transgresser les règles d’intégrité et les contraintes définies par le langage relationnel du SGBDR en utilisant un langage de plus bas niveau (gérant une seule ligne à la fois)
« 

Bref, peut-on parler de vrai SGBD relationnel au sens de Codd ?
À l’évidence NON !

Mais poussons un peu les tests…

2 – Test de « compréhension » du moteur relationnel.

Dans un papier de 2004 intitulé « A Cure for Madness » , Chris Date attire l’attention sur le fait que toute requête doit être évaluée logiquement par l’optimiseur, avant d’être traitée syntaxiquement et d’être exécutée.

Voici un exemple tiré de l’exemple posté par Codd :


CREATE TABLE T_CHRIS_DATE (TYP CHAR(4), VAL VARCHAR(16));
 
INSERT INTO T_CHRIS_DATE VALUES
('NUM', '1234'),
('ALFA', 'ecfs'),
('NUM', '4567'),
('NUM', '11'),
('ALFA', 'rtg'),
('ALFA', 'yfc'),
('NUM', '9'),
('ALFA', 'pkn');

Recherchons les nombres supérieurs à 1000

code>SELECT *
FROM   T_CHRIS_DATE
WHERE  VAL > 1000;

********** Erreur **********

ERREUR: l'opérateur n'existe pas : character varying > integer
État SQL :42883
Astuce : Aucun opérateur ne correspond au nom donné et aux types d'arguments.
Vous devez ajouter des conversions explicites de type.
Caractère : 41

Sur ce point tous les SGBDR se ressemblent et génèrent une erreur !
Mais l’erreur n’est pas du tout la même sur les autres SGBDR qui font une erreur de conversion bien normale…
Tout cela n’est pas bien grave, car le principe est que l’on ne mélange pas les choux et les carottes, or SQL est un langage typé, il faut soit considérer des chaines de caractères soit des entiers, pas les deux.

Mais nous avons la chance dans cette table que des chaînes de caractères, bien que typées comme telles représentent en fait des nombres et de surcroit un critère dans la colonne TYP permet de les distinguer.
Il suffit donc de faire :


SELECT *
FROM   T_CHRIS_DATE
WHERE  TYP = 'NUM'
  AND  VAL > 1000;

Malheureusement, alors que cette requête est correctement exécutée sur Oracle, IBM DB2 ou SQL Server, PostGreSQL s’avère incapable de produire le même résultat !

Il persiste dans sa fausse erreur, qui la confine à la bêtise !

********** Erreur **********
 
ERREUR: l'opérateur n'existe pas : character varying > integer
État SQL :42883
Astuce : Aucun opérateur ne correspond au nom donné et aux types d'arguments.
Vous devez ajouter des conversions explicites de type.
Caractère : 60

Qu’à cela ne tienne, obéissons lui et castons (Lagaffe !)


SELECT *
FROM   T_CHRIS_DATE
WHERE  TYP = 'NUM'
  AND  CAST(VAL AS INT) > 1000;

Finalement PostGreSQL s’en sort bien :


TYP      VAL
-------- ----------
NUM      1234
NUM      4567

Mais récrivons cette requête autrement :


SELECT *
FROM   T_CHRIS_DATE
WHERE  CAST(VAL AS INT) > 1000
  AND  TYP = 'NUM';

Bravo, il ne tient justement pas compte de l’ordre des prédicats dans la clause WHERE, tout comme ses grands ainés !

Essayons encore une autre version :


SELECT *
FROM   (SELECT *
        FROM   T_CHRIS_DATE
        WHERE  TYP = 'NUM') AS T
WHERE  CAST(VAL AS INT) > 1000;

Pas beaucoup de mérite, mais bien quand même… Essayons encore une autre approche :


SELECT *
FROM   (SELECT *
        FROM   T_CHRIS_DATE
        WHERE  CAST(VAL AS INT) > 1000) AS T
WHERE  TYP = 'NUM';

Formidable, tout comme ses grand frères, PostGreSQL a récrit la requête interne, normalement infaisable, par algébrisation afin de la rendre opérationnelle.
C’est un remarquable progrès, car il y a encore quelques années PostGreSQL n’était pas capable d’une telle prouesse.

CONCLUSION

Si l’on ne peut pas encore parler de véritable SGBDR, par le fait que la règle 7 de Codd est mal implémentée, on peut dire quze fonctionnellement on s’en rapproche à grand pas et au moins une solution de contournement est produite pour ceux qui veukent passer du monde commercial au monde libre.
Il reste cepandant encore quelques points que je qualifierait de « durs » avec MySQL, notamment en ce qui concerne la règle 11 concernant le partitionnement des tables.
En effet, la solution de partitionnement des tables à la sauce PostGreSQL est tout sauf exploitable et les performances en sont globalement plus que piètre et souvent moins bonne qu’une solution de table non partitionnée (voir les discussions interne sur ce délicat sujet « Table partitioning« ), ainsi que les nombreuses complaintes sur l’exploitation du partitionnement mode PG dans les forums de discussions)

En fait il y a même tromperie sur la marchandise, car ce qui est présenté comme un partitionnement n’est autre que de la fédération de tables, ce que la plupart des SGBDR faisaient il y a bien longtemps (pour une explication de la différence de ces deux concepts, voir la 4e édition de mon ouvrage sur SQL, collection Synthex, Pearson Eduction, paris Juin 2012).

RÈGLE 11 – Indépendance de distribution :
 »
Le langage relationnel doit permettre aux programmes d’application et aux requêtes de demeurer identiques sur le plan logique lorsque des données, quelles qu’elles soient, sont physiquement réparties ou centralisées.
« 


--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *

MVP Microsoft SQL Server

8 réflexions au sujet de « PostGreSQL est-il un vrai SGBD relationnel ? »

  1. Avatar de tchize_tchize_

    Bonjour,

    sous des abords intéressant, je me permet de mettre fortement en doute les dires de l’auteur, il me semble qu’aucune de ses informations ne sont etayées par des tests réels. Avez vous réellement fait vos test sur les sgbd précités, ou vous contentez vous de propager des rumeurs trouvées ailleurs? De simple tests remettent en cause vos dire par rapport à Oracle:

    Cette requête passe sur Oracle, MS SQL Server, IBM DB2, etc…
    Je ne suis pas d’accord. Certe le update passe, mais le insert ne marche pas:


    Error starting at line 3 in command:
    INSERT INTO T_UNIK VALUES (1), (2), (3)
    Error at Command Line:3 Column:29
    Error report:
    SQL Error: ORA-00933: SQL-opdracht is niet juist beëindigd.
    00933. 00000 – « SQL command not properly ended »
    *Cause:
    *Action:

    Autre chose:

    SELECT *
    FROM   T_CHRIS_DATE
    WHERE  TYP = 'NUM'
      AND  VAL > 1000;

    Malheureusement, alors que cette requête est correctement exécutée sur Oracle, IBM DB2 ou SQL Server, PostGreSQL s’avère incapable de produire le même résultat !

    Il persiste dans sa fausse erreur, qui la confine à la bêtise !

    Non et non, oracle, qui pourtant est réputé pour avoir un bon analyseur et optimisateur de requêtes, sort le même résultat. L’optimisateur n’est pas une raison pour laisser faire ce qui n’est pas autorisé. Un varchar, ce n’est pas un nombre!

    Je ne dispose ici que d’un Oracle, mais quand en prenant le seul SGBD que je manipule régulièrement, je contredit tout ce qui est dessus, je ne peux que me poser des questions sur les autres SGBD…

    1. Avatar de SQLproSQLpro Auteur de l’article

      L’auteur de l’article original de « a cure for madness » qui n’est autre que Chris Date (http://en.wikipedia.org/wiki/Christopher_J._Date), avait testé ceci sur Oracle, qui à l’époque ne le supportait pas et sous IBM DB2.
      Oracle avait ensuite rectifié l’algébriseur (et non l’optimiseur) pour que cette requête fonctionne.
      Oracle serait-il revenu en arrière ?
      Lisez ceci : http://www.freelists.org/post/oracle-l/A-Cure-for-Madness,3

      A +

    2. Avatar de punkoffpunkoff

      l’erreur ORA-00933 ne signifie pas qu’il y a eu violation de contrainte.

      Sous un Oracle 11gR2 le code suivant s’exécute sans aucun problème :

      [code]
      CREATE TABLE T_UNIK (C INT CONSTRAINT PK PRIMARY KEY);

      begin
      INSERT INTO T_UNIK VALUES (1) ;
      INSERT INTO T_UNIK VALUES (2);
      INSERT INTO T_UNIK VALUES (3);
      commit;
      end;

      SELECT * FROM T_UNIK;

      begin
      UPDATE T_UNIK SET C = C + 1;
      commit;
      end;[/code]

  2. Avatar de sqlprosqlpro Auteur de l’article

    Arnaud B. : votre remarque est tout à fait valable. Il n’en reste pas moins que ce n’est pas le fonctionnement attendu d’un SGBDR. Normalement le comportement de mise à jour dans une contrainte UNIQUE ou PK doit pouvoir se faire sans gestion de la déférabilité, ce que Oracle, IBM DB2, MS SQL Server ou Sybase ASA/ASE font depuis des lustres. Il est vrai que ce sont des SGBDR qui parallélisent le traitement des requêtes, ce qu n’est toujours pas capable PostGreSQL.

  3. Avatar de Arnaud B.Arnaud B.

    Concernant la règle n°7, il n’est pas obligatoire de reconstruire les tables si l’on a pris soin de les avoir créées avec l’option DEFERRABLE (on peut tout à fait garder INITIALLY IMMEDIATE).
    Ainsi il suffira au début de la transaction d’indiquer SET CONSTRAINTS ALL DEFFERED pour que la validation des contraintes intervienne en fin de la transaction en cours.

  4. Avatar de mikedavemmikedavem

    La conversion est faite implicitement par SQL Server dans le cas de la deuxième requête.
    On parle de précédence de type et de rang de type. Le rang du type CHAR est plus faible que le rang du type INT. C’est la raison pour laquelle SQL Server peut convertir implicitement sans aucun code supplémentaire le type CHAR en INT sans problème (tant que la conversion s’effectue correctement bien entendu).

    ++

  5. Avatar de zinzinetizinzineti

    Bonjour Frédéric,

    Sous ORACLE la requête suivante :

    SQL> SELECT *
    2 FROM T_CHRIS_DATE
    3 WHERE TYP = ‘NUM’
    4 AND VAL > 1000;
    ERROR:
    ORA-01722: Nombre non valide

    renvoie aussi une erreur ORA-01722

    ORA-01722 :invalid number
    Cause :The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.
    Action : Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character « E » or « e » and retry the operation.

    La version d’ORACLE utilisée :

    Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Prod
    PL/SQL Release 10.2.0.1.0 – Production
    CORE 10.2.0.1.0 Production
    TNS for 32-bit Windows: Version 10.2.0.1.0 – Production
    NLSRTL Version 10.2.0.1.0 – Production

    Cette requête passe sous SQL SERVER 2008 R2, mais de quelle façon ? si on regarde le plan d’exécution on se rend compte aisément que SQL SERVER à juste fait une conversion IMPLICITE (CONVERT_IMPLICIT)de la colonne VAL en INTEGER. S’il existait sous SQL SERVER une option de configuration de la session utilisateur du style SET CONVERT_IMPLICIT ON (comme par exemple l’option de session IMPLICIT_TRANSACTIONS qu’on peut paramétrer à ON puisque cette option est à OFF par défaut pour SQL SERVER)

    Dans ce cas, SQL SERVER va à mon avis renvoyer aussi une erreur du même genre.

    A+

Laisser un commentaire