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(dot)klaver(at)gmail(dot)com