Re: vacuum, performance, and MVCC

On Jun 22, 2006, at 1:09 PM, Tom Lane wrote:
> Lukas Smith <smith(at)pooteeweet(dot)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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461