Skip site navigation (1) Skip section navigation (2)

URGENET: ALTER statement exectuation hangs when it is executed multiple time on a table

From: Dennis Wang <dennis_02_2002(at)hotmail(dot)com>
To: Postgresql Help <pgsql-interfaces(at)postgresql(dot)org>
Subject: URGENET: ALTER statement exectuation hangs when it is executed multiple time on a table
Date: 2008-01-11 00:40:07
Message-ID: BAY137-W832842CAB356C78B3C728D64B0@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-interfaces
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. rerun the 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 process created 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 are some pre-existed records in the table? or something in PostgreSQL that I am not aware of cause the problem. 
 
Thanks for the help!
 
Dennis
_________________________________________________________________
What are you waiting for? Join Lavalife FREE
http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Flavalife9%2Eninemsn%2Ecom%2Eau%2Fclickthru%2Fclickthru%2Eact%3Fid%3Dninemsn%26context%3Dan99%26locale%3Den%5FAU%26a%3D30288&_t=764581033&_r=email_taglines_Join_free_OCT07&_m=EXT

Responses

pgsql-interfaces by date

Next:From: Tom LaneDate: 2008-01-11 01:09:58
Subject: Re: URGENET: ALTER statement exectuation hangs when it is executed multiple time on a table
Previous:From: Dennis WangDate: 2008-01-11 00:25:13
Subject: URGENET: ALTER statement exectuation hangs when it is executed multiple time on a table

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group