Une transaction PostgreSQL ouverte en lecture seule peut bloquer DROP TABLE, car son SELECT conserve un verrou de table ACCESS SHARE jusqu’à la fin de la transaction. La suppression nécessite un verrou conflictuel ACCESS EXCLUSIVE et attend donc, même après le retour de la requête. PostgreSQL : verrouillage explicite

Ce guide montre comment reproduire cette attente depuis un Mac, identifier la session qui bloque et fermer correctement la transaction avec node-postgres. Il n’utilise que des lignes d’article synthétiques. Les instructions SQL ciblent PostgreSQL 18, la version utilisée par la documentation officielle actuelle consultée pour cet article ; consultez le manuel correspondant à la version de votre serveur avant d’appliquer des paramètres propres à une version.

Que signifie « lecture seule » pour les verrous PostgreSQL ?

READ ONLY restreint les commandes qu’une transaction peut exécuter. Par exemple, cette option interdit les modifications des tables ordinaires et des commandes telles que DROP TABLE. Elle ne dispense pas les lectures du verrouillage des tables. Dans ce scénario, la transaction qui lit et la session qui tente la suppression sont distinctes. PostgreSQL : SET TRANSACTION

Un SELECT ordinaire acquiert automatiquement un verrou ACCESS SHARE sur les tables référencées. DROP TABLE exige le verrou ACCESS EXCLUSIVE, qui entre en conflit avec ce verrou. Les verrous sont normalement conservés jusqu’à la fin de la transaction ; une exception documentée concerne le retour à un point de sauvegarde établi avant l’acquisition d’un verrou. L’exemple ci-dessous n’utilise aucun point de sauvegarde. PostgreSQL : verrouillage explicite

Il faut distinguer la fin de la requête de la fin de la transaction. Voir les lignes d’article dans votre terminal indique que la requête a renvoyé son résultat. Vérifiez si la transaction qui l’entoure est également terminée. Dans psql, la validation automatique est normalement activée, mais un BEGIN explicite maintient la transaction ouverte jusqu’à sa clôture. PostgreSQL : psql

Comment reproduire le blocage de la suppression sur un Mac ?

Utilisez une base de données PostgreSQL 18 locale de test existante, avec psql déjà disponible dans le Terminal de votre Mac. Ouvrez trois onglets de terminal connectés à cette même base de données. Nommez-les A, B et C. Il doit s’agir de connexions distinctes ; de simples volets séparés dans un éditeur ne suffisent pas à les établir.

La commande de connexion suivante suppose que votre base locale de test se nomme postgres. Remplacez ce nom si nécessaire par celui de votre base de test existante. Exécutez la commande dans chaque onglet. L’option -X évite de charger un fichier de démarrage personnel psql. PostgreSQL : psql

psql -X -d postgres

Dans chaque connexion, activez explicitement la validation automatique et vérifiez la version du serveur. Les blocs ci-dessous forment un parcours ordonné : les blocs suivants dépendent de la configuration et de l’état des sessions établis précédemment.

\set AUTOCOMMIT on
SHOW server_version;

Créer la table d’articles synthétiques

Dans l’onglet C, exécutez une seule fois la configuration. Utilisez un nom de table inutilisé : si public.article_lock_demo existe déjà, arrêtez-vous et choisissez un autre nom, à utiliser de manière cohérente dans tous les exemples. La configuration ne supprime volontairement aucune table existante.

CREATE TABLE public.article_lock_demo (
    id integer PRIMARY KEY,
    title text NOT NULL
);

INSERT INTO public.article_lock_demo (id, title)
VALUES
    (1, 'Choosing a notebook'),
    (2, 'Organizing a reading list');

Laisser la transaction de lecture ouverte

Dans l’onglet A, exécutez ce bloc et laissez la connexion à son invite de commande. Ne validez pas encore la transaction.

SET application_name = 'article_reader_demo';
BEGIN READ ONLY;
SHOW transaction_read_only;
SELECT id, title
FROM public.article_lock_demo
ORDER BY id;

Vous devriez voir transaction_read_only afficher on, puis les deux lignes synthétiques. La lecture est terminée, mais la transaction explicite est toujours ouverte.

Tenter la suppression depuis une autre connexion

Dans l’onglet B, exécutez le bloc suivant. Soumettez DROP TABLE comme dernière instruction, sans ajouter de commande après.

SET application_name = 'article_drop_demo';
BEGIN;
SET LOCAL lock_timeout = '60s';
DROP TABLE public.article_lock_demo;

