I'm having this very disturbing problem. I got a table with about 100,000 rows in it. Our software deletes the majority of these rows and then bulk loads another 100,000 rows into the same table. All this is happening within a single transaction. I then perform a simple "select count(*) from ..." statement that never returns. In the mean time, the backend Postgres process is taking close to 100% of the CPU. The hang-up does not always happen on the same statement but eventually it happens 2 out of 3 times. If I dump and then restore the schema where this table resides the problem is gone until the next time we run through the whole process of deleting, loading and querying the table.
There is no other activity in the database. All requested locks are granted. Has anyone seen similar behavior? Some details: Postgres v 8.1.2 Linux Fedora 3 shared_buffers = 65536 temp_buffers = 32768 work_mem = 131072 maintenance_work_mem = 131072 max_stack_depth = 8192 max_fsm_pages = 40000 wal_buffers = 16 checkpoint_segments = 16 top reports PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 19478 postgres 25 0 740m 721m 536m R 99.7 4.4 609:41.16 postmaster ps -ef | grep postgres reports postgres 19478 8061 99 00:11 ? 10:13:03 postgres: user dbase [local] SELECT strace -p 19478 no system calls reported Thanks for the help! Jozsef
