From: Simon Riggs <simon@2ndquadrant.com>
To: Joshua D. Drake <jd@commandprompt.com>
Cc: Gregory Stark <stark@enterprisedb.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Michael Paesold <mpaesold@gmx.at>
Cc: NikhilS <nikkhils@gmail.com>
Cc: David Fetter <david@fetter.org>
Cc: Jonah H. Harris <jonah.harris@gmail.com>
Cc: PostgreSQL Docs <pgsql-docs@postgresql.org>
Cc: PostgreSQL Patches <pgsql-patches@postgresql.org>
Subject: Re: [PATCHES] Partition: use triggers instead of rules
Date: Fri, 30 Nov 2007 09:54:19 +0000
Message-ID: <1196416459.4246.1514.camel@ebony.site> (raw)
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>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: simon@2ndquadrant.com, jd@commandprompt.com, stark@enterprisedb.com, tgl@sss.pgh.pa.us, mpaesold@gmx.at, nikkhils@gmail.com, david@fetter.org, jonah.harris@gmail.com, pgsql-patches@postgresql.org
Subject: Re: [PATCHES] Partition: use triggers instead of rules
In-Reply-To: <1196416459.4246.1514.camel@ebony.site>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox