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

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

From: NikhilS <nikkhils(at)gmail(dot)com>
To: "Gregory Stark" <stark(at)enterprisedb(dot)com>
Cc: "Joshua D(dot) Drake" <jd(at)commandprompt(dot)com>, "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Michael Paesold" <mpaesold(at)gmx(dot)at>, "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: [DOCS] Partition: use triggers instead of rules
Date: 2007-11-30 07:04:05
Message-ID: d3c4af540711292304k7092335ete0470fd896bbb2ab@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-docspgsql-patches
Hi,

On Nov 30, 2007 1:52 AM, Gregory Stark <stark(at)enterprisedb(dot)com> wrote:

>
> "Joshua D. Drake" <jd(at)commandprompt(dot)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

In response to

Responses

pgsql-docs by date

Next:From: Simon RiggsDate: 2007-11-30 09:54:19
Subject: Re: [PATCHES] Partition: use triggers instead of rules
Previous:From: Gregory StarkDate: 2007-11-29 20:22:55
Subject: Re: [DOCS] Partition: use triggers instead of rules

pgsql-patches by date

Next:From: Jorgen Austvik - Sun NorwayDate: 2007-11-30 08:43:30
Subject: Re: pg_regress: paths in largeobject test
Previous:From: Kris JurkaDate: 2007-11-29 22:02:11
Subject: Re: Re: [HACKERS] [GENERAL] plperl and regexps with accented characters - incompatible?

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