Subject: Re: [PATCHES] Partition: use triggers instead of rules
From: Simon Riggs <simon@2ndquadrant.com>
To: "Joshua D. Drake" <jd@commandprompt.com>
Cc: Gregory Stark <stark@enterprisedb.com>, Tom Lane <tgl@sss.pgh.pa.us>,
	Michael Paesold <mpaesold@gmx.at>, NikhilS <nikkhils@gmail.com>,
	David Fetter <david@fetter.org>,
	"Jonah H. Harris" <jonah.harris@gmail.com>,
	PostgreSQL Docs <pgsql-docs@postgresql.org>,
	PostgreSQL Patches <pgsql-patches@postgresql.org>
In-Reply-To: <20071129095233.67f2b913@commandprompt.com>
References: <20071128202615.GG24146@fetter.org>
	<21686.1196283184@sss.pgh.pa.us>
	<36e682920711281858x9a37039sa104036bf025de41@mail.gmail.com>
	<20071129031702.GU24146@fetter.org> <2854.1196315753@sss.pgh.pa.us>
	<20071128224154.06f950c9@commandprompt.com>
	<d3c4af540711290705q35ea51cdkfaa94b0c07a1aecc@mail.gmail.com>
	<474EDAE2.3010705@gmx.at> <474EE25E.3080304@commandprompt.com>
	<14455.1196353045@sss.pgh.pa.us> <474EE992.2050209@commandprompt.com>
	<87zlwxj7xu.fsf@oxford.xeocode.com>
	<20071129091359.206fd8f2@commandprompt.com>
	<87ve7lj6y8.fsf@oxford.xeocode.com>
	<20071129095233.67f2b913@commandprompt.com>
Content-Type: text/plain
Date: Fri, 30 Nov 2007 09:54:19 +0000
Message-Id: <1196416459.4246.1514.camel@ebony.site>
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

On Thu, 2007-11-29 at 09:52 -0800, Joshua D. Drake wrote:

> In any of the above cases a trigger is going to work better than a
> rule with the exceptions of what TGL pointed out and in simpler
> partitioning environments where the number of partitions are very low.

Agreed to this and in general to JD's points.


Tom's point about Rules being statement-level is valid only when all
rows from an INSERT SELECT go into one partition. If that were the case
then it seems strange to spend lots of time designing a rules
infrastructure anyway.

If you try to do lots of partitions and RULEs then it sucks. I spoke
against their inclusion originally and do so again now. My point then
was you don't need this for most partitioning applications.

What nobody has mentioned is that Triggers suck as well, so neither
Triggers nor Rules should be given centre stage. COPY only makes sense
running into the table you are loading and if you're trying to load
large amounts of data using INSERTs + anything then you need a whack.
Rob did a beautiful de-construction of all of this in Montreal, BTW,
with humour too.

Current PostgreSQL partitioning is not the same as Oracle's and papering
over the cracks doesn't help anybody much. ISTM we should say to people
to use COPY into a named partition for high speed, plus these other
suggestions if you want some fancy logic, but go careful, cos they're
slow.

It would be nice to have an example of using CREATE TABLE LIKE + COPY in
same transaction, then ALTER TABLE ... INHERITS to add the partition
onto the main table. That is now the fastest way in 8.3.

I'll leave it to y'all from here though. 

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com