Maaz Zindani

Loading the index…

↑↓ move↵ openesc close4072 pages
Databases and Redis

ALTER SCHEMA ... OWNER TO never finishes in PostgreSQL

ErrorALTER SCHEMA sales OWNER TO app_owner; runs for minutes with no result

Short answer

A statement that hangs instead of failing is usually waiting for a lock that another session holds. Find the session with pg_blocking_pids, end its transaction (often an idle one left open, or a forgotten prepared transaction), and set lock_timeout so the next attempt fails fast instead of waiting.

  • Applies to PostgreSQL 16, PostgreSQL 17, PostgreSQL 18
  • Updated
  • By Maaz Zindani, Senior Software Engineer

Fix it fast

  1. 1Find out which cause you have

    From a second session, list sessions that are waiting and the process IDs that block them.

    SELECT pid, pg_blocking_pids(pid) AS blocked_by, state, query
    FROM pg_stat_activity
    WHERE cardinality(pg_blocking_pids(pid)) > 0;
  2. 2Match what you see, then jump to its fix

    1. 1blocked_by lists one or more PIDs, and those sessions show state idle in transaction or a long-running queryAnother session holds a conflicting lock inside an open transactionFix
    2. 2blocked_by contains the process ID 0, and no session in pg_stat_activity matchesA prepared transaction still holds the lockFix
    3. 3The statement waits with no end, because no timeout is setNothing limits how long the statement waitsFix
    4. 4The blockers are sessions in state idle in transaction that belong to an applicationIdle sessions keep transactions open and hold locks for hoursFix

What does the error look like?

  • ALTER SCHEMA sales OWNER TO app_owner; runs for minutes with no result
  • Another session shows pg_blocking_pids(pid) with a non-empty list for the waiting backend

Cause 1 of 4

Another session holds a conflicting lock inside an open transaction

1Check

Look at what each blocking PID is doing and since when.

SELECT pid, usename, state, xact_start, query
FROM pg_stat_activity
WHERE pid = 12345;

2Fix

Ask the owner of that session to commit or roll back. If you must clear it yourself, cancel the current query first; terminate the whole session only if that is not enough. You need to be a superuser, a member of the role, or hold pg_signal_backend.

SELECT pg_cancel_backend(12345);
SELECT pg_terminate_backend(12345);

3Verify

Your ALTER SCHEMA returns, and the blocker query returns no rows.

SELECT nspname, pg_get_userbyid(nspowner) FROM pg_namespace WHERE nspname = 'sales';

Confirmed by postgresql.org

Did this fix it?

Cause 2 of 4

A prepared transaction still holds the lock

1Check

The manual says a prepared transaction that holds a conflicting lock is shown with process ID 0.

SELECT gid, prepared, owner, database FROM pg_prepared_xacts;

2Fix

Roll back the prepared transaction, or commit it if its owner confirms it should go through. Use the gid from the view. The owner or a superuser can do it, from any session.

ROLLBACK PREPARED 'the-gid-from-the-view';

3Verify

The view is empty and the blocked statement finishes.

SELECT count(*) FROM pg_prepared_xacts;

Confirmed by postgresql.org

Did this fix it?

Cause 3 of 4

Nothing limits how long the statement waits

1Check

lock_timeout is 0 by default, which means a statement waits for a lock without limit.

SHOW lock_timeout;

2Fix

Set a short lock_timeout in the session that runs the change, not in postgresql.conf. The manual advises against the global setting, because it would affect every session. If the blocker does not leave in time, the statement fails and you can retry in a quiet moment.

SET lock_timeout = '2s';
ALTER SCHEMA sales OWNER TO app_owner;

3Verify

A blocked attempt now ends with an error after the timeout, instead of hanging.

SHOW lock_timeout;

Confirmed by postgresql.org

Did this fix it?

Cause 4 of 4

Idle sessions keep transactions open and hold locks for hours

1Check

A connection that opened a transaction and then went quiet holds its locks until it ends. idle_in_transaction_session_timeout ends such sessions after a set time; it is 0 (disabled) by default.

SHOW idle_in_transaction_session_timeout;

2Fix

Set it for the application's role or sessions, so a forgotten transaction cannot hold locks for long. Fix the application code that leaves transactions open too.

SET idle_in_transaction_session_timeout = '60s';

3Verify

Idle open transactions disappear from pg_stat_activity after the timeout.

SELECT pid, state, xact_start FROM pg_stat_activity WHERE state = 'idle in transaction';

Confirmed by postgresql.org

Did this fix it?

How do I stop it coming back?

  • Run schema changes with lock_timeout set, in a quiet period, and retry instead of waiting.
  • Make the application commit or roll back every transaction; do not leave one open while waiting for user input.
  • Check pg_prepared_xacts after crashes of anything that uses two-phase commit.

Sources

How was this page made?

I drafted this page with AI assistance from the official documents listed under Sources. Every cause above names the document that confirms it. I have not reproduced this fix on a machine of my own yet, so read it as documented guidance, not a test report. Found a mistake? Tell me.

Which stack is behind this page?

Related fixes

Next step

Want this handled for good?

I look after servers, apps and releases end to end. Tell me what keeps breaking.