15

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.

4
  • 1
    Should be fine to cancel the ALTER TABLE. PostgreSQL has transactional DDL, and you should be left in the same state as if you had not run the ALTER TABLE at all. Feb 6, 2015 at 19:07
  • So when you say that PostgreSQL has a transaction DDL, does that mean that any schema-changing query is essentially ran inside a transaction?
    – JMTyler
    Feb 6, 2015 at 19:08
  • 1
    In your case, the ALTER TABLE is "essentially ran inside a transaction", since you said "We did not wrap the ALTER TABLE in a transaction.". If you wanted to though, you could write BEGIN; ALTER TABLE foo ... ; ALTER TABLE bar ... ; etc. ; COMMIT; -- that's the real killer feature of PostgreSQL having transactional DDL. But for your immediate situation, yes, the ALTER TABLE on its own can be safely canceled and will be rolled-back as if it never happened. Feb 6, 2015 at 19:11
  • Thank you so much for your quick replies! This is very good information. Could you post it as an answer so I can mark it as accepted?
    – JMTyler
    Feb 6, 2015 at 19:13

2 Answers 2

20

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.

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.

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?

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() and pg_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.

BEGIN;
ALTER TABLE foo ...;
ALTER TABLE bar ...;
-- more stuff
COMMIT; -- or ROLLBACK; if you've changed your mind

(awesome for making sure that schema migrations go in either all-together or not at all.) You said, though:

We did not wrap the ALTER TABLE in a transaction.

That's fine for a single command -- from the docs,

PostgreSQL actually treats every SQL statement as being executed within a transaction. If you do not issue a BEGIN command, then each individual statement has an implicit BEGIN and (if successful) COMMIT wrapped around it. A group of statements surrounded by BEGIN and COMMIT is sometimes called a transaction block.

So canceling that ALTER TABLE, either through pg_cancel_backend() or a Ctrl-C issued from the controlling psql prompt, will have a similar effect as if you had done

BEGIN;
ALTER TABLE ... ;
ROLLBACK;

(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 to ROLLBACK anyway.)

7

To elaborate on Josh's correct and excellent answer:

Is it safe for us to outright cancel our ALTER TABLE query?

Yes.

It would be safe even if it was in the middle of rewriting the table.

If you wanted to you could just shut down the whole PostgreSQL server, or in fact the machine it runs on, restart it, and everything would be fine. DDL in PostgreSQL is transactional and crash safe.

DDL operations are logged through WAL and it's guaranteed that they can either be rolled back or completed upon recovery after a crash or abort.

3
  • 5
    Just a note about "you could just shut down the whole PostgreSQL server, or in fact the machine it runs on, restart it, and everything would be fine" -- quite true as long as you have reliable hardware which does not lie about fsync, wiki.postgresql.org/wiki/Reliable_Writes Feb 7, 2015 at 17:12
  • 2
    @JoshKupershmidt Sure, but that's not specific to DDL. If you have sync issues then you're crash-unsafe for everything. Feb 7, 2015 at 23:04
  • So I guess it writes a copy of the table, and just does an exclusive lock on it so that it can be sure to "copy all rows", nice!
    – rogerdpack
    Sep 2, 2020 at 15:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.