Hi, On Nov 30, 2007 1:52 AM, Gregory Stark <[EMAIL PROTECTED]> wrote:
> > "Joshua D. Drake" <[EMAIL PROTECTED]> 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