La suppression devrait attendre tant que A reste ouverte. Le délai indicatif de 60 secondes vous laisse le temps d’examiner l’attente ; il ne s’agit pas d’un paramètre universel recommandé. lock_timeout annule une instruction qui attend trop longtemps l’acquisition d’un verrou, et s’applique séparément à chaque tentative d’acquisition. PostgreSQL : paramètres par défaut des connexions clientes

Laissez ouverte la transaction explicite de B après le retour de la suppression. Vous l’annulerez plus tard, afin de préserver la table de démonstration même si la suppression réussit. DROP TABLE supprime la table ainsi que ses index et contraintes associés lorsque sa transaction est validée. PostgreSQL : DROP TABLE

Comment déterminer quelle session bloque la suppression ?

Pendant que B attend, exécutez la requête suivante dans C. Elle identifie les deux connexions de démonstration et demande à PostgreSQL quelles sessions bloquent la demande de verrou de chacune d’elles. pg_blocking_pids() renvoie un tableau d’identifiants de processus qui bloquent, ou un tableau vide si le processus n’est pas bloqué. PostgreSQL : fonctions et opérateurs d’information système

SELECT
    pid,
    application_name,
    state,
    xact_start,
    wait_event_type,
    wait_event,
    pg_blocking_pids(pid) AS blocking_pids,
    query
FROM pg_stat_activity
WHERE datname = current_database()
  AND application_name IN (
      'article_reader_demo',
      'article_drop_demo'
  )
ORDER BY application_name;

A devrait afficher idle in transaction : une transaction est ouverte, mais aucune requête n’est en cours d’exécution. B peut afficher active pendant l’attente, avec un type d’événement d’attente Lock. PostgreSQL précise que l’état d’exécution et les événements d’attente sont indépendants ; active seul ne permet donc pas d’établir qu’une commande progresse. PostgreSQL : système de statistiques cumulatives

Examinez ensuite les demandes de verrou sur la table. Exécutez cette requête dans C avant de mettre fin à la transaction de A.

SELECT
    a.application_name,
    l.pid,
    l.mode,
    l.granted
FROM pg_locks AS l
JOIN pg_stat_activity AS a ON a.pid = l.pid
WHERE l.locktype = 'relation'
  AND l.database = (
      SELECT oid
      FROM pg_database
      WHERE datname = current_database()
  )
  AND l.relation = 'public.article_lock_demo'::regclass
ORDER BY a.application_name, l.mode;

Le résultat attendu ci-dessous est un exemple illustratif, et non une sortie d’exécution capturée. Dans pg_locks, granted = true signifie que le processus détient le verrou ; false signifie qu’il attend de l’obtenir. PostgreSQL : pg_locks

Session Mode de verrou granted Interprétation
article_reader_demo AccessShareLock true La session de lecture détient son verrou sur la table
article_drop_demo AccessExclusiveLock false La suppression attend son verrou sur la table

Utilisez les deux requêtes comme une séquence de diagnostic : établissez d’abord la relation de blocage, puis examinez les verrous détenus et demandés sur la table concernée. Le fait qu’une session soit inactive ne constitue pas une preuve suffisante à lui seul ; la relation et les lignes de verrou permettent de la relier à cette suppression précise.

Si B a déjà expiré, sa demande de verrou en attente n’apparaîtra plus. Exécutez ROLLBACK; dans B, répétez le bloc de transaction de B, puis examinez rapidement la situation pendant que A est toujours ouverte.

Comment libérer la session de lecture et terminer la démonstration ?

Dans A, terminez la transaction en lecture seule :

COMMIT;

Si B attend toujours et que A était son seul bloqueur, la suppression peut maintenant aboutir. Dans B, annulez immédiatement cette suppression de démonstration pour que la table synthétique reste disponible :

ROLLBACK;

Si le délai de B a expiré avant que vous validiez A, exécutez d’abord ROLLBACK; dans B. Une fois A terminée, répétez le bloc de B, puis exécutez à nouveau ROLLBACK; une fois la suppression réussie. Cette procédure couvre les deux résultats possibles sans laisser la transaction de B inachevée.

L’aide à la décision suivante permet de distinguer un problème lié à la durée de vie d’une transaction d’une autre explication. Les états des sessions suivent ceux décrits dans la documentation de PostgreSQL. PostgreSQL : système de statistiques cumulatives

