From: Dennis Wang <dennis_02_2002@hotmail.com>
To: Postgresql Help <pgsql-interfaces@postgresql.org>
Subject: URGENET: ALTER statement exectuation hangs when it is executed multiple time on a table
Date: Fri, 11 Jan 2008 11:10:07 +1030
Message-ID: <BAY137-W832842CAB356C78B3C728D64B0@phx.gbl> (raw)
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%2Fclickth...
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-interfaces@postgresql.org
Cc: dennis_02_2002@hotmail.com
Subject: Re: URGENET: ALTER statement exectuation hangs when it is executed multiple time on a table
In-Reply-To: <BAY137-W832842CAB356C78B3C728D64B0@phx.gbl>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox