Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 522B72E0161 for ; Fri, 30 Nov 2007 03:04:12 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 07351-01-6 for ; Fri, 30 Nov 2007 03:04:07 -0400 (AST) Received: from rv-out-0910.google.com (rv-out-0910.google.com [209.85.198.191]) by postgresql.org (Postfix) with ESMTP id D77272E0073 for ; Fri, 30 Nov 2007 03:04:06 -0400 (AST) Received: by rv-out-0910.google.com with SMTP id l15so1866722rvb for ; Thu, 29 Nov 2007 23:04:05 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; bh=RgBqtLLDsKOFbSluSNtcCwSlWOEcl744lP/rPdDROy0=; b=Yfj+xnWow4WnMQofvvRLITVIGmKK9yxxstMJt6UzihR9jpZVbJZfDQfWZz0ClDC7+qg/JWRQj86KvJeYRSWy7E5nFUTN/0/mnVgFN6CPDaVEYk4HvZxWEix1wfwXsX9DW0lD3MWgqhXl+yKyDHf7LO8DfjdUYx2dSsLjewhoYiE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=nnL1GyOBCm/loQllhbxX0vXeKURteokclTMcNEHypfOJC2+i52V6lhP5Md5QYL8b9XT+468H4bozcVX57i9WQhTDRIOasI2Xjz+TtAWdHsbMMb5ZgTbdjhTf3tP30eIrChIlJUxgEv/TqKckUYaa/Hi+C6x37WPU6lgNtHiy5iA= Received: by 10.114.38.2 with SMTP id l2mr652748wal.1196406245777; Thu, 29 Nov 2007 23:04:05 -0800 (PST) Received: by 10.114.167.11 with HTTP; Thu, 29 Nov 2007 23:04:05 -0800 (PST) Message-ID: Date: Fri, 30 Nov 2007 12:34:05 +0530 From: NikhilS To: "Gregory Stark" Subject: Re: [DOCS] Partition: use triggers instead of rules Cc: "Joshua D. Drake" , "Tom Lane" , "Michael Paesold" , "David Fetter" , "Jonah H. Harris" , "PostgreSQL Docs" , "PostgreSQL Patches" In-Reply-To: <87d4tser8g.fsf@oxford.xeocode.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_12784_17156356.1196406245751" 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> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200711/302 X-Sequence-Number: 4543 ------=_Part_12784_17156356.1196406245751 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Hi, On Nov 30, 2007 1:52 AM, Gregory Stark wrote: > > "Joshua D. Drake" 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 ------=_Part_12784_17156356.1196406245751 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 ------=_Part_12784_17156356.1196406245751--