pg_cancel_backend() returned true. The queue didn't move.

Ten sessions are stuck. You skip the slow-query hunt, do the hard part properly, and find the one session at the front of the queue — the one holding a lock that everyone else is waiting on.

You press cancel.

Postgres says t. Nothing happens.

Not "nothing happens yet." Nothing happens at all. The session is still there, the locks are still held, and the nine sessions behind it are exactly where they were. The call succeeded and did nothing, which is a worse outcome than failing, because a failure would have told you to try something else.

Cancel cancels a query. That session isn't running one.

pg_cancel_backend(pid) sends SIGINT to a backend and interrupts the query it is currently executing. That's the whole contract. The return value means the signal reached a valid backend — not that any work stopped.

Now look at what the classic jam actually is. A client ran a statement inside a transaction, took its locks, and then went quiet without committing. Postgres calls that state idle in transaction. There is no query running. There is nothing for SIGINT to interrupt.

And the locks? They belong to the transaction, not to the statement that acquired them. Cancelling a query — even a real one — doesn't end the transaction. On a session that isn't running a query, cancel has nothing to grab at either end.

The measurement

Two sessions, one row. Session A updates it inside a transaction and then does nothing. Session B tries to update the same row.

 pid |        state        | wait_event_type | blocked_by |              query
-----+---------------------+-----------------+------------+----------------------------------
  99 | idle in transaction | Client          | {}         | UPDATE t SET v='held' WHERE id=1
 120 | active              | Lock            | {99}       | UPDATE t SET v='B' WHERE id=1;

pid 99 is the head. Fire cancel at it:

SELECT pg_cancel_backend(99);

 cancel_returned
-----------------
 t

Three seconds later:

 pid |        state        | wait_event_type | blocked_by
-----+---------------------+-----------------+------------
  99 | idle in transaction | Client          | {}
 120 | active              | Lock            | {99}

Identical. Same state, same wait, same blocker. Now terminate instead:

SELECT pg_terminate_backend(99);

 terminate_returned
--------------------
 t

 id | v
----+---
  1 | B

Session B's update lands immediately. Same pid, same t, completely different outcome.

The activity list is showing you a fossil

Go back to that first table and look at the query column for pid 99:

UPDATE t SET v='held' WHERE id=1

That session is not running that query. It finished it a while ago. pg_stat_activity.query is the last statement the backend ran, not a live one — and for an idle session it just sits there, looking exactly like work in progress.

This is why the usual instinct fails. You open the activity list, you see a session with a plausible-looking UPDATE, and every visual cue says "here is a query, cancel it." The one column that would have told you the truth is state, which is the column nobody sorts by.

wait_event_type gives it away too, once you know to look: pid 99 says Client. It isn't waiting on a lock or on I/O. It's waiting on you — on the application, to send the next statement. It'll wait all afternoon.

So the button was sitting exactly where it can't work

I found this while testing something else, and the annoying part wasn't the Postgres behaviour. It was the realisation that in my own tool, the cancel button was rendered on every session in the panel — including at the top of a wait-for chain, which is precisely where the head blocker is idle in transaction.

The button was placed with maximum prominence at the one spot where it is guaranteed to be a no-op. It would even report success.

The rule to fix that looks like a one-liner:

@property
def cancellable(self) -> bool:
    """Whether "cancel" has anything to act on. It stops the *running query*
    (pg_cancel_backend / KILL QUERY), so a session that isn't running one —
    idle, or idle in transaction — accepts the signal and changes nothing;
    pg_cancel_backend even returns true."""
    return self.state == "active"

One line, and one line too coarse

Apply that same rule inside a wait-for tree and you break the sessions that were fine.

A blocked session — one of the nine stacked behind the head — is waiting on a lock. A lock wait only ever happens part-way through a statement: the backend started executing, reached the point where it needed the lock, and stopped there. There is a real, live query to cancel. Cancel works on it.

But in the tree, that node's state is empty. The row it came from is a lock-wait row, which describes what this session is waiting for — the mode, the object, the duration. Only the blocker side of the join carries session state. So state == "active" is false for every victim, and the naive rule disables cancel on precisely the sessions where it works.

The fix is to read the fact that's actually present:

@property
def cancellable(self) -> bool:
    """Same rule as Activity.cancellable, and it bites hardest here: the
    classic head blocker is idle in transaction, where cancel is a no-op.

    A node that carries wait fields is blocked *on a lock*, which only
    happens part-way through a statement — so it has a running query to
    cancel even though `state` is unset for it."""
    if self.lock_mode is not None:
        return True
    return self.state == "active"

If it's waiting for a lock, it's mid-statement. That's not an inference about state — it's what a lock wait is.

Don't hide it, and don't wait until afterwards

Two tempting designs, both worse:

Hide the button. Now the head blocker is the only row with no controls, and the operator wonders whether the tool is broken.

Let them press it and show an error. There is no error. Postgres returns t. You'd have to invent a failure that didn't happen.

What's left is to render it disabled, in place, carrying its own reason:

This session is not running a query, so there is nothing to cancel — it would report success and release nothing. Only kill ends its transaction.

The control stays where the eye expects it, and it explains itself at the moment of the mistake instead of after it.

MySQL has the same split

KILL QUERY <id> stops the running statement; KILL <id> (or KILL CONNECTION) drops the connection and rolls the transaction back. Same distinction, same trap — a connection sitting in Sleep with an open transaction has no statement for KILL QUERY to take.

The state names differ, so normalise once: a session running a statement is reported as active on both sides (MySQL's COMMAND='Query' maps onto it). After that, one rule reads correctly on either engine.

The honest part

Terminate isn't free, and I'm not selling it as the answer. You are throwing away a transaction that someone — some service, some human with a psql window — believes is still open. The application gets a dropped connection, and how gracefully it handles that is not your call to make from a monitoring panel.

Cancel is still the right tool for the case it was built for: a genuinely long-running active query you want to stop without killing the session. Nothing here says otherwise.

And the real fix is upstream of both buttons. A session that goes idle in transaction for four minutes is an application bug — a transaction opened around a network call, a with block that isn't there, a connection returned to the pool without a rollback. idle_in_transaction_session_timeout will end them for you on a schedule, which beats noticing by hand at 2am.

But when you are noticing by hand at 2am, it matters a great deal that the button you press does something. t is not the same as done.

---

This is one piece of cli2ui — a local-only web UI over the psql commands you keep half-remembering. No AI, no SaaS. It's MIT-licensed on GitHub. What's the longest idle in transaction you've caught in production?