BUG #6101: ALTER TABLE hangs with AccessExclusiveLock

The following bug has been logged online:

Bug reference: 6101
Logged by: Dmitry
Email address: y4gr5rzn(at)gmail(dot)com
PostgreSQL version: 9.0.4
Operating system: Debian i686 2.6.32-5-686-bigmem
Description: ALTER TABLE hangs with AccessExclusiveLock
Details:

ALTER locks the table AccessExclusiveLock and hangs.
I can break it with Ctrl-C in psql, but it 100% reproduces in any session.

Command: ALTER TABLE Amazon.Books ADD COLUMN textsearchable_index_col
tsvector;

Similar reports:
http://archives.postgresql.org/pgsql-admin/2002-04/msg00284.php
http://www.justskins.com/forums/how-to-release-a-133616.html

pg_locks: AccessExclusiveLock granted=f
ps:
postgres 3535 0.0 0.0 50064 652 ? Ss Jul05 0:00 postgres:
al mydb [local] idle in transaction
postgres 22295 0.1 1.0 51804 34028 ? Ss Jul03 7:18 postgres:
al mydb [local] ALTER TABLE waiting

Also, i can submit SQL table description, full pg_locks, pg_class,
pg_stat_activity.

On 8/07/2011 7:33 PM, Dmitry wrote:
> The following bug has been logged online:
>
> Bug reference: 6101
> Logged by: Dmitry
> Email address: y4gr5rzn(at)gmail(dot)com
> PostgreSQL version: 9.0.4
> Operating system: Debian i686 2.6.32-5-686-bigmem
> Description: ALTER TABLE hangs with AccessExclusiveLock
> Details:
>
> ALTER locks the table AccessExclusiveLock and hangs.
> I can break it with Ctrl-C in psql, but it 100% reproduces in any session.

Are there any other sessions running concurrently? Is a lock on the
table you are trying to ALTER already held by another transaction
according to pg_locks ?

So far there's no evidence that this is anything except locking doing
its job.

--
Craig Ringer

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088 Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/

On 9/07/2011 4:05 PM, Dmitriy Savricov wrote:
> As i figured out, the reason is in "<IDLE> in transaction" process,
> which hangs after table filling. From yestaday, I could not understand
> what the process keeps the lock, and thinked that reason was inside
> FTS - from system tables (pg_class) it was impossible to determine who
> has locked the database. Today, I accidentally found the python
> process in transaction.
>
> Thank you very much for your help, and sorry for false report.
>
No worries. In future it'd be best to submit a test case that can be run
as an SQL file, along with things like the contents of pg_locks and
pg_stat_activity during the suspected fault, log excerpts, etc etc if
you find something that you think might be a bug. If in doubt, ask on
the pgsql-general mailing list, providing as much detail as you can.

Thanks for following up.

--
Craig Ringer

POST Newspapers
276 Onslow Rd, Shenton Park
Ph: 08 9381 3088 Fax: 08 9388 2258
ABN: 50 008 917 717
http://www.postnewspapers.com.au/