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

Re: what are rules for?

From: "Michael Shulman" <shulman(at)mathcamp(dot)org>
To: "Dean Rasheed" <dean_rasheed(at)hotmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org, tgl(at)sss(dot)pgh(dot)pa(dot)us, kleptog(at)svana(dot)org, adam(dot)r(at)sbcglobal(dot)net
Subject: Re: what are rules for?
Date: 2008-06-27 15:11:12
Message-ID: c3f821000806270811y6f574ceax6a7b067d8b40f841@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
On Fri, Jun 27, 2008 at 3:06 AM, Dean Rasheed <dean_rasheed(at)hotmail(dot)com> wrote:
>> Someone pointed out in an earlier thread that a
>> way to fix this, for updates on a multi-table view (where most of the
>> complication lies), is to write a "trigger" function that updates all
>> the constituent tables except for one, and then write a rule that
>> calls that function and then updates the one remaining table itself.
>> This seems to work okay although I have not tested it with many
>> clients.
>
> Yes that would seem to work. For UPDATE anyway. Although if it were purely
> DML that you were doing, you would probably be better off just having multiple
> UPDATE statements in the rule body. Then they would stand a better chance
> of being rewritten and executed more efficiently.

As Richard Broersma pointed out in the earlier thread, this approach
has a tendency to result in "partial updates" if the WHERE clause in
the UPDATE statement issued on the view includes more than just the
primary key.

http://archives.postgresql.org/pgsql-general/2008-06/msg00479.php
http://archives.postgresql.org/pgsql-general/2006-12/msg01048.php

This is probably another one of the "subtle pitfalls" you mentioned,
but to me it means that using multiple UPDATE statements in the rule
body is unacceptable.

Mike

In response to

pgsql-general by date

Next:From: Phillip MillsDate: 2008-06-27 15:17:17
Subject: Re: Partial Index Too Literal?
Previous:From: Lennin CaroDate: 2008-06-27 14:43:13
Subject: Re: ERROR: concurrent insert in progress

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