Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 369A49FA2A7 for ; Wed, 10 Jan 2007 21:24:07 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 69307-01 for ; Wed, 10 Jan 2007 21:23:50 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from noel.decibel.org (noel.decibel.org [67.100.216.10]) by postgresql.org (Postfix) with ESMTP id 3CB719FA19A for ; Wed, 10 Jan 2007 21:24:01 -0400 (AST) Received: by noel.decibel.org (Postfix, from userid 1001) id 0F80056423; Wed, 10 Jan 2007 19:24:00 -0600 (CST) Received: (hashcash-sendmail, from uid 1001); Wed, 10 Jan 2007 19:23:55 -0600 Date: Wed, 10 Jan 2007 19:23:55 -0600 From: "Jim C. Nasby" To: Steven Flatt Cc: Colin Taylor , pgsql-performance@postgresql.org Subject: Re: table partioning performance Message-ID: <20070111012355.GF12217@nasby.net> References: <84d3067d0701062037k3229c9ddjbde73b1fd79ee209@mail.gmail.com> <357fa7590701081202p726c049ej61390dd1816d1369@mail.gmail.com> <20070110202442.GV12217@nasby.net> <357fa7590701101339m53a4b12foc805ff0060be609b@mail.gmail.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <357fa7590701101339m53a4b12foc805ff0060be609b@mail.gmail.com> X-Operating-System: FreeBSD 6.1-RELEASE-p10 amd64 X-Distributed: Join the Effort! http://www.distributed.net User-Agent: Mutt/1.5.13 (2006-08-11) X-Hashcash: 1:20:070111:steven.flatt@gmail.com::NxLkJWg4ev6t/kD2:000000000000000 00000000000000000000000018SK X-Hashcash: 1:20:070111:colin.taylor@gmail.com::lrzOl/p7GgyaUYEB:000000000000000 00000000000000000000000014Md X-Hashcash: 1:20:070111:pgsql-performance@postgresql.org::Ol2aHuOxRKpF6NK1:00000 0000000000000000000000002LfV X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/162 X-Sequence-Number: 22763 On Wed, Jan 10, 2007 at 04:39:06PM -0500, Steven Flatt wrote: > On 1/10/07, Jim C. Nasby wrote: > > > >Except for the simplest partitioning cases, you'll be much better off > >using a trigger on the parent table to direct inserts/updates/deletes to > >the children. As a bonus, using a trigger makes it a lot more realistic > >to deal with an update moving data between partitions. > > > In our application, data is never moved between partitions. > > The problem I found with triggers is the non-robustness of the PLpgSQL > record data type. For example, in an "on insert" trigger, I can't determine > the fields of the NEW record unless I hard code the column names into the > trigger. This makes it hard to write a generic trigger, which I can use for > all our partitioned tables. It would have been somewhat of a pain to write > a separate trigger for each of our partitioned tables. > > For that and other reasons, we moved some of the insert logic up to the > application level in our product. Yeah, I think the key there would be to produce a function that wrote the function for you. -- Jim Nasby jim@nasby.net EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)