Re: [GENERAL] Rewritten rows on unchanged values

[Adrian Klaver]

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.kla...@gmail.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general