"slow" queries - Mailing list pgsql-performance

Actually, they're all deadlocked. The question is why?

Here's a brief background. The ts_defects table is partitioned by
occurrence date; each partition contains the rows for 1 day. When the
data gets old enough, the partition is dropped. Since the correct
partition can be determined from the occurrence date, there is no
trigger: inserts are done directly into the correct partition. Multiple
threads may be inserting into a partition at the same time. The thread
that checks for old data to be dropped runs at 00:30 each night. It also
creates the partition for the next day.

Below is the output from:
select xact_start,query_start,substring(current_query from 0 for 40)
from pg_stat_activity order by xact_start;

run at 18:40 on 28 Feb 2009 (i.e. these queries have been running for
 > 6 hours). The 1st select is not on any of the ts_defect partitions
nor is the CREATE VIEW. The SELECT's shown last are not (directly)
generated by the java program that is running the drop table, inserts,
the 1st select and the CREATE VIEW.

Thanks for your ideas,
Brian


  2009-02-28 00:30:00.01572-08  | 2009-02-28 00:30:00.015758-08 | drop
table ts_defects_20090225
  2009-02-28 00:30:00.693353-08 | 2009-02-28 00:30:00.69337-08  | select
transetdef0_.ts_id as ts1_85_0_,
  2009-02-28 00:30:01.875671-08 | 2009-02-28 00:30:01.875911-08 | insert
into ts_defects_20090228 (ts_id,
  2009-02-28 00:30:01.875673-08 | 2009-02-28 00:30:01.875911-08 | insert
into ts_defects_20090228 (ts_id,
  2009-02-28 00:30:01.875907-08 | 2009-02-28 00:30:01.87611-08  | insert
into ts_defects_20090228 (ts_id,
  2009-02-28 00:30:01.87615-08  | 2009-02-28 00:30:01.876334-08 | insert
into ts_defects_20090228 (ts_id,
  2009-02-28 00:30:01.87694-08  | 2009-02-28 00:30:01.877153-08 | insert
into ts_defects_20090228 (ts_id,
  2009-02-28 00:30:01.876952-08 | 2009-02-28 00:30:01.877171-08 | insert
into ts_defects_20090228 (ts_id,
  2009-02-28 00:30:01.876965-08 | 2009-02-28 00:30:01.87716-08  | insert
into ts_defects_20090228 (ts_id,
  2009-02-28 00:30:01.877267-08 | 2009-02-28 00:30:01.877483-08 | insert
into ts_defects_20090228 (ts_id,
  2009-02-28 00:30:01.877928-08 | 2009-02-28 00:30:01.878101-08 | insert
into ts_defects_20090228 (ts_id,
  2009-02-28 00:30:06.822733-08 | 2009-02-28 00:30:06.822922-08 | insert
into ts_defects_20090228 (ts_id,
  2009-02-28 01:01:00.95051-08  | 2009-02-28 01:01:00.950605-08 | CREATE
VIEW TranSetGroupSlaPerformanceA
  2009-02-28 09:12:33.181039-08 | 2009-02-28 09:12:33.181039-08 | SELECT
c.oid, c.relname, pg_get_userbyi
  2009-02-28 09:19:47.335621-08 | 2009-02-28 09:19:47.335621-08 | SELECT
c.oid, c.relname, pg_get_userbyi
  2009-02-28 10:52:36.638467-08 | 2009-02-28 10:52:36.638467-08 | SELECT
c.oid, c.relname, pg_get_userbyi
  2009-02-28 11:01:05.023126-08 | 2009-02-28 11:01:05.023126-08 | SELECT
c.oid, c.relname, pg_get_userbyi