On 03/22/2013 06:41 AM, Ryan Kelly 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.
I don't have the answer but Tom Lane does, from:
http://postgresql.1045698.n5.nabble.com/GENERAL-Update-on-tables-when-the-row-doesn-t-change-td1844002.html
"Because testing for this would almost surely be a net loss for the vast
majority of applications. Checking to see if the new row value exactly
equals the old is hardly a zero-cost operation; if you pay that on every
update, that's a lot of overhead that you are hoping to make back by
sometimes avoiding the physical store of the new tuple. In most
applications I think the "sometimes" isn't going to be often enough
to justify doing it.
If you have a particular table in a particular app where it is worth it,
I'd recommend writing a BEFORE UPDATE trigger to make the comparisons
and suppress the update when NEW and OLD are equal. "
Perhaps you could provide an example where an replacing the tuple would
be required in the presence of multiple transactions?
I am not sure what you are asking above?
-Ryan Kelly
--
Adrian Klaver
adrian.klaver@xxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
