TRUNCATE HANGS - Mailing list pgsql-bugs

Hello,
I'm running into a problem with truncate causing my test application to
fail. I've noticed some similar
problems posted but noting that quite lines up with what I'm seeing.

I have a partitioned table.  Storing m records per partition, where I use a
sequence table
to keep track of the over all record count and a sequence table to track
records per partition, using is_called column to indicate data in the
partition. When an insert occurs to partition N, the sequence
table for partition N+1 (wrap to zero as needed) is checked. If is_called is
true, partition N+1 is truncated. The insert to partition N is based on the
total record count.
To invoke the truncation, a truncate list table is update. This table is
read by a scheduled process (cron)
which actually invokes TRUNCATE on the named table.

For test purposes, the scope is 10 million records, 900K per partition.
The test itself is two threads. Thread A is inserting records in a fairly
tight loop. About 3k per second.
Thread B is looks for the MIN, and MAX keys then randomly selects a chunk
from the table set. There is
no correlation between the record key and record count, CHECK constraints
will not apply. For my purposes pg index searches to read the data without
CHECK is reasonable.

Running this test, a deadlock can be created without fail. Postgres
processes always look like:
postgres 16989 26837 64 11:20 ?        00:42:06 postgres: postgres uisdb
127.0.0.1(34404) INSERT waiting
postgres 16990 26837 44 11:20 ?        00:28:51 postgres: postgres uisdb
127.0.0.1(34405) idle in transaction
postgres 16991 26837  5 11:20 ?        00:03:43 postgres: postgres uisdb
127.0.0.1(34406) PARSE waiting
root     16992 28129  0 11:20 pts/3    00:00:00 psql -U postgres -d uisdb
postgres 16993 26837  0 11:20 ?        00:00:00 postgres: postgres uisdb
[local] idle
postgres 17801 17800  0 12:09 ?        00:00:00 /bin/bash
/var/lib/pgsql/data/scripts/partition_rolling.sh
postgres 17813 17801  0 12:09 ?        00:00:00 psql -U postgres -d uisdb -t
--command TRUNCATE ONLY contact_reports_part0;
postgres 17814 26837  0 12:09 ?        00:00:00 postgres: postgres uisdb
[local] TRUNCATE TABLE waiting
root     18074 10513  0 12:25 pts/4    00:00:00 grep post
postgres 26837     1  0 Dec03 ?        00:00:02 /usr/bin/postmaster -p 5432
-D /var/lib/pgsql/data
postgres 26839 26837  0 Dec03 ?        00:00:00 postgres: logger process
postgres 26841 26837  0 Dec03 ?        00:00:14 postgres: writer process
postgres 26842 26837  0 Dec03 ?        00:00:03 postgres: wal writer process
postgres 26843 26837  0 Dec03 ?        00:00:00 postgres: autovacuum
launcher process
postgres 26844 26837  0 Dec03 ?        00:00:02 postgres: stats collector
process


I'm new to pg, still working thru pg_locks etc. However, I can line up the
pg_locks where not granted to these insert, pending and truncate processes
above. When I kill the 'idle' process, things unblock, but I'm at loss for
what is the underlying cause of this problem.

Thanks in advance for any suggestions.
Tom


--
View this message in context: http://postgresql.1045698.n5.nabble.com/TRUNCATE-HANGS-tp3292333p3292333.html
Sent from the PostgreSQL - bugs mailing list archive at Nabble.com.