postgres locks release - ghdrako/doc_snipets GitHub Wiki

-- gets you the PID
SELECT pid, relname FROM pg_locks l JOIN pg_class t ON l.relation = t.oid AND t.relkind = 'r' WHERE t.relname = 'YOUR_TABLE_NAME'
-- cancel the running query
SELECT pg_cancel_backend(PID);
-- if cancel not work end the process and its associated database connection
SELECT pg_terminate_backend(YOUR_PID_FROM_PREVIOUS_QUERY);

If PID is null:

Check if PostgreSQL has a pending prepared transaction that's never been committed or rolled back:

SELECT database, gid FROM pg_prepared_xacts;

If you get a result, then for each transaction gid you must execute a ROLLBACK from the database having the problem:

ROLLBACK PREPARED 'the_gid';

For further information, click here.

Could not drop table. Could not reboot the database so tested a few things until this sequence worked :

truncate table foo;
drop index concurrently foo_something; times 4-5x
alter table foo drop column whatever_foreign_key; times 3x
alter table foo drop column id;
drop table foo;

You can log any time your query is waiting on a lock by turning on log_lock_waits. Lock_waits in your logs can be a good indicator that processes are being contentious. There is virtually no overhead on enabling this and it’s very safe for production databases. This is set to “on” by default on Crunchy Bridge clusters:

log_lock_waits = on

Set a lock_timeout

We generally recommend clients set a lock_timeout within a session so that it will cancel the transaction and relinquish any locks it was holding after a certain period of time. This helps to prevent other processes from getting caught up behind them in a chain.

-- Option 3: Database level (applies to all connections)
ALTER SYSTEM SET lock_timeout = '10s';

-- Option 1: Session level (temporary)
SET lock_timeout = '5s';

-- Option 2: Role level (recommended - persistent across sessions)
CREATE ROLE ddl_user WITH LOGIN PASSWORD 'secure_password';
ALTER ROLE ddl_user SET lock_timeout = '5s';

Retry logic

-- Example DDL script with retry logic
DO $$
DECLARE
    max_attempts INTEGER := 10;
    attempt INTEGER := 1;
    success BOOLEAN := FALSE;
BEGIN
    WHILE attempt <= max_attempts AND NOT success LOOP
        BEGIN
            SET lock_timeout = '2s';
            ALTER TABLE users ALTER COLUMN description TYPE text;
            success := TRUE;
            RAISE NOTICE 'DDL succeeded on attempt %', attempt;
        EXCEPTION
            WHEN lock_not_available THEN
                RAISE NOTICE 'Attempt % failed, retrying in 30 seconds...', attempt;
                PERFORM pg_sleep(30);
                attempt := attempt + 1;
        END;
    END LOOP;

    IF NOT success THEN
        RAISE EXCEPTION 'DDL failed after % attempts', max_attempts;
    END IF;
END $$;

Find the source of the lock

WITH sos AS (
	SELECT array_cat(array_agg(pid),
           array_agg((pg_blocking_pids(pid))[array_length(pg_blocking_pids(pid),1)])) pids
	FROM pg_locks
	WHERE NOT granted
)
SELECT a.pid, a.usename, a.datname, a.state,
	   a.wait_event_type || ': ' || a.wait_event AS wait_event,
       current_timestamp-a.state_change time_in_state,
       current_timestamp-a.xact_start time_in_xact,
       l.relation::regclass relname,
       l.locktype, l.mode, l.page, l.tuple,
       pg_blocking_pids(l.pid) blocking_pids,
       (pg_blocking_pids(l.pid))[array_length(pg_blocking_pids(l.pid),1)] last_session,
       coalesce((pg_blocking_pids(l.pid))[1]||'.'||coalesce(case when locktype='transactionid' then 1 else array_length(pg_blocking_pids(l.pid),1)+1 end,0),a.pid||'.0') lock_depth,
       a.query
FROM pg_stat_activity a
     JOIN sos s on (a.pid = any(s.pids))
     LEFT OUTER JOIN pg_locks l on (a.pid = l.pid and not l.granted)
ORDER BY lock_depth;

postgres locks release - ghdrako/doc_snipets GitHub Wiki

proactive locks manage

Logging lock_waits

Set a lock_timeout

Retry logic