Обсуждение: ALTER TABLE ADD COLUMN Hangs
Howdy,
I’m running in an 8.3.3 environment. What could cause, or how can I diagnose, why an ‘ALTER TABLE <name> ADD COLUMN’ statement runs for hours eventually locking users out and requiring to be killed in the end?
Thanks,
Sam
On Thu, Apr 22, 2010 at 11:47:34AM +0930, Samuel Stearns wrote: > Howdy, > > I'm running in an 8.3.3 environment. What could cause, or how can I diagnose, why an 'ALTER TABLE <name> ADD COLUMN' statementruns for hours eventually locking users out and requiring to be killed in the end? > ALTER TABLE commands require an exclusive lock on the table. I'm guessing you've got a long-running transaction ahead of the ALTER TABLE. take a look at pg_stat_activity, in particular 'waiting' and pg_locks. Ross -- Ross Reedstrom, Ph.D. reedstrm@rice.edu Systems Engineer & Admin, Research Scientist phone: 713-348-6166 The Connexions Project http://cnx.org fax: 713-348-3665 Rice University MS-375, Houston, TX 77005 GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
On Wed, Apr 21, 2010 at 8:17 PM, Samuel Stearns <SStearns@internode.com.au> wrote: > Howdy, > > I’m running in an 8.3.3 environment. What could cause, or how can I > diagnose, why an ‘ALTER TABLE <name> ADD COLUMN’ statement runs for hours > eventually locking users out and requiring to be killed in the end? By any chance you got a default on there? If so the whole table gets updated to the default, thus rewritten and locked while this happens.
On Wed, Apr 21, 2010 at 8:17 PM, Samuel Stearns <SStearns@internode.com.au> wrote: > Howdy, > > I’m running in an 8.3.3 environment. What could cause, or how can I > diagnose, why an ‘ALTER TABLE <name> ADD COLUMN’ statement runs for hours > eventually locking users out and requiring to be killed in the end? And not that it matters here, but is there a reason for running such out of date pgsql code?
Thanks Ross and Scott. I got it to complete finally thanks to Ross' tip of looking at pg_locks. I had to kill a few processeswith exclusive locks on the table and then presto! Scott, the query didn't have a DEFAULT keyword with it and I've got a request in with our sysadmins to upgrade us to 8.3.10. Thanks both of you for all your help. Sam -----Original Message----- From: Scott Marlowe [mailto:scott.marlowe@gmail.com] Sent: Thursday, 22 April 2010 4:15 PM To: Samuel Stearns Cc: pgsql-admin@postgresql.org Subject: Re: [ADMIN] ALTER TABLE <name> ADD COLUMN Hangs On Wed, Apr 21, 2010 at 8:17 PM, Samuel Stearns <SStearns@internode.com.au> wrote: > Howdy, > > I'm running in an 8.3.3 environment. What could cause, or how can I > diagnose, why an 'ALTER TABLE <name> ADD COLUMN' statement runs for hours > eventually locking users out and requiring to be killed in the end? And not that it matters here, but is there a reason for running such out of date pgsql code?