Création d’objets et mises à jour

Module S2

Dalibo SCOP

24.09

29 août 2024

Sur ce document

Formation	Module S2
Titre	Création d’objets et mises à jour
Révision	24.09
PDF	https://dali.bo/s2_pdf
EPUB	https://dali.bo/s2_epub
HTML	https://dali.bo/s2_html
Slides	https://dali.bo/s2_slides
TP	https://dali.bo/s2_tp
TP (solutions)	https://dali.bo/s2_solutions

Chers lectrices & lecteurs,

Nos formations PostgreSQL sont issues de nombreuses années d’études, d’expérience de terrain et de passion pour les logiciels libres. Pour Dalibo, l’utilisation de PostgreSQL n’est pas une marque d’opportunisme commercial, mais l’expression d’un engagement de longue date. Le choix de l’Open Source est aussi le choix de l’implication dans la communauté du logiciel.

Au‑delà du contenu technique en lui‑même, notre intention est de transmettre les valeurs qui animent et unissent les développeurs de PostgreSQL depuis toujours : partage, ouverture, transparence, créativité, dynamisme… Le but premier de nos formations est de vous aider à mieux exploiter toute la puissance de PostgreSQL mais nous espérons également qu’elles vous inciteront à devenir un membre actif de la communauté en partageant à votre tour le savoir‑faire que vous aurez acquis avec nous.

Nous mettons un point d’honneur à maintenir nos manuels à jour, avec des informations précises et des exemples détaillés. Toutefois malgré nos efforts et nos multiples relectures, il est probable que ce document contienne des oublis, des coquilles, des imprécisions ou des erreurs. Si vous constatez un souci, n’hésitez pas à le signaler via l’adresse formation@dalibo.com !

À propos de DALIBO

DALIBO est le spécialiste français de PostgreSQL. Nous proposons du support, de la formation et du conseil depuis 2005.

Retrouvez toutes nos formations sur https://dalibo.com/formations

Remerciements

Ce manuel de formation est une aventure collective qui se transmet au sein de notre société depuis des années. Nous remercions chaleureusement ici toutes les personnes qui ont contribué directement ou indirectement à cet ouvrage, notamment :

Jean‑Paul Argudo, Alexandre Anriot, Carole Arnaud, Alexandre Baron, David Bidoc, Sharon Bonan, Franck Boudehen, Arnaud Bruniquel, Pierrick Chovelon, Damien Clochard, Christophe Courtois, Marc Cousin, Gilles Darold, Jehan‑Guillaume de Rorthais, Ronan Dunklau, Vik Fearing, Stefan Fercot, Pierre Giraud, Nicolas Gollet, Dimitri Fontaine, Florent Jardin, Virginie Jourdan, Luc Lamarle, Denis Laxalde, Guillaume Lelarge, Alain Lesage, Benoit Lobréau, Jean‑Louis Louër, Thibaut Madelaine, Adrien Nayrat, Alexandre Pereira, Flavie Perette, Robin Portigliatti, Thomas Reiss, Maël Rimbault, Julien Rouhaud, Stéphane Schildknecht, Julien Tachoires, Nicolas Thauvin, Be Hai Tran, Christophe Truffier, Cédric Villemain, Thibaud Walkowiak, Frédéric Yhuel.

Forme de ce manuel

Les versions PDF, EPUB ou HTML de ce document sont structurées autour des slides de nos formations. Le texte suivant chaque slide contient le cours et de nombreux détails qui ne peuvent être données à l’oral.

Licence Creative Commons CC-BY-NC-SA

Cette formation est sous licence CC-BY-NC-SA. Vous êtes libre de la redistribuer et/ou modifier aux conditions suivantes :

Paternité
Pas d’utilisation commerciale
Partage des conditions initiales à l’identique

Vous n’avez pas le droit d’utiliser cette création à des fins commerciales.

Si vous modifiez, transformez ou adaptez cette création, vous n’avez le droit de distribuer la création qui en résulte que sous un contrat identique à celui-ci.

Vous devez citer le nom de l’auteur original de la manière indiquée par l’auteur de l’œuvre ou le titulaire des droits qui vous confère cette autorisation (mais pas d’une manière qui suggérerait qu’ils vous soutiennent ou approuvent votre utilisation de l’œuvre). À chaque réutilisation ou distribution de cette création, vous devez faire apparaître clairement au public les conditions contractuelles de sa mise à disposition. La meilleure manière de les indiquer est un lien vers cette page web. Chacune de ces conditions peut être levée si vous obtenez l’autorisation du titulaire des droits sur cette œuvre. Rien dans ce contrat ne diminue ou ne restreint le droit moral de l’auteur ou des auteurs.

Le texte complet de la licence est disponible sur http://creativecommons.org/licenses/by-nc-sa/2.0/fr/legalcode

Cela inclut les diapositives, les manuels eux-mêmes et les travaux pratiques. Cette formation peut également contenir quelques images et schémas dont la redistribution est soumise à des licences différentes qui sont alors précisées.

Marques déposées

PostgreSQL® Postgres® et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.

Versions de PostgreSQL couvertes

Ce document ne couvre que les versions supportées de PostgreSQL au moment de sa rédaction, soit les versions 12 à 16.

Création d’objet et mises à jour

Introduction

DDL, gérer les objets
DML, écrire des données
Gérer les transactions

DDL (Data Definition Language)
DML (Data Manipulation Language)
TCL (Transaction Control Language)

Objectifs

Savoir créer, modifier et supprimer des objets
Savoir utiliser les contraintes d’intégrité
Savoir mettre à jour les données
Savoir utiliser les transactions

DDL

DDL
- Data Definition Language
- langage de définition de données
Permet de créer des objets dans la base de données

Objets d’une base de données

Objets définis par la norme SQL :
- schémas
- séquences
- tables
- contraintes
- domaines
- vues
- fonctions
- triggers

Créer des objets

Ordre CREATE
Syntaxe spécifique au type d’objet
Exemple :

CREATE SCHEMA s1;

La création d’objet passe généralement par l’ordre CREATE. La syntaxe dépend fortement du type d’objet. Voici trois exemples :

CREATE SCHEMA s1;
CREATE TABLE t1 (c1 integer, c2 text);
CREATE SEQUENCE s1 INCREMENT BY 5 START 10;

Pour créer un objet, il faut être propriétaire du schéma ou de la base auquel appartiendra l’objet ou avoir le droit CREATE sur le schéma ou la base.

Modifier des objets

Ordre ALTER
Syntaxe spécifique pour modifier la définition d’un objet, exemple:
Renommage

    ALTER type_objet ancien_nom RENAME TO nouveau_nom ;

changement de propriétaire

    ALTER type_objet nom_objet OWNER TO proprietaire ;

changement de schéma

    ALTER type_objet nom_objet SET SCHEMA nom_schema ;

Supprimer des objets

Ordre DROP
Exemples :
- supprimer un objet :
```
DROP type_objet nom_objet ;
```
- supprimer un objet et ses dépendances :
```
DROP type_objet nom_objet CASCADE ;
```

Schéma

Identique à un espace de nommage
Permet d’organiser les tables de façon logique
Possibilité d’avoir des objets de même nom dans des schémas différents
Pas d’imbrication (contrairement à des répertoires par exemple)
Schéma public
- créé par défaut dans une base
- souvent supprimé pour la sécurité

La notion de schéma dans PostgreSQL est à rapprocher de la notion d’espace de nommage (ou namespace) de certains langages de programmation. Le catalogue système qui contient la définition des schémas dans PostgreSQL s’appelle d’ailleurs pg_namespace.

Les schémas sont utilisés pour répartir les objets de façon logique, suivant un schéma interne à l’entreprise. Ils servent aussi à faciliter la gestion des droits (il suffit de révoquer le droit d’utilisation d’un schéma à un utilisateur pour que les objets contenus dans ce schéma ne soient plus accessibles à cet utilisateur).

Un schéma public est créé par défaut dans toute nouvelle base de données.

Jusque PostgreSQL 14, tout le monde a le droit d’y créer des objets. Cela posait des problèmes de sécurité, ce droit était souvent révoqué, ou le schéma supprimé. À partir de PostgreSQL 15, le droit d’écriture doit être donné explicitement.

Il existe des schémas système masqués, par exemple pour des tables temporaires, ou les tables système (schéma pg_catalog).

Gestion d’un schéma

CREATE SCHEMA nom_schéma
ALTER SCHEMA nom_schéma
- renommage
- changement de propriétaire
DROP SCHEMA [ IF EXISTS ] nom_schéma [ CASCADE ]

L’ordre CREATE SCHEMA permet de créer un schéma. Il suffit de lui spécifier le nom du schéma. CREATE SCHEMA offre d’autres possibilités qui sont rarement utilisées.

L’ordre ALTER SCHEMA nom_schema RENAME TO nouveau_nom_schema permet de renommer un schéma. L’ordre ALTER SCHEMA nom_schema OWNER TO proprietaire permet de donner un nouveau propriétaire au schéma.

Enfin, l’ordre DROP SCHEMA permet de supprimer un schéma. La clause IF EXISTS permet d’éviter la levée d’une erreur si le schéma n’existe pas (très utile dans les scripts SQL). La clause CASCADE permet de supprimer le schéma ainsi que tous les objets qui sont positionnés dans le schéma.

Exemples

Création d’un schéma reference :

CREATE SCHEMA reference;

Une table peut être créée dans ce schéma :

CREATE TABLE reference.communes (
  commune      text,
  codepostal   char(5),
  departement  text,
  codeinsee    integer
);

La suppression directe du schéma ne fonctionne pas car il porte encore la table communes :

DROP SCHEMA reference;
ERROR:  cannot drop schema reference because other objects depend on it
DETAIL:  table reference.communes depends on schema reference
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

L’option CASCADE permet de supprimer le schéma et ses objets dépendants :

DROP SCHEMA reference CASCADE;
NOTICE:  drop cascades to table reference.communes

Accès aux objets

Nommage explicite
- nom_schema.nom_objet
Chemin de recherche de schéma
- paramètre search_path
- SET search_path = schema1,schema2,public;
- par défaut : $user, public

Le paramètre search_path permet de définir un chemin de recherche pour pouvoir retrouver les tables dont le nom n’est pas qualifié par le nom de son schéma. PostgreSQL procèdera de la même façon que le système avec la variable $PATH : il recherche la table dans le premier schéma listé. S’il trouve une table portant ce nom dans le schéma, il préfixe le nom de table avec celui du schéma. S’il ne trouve pas de table de ce nom dans le schéma, il effectue la même opération sur le prochain schéma de la liste du search_path. S’il n’a trouvé aucune table de ce nom dans les schémas listés par search_path, PostgreSQL lève une erreur.

Comme beaucoup d’autres paramètres, le search_path peut être positionné à différents endroits. Par défaut, il est assigné à $user, public, c’est-à-dire que le premier schéma de recherche portera le nom de l’utilisateur courant, et le second schéma de recherche est public.

On peut vérifier la variable search_path à l’aide de la commande SHOW :

SHOW search_path;
  search_path
----------------
 "$user",public
(1 row)

Pour obtenir une configuration particulière, la variable search_path peut être positionnée dans le fichier postgresql.conf :

search_path = '"$user",public'

Cette variable peut aussi être positionnée au niveau d’un utilisateur. Chaque fois que l’utilisateur se connectera, il prendra le search_path de sa configuration spécifique :

