Updating row with updating function, bug or feature?

[Thomas Jacob <jacob@xxxxxxxxxxxxx>]

Hello List,

I've run into some weirdness in PSQL 8.3.8 (Ubuntu 8.04 LTS x86_64
package). When I update a row while using a function result
that updates that very same row in the "WHERE" part of the update,
the main update no longer takes place, even though the "WHERE"
conditions should match. But if I execute
the function before the update, and then do the update
based on the same logic, I see both changes.

Is this a bug, a feature or something else entirely?
Please CC replies to me as well, as I  am not on the list.

The following script illustrates the problem:

== SCRIPT ==

BEGIN;

CREATE TABLE test
(
        id INTEGER PRIMARY KEY,
        locked BOOLEAN DEFAULT FALSE,
        accessed TIMESTAMP WITH TIME ZONE
);

CREATE OR REPLACE FUNCTION lock(INTEGER) RETURNS BOOLEAN AS
$$
BEGIN
        UPDATE test SET locked=TRUE WHERE
        id = $1 AND NOT locked;
        RAISE NOTICE 'lock: % -> %', $1, FOUND;
        RETURN FOUND;
END;
$$
LANGUAGE plpgsql VOLATILE;

INSERT INTO test (id) VALUES(1);
INSERT INTO test (id) VALUES(2);

SELECT 'accessed is not set';

UPDATE test SET accessed=now() WHERE id=1 AND CASE WHEN id=1 THEN
lock(1) ELSE FALSE END;
SELECT * FROM test;

SELECT 'accessed is set';

SELECT lock(2);

UPDATE test SET accessed=now() WHERE id=2 AND locked;
SELECT * FROM test;


ROLLBACK;

== END SCRIPT ==

== OUTPUT ==

CREATE TABLE
CREATE FUNCTION
INSERT 0 1
INSERT 0 1
      ?column?
---------------------
 accessed is not set
(1 row)

psql:bugfeat.sql:26: NOTICE:  lock: 1 -> t
UPDATE 0
 id | locked | accessed
----+--------+----------
  2 | f      |
  1 | t      |
(2 rows)

    ?column?
-----------------
 accessed is set
(1 row)

psql:bugfeat.sql:31: NOTICE:  lock: 2 -> t
 lock
------
 t
(1 row)

UPDATE 1
 id | locked |           accessed
----+--------+-------------------------------
  1 | t      |
  2 | t      | 2009-09-30 15:27:20.497355+02
(2 rows)

ROLLBACK

== END OUTPUT ==

 Thanks & Regards,
     Thomas


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general