Re: vacuum, performance, and MVCC

On Jun 22, 2006, at 1:09 PM, Tom Lane wrote:
> Lukas Smith <smith@pooteeweet.org> writes:
>> Jochem van Dieten wrote:
>>> make the session handler smarter? And if you can't do that, put some
>>> logic in the session table that turns an update without changes  
>>> into a
>>> no-op?
>
>> err isnt that one the job of the database?
>
> No.  That idea has been suggested and rejected before.  Detecting that
> an UPDATE is a no-op would require a significant number of cycles, and
> in most applications, most or all of the time those cycles would be
> wasted effort.  If you have a need for this behavior, you can attach a
> BEFORE UPDATE trigger to a table that checks for all-fields-the- 
> same and
> suppresses the update.  I don't think that should be automatic though.

What would be nice to add is the ability to perform that check more  
easily. As of 8.1...

create function test() returns trigger as $$
begin
if NEW=OLD then
return null;
else
return NEW;
end if;
end;
create trigger test before update on test execute procedure test();
update test set i=i;
ERROR:  operator does not exist: test = test
HINT:  No operator matches the given name and argument type(s). You  
may need to add explicit type casts.
CONTEXT:  SQL statement "SELECT   $1 = $2 "
PL/pgSQL function "test" line 2 at if
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461