Appliquer un DDL en toute sécurité avec lock_timeout et des tentatives répétées (éviter les files de verrous)

Commande

-- Single attempt: give up if the lock is not acquired within 3 s instead of queueing and blocking traffic
SET lock_timeout = '3s';
ALTER TABLE customer ADD COLUMN IF NOT EXISTS segment text;
RESET lock_timeout;

-- Automatic retry: up to 5 attempts, 3 s apart
DO $$
DECLARE
  attempt int := 0;
BEGIN
  LOOP
    attempt := attempt + 1;
    BEGIN
      SET LOCAL lock_timeout = '2s';
      ALTER TABLE customer ADD COLUMN IF NOT EXISTS segment text;
      RAISE NOTICE 'OK, attempt %', attempt;
      EXIT;
    EXCEPTION WHEN lock_not_available THEN
      RAISE NOTICE 'lock not acquired, attempt %', attempt;
      IF attempt >= 5 THEN RAISE; END IF;
      PERFORM pg_sleep(3);
    END;
  END LOOP;
END $$;

Ce que fait la commande

Empêche un DDL ayant besoin d'ACCESS EXCLUSIVE d'attendre indéfiniment lorsqu'il ne peut pas obtenir le verrou, et de bloquer toutes les requêtes qui s'accumulent derrière lui (lock queue) ; réessaie à intervalles courts.

Quand l'utiliser

Lors de l'application en production, sur des tables très sollicitées, de changements rapides mais fortement verrouillants comme ALTER TABLE, CREATE/DROP TRIGGER, ATTACH PARTITION ou RENAME.

Attention

Sans lock_timeout, un ALTER TABLE placé derrière un seul long SELECT fait aussi attendre toutes les lectures/écritures suivantes et peut épuiser le pool de connexions en quelques minutes. lock_timeout ne limite que l'attente du verrou ; il ne limite pas la durée d'un DDL qui réécrit la table (par ex. changement de type de colonne). Le bloc DO est une transaction unique et conserve un snapshot entre les tentatives ; pour de longs scénarios de tentatives, préférez une boucle shell externe.