Re: Rewritten rows on unchanged values

On 03/22/2013 06:41 AM, Ryan Kelly wrote:
> On Fri, Mar 03/22/13, 2013 at 06:16:11AM -0700, Adrian Klaver wrote:
>> On 03/22/2013 05:32 AM, Bertrand Janin wrote:
>>> I noticed how rows were re-written to a different location (new ctid) even
>>> without changes to the values. This illustrate what I mean:
>>>
>>> CREATE TABLE demo (id serial, value text);
>>>
>>> -- generate a few pages of dummy data
>>> INSERT INTO demo (value)
>>> SELECT md5(s.a::text)
>>> FROM generate_series(1, 1000) AS s(a);
>>>
>>> -- ctid = (0,1)
>>> SELECT id, xmin, ctid, value
>>> FROM demo
>>> WHERE id = 1;
>>>
>>> UPDATE demo
>>> SET value = value
>>> WHERE id = 1;
>>>
>>> -- ctid = (8,41)
>>> SELECT id, xmin, ctid, value
>>> FROM demo
>>> WHERE id = 1;
>>>
>>> I'm curious as to what would prevent keeping the row where it is and maybe
>>> change xmin in place?
>>
>> Because Postgres uses MVCC:
>>
>> http://www.postgresql.org/docs/9.2/static/mvcc-intro.html
>>
>> So an update is a delete and an insert and you are really seeing a new row.
>>
>
> 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.

I don't have the answer but Tom Lane does, from:

http://postgresql.1045698.n5.nabble.com/GENERAL-Update-on-tables-when-the-row-doesn-t-change-td1844002.html

"Because testing for this would almost surely be a net loss for the vast
majority of applications. Checking to see if the new row value exactly
equals the old is hardly a zero-cost operation; if you pay that on every
update, that's a lot of overhead that you are hoping to make back by
sometimes avoiding the physical store of the new tuple. In most
applications I think the "sometimes" isn't going to be often enough
to justify doing it.

If you have a particular table in a particular app where it is worth it,
I'd recommend writing a BEFORE UPDATE trigger to make the comparisons
and suppress the update when NEW and OLD are equal. "

>
> Perhaps you could provide an example where an replacing the tuple would
> be required in the presence of multiple transactions?

I am not sure what you are asking above?

>
> -Ryan Kelly
>
>
>

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com