I have an application that executes an INSERT statement every second or
two. In my old design this data was inserted into one table. However, I
changed the design to have one table with multiple partitions. Now when
I run the application it runs for some random amount of time (random
number of INSERTs) and then it gets stuck at the INSERT query and it
never finishes (at least not in some reasonable time - aka few hours).
If I restart the application it continues inserting the data normally
for a while before getting stuck again.
What could be the reason for this? I am running PostgreSQL 8.1.3 on
Windows XP. I looked at the locks table and see no locks on that table
that would cause the problem. Also, the data inserted is all going into
the same partition.
Here's an example. I have a table "master_table" and temporal paritions
"table_01", "table_02", ..., "table_NN".
Insert statements look like this:
...
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')
The last statement never finishes.