Chapitre 4  PostgreSQL

• Introduction
• Objets binaires
• Sauvegarde
• Contraintes, triggers et procédures stockées
• Optimisation
• Sécurité
• Références

4.1  Introduction

4.1.1  PostgreSQL vs MySQL 

4.2  Objets binaires

4.2.1  Import/export de blobs

CREATE TABLE isohyetes (
    code_num_pays   numeric(3),
    annee           numeric(4),
    mois            numeric(2),
    image           oid
);
INSERT INTO isohyetes (code_num_pays, annee,mois,image)
    VALUES (33,2002,11, lo_import('/etc/motd'));
SELECT lo_export(isohyetes.image, '/tmp/motd') FROM isohyetes
    WHERE annee = 2002 and mois=11 and code_num_pays=33;

select lo_unlink((select image from isohyetes 
    where  annee = 2002 and
           mois=11 and code_num_pays=33));
delete from isohyetes where annee = 2002 and
           mois=11 and code_num_pays=33;

4.2.2  Blobs en PHP

<?php
$database = pg_connect ("dbname=cpc");
// pg_exec ($database, "begin");
$req="INSERT INTO isohyetes (code_num_pays, annee,mois,image)
     VALUES (33,2002,11, lo_import('/etc/motd'))"; 
if (!pg_exec ($database, $req))
     print pg_errormessage();
else {
  print ("ok<br>");
  $req="SELECT lo_export(isohyetes.image, '/tmp/motd') FROM isohyetes
    WHERE annee = 2002 and mois=11 and code_num_pays=33";
  if (!pg_exec ($database, $req))
     print pg_errormessage();
  else
    print "ok<br>";
}
 ?>

<?php
$database = pg_connect ("dbname=form1");
pg_exec ($database, "begin");
$oid = pg_locreate ($database);
echo ("$oid\n");
$handle = pg_loopen ($database, $oid, "w");
echo ("$handle\n");
pg_lowrite ($handle, "gaga");
pg_loclose ($handle);
pg_exec ($database, "commit");
 ?>

4.3  Sauvegarde

• Sauvegarde système au niveau des fichiers;
• sauvegarde PostgreSQL : dump.

4.3.1  Dump

pg_dump

• Les requêtes générées ne sont pas toujours bien organisées: cela peut poser un problème s'il y a des dépendances circulaires dans le schéma;
• par défaut, les blobs ne font pas partie du dump. Il faut explicitement utiliser l'option -b et un format de sortie de type tar ou customisé (options -F t ou -F c).

pg_dumpall

4.3.2  Restauration

pg_restore

4.4  Contraintes, triggers et procédures stockées

1. Les contraintes déclaratives
2. Les triggers (standard à partir de SQL/99) et procédures stockées (non-standard).

4.4.1  Les contraintes declaratives

• Contraintes de domaine: limitent les valeurs qui peuvent être prises par un attribut (not null, unique, default, checke.g. check(latitude between -90 and 90)).
• Contraintes d'intégrité d'entité: précisent la clé primaire
  (e.g. primary key (NUMERO_STATION)).
• Contraintes d'intégrité référentielle qui assurent la cohérence des relations clé primaire - clé étrangère correspondante (e.g.

    alter table STATION
           add foreign key (CODE_NUM_PAYS)
              references PAYS (CODE_NUM_PAYS);
  

  
  
  
• Les assertions, qui imposent des contraintes plus générales entre attributs quelconques, s'utilisent comme quand elles ne sont pas rattachées à une table en particulier, par exemple:

      create assertion pasdeventeaperte check (
         not exists (select * from
                     ventes where prixvente < 
                      (select prixachat from articles where
                       articles.prodid = ventes.prodid)));
  

4.4.2  Les triggers

     CREATE TRIGGER nom_du_trigger
     evenement ON table
     WHEN (condition)
     instructions
     FOR REACH ROW | STATEMENT

     create trigger coherencedatereleve
        after insert on releve_quotidien
          for each row(
         update  releve_quotidien set numero_annee = 
               date_part('year',date_releve);
         update  releve_quotidien set numero_mois = 
               date_part('month',
        date_releve));

4.4.3  Les procédures stockées

     CREATE FUNCTION miseajour () RETURNS opaque AS '
       BEGIN
         update  releve_quotidien set numero_annee = 
              extract(YEAR FROM date_releve);
         update  releve_quotidien set numero_mois = 
              extract(MONTH FROM date_releve);
         return NEW;
       END;
     ' LANGUAGE 'plpgsql';

     create trigger coherencedatereleve
        after insert on releve_quotidien
          for each row
            execute procedure miseajour();

drop trigger coherencedatereleve on releve_quotidien;
drop function miseajour();

4.5  Optimisation

4.5.1  Index de petites tables

4.5.2  Index de grosses tables

4.5.3  Explain

mabd=# EXPLAIN SELECT t12_bskid, t12_pnb, t12_lne, t12_tck
mabd-# FROM t12
mabd-# WHERE t12_bskid >= 35
mabd-# ORDER BY t12_bskid, t12_pnb, t12_tck, t12_lne;
Sort  (cost=1348.70..1348.70 rows=8565 width=16)
  ->  Seq Scan on t12  (cost=0.00..789.20 rows=8565 width=16)

mabd=# create index t12_bskid_pnb_tck_lne 
       on t12 (t12_bskid, t12_pnb, t12_tck, t12_lne);
CREATE

mabd=# EXPLAIN SELECT t12_bskid, t12_pnb, t12_lne, t12_tck
mabd-# FROM t12
mabd-# WHERE t12_bskid >= 35
mabd-# ORDER BY t12_bskid, t12_pnb, t12_tck, t12_lne;
NOTICE:  QUERY PLAN:
Sort  (cost=1348.70..1348.70 rows=8565 width=16)
  ->  Seq Scan on t12  (cost=0.00..789.20 rows=8565 width=16)

mabd=# vacuum analyze t12;
VACUUM
Et l'on regarde une nouvelle fois les performances:
\begin{verbatim}
mabd=# EXPLAIN SELECT t12_bskid, t12_pnb, t12_lne, t12_tck
mabd-# FROM t12
mabd-# WHERE t12_bskid >= 35
mabd-# ORDER BY t12_bskid, t12_pnb, t12_tck, t12_lne;
NOTICE:  QUERY PLAN:
Index Scan using t12_bskid_pnb_tck_lne on t12  
(cost=0.00..2232.11 rows=25693 width=16)

mabd=# set enable_seqscan='false';
SET VARIABLE

mabd=# set enable_seqscan='true';
SET VARIABLE

4.6  Sécurité

4.6.1  Fonctions et triggers

4.6.2  Privilèges

4.7  Références

1. http://postgresql.org
2. http://phd.pp.ru/Software/SQL/PostgreSQL-vs-MySQL.html
3. http://www.geocities.com/mailsoftware42/db/dbs.html
4. http://www.linux-france.org/article/serveur/psql/