ALTER ROLE nom_role SET search_path = "$user", public;

Cela peut aussi se faire au niveau d’une base de données. Chaque fois qu’un utilisateur se connectera à la base, il prendra le search_path de cette base, sauf si l’utilisateur a déjà une configuration spécifique :

ALTER DATABASE nom_base SET search_path = "$user", public;

La variable search_path peut également être positionnée pour un utilisateur particulier, dans une base particulière :

ALTER ROLE nom_role IN DATABASE nom_base SET search_path = "$user", public;

Enfin, la variable search_path peut être modifiée dynamiquement dans la session avec la commande SET :

SET search_path = "$user", public;

Avant la version 9.3, les requêtes préparées et les fonctions conservaient en mémoire le plan d’exécution des requêtes. Ce plan ne faisait plus référence aux noms des objets mais à leurs identifiants. Du coup, un search_path changeant entre deux exécutions d’une requête préparée ou d’une fonction ne permettait pas de cibler une table différente. Voici un exemple le montrant :

-- création des objets
CREATE SCHEMA s1;
CREATE SCHEMA s2;
CREATE TABLE s1.t1 (c1 text);
CREATE TABLE s2.t1 (c1 text);
INSERT INTO s1.t1 VALUES('schéma s1');
INSERT INTO s2.t1 VALUES('schéma s2');

SELECT * FROM s1.t1;
    c1
-----------
 schéma s1
(1 row)

SELECT * FROM s2.t1;
    c1
-----------
 schéma s2
(1 row)

-- il y a bien des données différentes dans chaque table

SET search_path TO s1;
PREPARE req AS SELECT * FROM t1;

EXECUTE req;
    c1
-----------
 schéma s1
(1 row)

SET search_path TO s2;
EXECUTE req;
    c1
-----------
 schéma s1
(1 row)

-- malgré le changement de search_path, nous en sommes toujours
-- aux données de l'autre table

b1=# SELECT * FROM t1;
    c1
-----------
 schéma s2
(1 row)

Dans ce cas, il est préférable de configurer le paramètre search_path directement au niveau de la fonction.

À partir de la version 9.3, dès que le search_path change, les plans en cache sont supprimés (dans le cas de la fonction) ou recréés (dans le cas des requêtes préparées).

Séquences

Séquence
- génère une séquence de nombres
Paramètres
- valeur minimale MINVALUE
- valeur maximale MAXVALUE
- valeur de départ START
- incrément INCREMENT
- cache CACHE
- cycle autorisé CYCLE

Les séquences sont des objets standards qui permettent de générer des séquences de valeur. Elles sont utilisées notamment pour générer un numéro unique pour un identifiant ou, plus rarement, pour disposer d’un compteur informatif, mis à jour au besoin.

Le cache de la séquence a pour effet de générer un certain nombre de valeurs en mémoire afin de les mettre à disposition de la session qui a utilisé la séquence. Même si les valeurs pré-calculées ne sont pas consommées dans la session, elles seront consommées au niveau de la séquence. Cela peut avoir pour effet de créer des trous dans les séquences d’identifiants et de consommer très rapidement les numéros de séquence possibles. Le cache de séquence n’a pas besoin d’être ajusté sur des applications réalisant de petites transactions. Il permet en revanche d’améliorer les performances sur des applications qui utilisent massivement des numéros de séquences, notamment pour réaliser des insertions massives.

Création d’une séquence

CREATE SEQUENCE nom [ INCREMENT incrément ]
    [ MINVALUE valeurmin | NO MINVALUE ]
    [ MAXVALUE valeurmax | NO MAXVALUE ]
    [ START [ WITH ] début ]
    [ CACHE cache ]
    [ [ NO ] CYCLE ]
    [ OWNED BY { nom_table.nom_colonne | NONE } ]

La syntaxe complète est donnée dans le slide.

Le mot clé TEMPORARY ou TEMP permet de définir si la séquence est temporaire. Si tel est le cas, elle sera détruite à la déconnexion de l’utilisateur.

Le mot clé INCREMENT définit l’incrément de la séquence, MINVALUE, la valeur minimale de la séquence et MAXVALUE, la valeur maximale. START détermine la valeur de départ initiale de la séquence, c’est-à-dire juste après sa création. La clause CACHE détermine le cache de séquence. CYCLE permet d’indiquer au SGBD que la séquence peut reprendre son compte à MINVALUE lorsqu’elle aura atteint MAXVALUE. La clause NO CYCLE indique que le rebouclage de la séquence est interdit, PostgreSQL lèvera alors une erreur lorsque la séquence aura atteint son MAXVALUE. Enfin, la clause OWNED BY détermine l’appartenance d’une séquence à une colonne d’une table. Ainsi, si la colonne est supprimée, la séquence sera implicitement supprimée.

Exemple de séquence avec rebouclage :

CREATE SEQUENCE testseq INCREMENT BY 1 MINVALUE 3 MAXVALUE 5 CYCLE START WITH 4;

SELECT nextval('testseq');
 nextval
---------
       4

SELECT nextval('testseq');
 nextval
---------
       5

SELECT nextval('testseq');
 nextval
---------
       3

Modification d’une séquence

ALTER SEQUENCE nom [ INCREMENT increment ]
    [ MINVALUE valeurmin | NO MINVALUE ]
    [ MAXVALUE valeurmax | NO MAXVALUE ]
    [ START [ WITH ] début ]
    [ RESTART [ [ WITH ] nouveau_début ] ]
    [ CACHE cache ] [ [ NO ] CYCLE ]
    [ OWNED BY { nom_table.nom_colonne | NONE } ]

Il est aussi possible de modifier
- le propriétaire
- le schéma

Les propriétés de la séquence peuvent être modifiées avec l’ordre ALTER SEQUENCE.

La séquence peut être affectée à un nouveau propriétaire :

ALTER SEQUENCE [ IF EXISTS ] nom OWNER TO nouveau_propriétaire

Elle peut être renommée :

ALTER SEQUENCE [ IF EXISTS ] nom RENAME TO nouveau_nom

Enfin, elle peut être positionnée dans un nouveau schéma :

ALTER SEQUENCE [ IF EXISTS ] nom SET SCHEMA nouveau_schema

Suppression d’une séquence

DROP SEQUENCE nom [, ...]

Voici la syntaxe complète de DROP SEQUENCE :

DROP SEQUENCE [ IF EXISTS ] nom [, ...] [ CASCADE | RESTRICT ]

Le mot clé CASCADE permet de supprimer la séquence ainsi que tous les objets dépendants (par exemple la valeur par défaut d’une colonne).

Séquences, utilisation

Obtenir la valeur suivante
- nextval('nom_sequence')
Obtenir la valeur courante
- currval('nom_sequence')
- mais nextval() doit être appelé avant dans la même session

La fonction nextval() permet d’obtenir le numéro de séquence suivant. Son comportement n’est pas transactionnel. Une fois qu’un numéro est consommé, il n’est pas possible de revenir dessus, malgré un ROLLBACK de la transaction. La séquence est le seul objet à avoir un comportement de ce type.

La fonction currval() permet d’obtenir le numéro de séquence courant, mais son usage nécessite d’avoir utilisé nextval() dans la session.

Il est possible d’interroger une séquence avec une requête SELECT. Cela permet d’obtenir des informations sur la séquence, dont la dernière valeur utilisée dans la colonne last_value. Cet usage n’est pas recommandé en production et doit plutôt être utilisé à titre informatif.

Exemples

Utilisation d’une séquence simple :

CREATE SEQUENCE testseq
INCREMENT BY 1 MINVALUE 10 MAXVALUE 20 START WITH 15 CACHE 1;

SELECT currval('testseq');
ERROR:  currval of sequence "testseq" is not yet defined in this session

SELECT * FROM testseq ;
- [ RECORD 1 ]-+--------
sequence_name  | testseq
last_value     | 15
start_value    | 15
increment_by   | 1
max_value      | 20
min_value      | 10
cache_value    | 5
log_cnt        | 0
is_cycled      | f
is_called      | f

SELECT nextval('testseq');
 nextval
---------
      15
(1 row)

SELECT currval('testseq');
 currval
---------
      15

SELECT nextval('testseq');
 nextval
---------
      16
(1 row)

ALTER SEQUENCE testseq RESTART WITH 5;
ERROR:  RESTART value (5) cannot be less than MINVALUE (10)

DROP SEQUENCE testseq;

Utilisation d’une séquence simple avec cache :

CREATE SEQUENCE testseq INCREMENT BY 1 CACHE 10;

SELECT nextval('testseq');
 nextval
---------
       1

Déconnexion et reconnexion de l’utilisateur :

SELECT nextval('testseq');
 nextval
---------
      11

Suppression en cascade d’une séquence :


CREATE TABLE t2 (id serial);

\d t2
                           Table "s2.t2"
 Column |  Type   |                    Modifiers
--------+---------+-------------------------------------------------
 id     | integer | not null default nextval('t2_id_seq'::regclass)

DROP SEQUENCE t2_id_seq;
ERROR:  cannot drop sequence t2_id_seq because other objects depend on it
DETAIL:  default for table t2 column id depends on sequence t2_id_seq
HINT:  Use DROP ... CASCADE to drop the dependent objects too.

DROP SEQUENCE t2_id_seq CASCADE;
NOTICE:  drop cascades to default for table t2 column id

\d t2
        Table "s2.t2"
 Column |  Type   | Modifiers
--------+---------+-----------
 id     | integer | not null

Type SERIAL

Type serial/bigserial/smallserial
- séquence générée automatiquement
- valeur par défaut nextval(...)
Préférer un entier avec IDENTITY

Certaines bases de données offrent des colonnes auto-incrémentées (autoincrement de MySQL ou identity de SQL Server).

PostgreSQL possède identity à partir de PostgreSQL 10. Il était déjà possible d’utiliser serial, un équivalent qui s’appuie sur les séquences et la possibilité d’appliquer une valeur par défaut à une colonne.

Par exemple, si l’on crée la table suivante :

CREATE TABLE exemple_serial (
  id SERIAL PRIMARY KEY,
  valeur INTEGER NOT NULL
);

On s’aperçoit que la table a été créée telle que demandé, mais qu’une séquence a aussi été créée. Elle porte un nom dérivé de la table associé à la colonne correspondant au type serial, terminé par seq :

postgres=# \d
                 List of relations
 Schema |         Name          |   Type   | Owner
--------+-----------------------+----------+--------
 public | exemple_serial        | table    | thomas
 public | exemple_serial_id_seq | sequence | thomas

En examinant plus précisément la définition de la table, on s’aperçoit que la colonne id porte une valeur par défaut qui correspond à l’appel de la fonction nextval() sur la séquence qui a été créée implicitement :

postgres=# \d exemple_serial
                         Table "public.exemple_serial"
 Column |  Type   |                          Modifiers
--------+---------+-------------------------------------------------------------
 id     | integer | not null default nextval('exemple_serial_id_seq'::regclass)
 valeur | integer | not null
Indexes:
    "exemple_serial_pkey" PRIMARY KEY, btree (id)

smallserial et bigserial sont des variantes de serial s’appuyant sur des types d’entiers plus courts ou plus longs.

Domaines

Permet d’associer
- un type standard
- et une contrainte (optionnelle)

Un domaine est utilisé pour définir un type utilisateur qui est en fait un type utilisateur standard accompagné de la définition de contraintes particulières.

