Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 016809FA354 for ; Wed, 10 Jan 2007 17:39:18 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-new, port 10024) with ESMTP id 95197-03 for ; Wed, 10 Jan 2007 17:39:04 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.4 Received: from wx-out-0506.google.com (wx-out-0506.google.com [66.249.82.236]) by postgresql.org (Postfix) with ESMTP id 9FDC39FB251 for ; Wed, 10 Jan 2007 17:39:07 -0400 (AST) Received: by wx-out-0506.google.com with SMTP id i28so239211wxd for ; Wed, 10 Jan 2007 13:39:06 -0800 (PST) DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=d/MRLUKS6rgWgfCdH4w0Ta3Y9fvH6OZb5ruRdUjxv+swKRPdagX6SQrczXDOmUy1+I9+t1k1hHDB57GjiDeashB0MLxPMpG0DdrvLfX7Nlx2sLAqw84kTzvUi5lM4UhwKDhe+myn0vR9MIa7smrArJ0ATswxWSwqAY5u9H/jY/I= Received: by 10.70.132.2 with SMTP id f2mr1433504wxd.1168465146524; Wed, 10 Jan 2007 13:39:06 -0800 (PST) Received: by 10.70.102.8 with HTTP; Wed, 10 Jan 2007 13:39:06 -0800 (PST) Message-ID: <357fa7590701101339m53a4b12foc805ff0060be609b@mail.gmail.com> Date: Wed, 10 Jan 2007 16:39:06 -0500 From: "Steven Flatt" To: "Jim C. Nasby" Subject: Re: table partioning performance Cc: "Colin Taylor" , pgsql-performance@postgresql.org In-Reply-To: <20070110202442.GV12217@nasby.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_14561_2350691.1168465146410" References: <84d3067d0701062037k3229c9ddjbde73b1fd79ee209@mail.gmail.com> <357fa7590701081202p726c049ej61390dd1816d1369@mail.gmail.com> <20070110202442.GV12217@nasby.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/158 X-Sequence-Number: 22759 ------=_Part_14561_2350691.1168465146410 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline 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. Steve ------=_Part_14561_2350691.1168465146410 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
On 1/10/07, Jim C. Nasby <jim@nasby.net> 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.
 
Steve
 
------=_Part_14561_2350691.1168465146410--