Return-Path: sp
Received: by postgres.Berkeley.EDU (5.61/1.29)
	id AA20173; Thu, 31 Oct 91 12:15:37 -0800
Date: Thu, 31 Oct 91 12:15:37 -0800
Message-Id: <9110312015.AA20173@postgres.Berkeley.EDU>
From: sp@postgres (Spyros Potamianos)
Subject: Re: questions about historical data
To: postgres@postgres.berkeley.edu
Sender: pg_adm@postgres.berkeley.edu
Cc: sp

On Oct 31,  4:17am, Tom Vijlbrief wrote:
} Subject: Re: questions about historical data
} 
}   [ .... stuff deleted .... ]
} * 
} Query sent to backend is "define rule revupdate is on replace to htest.a do replace htest (rev= current.rev + 1) "
} DEFINE
} Go 
} * 
} Query sent to backend is "append htest (a= 100, rev= 1) "
} APPEND
} Go 
} * 
} Query sent to backend is "replace htest (a= 101) "
} NOTICE:Oct 31 12:41:52:Non-functional update, only first update is performed
}  
} REPLACE
} Go 

First, let me explain why you got this message about the non functional
update.
Your 'htest' relation has one tuple [a=100, rev=1]. When you run the replace
command, our infamous tuple level rule system is presented with a "current"
tuple [a=100,rev=1] and a "new" tuple [a=101, rev=1].
The rule is activated and attempts to run the query:
    
    replace htest(rev = 1 + 1)

As a result *all* the tuples of htest will have their 'rev' field updated
to 2 (of course in our example there is only one tuple, but in general all
the tuples will be modofied - so probably this is not what we want...)
So, because of this rule activation the one and only tuple of htest is
updated from [a=100, rev=1] to [a=100, rev=2].
After the rule has been activated, the system proceeds with the original
update "replace htest(a=101)". So, it tries replace the "current" tuple
(i.e. the tuple that already exists in the database and now has the value
[a=100, rev=2] because of the rule activation) with the "new" one
[a=101, rev=1]. 
However that means updating the same tuple twice during the same
command.  This is a non functional update, the system detects this
unfortunate fact, and ignores this second update.
What needs to be done, is to update both attributes at the same time.

The way to do this is to define the following rule:

    define rule foo is
    on replace to htest.a
    do replace new (rev = current.rev + 1)
	       ^^^
This rule states that whenever you try to replace the 'a' attribute of a
tuple, you must also update its 'rev' attribute of the "new" tuple.
The system treats that as a special case. The rule system directly modifies
the "new" tuple (and NOT the "current" tuple) from [a=101, rev=1] to
[a=101, rev=2] and everything works (or at least it is supposed to :-) fine.
Note also, that this rule will do the right thing even if there are more
than one tuples in 'htest'.
Hope that helps....

Spyros Potamianos,
The Official POSTGRES Tuple Level Rule System Person.