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

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

From: Dennis Wang <dennis_02_2002(at)hotmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Postgresql Help <pgsql-interfaces(at)postgresql(dot)org>
Subject: Re: URGENET: ALTER statement exectuation hangs when it is executed multiple time on a t
Date: 2008-01-16 07:41:27
Message-ID: BAY137-W28E6F535F92B4E0814CB37D6400@phx.gbl (view raw or flat)
Thread:
Lists: pgsql-interfaces
Hi Tom

Thank you very much for the help.

> To: dennis_02_2002(at)hotmail(dot)com
> CC: pgsql-interfaces(at)postgresql(dot)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(at)sss(dot)pgh(dot)pa(dot)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

pgsql-interfaces by date

Next:From: Alvaro HerreraDate: 2008-01-16 15:01:20
Subject: Re: plperl fails to compile PostgreSQL v8.2.6
Previous:From: Greg Sabino MullaneDate: 2008-01-16 04:21:07
Subject: DBD::Pg 2.0.0 release candidate available for testing

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