Re: Postgres crashed when adding a sequence column

Clive Page wrote:
> I have a largish table (71 million rows) to which I needed to add a new
> integer column containing a unique identifier - a simple sequence seemed
> to be good enough. I discovered the CREATE SEQUENCE command which looked
> as if it would do the job, and did the following:
>
> ALTER TABLE intwfs ADD COLUMN id int ;
> CREATE SEQUENCE myseq;
> UPDATE intwfs SET id = nextval('myseq');

Nowt wrong with that.

> I expected it to take under an hour, but the process was still running
> after several hours, taking ~15% cpu and a modest amount of memory.
> Later on other users reported the machine was almost unusable and I found
> that postmaster was hogging over 99% of cpu and all of memory. I was
> about to stop the process, but before I could do that the postmaster
> crashed.
>
> Obviously I'm doing something that Postgres doesn't support, but I'm not
> quite clear what. Any suggestions on how to achieve the same objective
> more easily?

Nothing wrong with what you're doing, however, you are running a
transaction that touches 142 million rows (expiring the old rows and
adding new ones). Still, unless you are particularly short of memory, or
haven't tuned PostgreSQL it should be fine.

Some questions:
1. Is the table particularly wide (i.e. number/size of columns)?
2. Do you have any foreign keys/triggers on the table?

I suspect point 2, but that's just me guessing. What I'm guessing is
that there is a load of pending/deferred triggers working their way through.
--
Richard Huxton
Archonet Ltd