Обсуждение: Alter table command for renaming table is getting stuck.
Hi,
--
Dhaval.
I am working on a data warehousing project, our system is handling huge amount of live data.
We get data in form of events.
Frequency of events goes up to 1000 events/second. average frequency is 200 events/second.
We are using libpg to connect pg-server and insert each event into database tables.
As a part of data warehousing I am rotating table every 50000 records.
Also to reduce overhead I commit a transaction after inserting 1000 records.
Problem I am facing is as follows
While rotating (renaming) a table the "alter table" command is stuck and whole system is blocked.
Blocking is also observed while "creating new table" after rename command is finished.
No other process/query is accessing this table until rotation is done.
The problem occurs twice a day, rest of the time it is working fine.
I could not find the cause.
Please give me suggestions / solution
Thanks in advance.
--
Dhaval.
On Tue, 28 Apr 2009 15:40:22 +0530 Dhaval Rami wrote:
> No other process/query is accessing this table until rotation is done.
Are you sure? Not even VACUUM?
Look into the pg_locks system table to find out, if another connection
is accessing the table.
Kind regards
--
Andreas 'ads' Scherbaum
German PostgreSQL User Group
European PostgreSQL User Group - Board of Directors