Updating row with updating function, bug or feature?

Поиск
Список
Период
Сортировка
Искать
От
Thomas Jacob
Тема
Updating row with updating function, bug or feature?
Дата
Msg-id
1254317415.11386.12.camel@enterprise.ims-firmen.de
Список
Дерево обсуждения
Updating row with updating function, bug or feature? Thomas Jacob <jacob@internet24.de>
Re: Updating row with updating function, bug or feature? Tom Lane <tgl@sss.pgh.pa.us>
Re: Updating row with updating function, bug or feature? Thomas Jacob <jacob@internet24.de>
error message and documentation Ivano Luberti <luberti@archicoop.it>
Re: error message and documentation Tom Lane <tgl@sss.pgh.pa.us>
Re: error message and documentation Ivano Luberti <luberti@archicoop.it>
Re: error message and documentation Tom Lane <tgl@sss.pgh.pa.us>
Re: error message and documentation Ivano Luberti <luberti@archicoop.it>
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

В списке pgsql-general по дате отправления
От: Alban Hertroys
Дата:
От: Tom Lane
Дата:
FAQ