Message-ID: <BAY137-W832842CAB356C78B3C728D64B0@phx.gbl>
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
Importance: Normal
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0


I am using a shell script to copy some table records into PostgreSQL databa=
se. The logic steps are:
=20
  1. set default value for table id
  2. load some large records into the table
  3. drop the id definition
=20
  The actual PostgreSQL commands are:
     ALTER TABLE warngrid ALTER COLUMN id SET DEFAULT (nextval('warngrid_se=
q')* 10)+1;
     COPY warngrid FROM 'warngrid.csv' CSV HEADER;=20
     ALTER TABLE warngrid ALTER COLUMN id DROP DEFAULT;
=20
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.
=20
Then, the ALTER statement in step 1 hanging there. I did check the lock sta=
te of the warngrid table, it says the process created by the ALTER statemen=
t hold an "AccessExclusiveLock" on the table.=20
=20
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 prob=
lem.=20
=20
Thanks for the help!
=20
Dennis
_________________________________________________________________
What are you waiting for? Join Lavalife FREE
http://a.ninemsn.com.au/b.aspx?URL=3Dhttp%3A%2F%2Flavalife9%2Eninemsn%2Ecom=
%2Eau%2Fclickthru%2Fclickthru%2Eact%3Fid%3Dninemsn%26context%3Dan99%26local=
e%3Den%5FAU%26a%3D30288&_t=3D764581033&_r=3Demail_taglines_Join_free_OCT07&=
_m=3DEXT=