I'm trying to use ALTER TABLE on a live db (not in production, just poking around at a dev box and trying to learn some things)

Here is the command I'm trying:

psql -h $IP -p 5432 -U $DBUSER -d $DBNAME \
    -c "alter table request alter column sources set data type text;"

And what happens is... nothing. postgres just sits there silently for many, many minutes, until i give up and cancel it:

^CCancel request sent
ERROR:  canceling statement due to user request

Some reading I've done suggested that this should be fast because it's not adding any onerous constraints like UNIQUE or foreign keys or anything.

There are less than 100 rows in the table so even if this was a heavy command to run, it should still finish relatively quickly. The existing type is varchar(1000), is it just that it's difficult and slow for pg to convert from varchar to text? I would think that would be a trivial conversion, essentially just taking off the 1000 char limit.

Is it possible that the table is somehow locked and my command is just paused, waiting for something else to finish? How would I check that? If so, how would I say "no pg, this one is the important one, lock out everything else".

Generally it'd just be nice if I could get pg to print out some debugging to say whether or not the long pause is due to heavy processing or just waiting around for a lock to free.

There are 29 rows in pg_locks and some of them do say ExclusiveLock but beyond that I'm not really sure how to interpret this information.

Postgresql version is 9.3.9-0ubuntu0.14.04 (that is, the latest version available for Ubuntu Trusty).

Here's the block report: http://paste.ubuntu.com/12243226/

Potentially relevant: my db consumer is flask/sqlalchemy, perhaps it is the one defining the select statements as blocking the table. The fact that 9 statements are blocking and they all say "where request_id = 1" is quite strange because request_id 1 was deleted some time ago. Is this some kind of dangling ghost query? How can I clear this out?

Here's pg_stat_activity: http://paste.ubuntu.com/12243295/

  • What version of PostgreSQL are you using? For your appropriate version of PostgreSQL, what is the output of one of the Lock Monitoring queries, and pg_stat_activity? – Kassandry Sep 1 '15 at 0:55
  • 1
    On newer versions, SET log_lock_waits = on and SET client_min_messages = 'log' – Craig Ringer Sep 1 '15 at 2:32
  • I added a paste showing the first lock monitoring query for 9.3. It does clearly say that my 'alter table' is blocked by a 'select', but I'm not sure why that appears 9 times (I'm only running one alter table) or how to fix this. Thanks. – robru Sep 1 '15 at 4:50
  • "request_id = 1" was deleted, like, weeks ago. Why are those SELECTs dangling there and blocking alter-table? They don't block anything else, I can get, modify, and add other rows just fine. Is there some way to say "if a select query has been running for more than an hour, kill it"? I'd like to add that to a cron job... – robru Sep 1 '15 at 4:57
  • I came up with a hacky solution, I'd appreciate if you guys could comment on it, thanks. – robru Sep 1 '15 at 5:28

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. =)

  • 1
    Thanks for the detailed explanation. Indeed there was a bug in my flask code that opened a transaction, then had an unhandled exception and never committed the transaction, however that was fixed several weeks ago and these SELECTs have just been hanging around ever since. Ideally it won't happen again, but yeah it's nice to have a way to clear those out when I'm doing ALTER TABLE rather than just have my db migration script blocked & hang forever with no indication of what's gong on ;-) – robru Sep 1 '15 at 18:06

This seems to have worked, but I'm just stabbing in the dark here so if this is a terrible solution please let me know:

for pid in $(psql -d db -c "select pid from pg_stat_activity where state like '%idle%' and backend_start < NOW() - INTERVAL '30 minutes';" | egrep '^\s*[0-9]+\s*$'); do 
    psql -d db -c "SELECT pg_terminate_backend($pid);"

After running that my ALTER TABLE completed.

  • This helped me. So I ran the select pid query which gave me a bunch of results. I found some of them were select queries on the table I was trying to alter. I just killed those select pids and the alter went through in a jiffy. – Basit Ali May 22 '19 at 12:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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