Re: Updates: all or partial records

On 25/01/2010 5:29 PM, Adrian von Bidder wrote:
> On Monday 25 January 2010 08.25:30 John R Pierce wrote:
>>> My question is, which is more efficient? Performance-wise, does it
>>> matter whether unchanged fields are included or omitted on UPDATE
>>> statements
>>
>> my first order guess is, sending and having to parse the additional
>> unchanged fields in your UPDATE statement is more expensive than letting
>> the engine just copy them from the old tuple to the new.
>
> Especially since setting unchanged fields might also trigger all sorts of
> unneeded DB activity (check constraints, and doesn't pg now also allow
> firing trigger based on which fields were updated?) which will (presumably,
> don't know the code and haven't tested it) will probably not be triggered if
> postgres can know that the value is not to be changed.

It's also a whole lot clearer for anyone reading the logs with statement
logging enabled, makes it clearer what the actual intent of the UPDATE
statement is, etc.

In addition, if you decide to start using column permissions later
you'll need to omit columns you don't have UPDATE permission on for the
current user/role, and the best way to do that is never update columns
you haven't actually changed.

--
Craig Ringer