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
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
