pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedALTER TABLE <name> ADD COLUMN Hangs
5+ messages / 3 participants
[nested] [flat]
* ALTER TABLE <name> ADD COLUMN Hangs
@ 2010-04-22 02:17 Samuel Stearns <SStearns@internode.com.au>
2010-04-22 03:43 ` Re: ALTER TABLE <name> ADD COLUMN Hangs Ross J. Reedstrom <reedstrm@rice.edu>
2010-04-22 06:44 ` Re: ALTER TABLE <name> ADD COLUMN Hangs Scott Marlowe <scott.marlowe@gmail.com>
2010-04-22 06:45 ` Re: ALTER TABLE <name> ADD COLUMN Hangs Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 3 replies; 5+ messages in thread
From: Samuel Stearns @ 2010-04-22 02:17 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: ALTER TABLE <name> ADD COLUMN Hangs
2010-04-22 02:17 ALTER TABLE <name> ADD COLUMN Hangs Samuel Stearns <SStearns@internode.com.au>
@ 2010-04-22 03:43 ` Ross J. Reedstrom <reedstrm@rice.edu>
2 siblings, 0 replies; 5+ messages in thread
From: Ross J. Reedstrom @ 2010-04-22 03:43 UTC (permalink / raw)
To: pgsql-admin
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' statement runs 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: ALTER TABLE <name> ADD COLUMN Hangs
2010-04-22 02:17 ALTER TABLE <name> ADD COLUMN Hangs Samuel Stearns <SStearns@internode.com.au>
@ 2010-04-22 06:44 ` Scott Marlowe <scott.marlowe@gmail.com>
2 siblings, 0 replies; 5+ messages in thread
From: Scott Marlowe @ 2010-04-22 06:44 UTC (permalink / raw)
To: Samuel Stearns <SStearns@internode.com.au>; +Cc: pgsql-admin
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.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: ALTER TABLE <name> ADD COLUMN Hangs
2010-04-22 02:17 ALTER TABLE <name> ADD COLUMN Hangs Samuel Stearns <SStearns@internode.com.au>
@ 2010-04-22 06:45 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-04-22 22:56 ` Re: ALTER TABLE <name> ADD COLUMN Hangs Samuel Stearns <SStearns@internode.com.au>
2 siblings, 1 reply; 5+ messages in thread
From: Scott Marlowe @ 2010-04-22 06:45 UTC (permalink / raw)
To: Samuel Stearns <SStearns@internode.com.au>; +Cc: pgsql-admin
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?
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: ALTER TABLE <name> ADD COLUMN Hangs
2010-04-22 02:17 ALTER TABLE <name> ADD COLUMN Hangs Samuel Stearns <SStearns@internode.com.au>
2010-04-22 06:45 ` Re: ALTER TABLE <name> ADD COLUMN Hangs Scott Marlowe <scott.marlowe@gmail.com>
@ 2010-04-22 22:56 ` Samuel Stearns <SStearns@internode.com.au>
0 siblings, 0 replies; 5+ messages in thread
From: Samuel Stearns @ 2010-04-22 22:56 UTC (permalink / raw)
To: Scott Marlowe <scott.marlowe@gmail.com>; +Cc: pgsql-admin
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 processes with 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?
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2010-04-22 22:56 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-04-22 02:17 ALTER TABLE <name> ADD COLUMN Hangs Samuel Stearns <SStearns@internode.com.au>
2010-04-22 03:43 ` Ross J. Reedstrom <reedstrm@rice.edu>
2010-04-22 06:44 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-04-22 06:45 ` Scott Marlowe <scott.marlowe@gmail.com>
2010-04-22 22:56 ` Samuel Stearns <SStearns@internode.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox