Een open alleen-lezen PostgreSQL-transactie kan DROP TABLE blokkeren omdat de SELECT een tabelvergrendeling van het type ACCESS SHARE vasthoudt totdat de transactie eindigt. Voor het verwijderen is een conflicterende vergrendeling van het type ACCESS EXCLUSIVE nodig, dus moet de opdracht wachten, zelfs nadat de query is teruggekeerd. PostgreSQL: expliciete vergrendeling

Deze handleiding laat zien hoe je dit wachten vanaf een Mac reproduceert, de blokkerende sessie opspoort en de transactie correct afsluit in node-postgres. Er worden alleen synthetische artikelrijen gebruikt. De SQL is gericht op PostgreSQL 18, de versie die wordt gebruikt in de officiële, actuele documentatie die voor dit artikel is geraadpleegd; raadpleeg de bij jouw serverversie horende handleiding voordat je versiespecifieke instellingen toepast.

Wat betekent alleen-lezen voor PostgreSQL-vergrendelingen?

READ ONLY beperkt welke opdrachten een transactie mag uitvoeren. Zo zijn wijzigingen aan gewone tabellen en opdrachten zoals DROP TABLE niet toegestaan. Het vrijwaart leesopdrachten niet van tabelvergrendelingen. In dit scenario zijn de lezer en de sessie die de tabel probeert te verwijderen afzonderlijke transacties. PostgreSQL: SET TRANSACTION

Een gewone SELECT verkrijgt automatisch een ACCESS SHARE-vergrendeling op de tabellen waarnaar wordt verwezen. Voor DROP TABLE is ACCESS EXCLUSIVE vereist, wat met die vergrendeling conflicteert. Vergrendelingen blijven normaal gesproken bestaan totdat de transactie eindigt. Een gedocumenteerde uitzondering is terugrollen naar een savepoint dat is ingesteld voordat een vergrendeling werd verkregen. In het onderstaande voorbeeld worden geen savepoints gebruikt. PostgreSQL: expliciete vergrendeling

Het belangrijkste onderscheid is dat tussen het voltooien van een query en het voltooien van een transactie. Als je artikelrijen in je terminal ziet, weet je dat de query is teruggekeerd. Controleer of de omringende transactie ook is beëindigd. In psql is autocommit normaal gesproken ingeschakeld, maar een expliciete BEGIN houdt de transactie open totdat je deze afsluit. PostgreSQL: psql

Hoe reproduceer je de geblokkeerde tabelverwijdering op een Mac?

Gebruik een bestaande lokale PostgreSQL 18-testdatabase, met psql al beschikbaar in Terminal op je Mac. Open drie terminaltabbladen die met dezelfde database zijn verbonden. Noem ze A, B en C. Dit moeten afzonderlijke verbindingen zijn; afzonderlijke deelvensters in een editor zorgen daar op zichzelf niet voor.

Bij de volgende verbindingsopdracht wordt ervan uitgegaan dat je lokale testdatabase postgres heet. Vervang dit indien nodig door de naam van je bestaande testdatabase. Voer de opdracht in elk tabblad uit. De optie -X voorkomt dat een persoonlijk opstartbestand psql wordt geladen. PostgreSQL: psql

psql -X -d postgres

Schakel in elke verbinding autocommit expliciet in en controleer de serverversie. De onderstaande blokken vormen een stappenplan in de juiste volgorde: latere blokken zijn afhankelijk van de instellingen en sessiestatus die eerder zijn vastgelegd.

\set AUTOCOMMIT on
SHOW server_version;

Maak de synthetische artikeltabel aan

Voer in tabblad C de setup eenmaal uit. Gebruik een ongebruikte tabelnaam: als public.article_lock_demo al bestaat, stop dan en kies een andere naam die je consequent in alle voorbeelden gebruikt. De setup verwijdert met opzet geen bestaande tabel.

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');

Laat de lezertransactie open

Voer dit blok uit in tabblad A en laat de verbinding daarna bij de prompt staan. Voer nog geen commit uit.

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

Naar verwachting toont transaction_read_only on, gevolgd door de twee synthetische rijen. Het lezen is voltooid, maar de expliciete transactie blijft open.

Probeer de tabel vanuit een andere verbinding te verwijderen

