From: NikhilS <nikkhils@gmail.com>
To: Gregory Stark <stark@enterprisedb.com>
Cc: Joshua D. Drake <jd@commandprompt.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Michael Paesold <mpaesold@gmx.at>
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: [DOCS] Partition: use triggers instead of rules
Date: Fri, 30 Nov 2007 12:34:05 +0530
Message-ID: <d3c4af540711292304k7092335ete0470fd896bbb2ab@mail.gmail.com> (raw)
In-Reply-To: <87d4tser8g.fsf@oxford.xeocode.com>
References: <20071128202615.GG24146@fetter.org>
<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>
<87d4tser8g.fsf@oxford.xeocode.com>
Hi,
On Nov 30, 2007 1:52 AM, Gregory Stark <stark@enterprisedb.com> wrote:
>
> "Joshua D. Drake" <jd@commandprompt.com> writes:
>
> > If you have lots of data it doesn't mean you are modifying lots of
> > data.
>
> It sure can. How do you modify lots of data if you *don't* have lots data
> in
> the first place? Certainly it doesn't mean you necessarily are, some
> databases
> are OLTP which do no large updates. But data warehouses with oodles of
> data
> also often have to do large updates or deletions.
>
> > I don't think anyone here (good lord I hope not) would say that firing
> > a trigger over 500k rows is fast. Instead you should likely just work
> the data
> > outside the partition and then move it directly into the target
> > partition.
>
> Well you don't even have to do that. You can issue the updates directly
> against the partitions. In fact, that's precisely what the rules
> effectively
> do... Rules rewrite the query to be a query directly against the
> partitions.
>
> Come to think of it I think there actually is a correct way to use rules
> which
> wouldn't suffer from the problems that have come up. Instead of putting a
> WHERE clause on the rule just expand deletes and updates to expand to
> deletes
> and updates against *all* partitions. Then let constraint_exclusion kick
> in to
> narrow down which partitions should actually receive the updates and
> deletes.
> I think triggers are the only solution for insert though.
>
Another reason to go along with triggers is that "COPY" honors triggers, but
does not honor rules. While trying to do bulk inserts into a parent of
partitioned tables where rules are being employed, the COPY operation will
not be so straightforward.
Regards,
Nikhils
--
EnterpriseDB http://www.enterprisedb.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: nikkhils@gmail.com, stark@enterprisedb.com, jd@commandprompt.com, tgl@sss.pgh.pa.us, mpaesold@gmx.at, david@fetter.org, jonah.harris@gmail.com, pgsql-patches@postgresql.org
Subject: Re: [DOCS] Partition: use triggers instead of rules
In-Reply-To: <d3c4af540711292304k7092335ete0470fd896bbb2ab@mail.gmail.com>
* 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