Re: Rewritten rows on unchanged values

On 2013-03-22, Ryan Kelly <rpkelly22(at)gmail(dot)com> 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