Обсуждение: Two updates problem

I have table:

--------------------------------------------
CREATE TABLE "public"."test" (
  "id" INTEGER,
  "text1" VARCHAR(25),
  "text2" VARCHAR(25)
) WITH OIDS;
INSERT INTO test VALUES (1, 'qwerty', '111');
INSERT INTO test VALUES (2, 'asdfgh', '222');
--------------------------------------------

and function:

--------------------------------------------
CREATE OR REPLACE FUNCTION "public"."test1" () RETURNS integer AS
$body$
BEGIN
   UPDATE test SET text1='qqq' WHERE id = 2;
RETURN 2;
END;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
--------------------------------------------

When I do
SELECT test1();
it works fine and return "2".

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?

P.S.:
I'm using PostgreSQL 8.0.1 on Win32.
Sorry for my english.

___________________________________
Best regards, Yuri B. Lukyanov
7 июня 2005 г. 22:49:28
mailto:snaky@ulstu.ru

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

Richard Huxton <dev@archonet.com> writes:
> 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.

It's absolutely not very sensible to do that ... note the warnings in
http://www.postgresql.org/docs/8.0/static/sql-expressions.html#SYNTAX-EXPRESS-EVAL
We have no way to enforce "no functions with side effects in WHERE",
but you're not going to get any sympathy at all if you break that rule.

> 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.

It is intentional.  A given command can only see/update row versions
produced by earlier commands --- without this rule, you have the
"Halloween problem" that an UPDATE can see (and try to update) its own
output rows, leading to an infinite loop.

Actually the rule is "you can see row versions produced by commands
started earlier than your own command" (cmin < current cid), which
means there is another risk involved in this sort of programming:
if the function looks at the contents of the table being updated by
the outer UPDATE, it will see the partially completed effects of the
UPDATE.  While I suppose that's exactly what Yuri was after ;-),
it's generally considered a bad thing, because there is no guarantee
as to the order in which rows are updated, and thus no predictability
as to exactly what intermediate states the function will see.

As of PG 8.0, things are set up so that this only applies to functions
marked VOLATILE; if a function is marked STABLE or IMMUTABLE then it
runs with the same cid as the calling query, and therefore it does *not*
see any partial effects of that query.

Confused yet? ;-)

            regards, tom lane