An open read-only PostgreSQL transaction can block DROP TABLE because its SELECT holds an ACCESS SHARE table lock until the transaction ends. The drop needs a conflicting ACCESS EXCLUSIVE lock, so it waits—even after the query returns. PostgreSQL: Explicit Locking

This guide shows how to reproduce that wait from a Mac, identify the blocking session, and close the transaction correctly in node-postgres. It uses only synthetic article rows. The SQL targets PostgreSQL 18, the version served by the official current documentation retrieved for this article; consult your server’s matching manual before applying version-specific settings.

What does read-only mean for PostgreSQL locks?

READ ONLY restricts which commands a transaction may execute. For example, it disallows modifications to ordinary tables and commands such as DROP TABLE. It does not exempt reads from table locking. The reader and the session attempting the drop are separate transactions in this scenario. PostgreSQL: SET TRANSACTION

A plain SELECT automatically acquires ACCESS SHARE on referenced tables. DROP TABLE requires ACCESS EXCLUSIVE, which conflicts with that lock. Locks normally last until transaction end; a documented exception is rolling back to a savepoint established before a lock was acquired. The example below uses no savepoints. PostgreSQL: Explicit Locking

The key distinction is query completion versus transaction completion. Seeing article rows in your terminal tells you the query returned. Check whether the surrounding transaction has also ended. In psql, autocommit is normally enabled, but an explicit BEGIN keeps the transaction open until you finish it. PostgreSQL: psql

How can you reproduce the blocked drop on a Mac?

Use an existing local PostgreSQL 18 test database, with psql already available in your Mac’s Terminal. Open three terminal tabs connected to that same database. Call them A, B, and C. These must be separate connections; separate editor panes do not establish that by themselves.

The following connection command assumes your local test database is named postgres. Substitute your existing test database name if necessary. Run it in each tab. The -X option avoids loading a personal psql startup file. PostgreSQL: psql

psql -X -d postgres

In each connection, enable autocommit explicitly and check the server version. The blocks below are an ordered walkthrough: later blocks depend on the setup and session state established earlier.

\set AUTOCOMMIT on
SHOW server_version;

Create the synthetic article table

In tab C, run the setup once. Use an unused table name: if public.article_lock_demo already exists, stop and choose another name consistently throughout the examples. The setup intentionally does not remove an existing table.

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

Leave the reader transaction open

In tab A, run this block and leave the connection at its prompt. Do not commit yet.

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

Expect transaction_read_only to show on, followed by the two synthetic rows. The read has completed, but the explicit transaction remains open.

Attempt the drop from another connection

In tab B, run the following block. Submit the DROP TABLE as the final statement, without appending another command.

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

The drop should wait while A remains open. The illustrative 60-second limit gives you time to inspect the wait; it is not a recommended universal setting. lock_timeout aborts a statement that waits too long to acquire a lock, and applies separately to each lock acquisition attempt. PostgreSQL: Client Connection Defaults

Keep B’s explicit transaction open after the drop returns. You will roll it back later, preserving the demonstration table even if the drop succeeds. DROP TABLE removes the table and its associated indexes and constraints when its transaction is committed. PostgreSQL: DROP TABLE

How do you prove which session is blocking the drop?

While B is waiting, run the next query in C. It identifies the two demonstration connections and asks PostgreSQL which sessions are blocking each connection’s lock request. pg_blocking_pids() returns an array of blocking process IDs, or an empty array when the process is not blocked. PostgreSQL: System Information Functions and Operators

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;

Expect A to show idle in transaction: it has an open transaction but is not executing a query. B can show active while waiting, with a Lock wait event type. PostgreSQL documents execution state and wait events as independent, so active alone does not establish that a command is making progress. PostgreSQL: The Cumulative Statistics System

Next, inspect the actual table lock requests. Run this in C before ending A’s transaction.

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;

