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/