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:

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

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:

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

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