Re: Rewritten rows on unchanged values

Ryan Kelly <rpkelly22(at)gmail(dot)com> wrote:

> I'm having trouble understanding why it is necessary to generate a new
> tuple even when nothing has changed. It seems that the OP understands
> that MVCC is at work, but is questioning why this exact behavior occurs.
> I too have the same question.
>
> Perhaps you could provide an example where an replacing the tuple would
> be required in the presence of multiple transactions?

Well, someone might use such an update for concurrency control
purposes, since the SELECT FOR UPDATE in PostgreSQL isn't as strong
as it is in some other products.  An actual UPDATE will generate a
write conflict in some circumstances where SELECT FOR UPDATE will
not.  Aside from that, I think it's mainly an issue of performance
-- it is slower to process an UPDATE command and check whether any
of the columns in the SET list are actually being set to a new
value than to not generate the UPDATE in the first place.  There is
a trigger function to do this extra work for those who need it;
take a look at the suppress_redundant_updates_trigger() function:

http://www.postgresql.org/docs/current/interactive/functions-trigger.html

--
Kevin Grittner
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company