Re: Rewritten rows on unchanged values

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.

>
> Thanks,
> -b
>
>


--
Adrian Klaver
adrian.klaver@gmail.com