Обсуждение: Re: alter table is taking a long time
There is no one that can tell me what i can do here, or they will do here I'm thinking to restart postgrsql but what will happen with my table that i'm just altering just a filter On Nov 7, 2:38 am, "stanciuthe...@gmail.com" <stanciuthe...@gmail.com> wrote: > Hi > i have main table where and that table i have inherited 64 times, but > today i needed some extra space to a column so i have run an alter > table to one of my columns > > the query is running for over 4 hours and i don't have a clue when it > will stop, the storage of this 64 table has around 30 and milions of > records, > > Any advices on what i should need to do next, > > Regards
2009/11/7 stanciutheone@gmail.com <stanciutheone@gmail.com>: > There is no one that can tell me what i can do here, or they will do > here > > I'm thinking to restart postgrsql but what will happen with my table > that i'm just altering just a filter > > > On Nov 7, 2:38 am, "stanciuthe...@gmail.com" <stanciuthe...@gmail.com> > wrote: >> Hi >> i have main table where and that table i have inherited 64 times, but >> today i needed some extra space to a column so i have run an alter >> table to one of my columns >> >> the query is running for over 4 hours and i don't have a clue when it >> will stop, the storage of this 64 table has around 30 and milions of >> records, >> >> Any advices on what i should need to do next, >> >> Regards > > Does the column you're altering have a check constraint, primary key (possibly with cascade on it) or an index on it? And what is the structure of the inheritance? For example, do you have a straightforward 1 parent, many children tree? Thom
Hi, I recently had to do something similar: change one column from INT to BIGINT in a table which has inherited to a depth of3 and where some of the child tables had millions of records. All affected tables have to be rewritten for such a command. One consequence of this is that you (temporarily) need up totwice the amount of disk space that the tables are currently occupying. Another is that it takes a long time - for me Iran it overnight and it took at least 6 hours. Of course it depends on your hardware. I could not find any way to check on the progress of this query .. maybe someone else can help with that. I would not recommend restarting postgres. Can't you just cancel the query (control-C on psql if that is how you sent thecommand) or failing that send the postgresql backend process a SIGINT (not the master backend of course, the postgresbackend that is executing the ALTER command)? It should roll back to the state as before the command was entered. Regards // Mike -----Original Message----- From: stanciutheone@gmail.com [mailto:stanciutheone@gmail.com] Sent: Saturday, 7 November 2009 4:55 PM To: pgsql-general@postgresql.org Subject: Re: alter table is taking a long time There is no one that can tell me what i can do here, or they will do here I'm thinking to restart postgrsql but what will happen with my table that i'm just altering just a filter On Nov 7, 2:38 am, "stanciuthe...@gmail.com" <stanciuthe...@gmail.com> wrote: > Hi > i have main table where and that table i have inherited 64 times, but > today i needed some extra space to a column so i have run an alter > table to one of my columns > > the query is running for over 4 hours and i don't have a clue when it > will stop, the storage of this 64 table has around 30 and milions of > records, > > Any advices on what i should need to do next, > > Regards
Hi, stanciutheone@gmail.com wrote: > is just a varchar field that i want to make it bigge ,right now the > alter table is working for over 6 hours You can try to update the pg_attribute table directly. Just first do some select statements to ensure you only update what you really want to. Also, make a backup before you do it. update pg_attribute set attlen = 4 + <newlength> where attname = 'yourcolumnname' That will take only a couple of milliseconds to do. I used this before in scenarios where the column to be changed was referenced in many queries making it almost impossible to do a drop and recreating of the queries without any side effects. HTH, Johan Nel Pretoria, South Africa.
On 7 nov., 08:41, michael.har...@ericsson.com ("Michael Harris") wrote: > Hi, > > I recently had to do something similar: change one column from INT to BIGINT in a table which has inherited to a depthof 3 and where some of the child tables had millions of records. > > All affected tables have to be rewritten for such a command. One consequence of this is that you (temporarily) need upto twice the amount of disk space that the tables are currently occupying. Another is that it takes a long time - for meI ran it overnight and it took at least 6 hours. Of course it depends on your hardware. > > I could not find any way to check on the progress of this query .. maybe someone else can help with that. > > I would not recommend restarting postgres. Can't you just cancel the query (control-C on psql if that is how you sent thecommand) or failing that send the postgresql backend process a SIGINT (not the master backend of course, the postgresbackend that is executing the ALTER command)? It should roll back to the state as before the command was entered. > > Regards // Mike > > -----Original Message----- > From: stanciuthe...@gmail.com [mailto:stanciuthe...@gmail.com] > Sent: Saturday, 7 November 2009 4:55 PM > To: pgsql-gene...@postgresql.org > Subject: Re: alter table is taking a long time > Thank you mike, you was right nothing has changed, i haved cancel the alter table command and now is working like before thank you, right now i can go to sleep, if you need some extra help special in php please contact me Thanks a lot > There is no one that can tell me what i can do here, or they will do > here > > I'm thinking to restart postgrsql but what will happen with my table > that i'm just altering just a filter > > On Nov 7, 2:38 am, "stanciuthe...@gmail.com" <stanciuthe...@gmail.com> > wrote: > > Hi > > i have main table where and that table i have inherited 64 times, but > > today i needed some extra space to a column so i have run an alter > > table to one of my columns > > > the query is running for over 4 hours and i don't have a clue when it > > will stop, the storage of this 64 table has around 30 and milions of > > records, > > > Any advices on what i should need to do next, > > > Regards > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
is just a varchar field that i want to make it bigge ,right now the alter table is working for over 6 hours On 7 nov., 08:41, michael.har...@ericsson.com ("Michael Harris") wrote: > Hi, > > I recently had to do something similar: change one column from INT to BIGINT in a table which has inherited to a depthof 3 and where some of the child tables had millions of records. > > All affected tables have to be rewritten for such a command. One consequence of this is that you (temporarily) need upto twice the amount of disk space that the tables are currently occupying. Another is that it takes a long time - for meI ran it overnight and it took at least 6 hours. Of course it depends on your hardware. > > I could not find any way to check on the progress of this query .. maybe someone else can help with that. > > I would not recommend restarting postgres. Can't you just cancel the query (control-C on psql if that is how you sent thecommand) or failing that send the postgresql backend process a SIGINT (not the master backend of course, the postgresbackend that is executing the ALTER command)? It should roll back to the state as before the command was entered. > > Regards // Mike > > -----Original Message----- > From: stanciuthe...@gmail.com [mailto:stanciuthe...@gmail.com] > Sent: Saturday, 7 November 2009 4:55 PM > To: pgsql-gene...@postgresql.org > Subject: Re: alter table is taking a long time > > There is no one that can tell me what i can do here, or they will do > here > > I'm thinking to restart postgrsql but what will happen with my table > that i'm just altering just a filter > > On Nov 7, 2:38 am, "stanciuthe...@gmail.com" <stanciuthe...@gmail.com> > wrote: > > Hi > > i have main table where and that table i have inherited 64 times, but > > today i needed some extra space to a column so i have run an alter > > table to one of my columns > > > the query is running for over 4 hours and i don't have a clue when it > > will stop, the storage of this 64 table has around 30 and milions of > > records, > > > Any advices on what i should need to do next, > > > Regards > > -- > Sent via pgsql-general mailing list (pgsql-gene...@postgresql.org) > To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general
On Sat, Nov 07, 2009 at 10:48:14AM +0200, Johan Nel wrote: > update pg_attribute set attlen = 4 + <newlength> > where attname = 'yourcolumnname' > > That will take only a couple of milliseconds to do. It will also update *every* column with that name. Something involving the "attrelid" would be much safer. I'd use something like: update pg_attribute set attlen = 4 + <newlength> where attrelid = regclass 'your table name' and attname = 'yourcolumnname'; -- Sam http://samason.me.uk/
Hi Sam, Typo in my haste on initial mail. attlen should actually be atttypmod. > > update pg_attribute set attlen = 4 + <newlength> > > where attname = 'yourcolumnname' update pg_attribute set ATTTYPMOD = 4 + <newlength> where attname = 'yourcolumnname' and <additional where statements> > It will also update *every* column with that name. Something involving > the "attrelid" would be much safer. I'd use something like: > > update pg_attribute set attlen = 4 + <newlength> > where attrelid = regclass 'your table name' > and attname = 'yourcolumnname'; Yes I agree, that was why I initially said to do a couple of selects before doint the update to ensure only the applicable columns get updated. >> Just first do some select statements to ensure you only >> update what you really want to. Regards, Johan Nel Pretoria, South Africa. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Hi Sam, Typo in my haste on initial mail. attlen should actually be atttypmod. > > update pg_attribute set attlen = 4 + <newlength> > > where attname = 'yourcolumnname' update pg_attribute set ATTTYPMOD = 4 + <newlength> where attname = 'yourcolumnname' and <additional where statements> > It will also update *every* column with that name. Something involving > the "attrelid" would be much safer. I'd use something like: > > update pg_attribute set attlen = 4 + <newlength> > where attrelid = regclass 'your table name' > and attname = 'yourcolumnname'; Yes I agree, that was why I initially said to do a couple of selects before doint the update to ensure only the applicable columns get updated. >> Just first do some select statements to ensure you only >> update what you really want to. Regards, Johan Nel Pretoria, South Africa. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general