From: Gregory Stark <stark@enterprisedb.com>
To: Joshua D. Drake <jd@commandprompt.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: [DOCS] Partition: use triggers instead of rules
Date: Thu, 29 Nov 2007 20:22:55 +0000
Message-ID: <87d4tser8g.fsf@oxford.xeocode.com> (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>
"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.
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!
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: stark@enterprisedb.com, jd@commandprompt.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: [DOCS] Partition: use triggers instead of rules
In-Reply-To: <87d4tser8g.fsf@oxford.xeocode.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