Observation Action suivante
A est idle in transaction et le tableau des bloqueurs de B contient le PID de A Terminez la transaction de A depuis sa connexion
A est terminée, mais B signale encore des PID bloqueurs Examinez les autres bloqueurs
B signale un délai d’attente de verrou dépassé Annulez la transaction explicite de B avant de réessayer
B renvoie une erreur de dépendance Examinez les dépendances plutôt que de conclure à une attente de verrou
B renvoie immédiatement un résultat Vérifiez si la transaction de lecture prévue était toujours ouverte

IF EXISTS gère le cas où la table est absente, tandis que CASCADE contrôle la suppression des objets dépendants. Aucun des deux ne concerne l’attente d’un verrou. Pour cet exercice, conservez le comportement par défaut vis-à-vis des dépendances ; la syntaxe officielle de DROP TABLE ne comporte pas de variante CONCURRENTLY. PostgreSQL : DROP TABLE

Comment node-postgres doit-il fermer une transaction en lecture seule ?

Avec node-postgres, utilisez un seul client emprunté au pool pour BEGIN, toutes les instructions de la transaction et sa commande finale COMMIT ou ROLLBACK. La documentation officielle sur les transactions déconseille explicitement d’utiliser pool.query() pour une transaction, car les instructions pourraient être envoyées à différents clients. node-postgres : transactions

Considérez la fin de la transaction et la libération du client comme deux étapes distinctes de l’application. client.release() rend au pool un client emprunté ; terminez la transaction avant cet appel. Si l’annulation échoue, l’API du pool permet de détruire le client en passant un argument vrai à release(). node-postgres : API du pool

Pour ce programme complet, conservez la table synthétique créée dans le parcours. Utilisez un environnement d’exécution Node.js installé qui prend en charge les modules ES et AggregateError, et installez pg dans un répertoire de projet de travail :

npm install pg

Enregistrez le contenu suivant sous read-articles.mjs. Configurez sa connexion pour utiliser la même base de données locale de test que psql. node-postgres prend en charge les variables d’environnement de connexion PostgreSQL, notamment PGDATABASE. node-postgres : connexion

import pg from 'pg';

const { Pool } = pg;
const pool = new Pool({ connectionTimeoutMillis: 5000 });

async function main() {
  try {
    const client = await pool.connect();
    let discard = false;

    try {
      await client.query('BEGIN READ ONLY');
      const result = await client.query(
        'SELECT id, title FROM public.article_lock_demo ORDER BY id'
      );
      await client.query('COMMIT');
      console.table(result.rows);
    } catch (error) {
      try {
        await client.query('ROLLBACK');
      } catch (rollbackError) {
        discard = true;
        throw new AggregateError(
          [error, rollbackError],
          'Query and rollback failed'
        );
      }
      throw error;
    } finally {
      client.release(discard);
    }
  } finally {
    await pool.end();
  }
}

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

PGDATABASE=postgres node read-articles.mjs

Le programme valide la transaction avant d’afficher les lignes, l’annule en cas d’échec et libère le client dans les deux cas. Sa syntaxe JavaScript a été vérifiée avec Node.js ; le parcours avec la base de données a été confronté à la documentation, mais n’a pas été exécuté ici, car PostgreSQL n’était pas disponible dans l’environnement de recherche.

Pour une lecture indépendante, pool.query() est le raccourci recommandé par la documentation et évite d’emprunter manuellement un client. Choisissez une transaction explicite lorsque votre tâche l’exige, et effectuez tout traitement de fichiers ou toute interaction avec le bureau sans rapport avec celle-ci en dehors de sa durée de vie. node-postgres : API du pool

Quel délai d’expiration convient à une session de lecture qui n’a pas terminé sa transaction ?

Choisissez les délais en fonction de ce qui attend. lock_timeout limite le temps d’attente de la suppression pour acquérir un verrou. statement_timeout limite l’exécution des instructions ; il ne ferme donc pas une transaction simplement parce que l’application cesse d’envoyer des commandes après une lecture réussie. idle_in_transaction_session_timeout met spécifiquement fin aux sessions qui restent inactives au sein d’une transaction ouverte. Il s’agit de paramètres documentés pour PostgreSQL 18 ; choisissez des durées adaptées au travail attendu par votre application. PostgreSQL : paramètres par défaut des connexions clientes

Pour cette tâche, la vérification décisive consiste à savoir si la transaction de lecture est terminée. Confirmez le PID du bloqueur, examinez les verrous de la table et terminez la transaction sur la connexion qui la détient. Avec node-postgres, intégrez cette dernière commande de transaction aux chemins habituels de réussite et d’erreur afin qu’une lecture terminée ne laisse pas la prochaine suppression de table en attente.

Sources