Обсуждение: BUG #6101: ALTER TABLE hangs with AccessExclusiveLock
The following bug has been logged online: Bug reference: 6101 Logged by: Dmitry Email address: y4gr5rzn@gmail.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@gmail.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/