Re: 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> 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