alter table is taking a long time

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-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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...@..." <stanciuthe...@...>
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-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Thom Brown]

2009/11/7 stanciutheone@... <stanciutheone@...>:

> 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...@..." <stanciuthe...@...>
> 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

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Michael Harris (BR/EPA)]

Hi,

I recently had to do something similar: change one column from INT to BIGINT in a table which has inherited to a depth of 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 up to twice the amount of disk space that the tables are currently occupying. Another is that it takes a long time - for me I 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 the command) or failing that send the postgresql backend process a SIGINT (not the master backend of course, the postgres backend 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@... [mailto:stanciutheone@...]
Sent: Saturday, 7 November 2009 4:55 PM
To: pgsql-general@...
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...@..." <stanciuthe...@...>
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-general@...)
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...@... ("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 depth of 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 up to twice the amount of disk space that the tables are currently occupying. Another is that it takes a long time - for me I 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 the command) or failing that send the postgresql backend process a SIGINT (not the master backend of course, the postgres backend 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...@... [mailto:stanciuthe...@...]
> Sent: Saturday, 7 November 2009 4:55 PM
> To: pgsql-gene...@...
> 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...@..." <stanciuthe...@...>
> 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...@...)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 7 nov., 08:41, michael.har...@... ("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 depth of 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 up to twice the amount of disk space that the tables are currently occupying. Another is that it takes a long time - for me I 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 the command) or failing that send the postgresql backend process a SIGINT (not the master backend of course, the postgres backend 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...@... [mailto:stanciuthe...@...]
> Sent: Saturday, 7 November 2009 4:55 PM
> To: pgsql-gene...@...
> 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...@..." <stanciuthe...@...>
> 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...@...)
> To make changes to your subscription:http://www.postgresql.org/mailpref/pgsql-general

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Johan Nel-4]

Hi,

stanciutheone@... 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.

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Sam Mason]

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/

--
Sent via pgsql-general mailing list (pgsql-general@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[Johan Nel-4]

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@...)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general