From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | "Yuri B(dot) Lukyanov" <snaky(at)ulstu(dot)ru> |
Cc: | pgsql-general(at)postgresql(dot)org, Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Strange transaction-id behaviour? (was Re: Two updates problem) |
Date: | 2005-06-09 07:52:40 |
Message-ID: | 42A7F548.8020807@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Richard Huxton | 2005-06-09 07:58:34 | Re: how do you set foriegn keys in pgaccess? |
Previous Message | jeremy ` | 2005-06-09 05:22:09 | how do you set foriegn keys in pgaccess? |
From | Date | Subject | |
---|---|---|---|
Next Message | Bernd Helmle | 2005-06-09 09:54:50 | Re: Request for Comments: ALTER [OBJECT] SET SCHEMA |
Previous Message | Michael Glaesemann | 2005-06-09 07:09:05 | Re: interval->day AdjustIntervalForTypmod? |