Hi Tom

Thank you very much for the help.
To: dennis_02_2002@hotmail.com
CC: pgsql-interfaces@postgresql.org
Subject: Re: [INTERFACES] URGENET: ALTER statement exectuation hangs when it is executed multiple time on a table
Date: Thu, 10 Jan 2008 20:09:58 -0500
From: tgl@sss.pgh.pa.us

Dennis Wang 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 delete the records by login the database with 'psql' command, the ALTER statement is running by another process. So I guess the DELETE and ALTER are in two different session. However, the DELETE statement is a one shot command, it should commit immediately if I did not put it in a "BEGIN ... COMMIT" block.

I will try that again by issue COMMIT right after the DELETE to see any difference.

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)?
The process actually have the Lock.
regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org
Regard

Dennis




_________________________________________________________________
New music from the Rogue Traders - listen now!
http://ninemsn.com.au/share/redir/adTrack.asp?mode=click&clientID=832&referral=hotmailtaglineOct07&URL=http://music.ninemsn.com.au/roguetraders

Search Discussions

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-interfaces @
categoriespostgresql
postedJan 16, '08 at 7:41a
activeJan 16, '08 at 7:41a
posts1
users1
websitepostgresql.org
irc#postgresql

1 user in discussion

Dennis Wang: 1 post

People

Translate

site design / logo © 2022 Grokbase