Re: adding a column takes FOREVER!

I think you need exclusive access to the table in order to add columns (I'll stand/sit corrected if the more experienced responders say different). I've found that you have to either wait for them to get off or kill them first (as in pg_ctl kill ABRT).

From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Eric Smith
Sent: Tuesday, November 01, 2011 10:05 PM
To: Tom Lane
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] adding a column takes FOREVER!

Thank you for the response... to be perfectly honest, I don't know enough to know what I'm not telling you. Below is the string I use to create the table, so you can see the contents. I don't think I have foreign key references or triggers of any kind. Any ideas? (this is 8.3 running on Mac OS 10.7)

Thanks again,
Eric

[tableString setString:@""];
[tableString appendString:@"create table images (\"imageID\" varchar(11) primary key,"];
[tableString appendString:@"\"patientID\" varchar(11) null,"];
[tableString appendString:@"\"layoutID\" varchar(11) null,"];
for( iTooth = 0; iTooth < 33; iTooth++ ){
[tableString appendString:[NSString stringWithFormat:@"tooth_%d varchar(1) default 0,",iTooth]];
}
[tableString appendString:@"\"pixelsWide\" varchar(4) null,"];
[tableString appendString:@"\"pixelsHigh\" varchar(4) null,"];
[tableString appendString:@"\"bytesPerPixel\" varchar(1) null,"];
[tableString appendString:@"\"imageData\" bytea null,"];
[tableString appendString:@"\"filePath\" varchar(256) null,"];
[tableString appendString:@"orientation char(1) null,"];
[tableString appendString:@"sequence char(2) null,"];
[tableString appendString:@"\"genericInfo\" varchar(65536),"];
[tableString appendString:@"time time null,"];
[tableString appendString:@"\"saveState\" varchar(1) default \'0\',"];
[tableString appendString:@"date date null)"];

On Oct 21, 2011, at 8:24 PM, Tom Lane wrote:

Eric Smith <eric_h_smith(at)mac(dot)com<mailto:eric_h_smith(at)mac(dot)com>> writes:

I'm adding a column in postgres 8.3 with the syntax: alter table images add column "saveState" varchar(1) default '0'; It takes a good solid 20 minutes to add this column to a table with ~ 14,000 entries. Why so long? Is there a way to speed that up? The table has ~ 50 columns.

As Craig explained, that does require updating every row ... but for
only 14000 rows, it doesn't seem like it should take that long.
A quick test with 8.3 on my oldest and slowest machine:

regression=# create table foo as select generate_series(1,14000) as x;
SELECT
Time: 579.518 ms
regression=# alter table foo add column "saveState" varchar(1) default '0';
ALTER TABLE
Time: 482.143 ms

I'm thinking there is something you haven't told us about that creates a
great deal of overhead for updates on this table. Lots and lots o'
indexes? Lots and lots o' foreign key references? Inefficient
triggers?

Or maybe it's just blocking behind somebody else's lock?

regards, tom lane