PostgreSQL ne crée pas de transaction imbriquée lorsqu’on envoie un second BEGIN. Il affiche un avertissement et conserve la transaction en cours ; le premier COMMIT réussi y met fin. Utilisez des points de sauvegarde lorsqu’un utilitaire doit pouvoir annuler une partie des changements. PostgreSQL : BEGIN

Ce guide aide les développeurs travaillant dans le Terminal de Mac à diagnostiquer un utilitaire qui valide trop tôt une opération de modification d’article. Les exemples utilisent des lignes d’articles synthétiques et montrent comment conserver un seul responsable de la transaction tout en permettant à un utilitaire d’annuler son propre lot. Ils suivent la documentation actuelle de PostgreSQL et de node-postgres.

Que se passe-t-il quand BEGIN et COMMIT apparaissent dans une autre transaction ?

Les commandes de transaction SQL agissent sur la transaction en cours de la connexion. PostgreSQL ne compte pas les appels répétés à BEGIN en attendant des appels correspondants à COMMIT. Un second BEGIN ne modifie pas la transaction ; un COMMIT simple réussi valide cette transaction en cours. L’envoi d’un autre COMMIT simple ensuite produit un avertissement, car aucune transaction n’est ouverte. PostgreSQL : BEGIN, PostgreSQL : COMMIT

Pour une transaction saine, voici la séquence des limites :

Commande envoyée sur une connexion Limite résultante
Premier BEGIN Ouvre la transaction
Première insertion d’article Appartient à cette transaction
Second BEGIN Affiche un avertissement ; ne crée aucune limite interne
Seconde insertion d’article Appartient à la même transaction
Premier COMMIT Valide les deux insertions et met fin à la transaction
Autre instruction envoyée séparément S’exécute dans sa propre transaction selon le comportement normal de validation automatique
ROLLBACK ultérieur Ne peut pas annuler les insertions déjà validées

Cela explique un échec courant dans les applications : l’appelant ouvre une transaction, puis appelle un utilitaire qui envoie son propre BEGIN et son propre COMMIT. Le COMMIT de l’utilitaire met également fin à la transaction de l’appelant. Une exception ultérieure chez l’appelant ne peut pas rétablir l’opération initiale entièrement atomique. Cette conclusion découle des limites de transaction documentées par PostgreSQL. PostgreSQL : Transactions

Reproduire la validation prématurée dans le Terminal de Mac

Vous avez besoin d’une base de données PostgreSQL en cours d’exécution et de la commande psql installée, avec des paramètres de connexion fonctionnels. Ouvrez Terminal et démarrez une nouvelle session avec psql -X. L’option -X ignore les fichiers de démarrage, ce qui rend cette démonstration moins dépendante des personnalisations locales. Exécutez le script suivant dans cette session ; il active explicitement la validation automatique. PostgreSQL : psql

La table est temporaire et ne contient que des titres d’articles synthétiques. ON COMMIT PRESERVE ROWS conserve ses lignes pour les examiner au-delà des limites de transaction ; PostgreSQL supprime la table temporaire à la fin de la session. Cette démonstration porte sur le comportement de l’annulation pendant la session, et non sur le stockage persistant après la déconnexion. PostgreSQL : CREATE TABLE

\set AUTOCOMMIT on

CREATE TEMP TABLE article_boundary_demo (
    id integer PRIMARY KEY,
    title text NOT NULL
) ON COMMIT PRESERVE ROWS;

BEGIN;
INSERT INTO article_boundary_demo VALUES (1, 'Opening draft');

-- Deliberately incorrect attempt to open an inner transaction.
BEGIN;
INSERT INTO article_boundary_demo VALUES (2, 'Middle draft');
COMMIT;

-- The previous COMMIT has already ended the transaction.
INSERT INTO article_boundary_demo VALUES (3, 'Closing draft');
ROLLBACK;

SELECT id, title FROM article_boundary_demo ORDER BY id;

Le résultat attendu contient les trois lignes. Le second BEGIN signale qu’une transaction est déjà en cours. Le COMMIT valide les lignes 1 et 2 ; la ligne 3 est ensuite validée par validation automatique. Le dernier ROLLBACK n’a aucune transaction ouverte à annuler. Ces résultats sont ceux attendus d’après le comportement documenté ; ils ne proviennent pas d’une exécution réelle de la base de données. PostgreSQL : BEGIN, PostgreSQL : Transactions

Conservez cette reproduction dans psql interactif, comme indiqué. La façon dont le client soumet les commandes compte : plusieurs instructions SQL transmises ensemble dans une chaîne psql -c peuvent partager une transaction implicite, sauf si des commandes de transaction explicites les séparent. La documentation recommande d’utiliser des commandes distinctes ou l’entrée standard lorsqu’une exécution séparée est souhaitée. PostgreSQL : psql

Comment les points de sauvegarde préservent-ils la transaction externe ?

Un point de sauvegarde marque une position dans une transaction existante. ROLLBACK TO SAVEPOINT abandonne les changements effectués après cette position tout en préservant les travaux antérieurs. RELEASE SAVEPOINT supprime le marqueur et conserve les changements qui subsistent dans la transaction englobante. Il ne valide pas ces changements de façon indépendante. PostgreSQL : SAVEPOINT, PostgreSQL : RELEASE SAVEPOINT

Exécutez cet exemple complet dans la même session ou dans une autre session psql fraîche. Il crée sa propre table temporaire :

CREATE TEMP TABLE article_savepoint_demo (
    id integer PRIMARY KEY,
    title text NOT NULL
) ON COMMIT PRESERVE ROWS;

BEGIN;
INSERT INTO article_savepoint_demo VALUES (1, 'Opening draft');

SAVEPOINT optional_article;
INSERT INTO article_savepoint_demo VALUES (2, 'Optional draft');
ROLLBACK TO SAVEPOINT optional_article;
RELEASE SAVEPOINT optional_article;

INSERT INTO article_savepoint_demo VALUES (3, 'Closing draft');
COMMIT;

SELECT id, title FROM article_savepoint_demo ORDER BY id;

Les lignes attendues sont 1 et 3. La ligne 2 est abandonnée au point de sauvegarde, tandis que les lignes d’ouverture et de fermeture restent dans une seule transaction jusqu’à son COMMIT final. Remplacer ce dernier COMMIT par ROLLBACK abandonnerait les deux insertions conservées. PostgreSQL : SAVEPOINT, PostgreSQL : Transactions

La durée de vie des points de sauvegarde compte aussi lorsqu’on compose des utilitaires. Une annulation jusqu’à un point de sauvegarde le laisse disponible, mais détruit les points de sauvegarde ultérieurs. La libération d’un point de sauvegarde le supprime, ainsi que tous les points de sauvegarde actifs créés après lui. L’exemple libère explicitement son marqueur après la récupération. PostgreSQL : ROLLBACK TO SAVEPOINT, PostgreSQL : RELEASE SAVEPOINT

Comment les utilitaires node-postgres doivent-ils partager la transaction ?

node-postgres exige que toutes les instructions d’une transaction utilisent la même instance client et ne fournit aucune abstraction automatique d’imbrication des transactions. Transmettez aux utilitaires le client de l’appelant. Si l’application utilise un pool, acquérez un client avec pool.connect(), utilisez ce client pendant toute l’opération, puis libérez-le ; pool.query() peut répartir les requêtes successives entre différents clients. node-postgres : Transactions, node-postgres : Pool

Le programme complet suivant utilise un seul Client direct. Son appelant est responsable de BEGIN, de COMMIT et du ROLLBACK complet. L’utilitaire crée un point de sauvegarde autour d’un lot facultatif de deux lignes. Sa seconde insertion répète volontairement l’identifiant d’article 1, ce qui provoque une erreur d’unicité ; la récupération abandonne tout le lot facultatif, y compris sa première insertion réussie.