Les domaines sont utiles pour ramener la définition de contraintes communes à plusieurs colonnes sur un seul objet. La maintenance en est ainsi facilitée.

L’ordre CREATE DOMAIN permet de créer un domaine, ALTER DOMAIN permet de modifier sa définition, et enfin, DROP DOMAIN permet de supprimer un domaine.

Exemples

Gestion d’un domaine salaire :

-- ajoutons le domaine et la table
CREATE DOMAIN salaire AS integer CHECK (VALUE > 0);
CREATE TABLE employes (id serial, nom text, paye salaire);

\d employes
                               Table « public.employes »
 Colonne |  Type   |  NULL-able |              Par défaut
---------+---------+------------+--------------------------------------
 id      | integer |  not null  | nextval('employes_id_seq'::regclass)
 nom     | text    |            |
 paye    | salaire |            |

-- insérons des données dans la nouvelle table
INSERT INTO employes (nom, paye) VALUES ('Albert', 1500);
INSERT INTO employes (nom, paye) VALUES ('Alphonse', 0);
ERROR:  value for domain salaire violates check constraint "salaire_check"
-- erreur logique vu qu'on ne peut avoir qu'un entier strictement positif
INSERT INTO employes (nom, paye) VALUES ('Alphonse', 1000);
INSERT 0 1
INSERT INTO employes (nom, paye) VALUES ('Bertrand', NULL);
INSERT 0 1
-- tous les employés doivent avoir un salaire
-- il faut donc modifier la contrainte, pour s'assurer
-- qu'aucune valeur NULL ne soit saisi
ALTER DOMAIN salaire SET NOT NULL;
ERROR:  column "paye" of table "employes" contains null values
-- la ligne est déjà présente, il faut la modifier
UPDATE employes SET paye=1500 WHERE nom='Bertrand';
-- maintenant, on peut ajouter la contrainte au domaine
ALTER DOMAIN salaire SET NOT NULL;
INSERT INTO employes (nom, paye) VALUES ('Delphine', NULL);
ERROR:  domain salaire does not allow null values
-- la contrainte est bien vérifiée
-- supprimons maintenant la contrainte
DROP DOMAIN salaire;
ERROR:  cannot drop type salaire because other objects depend on it
DETAIL:  table employes column paye depends on type salaire
HINT:  Use DROP ... CASCADE to drop the dependent objects too.
-- il n'est pas possible de supprimer le domaine car il est référencé dans une
-- table.  Il faut donc utiliser l'option CASCADE
b1=# DROP DOMAIN salaire CASCADE;
NOTICE:  drop cascades to table employes column paye
DROP DOMAIN
-- le domaine a été supprimée ainsi que toutes les colonnes ayant ce type
\d employes
                               Table « public.employes »
 Colonne |  Type   | NULL-able |              Par défaut
---------+---------+-----------+--------------------------------------
 id      | integer | not null  | nextval('employes_id_seq'::regclass)
 nom     | text    |           |

Création et utilisation d’un domaine code_postal_us :

CREATE DOMAIN code_postal_us AS TEXT
CHECK(
   VALUE ~ '^\d{5}$'
OR VALUE ~ '^\d{5}-\d{4}$'
);

CREATE TABLE courrier_us (
  id_adresse SERIAL PRIMARY KEY,
  rue1 TEXT NOT NULL,
  rue2 TEXT,
  rue3 TEXT,
  ville TEXT NOT NULL,
  code_postal code_postal_us NOT NULL
);

INSERT INTO courrier_us (rue1,ville,code_postal)
VALUES ('51 Franklin Street', 'Boston, MA', '02110-1335' );

INSERT 0 1

INSERT INTO courrier_us (rue1,ville,code_postal)
VALUES ('10 rue d''Uzès','Paris','F-75002') ;

ERREUR:  la valeur pour le domaine code_postal_us viole la contrainte de
vérification « code_postal_us_check »

Tables

Équivalent ensembliste d’une relation
Composé principalement de
- colonnes ordonnées
- contraintes

Création d’une table

Définition de son nom
Définition de ses colonnes
- nom, type, contraintes éventuelles
Clauses de stockage
CREATE TABLE

CREATE TABLE

CREATE TABLE nom_table (
  definition_colonnes
  definition_contraintes
) clause_stockage;

Définition des colonnes

nom_colonne type [ COLLATE collation ] [ contrainte ]
[, ...]

Valeur par défaut

DEFAULT
- affectation implicite
Utiliser directement par les types sériés

La clause DEFAULT permet d’affecter une valeur par défaut lorsqu’une colonne n’est pas référencée dans l’ordre d’insertion ou si une mise à jour réinitialise la valeur de la colonne à sa valeur par défaut.

Les types sériés définissent une valeur par défaut sur les colonnes de ce type. Cette valeur est le retour de la fonction nextval() sur la séquence affectée automatiquement à cette colonne.

Exemples

Assignation d’une valeur par défaut :

CREATE TABLE valdefaut (
  id integer,
  i integer DEFAULT 0,
  j integer DEFAULT 0
);

INSERT INTO valdefaut (id, i) VALUES (1, 10);

SELECT * FROM valdefaut ;
 id | i  | j
----+----+---
  1 | 10 | 0
(1 row)

Copie de la définition d’une table

Création d’une table à partir d’une autre table
- CREATE TABLE ... (LIKE table clause_inclusion)
Avec les valeurs par défaut des colonnes :
- INCLUDING DEFAULTS
Avec ses autres contraintes :
- INCLUDING CONSTRAINTS
Avec ses index :
- INCLUDING INDEXES

L’ordre CREATE TABLE permet également de créer une table à partir de la définition d’une table déjà existante en utilisant la clause LIKE en lieu et place de la définition habituelle des colonnes. Par défaut, seule la définition des colonnes avec leur typage est repris.

Les clauses INCLUDING permettent de récupérer d’autres éléments de la définition de la table, comme les valeurs par défaut (INCLUDING DEFAULTS), les contraintes d’intégrité (INCLUDING CONSTRAINTS), les index (INCLUDING INDEXES), les clauses de stockage (INCLUDING STORAGE) ainsi que les commentaires (INCLUDING COMMENTS). Si l’ensemble de ces éléments sont repris, il est possible de résumer la clause INCLUDING à INCLUDING ALL.

La clause CREATE TABLE suivante permet de créer une table archive_evenements_2010 à partir de la définition de la table evenements :

CREATE TABLE archive_evenements_2010
 (LIKE evenements
  INCLUDING DEFAULTS
  INCLUDING CONSTRAINTS
  INCLUDING INDEXES
  INCLUDING STORAGE
  INCLUDING COMMENTS
);

Elle est équivalente à :

CREATE TABLE archive_evenements_2010
 (LIKE evenements
  INCLUDING ALL
);

Modification d’une table

ALTER TABLE
Définition de la table
- renommage de la table
- ajout/modification/suppression d’une colonne
- déplacement dans un schéma différent
- changement du propriétaire
Définition des colonnes
- renommage d’une colonne
- changement de type d’une colonne
Définition des contraintes
- ajout/suppression d’une contrainte

Conséquences des modifications d’une table

contention avec les verrous
vérification des données
performance avec une possible réécriture de la table

Suivant l’opération réalisée, les verrous posés ne seront pas les mêmes, même si le verrou par défaut sera un verrou exclusif. Par exemple, renommer une table nécessite un verrou exclusif mais changer la taille de l’échantillon statistiques bloque uniquement certaines opérations de maintenance (comme VACUUM et ANALYZE) et certaines opérations DDL. Il convient donc d’être très prudent lors de l’utilisation de la commande ALTER TABLE sur un serveur en production.

Certaines opérations nécessitent de vérifier les données. C’est évident lors de l’ajout d’une contrainte (comme une clé primaire ou une contrainte NOT NULL), mais c’est aussi le cas lors d’un changement de type de données. Passer une colonne du type text vers le type timestamp nécessite de vérifier que les données de cette colonne ne contiennent que des données convertibles vers le type timestamp. Dans les anciennes versions, la vérification était effectuée en permanence, y compris pour des cas simples où cela n’était pas nécessaire. Par exemple, convertir une colonne du type varchar(200) à varchar(100) nécessite de vérifier que la colonne ne contient que des chaînes de caractères de longueur inférieure à 100. Mais convertir une colonne du type varchar(100) vers le type varchar(200) ne nécessite pas de vérification. Les dernières versions de PostgreSQL font la différence, ce qui permet d’éviter de perdre du temps pour une vérification inutile.

Certaines opérations nécessitent une réécriture de la table. Par exemple, convertir une colonne de type varchar(5) vers le type int4 impose une réécriture de la table car il n’y a pas de compatibilité binaire entre les deux types. Ce n’est pas le cas si la modification est uniquement sur la taille d’une colonne varchar. Certaines optimisations sont ajoutées sur les nouvelles versions de PostgreSQL. Par exemple, l’ajout d’une colonne avec une valeur par défaut causait la réécriture complète de la table pour intégrer la valeur de cette nouvelle colonne alors que l’ajout d’une colonne sans valeur par défaut n’avait pas la même conséquence. À partir de la version 11, cette valeur par défaut est enregistrée dans la colonne attmissingval du catalogue système pg_attribute et la table n’a de ce fait plus besoin d’être réécrite.

Il convient donc d’être très prudent lors de l’utilisation de la commande ALTER TABLE. Elle peut poser des problèmes de performances, à cause de verrous posés par d’autres commandes, de verrous qu’elle réclame, de vérification des données, voire de réécriture de la table.

Suppression d’une table

Supprimer une table :

    DROP TABLE nom_table;

Supprimer une table et tous les objets dépendants :

    DROP TABLE nom_table CASCADE;

Contraintes d’intégrité

ACID
- Cohérence
- une transaction amène la base d’un état stable à un autre
Assurent la cohérence des données
- unicité des enregistrements
- intégrité référentielle
- vérification des valeurs
- identité des enregistrements
- règles sémantiques

Clé primaire d’une table

Identifie une ligne de manière unique
Une seule clé primaire par table
Une ou plusieurs colonnes
À choisir parmi les clés candidates
- clé naturelle ou artificielle (technique, invisible)
- les autres clés possibles peuvent être UNIQUE

Une clé primaire permet d’identifier une ligne de façon unique. Il n’en existe qu’une seule par table.

Une clé primaire exige que toutes les valeurs de la ou des colonnes qui composent cette clé soient uniques et non nulles. La clé peut être composée d’une seule colonne ou de plusieurs colonnes, selon le besoin.

La clé primaire est déterminée au moment de la conception du modèle de données. Cette clé peut être « naturelle » et visible de l’utilisateur, ou purement technique et non visible.

Exemple :

Par exemple, une table de factures peut avoir comme clé primaire « naturelle » le numéro de la facture.

Cela peut poser problème car il y a des contraintes légales sur l’incrémentation des numéros de facture qui obligent à le générer tard dans le processus. Enregistrer une facture incomplète sans son numéro devient compliqué. Un numéro provisoire est possible, mais le changer implique de modifier aussi toutes les tables qui y font référence. De manière générale, on évitera toujours de modifier une clé primaire.

Techniquement, la table des lignes de facture aurait alors comme clé primaire composée le numéro de la facture et le numéro de la ligne. Cela fonctionne, mais une clé composée, d’ailleurs souvent de type texte, est moins performante pour les jointures qu’un champ monocolonne numérique.

