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');
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?
--
Clive Page
