Skip site navigation (1) Skip section navigation (2)

Re: [PATCHES] Partition: use triggers instead of rules

From: Simon Riggs <simon(at)2ndquadrant(dot)com>
To: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>
Cc: Gregory Stark <stark(at)enterprisedb(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Michael Paesold <mpaesold(at)gmx(dot)at>, NikhilS <nikkhils(at)gmail(dot)com>, David Fetter <david(at)fetter(dot)org>, "Jonah H(dot) Harris" <jonah(dot)harris(at)gmail(dot)com>, PostgreSQL Docs <pgsql-docs(at)postgresql(dot)org>, PostgreSQL Patches <pgsql-patches(at)postgresql(dot)org>
Subject: Re: [PATCHES] Partition: use triggers instead of rules
Date: 2007-11-30 09:54:19
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-docspgsql-patches
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

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

In response to

pgsql-docs by date

Next:From: David FetterDate: 2007-12-01 04:56:20
Subject: Re: [PATCHES] Partition: use triggers instead of rules
Previous:From: NikhilSDate: 2007-11-30 07:04:05
Subject: Re: [DOCS] Partition: use triggers instead of rules

pgsql-patches by date

Next:From: Gregory StarkDate: 2007-11-30 10:09:22
Subject: Re: [GENERAL] Empty arrays with ARRAY[]
Previous:From: Brendan JurdDate: 2007-11-30 09:14:14
Subject: Re: [GENERAL] Empty arrays with ARRAY[]

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group