We started an ALTER TABLE
query hours ago and only recently realized (via pg_stat_activity
) that it is waiting on a lock. We discovered the other query that is holding a lock on the table we want to alter, and not letting it go.
Our query is a "simple" query (changing a column data type), but it is running on a massive table.
Rather than killing the process that is holding onto the lock, we've decided we'd rather kill the ALTER TABLE
.
We did not wrap the ALTER TABLE
in a transaction.
As far as I understand, the fact that our query is waiting for a lock means it has always been waiting for a lock, and it has never changed anything.
Is this true? Is it safe for us to outright cancel our ALTER TABLE
query? Or is it possible that the query has already modified something and cancelling it would leave our database in a halfway state of some kind?
PS: The plan is to cancel it using SELECT pg_cancel_backend(pid);
. If this is a bad idea, please let me know.
Best Answer
Right -- if you see that pg_stat_activity.waiting is "true" for an ALTER TABLE, that almost certainly means that it's patiently waiting for the ACCESS EXCLUSIVE lock on its target table, and its real work (rewriting the table if necessary, changing catalogs, rebuilding indexes, etc.) hasn't started yet.
Canceling queries (or, equivalently, rolling back a transaction) in PostgreSQL doesn't have any database corruption hazards which you might have been spooked by in certain other databases (e.g. the terrifying warning at the bottom of this page). That's why non-superusers are, in recent versions, free to use
pg_cancel_backend()
andpg_terminate_backend()
to kill their own queries running in other backends -- they are safe to use without fretting about database corruption. After all, PostgreSQL has to be prepared to deal with any process getting killed off e.g. SIGKILL from the OOM killer, server shutdown, etc. That's what the WAL log is for.You may have also seen that in PostgreSQL, it's possible to perform most DDL commands nested inside a (multi-statement) transaction, e.g.
(awesome for making sure that schema migrations go in either all-together or not at all.) You said, though:
That's fine for a single command -- from the docs,
So canceling that
ALTER TABLE
, either throughpg_cancel_backend()
or a Ctrl-C issued from the controlling psql prompt, will have a similar effect as if you had done(though as you hopefully got to see, canceling that expensive
ALTER TABLE
can save the database from a lot of unnecessary grinding if you're just going toROLLBACK
anyway.)