Une requête, plusieurs écritures dans PostgreSQL

Une opération métier touche souvent plusieurs tables : archiver des lignes, appliquer un lot de prix, libérer un créneau.

Chaque étape exécutée séparément ajoute un aller-retour et une fenêtre où l’état peut changer. PostgreSQL permet d’écrire ces étapes dans une seule instruction, avec WITH, UPDATE … FROM et RETURNING.

Cet article présente ces trois briques, la règle du snapshot qui les relie et l’index unique partiel qui protège un invariant.

Schéma pilote

Le contexte est celui d’un catalogue de produits.
Chaque produit a un nom, un prix de vente et un marqueur indiquant si les produits sont retirés du catalogue, c’est-à-dire plus commercialisés.
Ils sont alors archivés.

Les exemples ciblent PostgreSQL 18 et utilisent deux tables : products et products_archive.

CREATE TABLE products (
    id           int PRIMARY KEY,
    name         text NOT NULL,
    price        numeric(10, 2) NOT NULL,
    discontinued boolean NOT NULL DEFAULT false
);
CREATE TABLE products_archive (LIKE products);

INSERT INTO products VALUES
    (1, 'clavier', 40.00, false),
    (2, 'souris', 20.00, false),
    (3, 'ecran', 150.00, true);

Un WITH est évalué une fois et peut être intégré à la requête

Un WITH donne un nom à une sous-requête.

PostgreSQL l’évalue en général une seule fois, même si la requête principale la référence plusieurs fois^[1].

Depuis PostgreSQL 12, une CTE non récursive, sans effet de bord et référencée une seule fois est intégrée à la requête principale^[2].

MATERIALIZED force l’évaluation unique, NOT MATERIALIZED force l’intégration.

WITH discontinued_products AS MATERIALIZED (
    SELECT id, price FROM products WHERE discontinued
)
SELECT count(*), sum(price) FROM discontinued_products;

RETURNING renvoie les lignes écrites

INSERT, UPDATE, DELETE et MERGE acceptent une clause RETURNING. Elle renvoie les lignes telles qu’elles ont été écrites, sans second SELECT^[3].

L’article sur les jobs asynchrones l’utilise pour récupérer les jobs réservés par un worker.

PostgreSQL 18 ajoute les alias old et new dans RETURNING^[4].

UPDATE products SET price = price * 1.05
WHERE id = 1
RETURNING old.price AS old_price, new.price AS new_price;

UPDATE … FROM joint la cible à une autre table

UPDATE … FROM met à jour une table à partir des lignes d’une autre table^[3].

CREATE TABLE price_changes (product_id int, new_price numeric(10, 2));
INSERT INTO price_changes VALUES (1, 42.00), (2, 18.50);

UPDATE products p
SET price = c.new_price
FROM price_changes c
WHERE c.product_id = p.id;

La jointure doit produire au plus une ligne par ligne cible. Si price_changes contient deux lignes pour le même product_id, une seule est utilisée. La documentation précise qu’on ne peut pas prévoir laquelle.

Les CTE modifiantes partagent un snapshot

Un WITH peut contenir un INSERT, un UPDATE ou un DELETE. Cette sous-instruction s’exécute une fois et jusqu’au bout, même si la requête principale ne lit pas sa sortie. L’exemple suivant déplace les produits retirés du catalogue vers l’archive.

WITH moved AS (
    DELETE FROM products
    WHERE discontinued
    RETURNING *
)
INSERT INTO products_archive
SELECT * FROM moved;

Seul RETURNING transmet les effets d’une CTE modifiante au reste de la requête. Toutes les sous-instructions travaillent sur le même snapshot^[1], et l’ordre dans lequel elles écrivent n’est pas défini.

Elles ne voient donc pas les écritures des autres dans les tables.

Dans l’exemple suivant, b compte les lignes de products pendant que a en insère une. b ne la voit pas, même si les deux CTE sont dans la même requête.
Seul RETURNING fait remonter la ligne.

La requête principale lit l’id de a, et b.n vaut 3 et non 4.

BEGIN;
WITH a AS (
    INSERT INTO products VALUES (4, 'tapis', 8.00, false)
    RETURNING id
), b AS (
    SELECT count(*) AS n FROM products
)
SELECT a.id, b.n FROM a, b;
ROLLBACK;

Dans les deux exemples ci-dessous, le premier SELECT renvoie les anciens prix, le second les nouveaux.

BEGIN;
WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM products;
ROLLBACK;

BEGIN;
WITH t AS (
    UPDATE products SET price = price * 1.05
    RETURNING *
)
SELECT * FROM t;
ROLLBACK;

Un index unique partiel limite l’unicité à un sous-ensemble

Un index unique partiel impose l’unicité sur les seules lignes qui satisfont son prédicat^[5].
Cela exprime un invariant du type « au plus une tâche en attente par cible ».

CREATE TABLE tasks (
    id     serial PRIMARY KEY,
    target text NOT NULL,
    status text NOT NULL
);
CREATE UNIQUE INDEX tasks_pending_target_uq
    ON tasks (target) WHERE status = 'PENDING';

INSERT INTO tasks (target, status) VALUES ('plan-a', 'PENDING');
INSERT INTO tasks (target, status) VALUES ('plan-a', 'RUNNING');
INSERT INTO tasks (target, status) VALUES ('plan-a', 'PENDING');

Les deux premiers INSERT réussissent. Le troisième échoue avec une violation de contrainte d’unicité (SQLSTATE 23505).
Un UPDATE qui ramène la ligne RUNNING à PENDING échoue de la même façon.
INSERT … ON CONFLICT peut cibler cet index avec WHERE index_predicate^[6].

Conclusion

WITH, RETURNING et UPDATE … FROM permettent d’écrire une opération en plusieurs étapes dans une seule instruction.

Les CTE modifiantes suivent une règle : toutes les sous-instructions lisent le même snapshot, et RETURNING est le seul moyen de transmettre une écriture au reste de la requête.

L’index unique partiel complète l’ensemble. Il impose un invariant sur un sous-ensemble de lignes, et la base le vérifie à chaque écriture.

Reproduire les exemples

cd sql
./run.sh

Ressources

1. PostgreSQL 18 : WITH Queries
2. PostgreSQL 12 : notes de version
3. PostgreSQL 18 : UPDATE
4. PostgreSQL 18 : notes de version
5. PostgreSQL 18 : Partial Indexes
6. PostgreSQL 18 : INSERT, clause ON CONFLICT
7. PostgreSQL 15 : notes de version
8. PostgreSQL 17 : notes de version
9. PostgreSQL 18 : Transaction Isolation
10. PostgreSQL 18 : MERGE