Thread: URGENET: ALTER statement exectuation hangs when it is executed multiple time on a table
URGENET: ALTER statement exectuation hangs when it is executed multiple time on a table
From
Dennis Wang
Date:
I am using a shell script to copy some table records into PostgreSQL database. The logic steps are: 1. set default value for table id 2. load some large records into the table 3. drop the id definition The actual PostgreSQL commands are: ALTER TABLE warngrid ALTER COLUMN id SET DEFAULT (nextval('warngrid_seq')* 10)+1; COPY warngrid FROM 'warngrid.csv' CSV HEADER; ALTER TABLE warngrid ALTER COLUMN id DROP DEFAULT; For the first time, these operation execute fine. Then I do these steps: 4. delete all the records in the table 5. rerunthe shell script to copy records. Then, the ALTER statement in step 1 hanging there. I did check the lock state of the warngrid table, it says the processcreated by the ALTER statement hold an "AccessExclusiveLock" on the table. I wonder if PostgreSQL got some internal mechanism which prevent a table id field being altered many times when there aresome pre-existed records in the table? or something in PostgreSQL that I am not aware of cause the problem. Thanks for the help! Dennis _________________________________________________________________ It's simple! Sell your car for just $30 at CarPoint.com.au http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fci%5F450304%2Fet%5F2%2Fcg%5F801459%2Fpi%5F1004813%2Fai%5F859641&_t=762955845&_r=tig_OCT07&_m=EXT