Re: Rewritten rows on unchanged values

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.

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

-Ryan Kelly