Configurer et exécuter l’exemple complet

Utilisez une version actuelle prise en charge de Node.js avec npm installé. Dans un dossier de travail, exécutez npm install pg, puis enregistrez le programme ci-dessous sous le nom article-transactions.mjs. Il utilise votre environnement de connexion PostgreSQL existant, notamment PGHOST, PGPORT, PGDATABASE et PGUSER, lorsqu’ils sont configurés. Ces paramètres doivent désigner une base de données fonctionnelle avant l’exécution de l’exemple. node-postgres : Welcome, node-postgres : Connecting

import pg from 'pg'
const { Client } = pg

async function addOptionalArticles(client) {
  await client.query('SAVEPOINT optional_articles')
  try {
    await client.query(
      'INSERT INTO article_demo (id, title) VALUES ($1, $2)',
      [2, 'Optional draft']
    )
    await client.query(
      'INSERT INTO article_demo (id, title) VALUES ($1, $2)',
      [1, 'Duplicate draft']
    )
  } catch (error) {
    if (error.code !== '23505') throw error
    await client.query('ROLLBACK TO SAVEPOINT optional_articles')
    await client.query('RELEASE SAVEPOINT optional_articles')
    return false
  }
  await client.query('RELEASE SAVEPOINT optional_articles')
  return true
}

async function main() {
  const client = new Client({ connectionTimeoutMillis: 5000 })
  await client.connect()
  try {
    await client.query(`
      CREATE TEMP TABLE article_demo (
        id integer PRIMARY KEY,
        title text NOT NULL
      ) ON COMMIT PRESERVE ROWS
    `)
    await client.query('BEGIN ISOLATION LEVEL READ COMMITTED')
    try {
      await client.query(
        'INSERT INTO article_demo (id, title) VALUES ($1, $2)',
        [1, 'Opening draft']
      )
      const optionalKept = await addOptionalArticles(client)
      console.log('Optional batch kept:', optionalKept)
      await client.query(
        'INSERT INTO article_demo (id, title) VALUES ($1, $2)',
        [3, 'Closing draft']
      )
      await client.query('COMMIT')
    } catch (error) {
      try {
        await client.query('ROLLBACK')
      } catch (rollbackError) {
        throw new AggregateError(
          [error, rollbackError], 'Transaction and rollback failed'
        )
      }
      throw error
    }
    const result = await client.query(
      'SELECT id, title FROM article_demo ORDER BY id'
    )
    console.table(result.rows)
  } finally {
    await client.end()
  }
}

main().catch(error => {
  console.error(error)
  process.exitCode = 1
})

Exécutez node article-transactions.mjs. La sortie attendue indique Optional batch kept: false et affiche les identifiants d’article 1 et 3. Les valeurs utilisent l’interface de paramètres documentée query(text, values). La syntaxe du code JavaScript a été vérifiée avec Node.js ; le scénario de base de données a été examiné à la lumière de la documentation, mais n’a pas été exécuté ici. node-postgres : Queries

Ne récupérer que d’une erreur explicitement acceptée

PostgreSQL attribue 23505 à unique_violation et recommande de vérifier les codes d’erreur plutôt que le texte des messages. Ici, l’acceptation de ce code est une règle illustrative pour le lot synthétique facultatif. Les autres erreurs sont propagées à l’appelant, qui tente d’annuler la transaction entière. L’utilitaire renvoie un résultat explicite afin que le travail facultatif abandonné soit visible pour l’appelant. PostgreSQL : PostgreSQL Error Codes

La seule interception d’une exception JavaScript ne rétablit pas une transaction PostgreSQL en échec. La récupération exige une annulation jusqu’à un point de sauvegarde défini au préalable ou une annulation complète. En particulier, une transaction en état d’échec ne peut pas être récupérée en libérant simplement son point de sauvegarde. PostgreSQL : Transactions, PostgreSQL : RELEASE SAVEPOINT

