Yuri B. Lukyanov wrote:
I have table:
and function:
But this thing don't work:
UPDATE test SET text2='test' WHERE id = (SELECT test1());
(rows affected: 0)
Why? There is two updates on the same row, but work only first update
(in the function). Maybe it's bug?
Hmm - PostgreSQL has a transaction-counter that is used to track which
rows your current command can see.
I think the function is incrementing the transaction ID of the row your
main update is trying to access. So - after getting our "2" to compare
"id" to there is no matching row *visible to the original transaction
ID*. So - it finds no matches and does no update.
I'm not sure it's sensible to have the update in the WHERE clause - I
don't know that you can depend on how many times that function will be
called.
On the other hand, I wouldn't like to say this is the right behaviour -
I'm cc:ing this to the hackers list so they can take a look at it.
PS - I used the following to test.
BEGIN;
CREATE TABLE foo (a int4, b text);
INSERT INTO foo VALUES (1,'aaa');
INSERT INTO foo VALUES (2,'bbb');
CREATE TABLE bar (a int4, b text);
INSERT INTO bar VALUES (1,'ccc');
INSERT INTO bar VALUES (2,'ddd');
CREATE FUNCTION foo_func() RETURNS int4 AS '
BEGIN
UPDATE foo SET b = b || ''X'' WHERE a = 2;
UPDATE bar SET b = b || ''X'' WHERE a = 2;
RETURN 2;
END;
' LANGUAGE plpgsql;
-- UPDATE foo SET b = b || 'Y' WHERE a <= (SELECT foo_func());
UPDATE foo SET b = b || 'Y' WHERE a <= (SELECT foo_func());
SELECT * FROM foo;
SELECT * FROM bar;
ROLLBACK;
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
