ALTER SCHEMA ... OWNER TO never finishes in PostgreSQL
ALTER SCHEMA sales OWNER TO app_owner; runs for minutes with no resultShort 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.
Fix it fast
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;2Match what you see, then jump to its fix
- 1
blocked_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
blocked_by contains the process ID 0, and no session in pg_stat_activity matchesA prepared transaction still holds the lockFix - 3
The statement waits with no end, because no timeout is setNothing limits how long the statement waitsFix - 4
The blockers are sessions in state idle in transaction that belong to an applicationIdle sessions keep transactions open and hold locks for hoursFix
- 1
What does the error look like?
ALTER SCHEMA sales OWNER TO app_owner; runs for minutes with no resultAnother 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
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
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
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
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
Databases
PostgreSQL says must be owner of relation when you alter a table or change its owner
ERROR: must be owner of relation
Only a table's owner or a superuser may alter it, grant on it, or change its owner.
3 causes, each with a fixDatabases
DBeaver cannot run pg_restore: the utility is not found in the client home
Utility 'pg_restore' not found in client home '/usr/lib' (/usr/lib)
DBeaver runs pg_restore from a local PostgreSQL client folder; it does not bundle one.
3 causes, each with a fixDatabases
DBeaver cannot download the PostgreSQL driver from Maven
The PostgreSQL driver files are missing and the download does not complete
DBeaver downloads the PostgreSQL JDBC driver from a Maven repository the first time you connect.
3 causes, each with a fixDatabases
python manage.py migrate finishes but the tables for my app are missing
relation "myapp_book" does not exist
migrate only applies migration files that exist.
3 causes, each with a fixDatabases
Django save fails on PostgreSQL: null value in a column violates a not-null constraint
django.db.utils.IntegrityError: null value in column "price" of relation "shop_product" violates not-null constraint
A model field is NOT NULL in the database unless it has null=True.
3 causes, each with a fixDatabases
Django migration on PostgreSQL stops with 'pending trigger events'
django.db.utils.OperationalError: cannot ALTER TABLE "mytable" because it has pending trigger events
PostgreSQL runs a Django migration in one transaction.
2 causes, each with a fix