X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id C3D5356881 for ; Sun, 20 Mar 2005 00:03:22 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 00437-01 for ; Sun, 20 Mar 2005 00:03:18 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 5ECC1568C6 for ; Sun, 20 Mar 2005 00:03:17 +0000 (GMT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.1/8.13.1) with ESMTP id j2K03JAJ003376; Sat, 19 Mar 2005 19:03:19 -0500 (EST) To: Josh Berkus Cc: pgsql-performance@postgresql.org Subject: Re: What needs to be done for real Partitioning? In-reply-to: <200503191529.51794.josh@agliodbs.com> References: <200503191202.38089.josh@agliodbs.com> <18168.1111265368@sss.pgh.pa.us> <200503191529.51794.josh@agliodbs.com> Comments: In-reply-to Josh Berkus message dated "Sat, 19 Mar 2005 15:29:51 -0800" Date: Sat, 19 Mar 2005 19:03:19 -0500 Message-ID: <3375.1111276999@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.007 tagged_above=0 required=5 tests=AWL X-Spam-Level: X-Archive-Number: 200503/380 X-Sequence-Number: 11085 Josh Berkus writes: >>> -- INSERT INTO should automatically create new partitions where necessary >>> -- DELETE FROM should automatically drop empty partitions >> >> I am not sure I agree with either of those, and the reason is that they >> would turn low-lock operations into high-lock operations. > For INSERT, I think that's a problem we need to work through. Possibly, but I'm concerned about locking and deadlock issues. The reason that this is iffy is you would start the operation with only an INSERT-grade lock, and then discover that you needed to add a partition, which is surely something that needs an exclusive-grade lock (consider two sessions trying to add the same partition at the same time). So I don't see how to do it without lock upgrading, and lock upgrading is always a recipe for deadlocks. The DELETE case is even worse because you can't physically release storage until you're sure nothing in it is needed anymore by any open transaction --- that introduces VACUUM-like issues as well as the deadlock problem. > Or are you proposing, instead, that attempts to insert beyond the > range raise an error? That was what I had in mind --- then adding partitions would require a manual operation. This would certainly be good enough for "phase I" IMHO. regards, tom lane