The idle in transaction
state shown in the pg_stat_activity
output generally means that there's something that starts a transaction with BEGIN
, does some work, and then doesn't issue the COMMIT
so that the transaction is finished. This has the effect of leaving the ACCESS SHARE
locks acquired by the SELECT
statements hanging around, which block the ALTER TABLE
statement you're trying to run from acquiring the ACCESS EXCLUSIVE
lock it needs to change the type of a column for every row in the table.
They could only be considered "ghost queries", in the sense that they are hanging around waiting for an answer of COMMIT
or ROLLBACK
that will probably never come. But they are problematic, nonetheless as you figured out.
There might be a bug somewhere in your application that has left all these connections open and waiting for a commit. I'd definitely check that out, and see if perhaps a code block doesn't have a terminating COMMIT
.
More commonly (in my experience, YMMV of course), people start database sessions with a BEGIN
, do some work, and then go to lunch and never close their session.
It's best to fix the problem at the source, but if you absolutely, positively need to do something right now, pg_cancel_backend
and pg_terminate_backend
are not bad ways to go.
Commenting on your answer, I'd specifically select the state of idle in transaction
, rather than LIKE '%idle%'
because those are the ones causing your problems. The idle
state backends are just waiting around patiently for new client commands, and had last ran a ROLLBACK
command.
You can look at the Explicit Locking documentation for more information about what blocks what.
The description of idle-in-transaction
is in the Unix Monitoring Tools section for reference.
pg_cancel_backend
and pg_stat_activity
issue SIGINT
and SIGTERM
signals, respectively, and the effects of those signals (and warnings about other signals) are documented in the Server Shutdown section.
Hope that helps explain what you saw and sheds some light on your mystery. =)
You have almost 40000 rows where word='Facebook*'
. I suppose that these rows are somewhat randomly scattered on the whole table. This means that Postgres has to read tens of thousands of random blocks from a disk, which might be slow.
You can try to cluster rows that have the same word together. You can use the command cluster misspelled using test_scanid;
. This would take some time and block the table until done, so plan accordingly.
Best Answer
Check the wait events in
pg_stat_activity
to see if your query is running or hanging.In the latter case, examine
pg_locks
or use thepg_blocking_pids
function to see which session blocks you.If no locks are involved, see if the backend process consumes CPU or not. Optionally use
strace
to see what the backend is doing.