The expected pattern below is illustrative, not captured execution output. In pg_locks, granted = true means the process holds the lock; false means it is waiting for it. PostgreSQL: pg_locks

Session Lock mode granted Interpretation
article_reader_demo AccessShareLock true Reader holds its table lock
article_drop_demo AccessExclusiveLock false Drop awaits its table lock

Use the two queries together as a diagnostic sequence: first establish the blocking relationship, then inspect the relevant table’s held and requested locks. A session being idle is insufficient evidence by itself; the relationship and lock rows connect it to this particular drop.

If B has already timed out, its waiting lock request will no longer appear. Run ROLLBACK; in B, repeat B’s transaction block, and inspect promptly while A is still open.

How do you release the reader and finish the demonstration?

In A, end the read-only transaction:

COMMIT;

If B is still waiting and A was its only blocker, the drop can now complete. In B, immediately undo that demonstration drop so the synthetic table remains available:

ROLLBACK;

If B timed out before you committed A, run ROLLBACK; in B first. After A has ended, repeat B’s block and then run ROLLBACK; again once the drop succeeds. This handles both possible outcomes without leaving B’s transaction unfinished.

The following decision aid helps distinguish a transaction-lifetime issue from a different explanation. The activity labels follow PostgreSQL’s documented session states. PostgreSQL: The Cumulative Statistics System

Observation Next action
A is idle in transaction, and B’s blocker array contains A’s PID End A’s transaction from A’s connection
A has ended, but B still reports blocking PIDs Inspect the remaining blockers
B reports a lock timeout Roll back B’s explicit transaction before retrying
B returns a dependency error Inspect dependencies rather than treating it as a lock wait
B returns immediately Check whether the intended reader transaction was still open

IF EXISTS handles a missing table, while CASCADE controls removal of dependent objects. Neither is a lock-wait option. Keep the default dependency behavior for this exercise; the official DROP TABLE syntax has no CONCURRENTLY variant. PostgreSQL: DROP TABLE

How should node-postgres close a read-only transaction?

With node-postgres, use one checked-out client for BEGIN, every statement in the transaction, and its final COMMIT or ROLLBACK. The official transaction documentation explicitly warns against using pool.query() for a transaction because statements can be dispatched to different clients. node-postgres: Transactions

Treat transaction completion and client release as two separate application steps. client.release() returns a checked-out client to its pool; finish the transaction before that call. If rollback fails, the pool API supports destroying the client by passing a truthy argument to release(). node-postgres: Pool API

For this complete program, keep the synthetic table from the walkthrough. Use an installed Node.js runtime that supports ES modules and AggregateError, and install pg in a working project directory:

npm install pg

Save the following as read-articles.mjs. Configure its connection to the same local test database used by psql. node-postgres supports the PostgreSQL connection environment variables, including PGDATABASE. node-postgres: Connecting

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

The program commits before displaying the rows, rolls back on failure, and releases the client in either case. Its JavaScript syntax was checked with Node.js; the database walkthrough was inspected against the documentation but was not executed here because PostgreSQL was unavailable in the research environment.

For a single independent read, pool.query() is the documented convenience path and avoids manually checking out a client. Choose an explicit transaction when your task requires one, and keep unrelated file processing or desktop interaction outside its lifetime. node-postgres: Pool API

Which timeout addresses an unfinished reader?

Use timeouts according to what is waiting. lock_timeout bounds the drop’s lock acquisition wait. statement_timeout bounds statement execution, so it does not close a transaction simply because the application stops sending commands after a successful read. idle_in_transaction_session_timeout specifically terminates sessions left idle inside an open transaction. These are documented PostgreSQL 18 settings; choose durations for your application’s expected work. PostgreSQL: Client Connection Defaults

For this task, the decisive check is whether the reader’s transaction has ended. Confirm the blocking PID, inspect the table locks, and finish the transaction on its owning connection. In node-postgres, make that final transaction command part of the normal success and error paths so a completed read does not leave the next table drop waiting.

Sources