Insert into partitioned table hangs
Posted on 2006-05-23
I am running PostgreSQL 8.1.3 on Windows 2003 Server.
I have a master table that has many temporal partitions. I have a Java application that executes an insert on the master table every second or two (non-concurrently).
However, I noticed that at random times an insert statement just hangs. For example I will have 100 or 200 successfuly inserts and then it just sits there at the next insert and it never finishes. If I restart the Java application it throws a primary key violation for that last record that was hanging (aka the record has been inserted regardless of the statement hanging), and then it works for another couple of hundred statements until it fails again in the same fashion. Failure intervals vary from 20-30 statements prior to failure, to 500-1000 statements prior to failure.
The failing insert statement is no different than all the other ones, and it is supposed to insert into the same partition as all the other ones are.
I checked the pg_locks and this session is not holding any locks or waiting for any locks to be released. There are not many other transactions in the database, and ones that do exist are mostly selects in different schemas.
Here is an example of output from my Java application:
16:16:52: INSERT INTO master_table(id, speed, start) VALUES (84,57.7,'05-09-2006 20:53:42 -4:00')
16:16:53: INSERT INTO master_table(id, speed, start) VALUES (43,74.6,'05-09-2006 20:53:42 -4:00')
16:16:54: INSERT INTO master_table(id, speed, start) VALUES (71,74.2,'05-09-2006 20:53:42 -4:00')
This last statement never reaches the "DONE" print statement located immediatelly below the query execute statement.
This same application used to load the data into this table prior to it being partitioned and it never had issues.
What could be the issue and where should I be looking to fix this problem?