De nombreuses applications utilisent des clés naturelles composées, mais les bonnes pratiques conseillent depuis longtemps d’utiliser sur chaque table une clé technique monocolonne et numérique, de valeur arbitraire, générée par exemple avec une séquence ou un UUID, et invisible de l’utilisateur final.

Les jointures se font alors sur un seul champ. Les clés « naturelles » restent présentes sous forme de champ avec une contrainte d’unicité.

Dans notre exemple, la table des factures porterait une clé primaire numérique facture_id arbitraire, et un champ numero_facture, unique, qui pourrait être modifié. La table des lignes de facture porterait une clé primaire facture_ligne_id sans lien, et une clé étrangère (non composée) reprenant facture_id, et un champ unique numero_ligne.

Index :

La création d’une clé primaire crée implicitement un index sur le champ.

Déclaration d’une clé primaire

Construction :

[CONSTRAINT nom_contrainte]
PRIMARY KEY ( nom_colonne [, ... ] )

Séquence
serial
GENERATED ALWAYS BY IDENTITY
UUID

Exemple avec clé technique manuelle :

CREATE TABLE region
  (
    id       int   PRIMARY KEY,
    libelle  text  NOT NULL  UNIQUE
  );

INSERT INTO region VALUES (1, 'Alsace');
INSERT INTO region VALUES (2, 'Île-de-France');

-- La clé primaire est forcément NOT NULL
INSERT INTO region VALUES (NULL, 'Corse');
ERROR:  null value in column "id" of relation "region" violates not-null constraint
DÉTAIL : Failing row contains (null, Corse).

INSERT INTO region VALUES (1, 'Corse');
ERROR:  duplicate key value violates unique constraint "region_pkey"
DÉTAIL : Key (id)=(1) already exists.

TABLE region ;
 id |    libelle 
----+---------------
  1 | Alsace
  2 | Île de France

Exemple avec séquence :

La séquence génère des identifiants que l’on peut utiliser :

CREATE SEQUENCE departement_seq AS int ;

CREATE TABLE departement
  (
    id       int   PRIMARY KEY,
    libelle  text  NOT NULL UNIQUE,
    code     varchar(3) NOT NULL UNIQUE,
    region_id int NOT NULL REFERENCES region (id)
  );

INSERT INTO departement
SELECT nextval ('departement_seq'), 'Bas-Rhin', '67', 1 ;

INSERT INTO departement
VALUES ( nextval ('departement_seq'),  'Haut-Rhin', '68', 1 ),
       ( nextval ('departement_seq'),  'Paris', '75', 2 ) ;

-- Cette erreur va « consommer » un numéro de séquence
-- ce qui n'a pas d'importance pour une clé technique
INSERT INTO departement
SELECT nextval ('departement_seq'), 'Yvelines', '78', null ;

ERROR:  null value in column "region_id" of relation "departement" violates not-null constraint
DETAIL : Failing row contains (4, Yvelines, 78, null).

WITH nouveaudept AS (
  INSERT INTO departement
  SELECT nextval ('departement_seq'), 'Yvelines', '78', 2
  RETURNING *)
SELECT * FROM nouveaudept ;

 id | libelle  | code | region_id 
----+----------+------+-----------
   | Yvelines | 78   |         2
       
TABLE departement ;

 id |  libelle  | code | region_id 
----+-----------+------+-----------
  1 | Bas-Rhin  | 67   |         1
  2 | Haut-Rhin | 68   |         1
  3 | Paris     | 75   |         2
  5 | Yvelines  | 78   |         2

Exemples avec serial :

Noter que serial est considéré comme un type (il existe aussi bigserial). Il y a création d’une séquence en arrière-plan, utilisée de manière transparente.

CREATE TABLE ville
  (
    id       serial   PRIMARY KEY,
    libelle  text     NOT NULL,
    departement_id int NOT NULL REFERENCES departement
  );

INSERT INTO ville (libelle, departement_id) VALUES ('Strasbourg',1);
INSERT INTO ville (libelle, departement_id) VALUES ('Paris',3);

-- Interférer avec l'autoincrémentation est une mauvaise idée
INSERT INTO ville VALUES (3, 'Mulhouse', 2);
INSERT INTO ville (libelle, departement_id) VALUES ('Sélestat',1);

ERROR:  duplicate key value violates unique constraint "ville_pkey"
DETAIL : Key (id)=(3) already exists.

Exemple avec IDENTITY :

CREATE TABLE ville2
  (
    id       int   GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    libelle  text  NOT NULL,
    departement_id int NOT NULL REFERENCES departement
  );

Le maniement est identique au serial. Il faut préférer l’identité pour des raisons de respect de standard SQL et à cause de quelques spécificités dans la gestion automatique des séquences sous-jacentes.

Exemple avec une clé composée :

Nous verrons plus loin un exemple avec une clé primaire composée, qui doit se déclarer alors après les colonnes :

CREATE TABLE stock
  (
    vin_id        int    not null,
    contenant_id  int    not null,
    annee         int4   not null,
    nombre        int4   not null,

    PRIMARY KEY (vin_id,contenant_id,annee),

    FOREIGN KEY(vin_id) REFERENCES vin(id) ON DELETE CASCADE,
    FOREIGN KEY(contenant_id) REFERENCES contenant(id) ON DELETE CASCADE
  );

Contrainte d’unicité

Garantit l’unicité des valeurs d’une ou plusieurs colonnes
Permet plusieurs valeurs NULL
- en v15, possibilité de modifier ce comportement
Clause UNIQUE
Contrainte UNIQUE != index UNIQUE

Une contrainte d’unicité permet de garantir que les valeurs de la ou des colonnes sur lesquelles porte la contrainte sont uniques. Elle autorise néanmoins d’avoir plusieurs valeurs NULL car elles ne sont pas considérées comme égales mais de valeur inconnue (UNKNOWN).

Une contrainte d’unicité peut être créée simplement en créant un index UNIQUE approprié. Ceci est fortement déconseillé du fait que la contrainte ne sera pas référencée comme telle dans le schéma de la base de données. Il sera donc très facile de ne pas la remarquer au moment d’une reprise du schéma pour une évolution majeure de l’application. Une colonne possédant juste l’index UNIQUE peut malgré tout être référencée par une clé étrangère.

Les contraintes d’unicité créent implicitement un index qui permet de renforcer cette unicité.

Déclaration d’une contrainte d’unicité

Construction :

