On 2013-03-22, Ryan Kelly <rpkelly22@xxxxxxxxx> 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. > > Perhaps you could provide an example where an replacing the tuple would > be required in the presence of multiple transactions? create temp table foo as select 1::integer as x ; create temp table bar as select 1::integer as y ; create temp table goo as select 1::integer as z ; A B begin transaction; begin transaction; update foo set x=1; update bar set y=3; update foo set x=2; update goo set z=3; commit; commit; One of those has to fail. if you discard the "update foo set x=1;" bot can succeed and you end up with an inconsistant state. -- ⚂⚃ 100% natural -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
