alter table performance

On Thursday 17 December 2009, Antonio Goméz Soto
<antonio.gomez.soto@gmail.com> wrote:

Hi,

I am regularly altering tables, adding columns setting default values
etc. This very often takes a very long time and is very disk intensive,
and this gets pretty annoying.

Things are hampered by the fact that some of our servers run PG 7.3

Suppose I have a table and I want to add a non NULL column with a default
value. What I normally do is:

alter table person add column address varchar(64);
update person set address = '' where address IS NULL;
alter table person alter column address set not NULL;
alter table person alter column address set default '';

When the table contains millions of records this takes forever.

Am I doing something wrong? Do other people have the same problems?

Thanks,
Antonio

You could speed it up:

- drop all indexes on the table
- alter table person add column address varchar(64) not null default ''
- recreate the indexes

It will require exclusive access to the table for the duration, but it'll be
a lot faster and result in a lot less bloat than what you're doing now. It
still has to rewrite the whole table, but it's a lot faster than UPDATE.

(I have no idea if this works on 7.3).

--
"No animals were harmed in the recording of this episode. We tried but that
damn monkey was just too fast."

Antonio Gom�z Soto wrote on 17.12.2009 22:26:

Hi,

I am regularly altering tables, adding columns setting default values etc.
This very often takes a very long time and is very disk intensive, and this
gets pretty annoying.

Things are hampered by the fact that some of our servers run PG 7.3

Suppose I have a table and I want to add a non NULL column with a
default value.
What I normally do is:

alter table person add column address varchar(64);
update person set address = '' where address IS NULL;
alter table person alter column address set not NULL;
alter table person alter column address set default '';

When the table contains millions of records this takes forever.

Am I doing something wrong? Do other people have the same problems?

What's wrong with:

alter table person add column address varchar(64) not null default '';

Although I don't know if such a pre-historic version like 7.3 would support that.

It works for 8.4 and I believe this was working with 8.3 and 8.2 as well

Thomas

Thomas Kellerer <spam_eater@gmx.net> writes:

What's wrong with:
alter table person add column address varchar(64) not null default '';

This:

regression=# alter table person add column address varchar(64) not null default '';
ERROR: Adding columns with defaults is not implemented.
Add the column, then use ALTER TABLE SET DEFAULT.
regression=# \q

This is just one of many many things that could be improved by getting
off of 7.3. In general, complaining about performance (or features)
of a seven-year-old, long since EOL'd release is not a productive use of
anybody's time.

regards, tom lane

Op 17-12-09 23:46, Tom Lane schreef:

This is just one of many many things that could be improved by getting
off of 7.3. In general, complaining about performance (or features)
of a seven-year-old, long since EOL'd release is not a productive use of
anybody's time.

I'm sorry, didn't mean to.

I was just checking if I did it the right way, or if it was supposed to
be that slow.

I know we should upgrade the client machines, but in some cases we just
can't.

Anyway, thanks for giving me the answer.

Antonio

Hi,

is there a way in sql to dynamically test for version 7.3, so I can run the

alter table add column
update table set column = .. where column IS NULL;
alter table alter column set not null

on 7.3, and on newer versions:

alter table add column ... not null default '';

Maybe I can create pg/SQL function, that does this, and remove it
afterwards.
or is there a better way?

Thanks
Antonio

Op 17-12-09 23:46, Tom Lane schreef:

On Sat, Dec 19, 2009 at 4:07 PM, Antonio Goméz Soto
<antonio.gomez.soto@gmail.com> wrote:

Hi,

is there a way in sql to dynamically test for version 7.3, so I can run the

are you planning to run this many times? what is wrong with making
this manually?
doesn't seem like something to make automatic...

but if you insist in plpgsql you can execute "select version() into
some_text_var" and act acordingly

--
Atentamente,
Jaime Casanova
Soporte y capacitación de PostgreSQL
Asesoría y desarrollo de sistemas
Guayaquil - Ecuador
Cel. +59387171157

Op 19-12-09 22:20, Jaime Casanova schreef:

are you planning to run this many times? what is wrong with making
this manually?
doesn't seem like something to make automatic...

but if you insist in plpgsql you can execute "select version() into
some_text_var" and act acordingly

No, this is done in an automatic software update procedure across
hundreds of machines
which run different postgreSQL versions.

Thanks, I'll give this a try.

Antonio.