[ CONSTRAINT nom_contrainte]
{ UNIQUE { NULLS NOT DISTINCT } ( nom_colonne [, ... ] )

Voici un exemple complet.

Sans contrainte d’unicité, on peut insérer plusieurs fois la même valeur :

postgres=# CREATE TABLE utilisateurs(id integer);
CREATE TABLE
postgres=# INSERT INTO utilisateurs VALUES (10);
INSERT 0 1
postgres=# INSERT INTO utilisateurs VALUES (10);
INSERT 0 1

Ce n’est plus le cas en déclarant une contrainte d’unicité :

postgres=# TRUNCATE utilisateurs;
TRUNCATE TABLE
postgres=# ALTER TABLE utilisateurs ADD UNIQUE(id);
ALTER TABLE
postgres=# INSERT INTO utilisateurs (id) VALUES (10);
INSERT 0 1
postgres=# INSERT INTO utilisateurs (id) VALUES (10);
ERROR:  duplicate key value violates unique constraint "utilisateurs_id_key"
DETAIL:  Key (id)=(10) already exists.
postgres=# INSERT INTO utilisateurs (id) VALUES (11);
INSERT 0 1

Par contre, on peut insérer plusieurs valeurs NULL :

postgres=# INSERT INTO utilisateurs (id) VALUES (NULL);
INSERT 0 1
postgres=# INSERT INTO utilisateurs (id) VALUES (NULL);
INSERT 0 1
postgres=# INSERT INTO utilisateurs (id) VALUES (NULL);
INSERT 0 1

Ce comportement est modifiable en version 15. Lors de la création de la contrainte, il faut préciser ce nouveau comportement :

postgres=# TRUNCATE utilisateurs;
TRUNCATE TABLE
postgres=# ALTER TABLE utilisateurs DROP CONSTRAINT utilisateurs_id_key;
ALTER TABLE
postgres=# ALTER TABLE utilisateurs ADD UNIQUE NULLS NOT DISTINCT(id);
ALTER TABLE
postgres=# INSERT INTO utilisateurs (id) VALUES (10);
INSERT 0 1
postgres=# INSERT INTO utilisateurs (id) VALUES (10);
ERROR:  duplicate key value violates unique constraint "utilisateurs_id_key"
DETAIL:  Key (id)=(10) already exists.
postgres=# INSERT INTO utilisateurs (id) VALUES (11);
INSERT 0 1
postgres=# INSERT INTO utilisateurs (id) VALUES (NULL);
INSERT 0 1
postgres=# INSERT INTO utilisateurs (id) VALUES (NULL);
ERROR:  duplicate key value violates unique constraint "utilisateurs_id_key"
DETAIL:  Key (id)=(null) already exists.

Intégrité référentielle

Contrainte d’intégrité référentielle
- ou Clé étrangère
Référence une clé primaire ou un groupe de colonnes UNIQUE et NOT NULL
Garantie l’intégrité des données
FOREIGN KEY

Une clé étrangère sur une table fait référence à une clé primaire ou une contrainte d’unicité d’une autre table. La clé étrangère garantit que les valeurs des colonnes de cette clé existent également dans la table portant la clé primaire ou la contrainte d’unicité. On parle de contrainte référentielle d’intégrité : la contrainte interdit les valeurs qui n’existent pas dans la table référencée.

À titre d’exemple nous allons utiliser la base cave.

La base cave (dump de 2,6 Mo, pour 71 Mo sur le disque au final) peut être téléchargée et restaurée ainsi :

curl -kL https://dali.bo/tp_cave -o cave.dump
psql -c "CREATE ROLE caviste LOGIN PASSWORD 'caviste'"
psql -c "CREATE DATABASE cave OWNER caviste"
pg_restore -d cave cave.dump
# NB : une erreur sur un schéma 'public' existant est normale

Le schéma suivant montre les différentes tables de la base :

Ainsi, la base cave définit une table region et une table appellation. Une appellation d’origine est liée au terroir, et par extension à son origine géographique. La table appellation est donc liée par une clé étrangère à la table region : la colonne region_id de la table appellation référence la colonne id de la table region.

Cette contrainte permet d’empêcher les utilisateurs d’entrer dans la table appellation des identifiants de région (region_id) qui n’existent pas dans la table region.

Déclaration d’une clé étrangère

[ CONSTRAINT nom_contrainte ] FOREIGN KEY ( nom_colonne [, ...] )
    REFERENCES table_reference [ (colonne_reference [, ... ] ) ]
    [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
    [ ON DELETE action ] [ ON UPDATE action ] }

Exemples

Définition de la table stock :

CREATE TABLE stock
  (
    vin_id        int    not null,
    contenant_id  int    not null,
    annee         int4   not null,
    nombre        int4   not null,

    PRIMARY KEY(vin_id,contenant_id,annee),

    FOREIGN KEY(vin_id) REFERENCES vin(id) ON DELETE CASCADE,
    FOREIGN KEY(contenant_id) REFERENCES contenant(id) ON DELETE CASCADE
  );

Création d’une table mère et d’une table fille. La table fille possède une clé étrangère qui référence la table mère :

CREATE TABLE mere (id integer, t text);

CREATE TABLE fille (id integer, mere_id integer, t text);

ALTER TABLE mere ADD CONSTRAINT pk_mere PRIMARY KEY (id);

ALTER TABLE fille
  ADD CONSTRAINT fk_mere_fille
      FOREIGN KEY (mere_id)
      REFERENCES mere (id)
         MATCH FULL
         ON UPDATE NO ACTION
         ON DELETE CASCADE;

INSERT INTO mere (id, t) VALUES (1, 'val1'), (2, 'val2');

-- l'ajout de données dans la table fille qui font bien référence
-- à la table mere fonctionne
INSERT INTO fille (id, mere_id, t) VALUES (1, 1, 'val1');
INSERT INTO fille (id, mere_id, t) VALUES (2, 2, 'val2');

-- l'ajout de données dans la table fille qui ne font pas référence
-- à la table mere est annulé
INSERT INTO fille (id, mere_id, t) VALUES (3, 3, 'val3');
ERROR:  insert or update on table "fille" violates foreign key constraint
        "fk_mere_fille"
DETAIL:  Key (mere_id)=(3) is not present in table "mere".

b1=# SELECT * FROM fille;
 id | mere_id |  t
----+---------+------
  1 |       1 | val1
  2 |       2 | val2
(2 rows)

-- mettre à jour la référence dans la table mere ne fonctionnera pas
-- car la contrainte a été définie pour refuser les mises à jour
-- (ON UPDATE NO ACTION)

b1=# UPDATE mere SET id=3 WHERE id=2;
ERROR:  update or delete on table "mere" violates foreign key constraint
        "fk_mere_fille" on table "fille"
DETAIL:  Key (id)=(2) is still referenced from table "fille".

-- par contre, la suppression d'une ligne de la table mere référencée dans la
-- table fille va propager la suppression jusqu'à la table fille
-- (ON DELETE CASCADE)

b1=# DELETE FROM mere WHERE id=2;
DELETE 1

b1=# SELECT * FROM fille;
 id | mere_id |  t
----+---------+------
  1 |       1 | val1
(1 row)

b1=# SELECT * FROM mere;
 id |  t
----+------
  1 | val1
(1 row)

Vérification simple ou complète

Vérification complète ou partielle d’une clé étrangère
MATCH
- MATCH FULL (complète)
- MATCH SIMPLE (partielle)

La directive MATCH permet d’indiquer si la contrainte doit être entièrement vérifiée (MATCH FULL) ou si la clé étrangère autorise des valeurs NULL (MATCH SIMPLE). MATCH SIMPLE est la valeur par défaut.

Avec MATCH FULL, toutes les valeurs des colonnes qui composent la clé étrangère de la table référençant doivent avoir une correspondance dans la table référencée.

Avec MATCH SIMPLE, les valeurs des colonnes qui composent la clé étrangère de la table référençant peuvent comporter des valeurs NULL. Dans le cas des clés étrangères multicolonnes, toutes les colonnes peuvent ne pas être renseignées. Dans le cas des clés étrangères sur une seule colonne, la contrainte autorise les valeurs NULL.

Exemples

Les exemples reprennent les tables mere et fille créées plus haut.

INSERT INTO fille VALUES (4, NULL, 'test');

SELECT * FROM fille;
 id | mere_id |  t
----+---------+------
  1 |       1 | val1
  2 |       2 | val2
  4 |         | test
(2 rows)

Clé primaire et colonne identité

Identité d’un enregistrement
GENERATED … AS IDENTITY
- ALWAYS
- BY DEFAULT
Préférer à serial
Unicité non garantie sans contrainte explicite !

Cette contrainte permet d’avoir une colonne dont la valeur est incrémentée automatiquement, soit en permanence (clause ALWAYS), soit quand aucune valeur n’est saisie (clause BY DEFAULT). Cette technique d’auto-incrémentation correspond au standard SQL, contrairement au pseudo-type serial.

De plus, GENERATED … AS IDENTITY corrige certains défauts du pseudo-type serial. serial n’est en effet qu’un raccourci pour créer à la fois une séquence, un champ entier et un DEFAULT nextval() pour alimenter ce champ, et cela peut poser quelques soucis. Notamment, le DDL saisi par l’utilisateur diffère de celui stocké en base ou sorti par pg_dump, ce qui n’est pas idéal. Ou encore, l’ordre CREATE TABLE … LIKE (INCLUDING ALL) copie le serial sans en changer la séquence ni en créer une autre : on a alors une séquence partagée par deux tables. Il n’est pas non plus possible d’ajouter ou de supprimer un pseudo-type serial avec l’instruction ALTER TABLE. La suppression de la contrainte DEFAULT d’un type serial ne supprime pas la séquence associée. Tout ceci fait que la définition d’une colonne d’identité est préférable à l’utilisation du pseudo-type serial.

Il reste obligatoire de définir une clé primaire ou unique si l’on tient à l’unicité des valeurs car même une clause GENERATED ALWAYS AS IDENTITY peut être contournée avec une mise à jour portant la mention OVERRIDING SYSTEM VALUE.

Exemple :

CREATE TABLE personnes (id int GENERATED ALWAYS AS IDENTITY, nom TEXT);

CREATE TABLE

INSERT INTO personnes (nom) VALUES ('Dupont') ;
INSERT 0 1
INSERT INTO personnes (nom) VALUES ('Durand') ;
INSERT 0 1

SELECT * FROM personnes ;
 id |  nom
----+--------
  1 | Dupont
  2 | Durand
(2 lignes)

INSERT INTO personnes (id,nom) VALUES (3,'Martin') ;

ERROR:  cannot insert into column "id"
DÉTAIL : Column "id" is an identity column defined as GENERATED ALWAYS.
ASTUCE : Use OVERRIDING SYSTEM VALUE to override.

INSERT INTO personnes (id,nom) OVERRIDING SYSTEM VALUE VALUES (3,'Martin') ;
INSERT 0 1

INSERT INTO personnes (id,nom) OVERRIDING SYSTEM VALUE VALUES (3,'Dupond') ;
INSERT 0 1

SELECT * FROM personnes ;
 id |  nom
----+--------
  1 | Dupont
  2 | Durand
  3 | Martin
  3 | Dupond

Mise à jour de la clé primaire

Que faire en cas de mise à jour d’une clé primaire ?
- les clés étrangères seront fausses
ON UPDATE
ON DELETE
Définition d’une action au niveau de la clé étrangère
- interdiction
- propagation de la mise à jour
- NULL
- valeur par défaut

Si des valeurs d’une clé primaire sont mises à jour ou supprimées, cela peut entrainer des incohérences dans la base de données si des valeurs de clés étrangères font référence aux valeurs de la clé primaire touchées par le changement.

Afin de pouvoir gérer cela, la norme SQL prévoit plusieurs comportements possibles. La clause ON UPDATE permet de définir comment le SGBD va réagir si la clé primaire référencée est mise à jour. La clause ON DELETE fait de même pour les suppressions.

Les actions possibles sont :

NO ACTION (ou RESTRICT), qui produit une erreur si une ligne référence encore le ou les lignes touchées par le changement ;
CASCADE, pour laquelle la mise à jour ou la suppression est propagée aux valeurs référençant le ou les lignes touchées par le changement ;
SET NULL, la valeur de la colonne devient NULL ;
SET DEFAULT, pour lequel la valeur de la colonne prend la valeur par défaut de la colonne.

Le comportement par défaut est NO ACTION, ce qui est habituellement recommandé pour éviter les suppressions en chaîne mal maîtrisées.

Exemples

Les exemples reprennent les tables mere et fille créées plus haut.

Tentative d’insertion d’une ligne dont la valeur de mere_id n’existe pas dans la table mere :

INSERT INTO fille (id, mere_id, t) VALUES (1, 3, 'val3');
ERROR:  insert or update on table "fille" violates foreign key constraint
        "fk_mere_fille"
DETAIL:  Key (mere_id)=(3) is not present in table "mere".

Mise à jour d’une ligne de la table mere pour modifier son id. La clé étrangère est déclarée ON UPDATE NO ACTION, donc la mise à jour devrait être interdite :

UPDATE mere SET id = 3 WHERE id = 1;
ERROR:  update or delete on table "mere" violates foreign key constraint
        "fk_mere_fille" on table "fille"
DETAIL:  Key (id)=(1) is still referenced from table "fille".

Suppression d’une ligne de la table mere. La clé étrangère sur fille est déclarée ON DELETE CASCADE, la suppression sera donc propagée aux tables qui référencent la table mere :

DELETE FROM mere WHERE id = 1;

SELECT * FROM fille ;
 id | mere_id |  t
----+---------+------
  2 |       2 | val2
(1 row)

Vérifications

Présence d’une valeur
- NOT NULL
Vérification de la valeur d’une colonne
- CHECK

La clause NOT NULL permet de s’assurer que la valeur de la colonne portant cette contrainte est renseignée. Dit autrement, elle doit obligatoirement être renseignée. Par défaut, la colonne peut avoir une valeur NULL, donc n’est pas obligatoirement renseignée.

La clause CHECK spécifie une expression de résultat booléen que les nouvelles lignes ou celles mises à jour doivent satisfaire pour qu’une opération d’insertion ou de mise à jour réussisse. Les expressions de résultat TRUE ou UNKNOWN réussissent. Si une des lignes de l’opération d’insertion ou de mise à jour produit un résultat FALSE, une exception est levée et la base de données n’est pas modifiée. Une contrainte de vérification sur une colonne ne fait référence qu’à la valeur de la colonne tandis qu’une contrainte sur la table fait référence à plusieurs colonnes.

Actuellement, les expressions CHECK ne peuvent ni contenir des sous-requêtes ni faire référence à des variables autres que les colonnes de la ligne courante. C’est techniquement réalisable, mais non supporté.

Vérifications différés

Vérifications après chaque ordre SQL
- problèmes de cohérence
Différer les vérifications de contraintes
- clause DEFERRABLE, NOT DEFERRABLE
- INITIALLY DEFERED, INITIALLY IMMEDIATE

Par défaut, toutes les contraintes d’intégrité sont vérifiées lors de l’exécution de chaque ordre SQL de modification, y compris dans une transaction. Cela peut poser des problèmes de cohérences de données : insérer dans une table fille alors qu’on n’a pas encore inséré les données dans la table mère, la clé étrangère de la table fille va rejeter l’insertion et annuler la transaction.

Le moment où les contraintes sont vérifiées est modifiable dynamiquement par l’ordre SET CONSTRAINTS :

SET CONSTRAINTS { ALL | nom [, ...] } { DEFERRED | IMMEDIATE }

mais ce n’est utilisable que pour les contraintes déclarées comme déferrables.

Voici quelques exemples :

avec la définition précédente des tables mere et fille

b1=# BEGIN;
UPDATE mere SET id=3 where id=1;
ERROR:  update or delete on table "mere" violates foreign key constraint
        "fk_mere_fille" on table "fille"
DETAIL:  Key (id)=(1) is still referenced from table "fille".

cette erreur survient aussi dans le cas où on demande que la vérification des contraintes soit différée pour cette transaction :

BEGIN;
SET CONSTRAINTS ALL DEFERRED;
UPDATE mere SET id=3 WHERE id=1;
ERROR:  update or delete on table "mere" violates foreign key constraint
        "fk_mere_fille" on table "fille"
DETAIL:  Key (id)=(1) is still referenced from table "fille".

il faut que la contrainte soit déclarée comme étant différable :


CREATE TABLE mere (id integer, t text);
CREATE TABLE fille (id integer, mere_id integer, t text);
ALTER TABLE mere ADD CONSTRAINT pk_mere PRIMARY KEY (id);
ALTER TABLE fille
  ADD CONSTRAINT fk_mere_fille
      FOREIGN KEY (mere_id)
      REFERENCES mere (id)
         MATCH FULL
         ON UPDATE NO ACTION
         ON DELETE CASCADE
         DEFERRABLE;
INSERT INTO mere (id, t) VALUES (1, 'val1'), (2, 'val2');
INSERT INTO fille (id, mere_id, t) VALUES (1, 1, 'val1');
INSERT INTO fille (id, mere_id, t) VALUES (2, 2, 'val2');

BEGIN;
SET CONSTRAINTS all deferred;
UPDATE mere SET id=3 WHERE id=1;
SELECT * FROM mere;
 id |  t
----+------
  2 | val2
  3 | val1
(2 rows)

SELECT * FROM fille;
 id | mere_id |  t
----+---------+------
  1 |       1 | val1
  2 |       2 | val2
(2 rows)

UPDATE fille SET mere_id=3 WHERE mere_id=1;
COMMIT;

Vérifications plus complexes

Un trigger
- si une contrainte porte sur plusieurs tables
- si sa vérification nécessite une sous-requête
Préférer les contraintes déclaratives

Colonnes par défaut et générées

CREATE TABLE paquet (
  code       text          PRIMARY KEY,
  reception  timestamptz   DEFAULT now(),
  livraison  timestamptz   DEFAULT now() + interval '3d',
  largeur    int,    longueur int,   profondeur int,
  volume     int
      GENERATED ALWAYS AS ( largeur * longueur * profondeur )
      STORED    CHECK (volume > 0.0)
  ) ;

DEFAULT : expressions très simples, modifiables
GENERATED
- fonctions « immutables », ne dépendant que de la ligne
- difficilement modifiables
- peuvent porter des contraintes

Les champs DEFAULT sont très utilisés, mais PostgreSQL supporte les colonnes générées (ou calculées).

DEFAULT …

Les champs avec une clause DEFAULT sont remplis automatiquement à la création de la ligne, à moins qu’une valeur ne soit fournie dans l’ordre INSERT. Seules sont autorisées des expressions simples, sans variable, ni utilisation de champs de la ligne, ni sous-requête. Sont acceptées des constantes, certains calculs ou fonctions simples, comme now(), ou un appel à nextval ('nom_séquence').

Ajouter une clause DEFAULT sur une table calcule les valeurs pour toutes les lignes pré-existantes de la table Une optimisation évite de tout réécrire si les anciennes lignes se retrouvent toutes avec la même valeur constante.

La valeur par défaut peut être écrasée, notamment par INSERT ou UPDATE.

Changer l’expression d’une clause DEFAULT est possible :

ALTER TABLE paquet
ALTER COLUMN livraison SET DEFAULT now()+interval '4d';

mais cela n’a pas d’impact sur les lignes existantes, juste les nouvelles.

GENERATED ALWAYS AS ( … ) STORED

La syntaxe est :

nomchamp  <type> GENERATED ALWAYS AS ( <expression> ) STORED ;

Les colonnes générées sont recalculées à chaque fois que les champs sur lesquels elles sont basées changent, donc aussi lors d’un UPDATE (avant PostgreSQL 13, ils étaient systématiquement recalculés, parfois inutilement). Ces champs calculés sont impérativement marqués ALWAYS, c’est-à-dire obligatoires et non modifiables, et STORED, c’est-à-dire stockés sur le disque (et non recalculés à la volée comme dans une vue). Ils ne doivent pas se baser sur d’autres champs calculés.

Un intérêt est que les champs calculés peuvent porter des contraintes, par exemple la clause CHECK ci-dessous, mais encore des clés étrangères ou unique.

Exemple :

CREATE TABLE paquet (
  code       text          PRIMARY KEY,
  reception  timestamptz   DEFAULT now(),
  livraison  timestamptz   DEFAULT now() + interval '3d',
  largeur    int,    longueur int,   profondeur int,
  volume     int
      GENERATED ALWAYS AS ( largeur * longueur * profondeur )
      STORED    CHECK (volume > 0.0)
  ) ;

INSERT INTO paquet (code, largeur, longueur, profondeur)
VALUES ('ZZ1', 3, 5, 10) ;

\x on

TABLE paquet ;

-[ RECORD 1 ]-----------------------------
code       | ZZ1
reception  | 2024-04-19 18:02:41.021444+02
livraison  | 2024-04-22 18:02:41.021444+02
largeur    | 3
longueur   | 5
profondeur | 10
volume     | 150

-- Les champs DEFAULT sont modifiables
-- Changer la largeur va modifier le volume
UPDATE paquet
SET largeur=4,
    livraison = '2024-07-14'::timestamptz,
    reception = '2024-04-20'::timestamptz
WHERE code='ZZ1' ;

TABLE paquet ;

-[ RECORD 1 ]----------------------
code       | ZZ1
reception  | 2024-04-20 00:00:00+02
livraison  | 2024-07-14 00:00:00+02
largeur    | 4
longueur   | 5
profondeur | 10
volume     | 200

-- Le volume ne peut être modifié
UPDATE paquet 
SET volume = 250
WHERE code = 'ZZ1' ;

ERROR:  column "volume" can only be updated to DEFAULT
DETAIL : Column "volume" is a generated column.

Expression immutable :

Avec GENERATED, l’expression du calcul doit être « immutable », c’est-à-dire ne dépendre que des autres champs de la même ligne, n’utiliser que des fonctions elles-mêmes immutables, et rien d’autre. Il n’est donc pas possible d’utiliser des fonctions comme now(), ni des fonctions de conversion de date dépendant du fuseau horaire, ou du paramètre de formatage de la session en cours (toutes choses autorisées avec DEFAULT), ni des appels à d’autres lignes ou tables…

La colonne calculée peut être convertie en colonne « normale » :

ALTER TABLE paquet ALTER COLUMN volume DROP EXPRESSION ;

Mais modifier l’expression n’est pas (encore) possible, sauf à supprimer la colonne générée et en créer une nouvelle, ce qui implique de recalculer toutes les lignes et réécrire toute la table.

Il est possible de créer sa propre fonction pour l’expression, qui doit aussi être immutable :

CREATE OR REPLACE FUNCTION volume (l int, h int, p int)
RETURNS int
AS $$
 SELECT l * h * p ;
$$
LANGUAGE sql
-- cette fonction dépend uniquement des données de la ligne donc :
PARALLEL SAFE
IMMUTABLE ;

ALTER TABLE paquet DROP COLUMN volume ;

ALTER TABLE paquet ADD COLUMN volume int
  GENERATED ALWAYS AS ( volume (largeur, longueur, profondeur) )
  STORED;

TABLE paquet ;

-[ RECORD 1 ]----------------------
code       | ZZ1
reception  | 2024-04-20 00:00:00+02
livraison  | 2024-07-14 00:00:00+02
largeur    | 4
longueur   | 5
profondeur | 10
volume     | 200

Attention : modifier la fonction ne réécrit pas spontanément la table, il faut forcer la réécriture avec par exemple :

UPDATE paquet SET longueur = longueur ;

Ne pas réécrire les anciennes valeurs calculées n’est pas un moyen de les conserver. En effet, en cas de sauvegarde logique et restauration, tous les champs seront recalculés avec la dernière formule !

Un autre piège : il faut résister à la tentation de déclarer une fonction comme immutable sans la certitude qu’elle l’est bien (penser aux paramètres de session, aux fuseaux horaires…), sous peine d’incohérences dans les données.

Cas d’usage :

Les colonnes générées économisent la création de triggers, ou de vues de « présentation ». Elles facilitent la dénormalisation de données calculées dans une même table tout en garantissant l’intégrité.

Un cas d’usage courant est la dénormalisation d’attributs JSON pour les manipuler comme des champs de table classiques :

ALTER TABLE personnes
ADD COLUMN  lastname text
GENERATED ALWAYS AS ((datas->>'lastName')) STORED ;

L’accès au champ est notablement plus rapide que l’analyse systématique du champ JSON.

Par contre, les colonnes GENERATED ne sont pas un bon moyen pour créer des champs portant la dernière mise à jour. Certes, PostgreSQL ne vous empêchera pas de déclarer une fonction (abusivement) immutable utilisant now() ou une variante. Mais ces informations seront perdues en cas de restauration logique. Dans ce cas, les triggers restent une option plus complexe mais plus propre.

DML : mise à jour des données

SELECT peut lire les données d’une table ou plusieurs tables
- mais ne peut pas les mettre à jour
Ajout de données dans une table
- INSERT
Modification des données d’une table
- UPDATE
Suppression des données d’une table
- DELETE

Ajout de données : INSERT

Ajoute des lignes à partir des données de la requête
Ajoute des lignes à partir d’une requête SELECT
Syntaxe :

INSERT INTO nom_table [ ( nom_colonne [, ...] ) ]
    { liste_valeurs | requete }

L’ordre INSERT insère de nouvelles lignes dans une table. Il permet d’insérer une ou plusieurs lignes spécifiées par les expressions de valeur, ou zéro ou plusieurs lignes provenant d’une requête.

La liste des noms de colonnes est optionnelle. Si elle n’est pas spécifiée, alors PostgreSQL utilisera implicitement la liste de toutes les colonnes de la table dans l’ordre de leur déclaration, ou les N premiers noms de colonnes si seules N valeurs de colonnes sont fournies dans la clause VALUES ou dans la requête. L’ordre des noms des colonnes dans la liste n’a pas d’importance particulière, il suffit de nommer les colonnes mises à jour.

Chaque colonne absente de la liste, implicite ou explicite, se voit attribuer sa valeur par défaut, s’il y en a une ou NULL dans le cas contraire. Les expressions de colonnes qui ne correspondent pas au type de données déclarées sont transtypées automatiquement, dans la mesure du possible.

INSERT avec liste d’expressions

INSERT INTO nom_table [ ( nom_colonne [, ...] ) ]
    VALUES ( { expression | DEFAULT } [, ...] ) [, ...]

La clause VALUES permet de définir une liste d’expressions qui va constituer la ligne à insérer dans la base de données. Les éléments de cette liste d’expression sont séparés par une virgule. Cette liste d’expression est composée de constantes ou d’appels à des fonctions retournant une valeur, pour obtenir par exemple la date courante ou la prochaine valeur d’une séquence. Les valeurs fournies par la clause VALUES ou par la requête sont associées à la liste explicite ou implicite des colonnes de gauche à droite.

Exemples

Insertion d’une ligne dans la table stock :

INSERT INTO stock (vin_id, contenant_id, annee, nombre)
 VALUES (12, 1, 1935, 1);

Insertion d’une ligne dans la table vin :

INSERT INTO vin (id, recoltant_id, appellation_id, type_vin_id)
 VALUES (nextval('vin_id_seq'), 3, 6, 1);

INSERT à partir d’un SELECT

INSERT INTO nom_table [ ( nom_colonne [, ...] ) ]
    requête

L’ordre INSERT peut aussi prendre une requête SQL en entrée. Dans ce cas, INSERT va insérer autant de lignes dans la table d’arrivée qu’il y a de lignes retournées par la requête SELECT. L’ordre des colonnes retournées par SELECT doit correspondre à l’ordre des colonnes de la liste des colonnes. Leur type de données doit également correspondre.

Exemples

Insertion dans une table stock2 à partir d’une requête SELECT sur la table stock1 :

INSERT INTO stock2 (vin_id, contenant_id, annee, nombre)
SELECT vin_id, contenant_id, annee, nombre FROM stock;

INSERT et colonnes implicites

L’ordre physique peut changer dans le temps
- résultats incohérents
- requêtes en erreurs

Il est préférable de lister explicitement les colonnes touchées par l’ordre INSERT afin de garder un ordre d’insertion déterministe. En effet, l’ordre des colonnes peut changer notamment lorsque certains ETL sont utilisés pour modifier le type d’une colonne varchar(10) en varchar(11). Par exemple, pour la colonne username, l’ETL Kettle génère les ordres suivants :

ALTER TABLE utilisateurs ADD COLUMN username_KTL VARCHAR(11);
UPDATE utilisateurs SET username_KTL=username;
ALTER TABLE utilisateurs DROP COLUMN username;
ALTER TABLE utilisateurs RENAME username_KTL TO username

Il génère des ordres SQL inutiles et consommateurs d’entrées/sorties disques car il doit générer des ordres SQL compris par tous les SGBD du marché. Or, tous les SGBD ne permettent pas de changer le type d’une colonne aussi simplement que dans PostgreSQL.

Exemples

Exemple de modification du schéma pouvant entrainer des problèmes d’insertion si les colonnes ne sont pas listées explicitement :

CREATE TABLE insere (id integer PRIMARY KEY, col1 varchar(5), col2 integer);

INSERT INTO insere VALUES (1, 'XX', 10);

ALTER TABLE insere ADD COLUMN col1_tmp varchar(6);
UPDATE insere SET col1_tmp = col1;
ALTER TABLE insere DROP COLUMN col1;
ALTER TABLE insere RENAME COLUMN col1_tmp TO col1;

INSERT INTO insere VALUES (2, 'XXX', 10);
ERROR:  invalid input syntax for integer: "XXX"
LINE 1: INSERT INTO insere VALUES (2, 'XXX', 10);

Mise à jour de données : UPDATE

Ordre UPDATE
Met à jour une ou plusieurs colonnes d’une même ligne
- à partir des valeurs de la requête
- à partir des anciennes valeurs
- à partir d’une requête SELECT
- à partir de valeurs d’une autre table

Construction d’UPDATE

UPDATE nom_table
    SET
    {
     nom_colonne = { expression | DEFAULT }
    |
     ( nom_colonne [, ...] ) = ( { expression | DEFAULT } [, ...] )
    } [, ...]
    [ FROM liste_from ]
    [ WHERE condition | WHERE CURRENT OF nom_curseur ]

L’ordre UPDATE permet de mettre à jour les lignes d’une table.

L’ordre UPDATE ne met à jour que les lignes qui satisfont les conditions de la clause WHERE. La clause SET permet de définir les colonnes à mettre à jour. Le nom des colonnes mises à jour doivent faire partie de la table mise à jour.

Les valeurs mises à jour peuvent faire référence aux valeurs avant mise à jour de la colonne, dans ce cas on utilise la forme nom_colonne = nom_colonne. La partie de gauche référence la colonne à mettre à jour, la partie de droite est une expression qui permet de déterminer la valeur à appliquer à la colonne. La valeur à appliquer peut bien entendu être une référence à une ou plusieurs colonnes et elles peuvent être dérivées par une opération arithmétique.

La clause FROM ne fait pas partie de la norme SQL mais certains SGBDR la supportent, notamment SQL Server et PostgreSQL. Elle permet de réaliser facilement la mise à jour d’une table à partir des valeurs d’une ou plusieurs tables annexes.

La norme SQL permet néanmoins de réaliser des mises à jour en utilisant une sous-requête, permettant d’éviter l’usage de la clause FROM.

Exemples

Mise à jour du prix d’un livre particulier :

UPDATE livres SET prix = 10 WHERE isbn = '978-3-8365-3872-5';

Augmentation de 5 % du prix des livres :

UPDATE livres SET prix = prix * 1.05;

Mise à jour d’une table employees à partir des données d’une table bonus_plan :

UPDATE employees e
   SET commission_rate = bp.commission_rate
  FROM bonus_plan bp
    ON (e.bonus_plan = bp.planid)

La même requête avec une sous-requête, conforme à la norme SQL :

UPDATE employees
   SET commission_rate = (SELECT commission_rate
                            FROM bonus_plan bp
                           WHERE bp.planid = employees.bonus_plan);

Lorsque plusieurs colonnes doivent être mises à jour à partir d’une jointure, il est possible d’utiliser ces deux écritures :

UPDATE employees e
   SET commission_rate = bp.commission_rate,
       commission_rate2 = bp.commission_rate2
  FROM bonus_plan bp
    ON (e.bonus_plan = bp.planid);

UPDATE employees e
   SET (commission_rate, commission_rate2) = (
      SELECT bp.commission_rate, bp.commission_rate2
      FROM bonus_plan bp ON (e.bonus_plan = bp.planid)
   );

Suppression de données : DELETE

Supprime les lignes répondant au prédicat
Syntaxe :

DELETE FROM nom_table [ [ AS ] alias ]
    [ WHERE condition

L’ordre DELETE supprime l’ensemble des lignes qui répondent au prédicat de la clause WHERE.

DELETE FROM nom_table [ [ AS ] alias ]
    [ WHERE condition | WHERE CURRENT OF nom_curseur ]

Exemples

Suppression d’un livre épuisé du catalogue :

DELETE FROM livres WHERE isbn = '978-0-8707-0635-6';

Clause RETURNING

Spécifique à PostgreSQL
Permet de retourner les lignes complètes ou partielles résultants de INSERT, UPDATE ou DELETE
Syntaxe :

requete_sql RETURNING ( * | expression )

La clause RETURNING est une extension de PostgreSQL. Elle permet de retourner les lignes insérées, mises à jour ou supprimées par un ordre DML de modification. Il est également possible de dériver une valeur retournée.

L’emploi de la clause RETURNING peut nécessiter des droits complémentaires sur les objets de la base.

Exemples

Mise à jour du nombre de bouteilles en stock :

SELECT annee, nombre FROM stock
WHERE vin_id = 7 AND contenant_id = 1 AND annee = 1967;
 annee | nombre
-------+--------
  1967 |     17
(1 row)

UPDATE stock SET nombre = nombre - 1
WHERE vin_id = 7 AND contenant_id = 1 AND annee = 1967 RETURNING nombre;
 nombre
--------
     16
(1 row)

Transactions

ACID
- Atomicité
- un traitement se fait en entier ou pas du tout
TCL pour Transaction Control Language
- valide une transaction
- annule une transaction
- points de sauvegarde

Auto-commit et transactions

Par défaut, PostgreSQL fonctionne en auto-commit
- à moins d’ouvrir explicitement une transaction
Ouvrir une transaction
- BEGIN TRANSACTION

Validation ou annulation d’une transaction

Valider une transaction
- COMMIT
Annuler une transaction
- ROLLBACK
Sans validation, une transaction est forcément annulée

Une transaction est toujours terminée par un COMMIT ou un END quand on veut que les modifications soient définitivement enregistrées, et par un ROLLBACK dans le cas contraire.

La transaction en cours d’une session qui se termine, quelle que soit la raison, sans COMMIT et sans ROLLBACK est considérée comme annulée.

Exemples

Avant de retirer une bouteille du stock, on vérifie tout d’abord qu’il reste suffisamment de bouteilles en stock :

BEGIN TRANSACTION;

SELECT annee, nombre FROM stock WHERE vin_id = 7 AND contenant_id = 1
AND annee = 1967;
 annee | nombre
-------+--------
  1967 |     17
(1 row)

UPDATE stock SET nombre = nombre - 1
WHERE vin_id = 7 AND contenant_id = 1 AND annee = 1967 RETURNING nombre;
 nombre
--------
     16
(1 row)

COMMIT;

Programmation

Certains langages implémentent des méthodes de gestion des transactions
- PHP, Java, etc.
Utiliser ces méthodes prioritairement

La plupart des langages permettent de gérer les transactions à l’aide de méthodes ou fonctions particulières. Il est recommandé de les utiliser.

En Java, ouvrir une transaction revient à désactiver l’auto-commit :

String url =
    "jdbc:postgresql://localhost/test?user=fred&password=secret&ssl=true";
Connection conn = DriverManager.getConnection(url);
conn.setAutoCommit(false);

La transaction est confirmée (COMMIT) avec la méthode suivante :

conn.commit();

À l’inverse, elle est annulée (ROLLBACK) avec la méthode suivante :

conn.rollback();

Points de sauvegarde

Certains traitements dans une transaction peuvent être annulés
- mais la transaction est atomique
Définir un point de sauvegarde
- SAVEPOINT nom_savepoint
Valider le traitement depuis le dernier point de sauvegarde
- RELEASE SAVEPOINT nom_savepoint
Annuler le traitement depuis le dernier point de sauvegarde
- ROLLBACK TO SAVEPOINT nom_savepoint

Au sein d’une transaction, les points de sauvegarde (ordre SAVEPOINT nom) permettent d’encadrer un traitement sur lequel on peut vouloir revenir sans quitter la transaction (par exemple, selon le retour d’un test), ou au cas où un ordre tombe en erreur.

Revenir à l’état du point de sauvegarde se fait avec ROLLBACK TO SAVEPOINT pour annuler uniquement la partie du traitement voulue, ou pour revenir à l’état d’avant l’erreur, sans annuler le début de la transaction.

Les savepoints n’existent qu’au sein de la transaction ; on ne peut bien sûr revenir sur une validation par COMMIT.

L’ordre RELEASE SAVEPOINT permet de « libérer » (oublier) un savepoint précédent (ainsi que ceux posés après). C’est surtout utile pour libérer quelques ressources dans des transactions complexes.

Les points de sauvegarde sont des éléments nommés, il convient donc de leur affecter un nom particulier. Leur nom doit être unique dans la transaction courante.

Les langages de programmation permettent également de gérer les points de sauvegarde en utilisant des méthodes dédiées. Par exemple, en Java :

Savepoint save1 = connection.setSavepoint();

En cas d’erreurs, la transaction peut être ramenée à l’état du point de sauvegarde avec :

connection.rollback(save1);

À l’inverse, un point de sauvegarde est relâché de la façon suivante :

connection.releaseSavepoint(save1);

Exemples

Transaction avec un point de sauvegarde et la gestion de l’erreur :

BEGIN;

INSERT INTO mere (id, val_mere) VALUES (10, 'essai');

SAVEPOINT insert_fille;

INSERT INTO fille (id_fille, id_mere, val_fille) VALUES (1, 10, 'essai 2');
ERROR:  duplicate key value violates unique constraint "fille_pkey"
DETAIL:  Key (id_fille)=(1) already exists.

ROLLBACK TO SAVEPOINT insert_fille;

COMMIT;

SELECT * FROM mere;
 id | val_mere
----+----------
  1 | mere 1
  2 | mere 2
 10 | essai

Conclusion

SQL : toujours un traitement d’ensembles d’enregistrements
- c’est le côté relationnel
Pour les définitions d’objets
- CREATE, ALTER, DROP
Pour les données
- INSERT, UPDATE, DELETE

Questions

N’hésitez pas, c’est le moment !

Travaux pratiques

Cet exercice utilise la base tpc.

La base tpc (dump de 31 Mo, pour 267 Mo sur le disque au final) et ses utilisateurs peuvent être installés comme suit :

curl -kL https://dali.bo/tp_tpc -o /tmp/tpc.dump
curl -kL https://dali.bo/tp_tpc_roles -o /tmp/tpc_roles.sql
# Exécuter le script de création des rôles
psql < /tmp/tpc_roles.sql
# Création de la base
createdb --owner tpc_owner tpc
# L'erreur sur un schéma 'public' existant est normale
pg_restore -d tpc /tmp/tpc.dump
rm -- /tmp/tpc.dump /tmp/tpc_roles.sql

Les mots de passe sont dans le script /tmp/tpc_roles.sql. Pour vous connecter :

$ psql -U tpc_admin -h localhost -d tpc

Pour cet exercice, les modifications de schéma doivent être effectuées par un rôle ayant suffisamment de droits pour modifier son schéma. Le rôle tpc_admin a les droits suffisants.

Ajouter une colonne email de type text à la table contacts. Cette colonne va permettre de stocker l’adresse e-mail des clients et des fournisseurs. Ajouter également un commentaire décrivant cette colonne dans le catalogue de PostgreSQL (utiliser la commande COMMENT).

Mettre à jour la table des contacts pour indiquer l’adresse e-mail de Client6657 qui est client6657@dalibo.com.

Ajouter une contrainte d’intégrité qui valide que la valeur de la colonne email créée est bien formée (vérifier que la chaîne de caractère contient au moins le caractère @).

Valider la contrainte dans une transaction de test.

Déterminer quels sont les contacts qui disposent d’une adresse e-mail et affichez leur nom ainsi que le code de leur pays.

La génération des numéros de commande est actuellement réalisée à l’aide de la séquence commandes_commande_id_seq. Cette méthode ne permet pas de garantir que tous les numéros de commande se suivent. Proposer une solution pour sérialiser la génération des numéros de commande. Autrement dit, proposer une méthode pour obtenir un numéro de commande sans avoir de « trou » dans la séquence en cas d’échec d’une transaction.

Noter le nombre de lignes de la table pieces. Dans une transaction, majorer de 5% le prix des pièces de moins de 1500 € et minorer de 5 % le prix des pièces dont le prix actuel est égal ou supérieur à 1500 €. Vérifier que le nombre de lignes mises à jour au total correspond au nombre total de lignes de la table pieces.

Dans une même transaction, créer un nouveau client en incluant l’ajout de l’ensemble des informations requises pour pouvoir le contacter. Un nouveau client a un solde égal à 0.

Travaux pratiques (solutions)

Ajouter une colonne email de type text à la table contacts. Cette colonne va permettre de stocker l’adresse e-mail des clients et des fournisseurs. Ajouter également un commentaire décrivant cette colonne dans le catalogue de PostgreSQL (utiliser la commande COMMENT).

-- Ajouter une colonne email de type text
ALTER TABLE contacts
ADD COLUMN email text;

-- Ajouter un commentaire
COMMENT ON COLUMN contacts.email IS 'Adresse e-mail du contact';

Mettre à jour la table des contacts pour indiquer l’adresse e-mail de Client6657 qui est client6657@dalibo.com.

UPDATE contacts
   SET email = 'client6657@dalibo.com'
WHERE nom = 'Client6657';

Vérifier les résultats :

SELECT *
FROM contacts
WHERE nom = 'Client6657';

Ajouter une contrainte d’intégrité qui valide que la valeur de la colonne email créée est bien formée (vérifier que la chaîne de caractère contient au moins le caractère @).

ALTER TABLE contacts
ADD CONSTRAINT chk_contacts_email_valid
   CHECK (email LIKE '%@%');

Cette expression régulière est simplifiée et simpliste pour les besoins de l’exercice. Des expressions régulières plus complexes permettent de valider réellement une adresse e-mail.

Voici un exemple un tout petit peu plus évolué en utilisant une expression rationnelle simple, ici pour vérifier que la chaîne précédent le caractère @ contient au moins un caractère, et que la chaîne le suivant est une chaîne de caractères contenant un point :

ALTER TABLE contacts
ADD CONSTRAINT chk_contacts_email_valid
   CHECK (email ~ '.+@.+\..+');

Valider la contrainte dans une transaction de test.

Démarrer la transaction :

BEGIN ;

Tenter de mettre à jour la table contacts avec une adresse e-mail ne répondant pas à la contrainte :

UPDATE contacts
   SET email = 'test';

L’ordre UPDATE retourne l’erreur suivante, indiquant que l’expression régulière est fonctionnelle :

ERROR:  new row for relation "contacts" violates check constraint
        "chk_contacts_email_valid"
DETAIL: Failing row contains
        (300001, Client1737, nkD, SA, 20-999-929-1440, test).

La transaction est ensuite annulée :

ROLLBACK ;

Déterminer quels sont les contacts qui disposent d’une adresse e-mail et affichez leur nom ainsi que le code de leur pays.

SELECT nom, code_pays
FROM contacts
WHERE email IS NOT NULL;

La génération des numéros de commande est actuellement réalisée à l’aide de la séquence commandes_commande_id_seq. Cette méthode ne permet pas de garantir que tous les numéros de commande se suivent. Proposer une solution pour sérialiser la génération des numéros de commande. Autrement dit, proposer une méthode pour obtenir un numéro de commande sans avoir de « trou » dans la séquence en cas d’échec d’une transaction.

La solution la plus simple pour imposer la sérialisation des numéros de commandes est d’utiliser une table de séquences. Une ligne de cette table correspondra au compteur des numéros de commande.

-- création de la table qui va contenir la séquence :
CREATE TABLE numeros_sequences (
   nom text NOT NULL PRIMARY KEY,
   sequence integer NOT NULL
);

-- initialisation de la séquence :
INSERT INTO numeros_sequences (nom, sequence)
SELECT 'sequence_numero_commande', max(numero_commande)
FROM commandes;

L’obtention d’un nouveau numéro de commande sera réalisé dans la transaction de création de la commande de la façon suivante :

BEGIN ;

UPDATE numeros_sequences
   SET sequence = sequence + 1
WHERE nom = 'numero_commande'
RETURNING sequence;

/* insertion d'une nouvelle commande en utilisant le numéro de commande
   retourné par la commande précédente :
   INSERT INTO commandes (numero_commande, ...)
   VALUES (<la nouvelle valeur de la séquence>, ...) ;
*/

COMMIT ;

L’ordre UPDATE pose un verrou exclusif sur la ligne mise à jour. Tant que la mise à jour n’aura pas été validée ou annulée par COMMIT ou ROLLBACK, le verrou posé va bloquer toutes les autres transactions qui tenteraient de mettre à jour cette ligne. De cette façon, toutes les transactions seront sérialisées.

Concernant la génération des numéros de séquence, si la transaction est annulée, alors le compteur sequence retrouvera sa valeur précédente et la transaction suivante obtiendra le même numéro de séquence. Si la transaction est validée, alors le compteur sequence est incrémenté. La transaction suivante verra alors cette nouvelle valeur et non plus l’ancienne. Cette méthode garantit qu’il n’y ait pas de rupture de séquence.

Il va de soi que les transactions de création de commandes doivent être extrêmement courtes. Si une telle transaction est bloquée, toutes les transactions suivantes seront également bloquées, paralysant ainsi tous les utilisateurs de l’application.

Noter le nombre de lignes de la table pieces. Dans une transaction, majorer de 5% le prix des pièces de moins de 1500 € et minorer de 5 % le prix des pièces dont le prix actuel est égal ou supérieur à 1500 €. Vérifier que le nombre de lignes mises à jour au total correspond au nombre total de lignes de la table pieces.

BEGIN ;

SELECT count(*)
FROM pieces;

UPDATE pieces
   SET prix = prix * 1.05
WHERE prix < 1500;

UPDATE pieces
   SET prix = prix * 0.95
WHERE prix >= 1500;

Au total, la transaction a mis à jour 214200 (99922+114278) lignes, soit 14200 lignes de trop mises à jour.

Annuler la mise à jour :

ROLLBACK ;

Explication : Le premier UPDATE a majoré de 5 % les pièces dont le prix est inférieur à 1500 €. Or, tous les prix supérieurs à 1428,58 € passent la barre des 1500 € après le premier UPDATE. Le second UPDATE minore les pièces dont le prix est égal ou supérieur à 1500 €, ce qui inclue une partie des prix majorés par le précédent UPDATE. Certaines lignes ont donc subies deux modifications au lieu d’une. L’instruction CASE du langage SQL, qui sera abordée dans le prochain module, propose une solution à ce genre de problématique :

UPDATE pieces
   SET prix = (
      CASE
         WHEN prix < 1500 THEN prix * 1.05
         WHEN prix >= 1500 THEN prix * 0.95
      END
   );

Dans une même transaction, créer un nouveau client en incluant l’ajout de l’ensemble des informations requises pour pouvoir le contacter. Un nouveau client a un solde égal à 0.

-- démarrer la transaction
BEGIN ;

-- créer le contact et récupérer le contact_id généré
INSERT INTO contacts (nom, adresse, telephone, code_pays)
   VALUES ('M. Xyz', '3, Rue du Champignon, 96000 Champiville',
           '+33554325432', 'FR')
RETURNING contact_id;

-- réaliser l'insertion en utilisant le numéro de contact récupéré précédemment
INSERT INTO clients (solde, segment_marche, contact_id, commentaire)
   -- par exemple ici avec le numéro 350002
   VALUES (0, 'AUTOMOBILE', 350002, 'Client très important');

-- valider la transaction
COMMIT ;

Création d’objets et mises à jour

Sur ce document

Licence Creative Commons CC-BY-NC-SA

Marques déposées

Versions de PostgreSQL couvertes

Création d’objet et mises à jour

Introduction

Menu

Objectifs

DDL

Objets d’une base de données

Créer des objets

Modifier des objets

Supprimer des objets

Schéma

Gestion d’un schéma

Accès aux objets

Séquences

Création d’une séquence

Modification d’une séquence

Suppression d’une séquence

Séquences, utilisation

Type SERIAL

Domaines

Tables

Création d’une table

CREATE TABLE

Définition des colonnes

Valeur par défaut

Copie de la définition d’une table

Modification d’une table

Conséquences des modifications d’une table

Suppression d’une table

Contraintes d’intégrité

Clé primaire d’une table

Déclaration d’une clé primaire

Contrainte d’unicité

Déclaration d’une contrainte d’unicité

Intégrité référentielle

Déclaration d’une clé étrangère

Vérification simple ou complète

Clé primaire et colonne identité

Mise à jour de la clé primaire

Vérifications

Vérifications différés

Vérifications plus complexes

Colonnes par défaut et générées

DML : mise à jour des données

Ajout de données : INSERT

INSERT avec liste d’expressions

INSERT à partir d’un SELECT

INSERT et colonnes implicites

Mise à jour de données : UPDATE

Construction d’UPDATE

Suppression de données : DELETE

Clause RETURNING

Transactions

Auto-commit et transactions

Validation ou annulation d’une transaction

Programmation

Points de sauvegarde

Conclusion

Questions

Travaux pratiques

Travaux pratiques (solutions)