Kevin Hunter wrote:
Hullo list,
A perhaps esoteric question:
Short version:
What do the specs say (if anything) about returning information from
UPDATE commands? Or about handling update request that don't
effectively do anything?
Longer version:
CREATE TABLE test (
id SERIAL NOT NULL,
name TEXT NOT NULL,
passion TEXT NOT NULL,
PRIMARY KEY( id )
);
INSERT INTO test (name, passion) VALUES ('colin', 'contra-dancing');
INSERT INTO test (name, passion) VALUES ('alex', 'contemplating');
INSERT INTO test (name, passion) VALUES ('kevin', 'soccer');
INSERT INTO test (name, passion) VALUES ('toby', 'biking');
BEGIN;
UPDATE test SET name = 'kevin' WHERE passion = 'soccer';
Previous statement 5 times (or whatever)
COMMIT;
Even though the last 5 statements effectively do nothing, every UPDATE
returns "UPDATE 1". If I do the same thing in MySQL, I get "Rows
matched: 1 Changed: 0 Warnings: 0". (I used the INNODB engine in MySQL.)
In PHP, the {pg,mysql}_affected_rows functions return the same results:
1 from Postgres and 0 from MySQL.
So, two questions: which behavior is correct, or is it even defined? If
Postgres behavior is correct, why does it need to write to disk, (since
the tuple isn't actually changing in value)?
Experience tells me that Postgres is probably doing the correct thing,
but it almost seems that it could be corner case, doesn't matter either
way, and is could be just a consequence of the MVCC guarantees, etc.
TIA,
Kevin
I think your comparing apples and oranges. I'll bet that mysql is
taking a shortcut and testing the value before updating it.
The update is probably more close to:
update test set name = 'kevin' where passion = 'soccer' and name <> 'kevin';
In this case, pg too, would only update once.
-Andy
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
