pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedURGENET: ALTER statement exectuation hangs when it is executed multiple time on a table
4+ messages / 2 participants
[nested] [flat]
* URGENET: ALTER statement exectuation hangs when it is executed multiple time on a table
@ 2008-01-11 00:25 Dennis Wang <dennis_02_2002@hotmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Dennis Wang @ 2008-01-11 00:25 UTC (permalink / raw)
To: pgsql-interfaces
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
_________________________________________________________________
It's simple! Sell your car for just $30 at CarPoint.com.au
http://a.ninemsn.com.au/b.aspx?URL=http%3A%2F%2Fsecure%2Dau%2Eimrworldwide%2Ecom%2Fcgi%2Dbin%2Fa%2Fc...
^ permalink raw reply [nested|flat] 4+ messages in thread
* URGENET: ALTER statement exectuation hangs when it is executed multiple time on a table
@ 2008-01-11 00:40 Dennis Wang <dennis_02_2002@hotmail.com>
0 siblings, 1 reply; 4+ messages in thread
From: Dennis Wang @ 2008-01-11 00:40 UTC (permalink / raw)
To: pgsql-interfaces
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...
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: URGENET: ALTER statement exectuation hangs when it is executed multiple time on a table
@ 2008-01-11 01:09 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dennis Wang <dennis_02_2002@hotmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2008-01-11 01:09 UTC (permalink / raw)
To: Dennis Wang <dennis_02_2002@hotmail.com>; +Cc: pgsql-interfaces
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: URGENET: ALTER statement exectuation hangs when it is executed multiple time on a t
@ 2008-01-16 07:41 Dennis Wang <dennis_02_2002@hotmail.com>
0 siblings, 0 replies; 4+ messages in thread
From: Dennis Wang @ 2008-01-16 07:41 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-interfaces
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=hotmailtaglin...
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-01-16 07:41 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-01-11 00:25 URGENET: ALTER statement exectuation hangs when it is executed multiple time on a table Dennis Wang <dennis_02_2002@hotmail.com>
2008-01-11 00:40 URGENET: ALTER statement exectuation hangs when it is executed multiple time on a table Dennis Wang <dennis_02_2002@hotmail.com>
2008-01-11 01:09 ` Tom Lane <tgl@sss.pgh.pa.us>
2008-01-16 07:41 Re: URGENET: ALTER statement exectuation hangs when it is executed multiple time on a t Dennis Wang <dennis_02_2002@hotmail.com>
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