SQL spec/implementation question: UPDATE

From: Kevin Hunter <hunteke(at)earlham(dot)edu>
To: Postgres General List <pgsql-general(at)postgresql(dot)org>
Subject: SQL spec/implementation question: UPDATE
Date: 2007-10-21 21:16:23
Message-ID: 471BC1A7.1080401@earlham.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bob Pawley 2007-10-21 21:32:21 Select Command
Previous Message Tom Lane 2007-10-21 21:14:09 Re: looking for some real world performance numbers