Voer in tabblad B het volgende blok uit. Dien DROP TABLE in als laatste statement, zonder er nog een opdracht aan toe te voegen.

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

De opdracht om de tabel te verwijderen hoort te wachten zolang A open blijft. De illustratieve limiet van 60 seconden geeft je tijd om het wachten te inspecteren; dit is geen universeel aanbevolen instelling. lock_timeout breekt een statement af als het te lang moet wachten om een vergrendeling te verkrijgen. De instelling geldt afzonderlijk voor elke poging om een vergrendeling te verkrijgen. PostgreSQL: standaardinstellingen voor clientverbindingen

Laat de expliciete transactie van B open nadat de opdracht voor het verwijderen is teruggekeerd. Je rolt deze later terug, zodat de demonstratietabel behouden blijft, zelfs als het verwijderen slaagt. Wanneer de transactie waarin DROP TABLE wordt uitgevoerd wordt vastgelegd, worden de tabel en de bijbehorende indexen en beperkingen verwijderd. PostgreSQL: DROP TABLE

Hoe bewijs je welke sessie de tabelverwijdering blokkeert?

Voer de volgende query in C uit terwijl B wacht. De query identificeert de twee demonstratieverbindingen en vraagt PostgreSQL welke sessies de vergrendelingsaanvraag van elke verbinding blokkeren. pg_blocking_pids() retourneert een array met blokkerende proces-ID's, of een lege array als het proces niet wordt geblokkeerd. PostgreSQL: systeeminformatiefuncties en -operatoren

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;

Naar verwachting heeft A de status idle in transaction: er staat een transactie open, maar er wordt geen query uitgevoerd. B kan tijdens het wachten de status active tonen, met het wachtgebeurtenistype Lock. PostgreSQL documenteert de uitvoeringsstatus en wachtgebeurtenissen als onafhankelijk van elkaar. Alleen active toont dus niet aan dat een opdracht voortgang boekt. PostgreSQL: het cumulatieve statistiekensysteem

Bekijk vervolgens de daadwerkelijke tabelvergrendelingen die zijn aangevraagd. Voer dit in C uit voordat je de transactie van A beëindigt.

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;

Het verwachte patroon hieronder is illustratief en geen vastgelegde uitvoer. In pg_locks betekent granted = true dat het proces de vergrendeling vasthoudt; false betekent dat het proces erop wacht. PostgreSQL: pg_locks

Sessie Vergrendelingsmodus granted Interpretatie
article_reader_demo AccessShareLock true De lezer houdt de tabelvergrendeling vast
article_drop_demo AccessExclusiveLock false De tabelverwijdering wacht op de tabelvergrendeling

Gebruik de twee queries samen als diagnostische volgorde: stel eerst vast welke sessie blokkeert en inspecteer daarna de vastgehouden en aangevraagde vergrendelingen van de betreffende tabel. Alleen de inactieve status van een sessie is onvoldoende bewijs; de relatie en vergrendelingsrijen koppelen die sessie aan deze specifieke verwijdering.

Als B al een time-out heeft gehad, wordt de wachtende vergrendelingsaanvraag niet meer getoond. Voer ROLLBACK; uit in B, herhaal het transactieblok van B en inspecteer meteen terwijl A nog open is.

Hoe geef je de lezer vrij en rond je de demonstratie af?

Beëindig in A de alleen-lezen transactie:

COMMIT;

Als B nog wacht en A de enige blokkerende sessie was, kan de tabel nu worden verwijderd. Maak die demonstratieverwijdering in B meteen ongedaan, zodat de synthetische tabel beschikbaar blijft:

ROLLBACK;

Als B een time-out had voordat je in A de commit uitvoerde, voer dan eerst ROLLBACK; uit in B. Nadat A is beëindigd, herhaal je het blok van B en voer je daarna opnieuw ROLLBACK; uit zodra het verwijderen is geslaagd. Zo worden beide mogelijke uitkomsten afgehandeld zonder de transactie van B onafgemaakt te laten.

Het volgende beslisschema helpt je een probleem met de transactieduur te onderscheiden van een andere verklaring. De statuslabels voor activiteiten volgen de gedocumenteerde sessiestatussen van PostgreSQL. PostgreSQL: het cumulatieve statistiekensysteem

