Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Szzxj-0002r1-77 for pgsql-general@postgresql.org; Sat, 11 Aug 2012 00:53:55 +0000 Received: from hogranch.com ([75.101.82.47]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1Szzxg-0008TP-QX for pgsql-general@postgresql.org; Sat, 11 Aug 2012 00:53:54 +0000 Received: from [192.168.0.2] (porker [192.168.0.2]) by hogranch.com (8.11.6/8.11.6) with ESMTP id q7B0rf926797 for ; Fri, 10 Aug 2012 17:53:41 -0700 Message-ID: <5025AD10.5030004@hogranch.com> Date: Fri, 10 Aug 2012 17:53:36 -0700 From: John R Pierce User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:14.0) Gecko/20120713 Thunderbird/14.0 MIME-Version: 1.0 To: PostgreSQL Subject: partitioning Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-HawgScanner-Information: Please contact the ISP for more information X-HawgScanner: Found to be clean X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201208/216 X-Sequence-Number: 189393 we have a realtime manufacturing system that uses messaging to a data service that uses a very large database we're trying to migrate to postgres. key tables in this database have a 6 month retention, and are quite large, so they are partitioned by week, and we drop the oldest weeks periodically. Sometimes, the DROP TABLE's hang, then fail with a deadlock. We are getting DROP TABLE deadlocks under the full production workload which involves 100 or so active database worker processes pretty much continuously doing transactions in respond to message from the floor. There are a lot of lookups in the partitioned tables parents that don't involve the oldest partitions, We've been unable to simulate this in my lab to date. I've been unable to get ahold of a log of this event, as currently production has gone to manually stopping the workers and running the partition purge twice a month by hand, I'm asking them to reproduce so I can see whats going on. this is kind of frustrating, because I'm so far removed from the problem, and am having problems getting adequate information. -- john r pierce N 37, W 122 santa cruz ca mid-left coast