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
valuefor 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
_________________________________________________________________
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
Re: URGENET: ALTER statement exectuation hangs when it is executed multiple time on a table
From
Tom Lane
Date:
Dennis Wang <dennis_02_2002@hotmail.com> writes:
> 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.
Did you do all this in one database session, or more than one? If the
latter, maybe you haven't committed the transaction that did the DELETE?
> 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.
Did it actually *have* that lock, or was it waiting for it (granted = f
in pg_locks)?
regards, tom lane