Waarneming Volgende actie
A heeft de status idle in transaction en de array met blokkerende processen van B bevat de PID van A Beëindig de transactie van A via de verbinding van A
A is beëindigd, maar B meldt nog steeds blokkerende PID's Inspecteer de overige blokkerende sessies
B meldt een time-out bij het verkrijgen van een vergrendeling Rol de expliciete transactie van B terug voordat je het opnieuw probeert
B retourneert een fout over afhankelijkheden Inspecteer de afhankelijkheden in plaats van dit als wachten op een vergrendeling te behandelen
B keert onmiddellijk terug Controleer of de beoogde lezertransactie nog open was

IF EXISTS handelt af dat een tabel ontbreekt, terwijl CASCADE bepaalt hoe afhankelijke objecten worden verwijderd. Geen van beide is een optie voor het wachten op een vergrendeling. Gebruik voor deze oefening het standaardgedrag voor afhankelijkheden; de officiële syntaxis voor DROP TABLE heeft geen variant met CONCURRENTLY. PostgreSQL: DROP TABLE

Hoe moet node-postgres een alleen-lezen transactie afsluiten?

Gebruik met node-postgres één uit de pool geleende client voor BEGIN, elk statement in de transactie en de afsluitende COMMIT of ROLLBACK. De officiële documentatie over transacties waarschuwt nadrukkelijk dat je voor een transactie geen pool.query() moet gebruiken, omdat statements naar verschillende clients kunnen worden verstuurd. node-postgres: transacties

Beschouw het voltooien van de transactie en het vrijgeven van de client als twee afzonderlijke stappen in de applicatie. client.release() geeft een uit de pool geleende client terug aan de pool; voltooi de transactie voordat je die aanroep doet. Als terugrollen mislukt, kan de client met de pool-API worden vernietigd door een waarheidsgetrouwe waarde aan release() door te geven. node-postgres: Pool-API

Behoud voor dit volledige programma de synthetische tabel uit de stappen hierboven. Gebruik een geïnstalleerde Node.js-runtime die ES-modules en AggregateError ondersteunt, en installeer pg in een projectmap waarin je werkt:

npm install pg

Sla het volgende op als read-articles.mjs. Configureer de verbinding met dezelfde lokale testdatabase die door psql wordt gebruikt. node-postgres ondersteunt de omgevingsvariabelen voor PostgreSQL-verbindingen, waaronder PGDATABASE. node-postgres: verbinding maken

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

Het programma voert een commit uit voordat het de rijen toont, rolt terug bij fouten en geeft de client in beide gevallen vrij. De JavaScript-syntaxis is gecontroleerd met Node.js; de databasestappen zijn getoetst aan de documentatie, maar zijn hier niet uitgevoerd omdat PostgreSQL niet beschikbaar was in de onderzoeksomgeving.

Voor één onafhankelijke leesopdracht is pool.query() de gedocumenteerde gemaksroute; je hoeft dan niet handmatig een client uit de pool te lenen. Kies een expliciete transactie wanneer je taak die vereist, en voer niet-gerelateerde bestandsverwerking of interactie met de desktop uit buiten de looptijd ervan. node-postgres: Pool-API

Welke time-out is geschikt voor een lezer die de transactie open laat?

Gebruik time-outs op basis van wat er wacht. lock_timeout begrenst hoe lang de verwijdering wacht op het verkrijgen van een vergrendeling. statement_timeout begrenst de uitvoering van statements; de instelling sluit een transactie dus niet af alleen omdat de applicatie na een geslaagde leesopdracht geen opdrachten meer verstuurt. idle_in_transaction_session_timeout beëindigt specifiek sessies die inactief zijn gebleven binnen een open transactie. Dit zijn gedocumenteerde instellingen van PostgreSQL 18; kies looptijden die passen bij het verwachte werk van je applicatie. PostgreSQL: standaardinstellingen voor clientverbindingen

Voor deze taak is de beslissende controle of de transactie van de lezer is beëindigd. Bevestig de blokkerende PID, inspecteer de tabelvergrendelingen en beëindig de transactie via de verbinding die deze bezit. Neem in node-postgres die laatste transactieopdracht op in de normale paden voor succes en fouten, zodat een voltooide leesopdracht niet zorgt dat de volgende tabelverwijdering blijft wachten.

Bronnen