Strange transaction-id behaviour? (was Re: Two updates problem)

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