Re: [GENERAL] alter table performance
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table performance
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: Thomas Kellererspam_ea...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table performance
On Sat, Dec 19, 2009 at 4:07 PM, Antonio Goméz Soto antonio.gomez.s...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] alter table performance
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table performance
On Thursday 17 December 2009, Antonio Goméz Soto antonio.gomez.s...@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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table performance
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table performance
Thomas Kellerer spam_ea...@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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] alter table performance
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