L’utilitaire suppose qu’une transaction est active et que ce client est utilisé de manière séquentielle. Il ne s’agit pas d’un wrapper général de transactions imbriquées : une abstraction d’imbrication réutilisable devrait aussi gérer explicitement la propriété de la transaction et les noms des points de sauvegarde.

Quelles limites d’isolation subsistent après la correction des limites ?

Les points de sauvegarde permettent une annulation partielle dans la transaction en cours ; ils ne définissent pas une politique d’isolation distincte. Choisissez le niveau d’isolation à la limite externe. PostgreSQL interdit de le modifier après la première requête ou instruction de modification de données de la transaction ; un utilitaire ne peut donc pas renforcer une transaction déjà utilisée en envoyant un autre BEGIN. PostgreSQL : SAVEPOINT, PostgreSQL : SET TRANSACTION

Pour les requêtes ordinaires, retenez les distinctions suivantes :

Niveau d’isolation Limite pertinente
READ COMMITTED Des requêtes successives peuvent voir les changements nouvellement validés par d’autres transactions. Une transaction n’implique pas un instantané fixe unique.
REPEATABLE READ Les requêtes partagent l’instantané établi par la première instruction qui n’est pas une commande de contrôle de transaction, tout en voyant leurs propres changements antérieurs. Des échecs de sérialisation restent possibles.
SERIALIZABLE Les transactions validées avec succès ont un effet cohérent avec un certain ordre sériel. Les applications doivent néanmoins gérer les échecs de sérialisation.
READ UNCOMMITTED PostgreSQL le traite comme READ COMMITTED.

PostgreSQL : Transaction Isolation, PostgreSQL : SET TRANSACTION

Un échec de sérialisation exige de réessayer la transaction complète, y compris les décisions qui ont produit son SQL. Réessayer uniquement le travail du point de sauvegarde de l’utilitaire ne renouvelle pas l’instantané de la transaction externe. PostgreSQL identifie les échecs de sérialisation par le SQLSTATE 40001. Le programme de démonstration propage ces erreurs ; il n’effectue pas de tentatives automatiques. PostgreSQL : Transaction Isolation

Comment choisir la bonne limite pour un utilitaire ?

Suivez cette séquence de décisions lorsque vous examinez un appelant et ses utilitaires d’édition d’articles :

  1. Tout doit réussir ensemble : laissez l’appelant gérer la transaction et transmettez son client aux utilitaires qui ne font qu’exécuter des requêtes.
  2. Un lot peut être abandonné pendant que l’appelant continue : définissez un point de sauvegarde avant ce lot, ne récupérez que des erreurs acceptées et renvoyez explicitement le résultat.
  3. Le lot doit être validé indépendamment : utilisez un client et une transaction distincts. La validation survivra alors à une annulation externe et ne pourra donc pas satisfaire la tâche initiale entièrement atomique.
  4. Une exécution concurrente produit un échec de sérialisation : relancez la transaction complète au lieu de traiter l’utilitaire comme une limite de nouvelle tentative indépendante.

Ces choix découlent de la sémantique des points de sauvegarde de PostgreSQL et de l’exigence de node-postgres selon laquelle une transaction doit rester sur un seul client. PostgreSQL : SAVEPOINT, node-postgres : Transactions, PostgreSQL : Transaction Isolation

Pour la tâche d’édition d’articles présentée ici, conservez un seul responsable de la transaction, transmettez un seul client tout au long de la chaîne d’appels et donnez un point de sauvegarde aux opérations facultatives. Examinez les commandes de transaction de chaque utilitaire : un COMMIT inattendu modifie la limite pour toute la connexion, tandis que la libération d’un point de sauvegarde laisse toujours l’appelant responsable du résultat final.

Sources