odd behavior: function not atomic/not seeing it's own updates/not acting serializable nor read committed

fwiw this was tested on 7.2 and 7.4 devel.

test case:
DROP TABLE locktest;
CREATE TABLE locktest (a INTEGER, c TIMESTAMPTZ DEFAULT now() NOT NULL);
INSERT INTO locktest VALUES (0);

CREATE OR REPLACE FUNCTION locktest1(integer) RETURNS INTEGER AS '
DECLARE
x INTEGER :=1;
y ALIAS FOR $1;
foo INTEGER;
BEGIN
LOCK TABLE locktest IN EXCLUSIVE MODE;
INSERT INTO locktest(a) VALUES (y);
LOOP
x:= x + 1;
EXIT WHEN x >= 1000000;
END LOOP;
INSERT INTO locktest(a) VALUES (y+1);
SELECT a FROM locktest ORDER BY c ASC LIMIT 1 INTO foo;
RAISE NOTICE \'original entry in column a is %\', foo;
UPDATE locktest SET a=a+y;
SELECT a FROM locktest ORDER BY c ASC LIMIT 1 INTO foo;
RAISE NOTICE \'original entry in column a is now %\', foo;
return y;
END;
' language 'plpgsql';

In two separate windows, I select the function locktest at (roughly) the
same time:

window#1
foo=# select locktest1(10);
NOTICE: original column a is 0
NOTICE: original column a is 10
locktest1
-----------
10
(1 row)

foo=#

window#2
foo=# select locktest1(20);
NOTICE: original column a is 0
NOTICE: original column a is 0
locktest1
-----------
20
(1 row)

foo=#
foo=# select * from locktest order by c;
a | c
----+-------------------------------
30 | 2002-12-16 16:37:15.728062-05
20 | 2002-12-16 16:37:23.067412-05
21 | 2002-12-16 16:37:23.067412-05
40 | 2002-12-16 16:37:25.802827-05
41 | 2002-12-16 16:37:25.802827-05
(5 rows)

foo=#

internally the second function gives conflicting results: it never sees
the updated information for the first entry (at 15 seconds into the
minute) as far as the select statements are concerned as evidenced by
the raise notices both being 0. In fact, it also does not see the
changes of it's own update command, even though the first function call
at least saw that. However on some level it does see the actual updated
information from the first function, because the initial entry ends up
as 30 (0 + 10 + 20). Furthermore, concurrency between the two is broken
because while the second function sees the update changes made by the
first function and updates them accordingly, it doesn't see the insert
changes made by the first function, and so they are not updated.

"this is broken on so many levels..."

If functions ran like they were in read committed mode, then my final
resutset should look like this:

a | c
----+-------------------------------
30 | 2002-12-16 16:43:02.374575-05
40 | 2002-12-16 16:43:11.640649-05
41 | 2002-12-16 16:43:11.640649-05
40 | 2002-12-16 16:43:16.705811-05
41 | 2002-12-16 16:43:16.705811-05
(5 rows)

since the second function call would wait for the first call to finish
before attempting to make changes, would see all three records in the
resultset of the first function, and would update all of those records
accordingly.

OTOH if functions ran as if they we're in serializable mode, the second
function would, upon attempt to update the first record, see that the
record was already updated, and throw a "ERROR: Can't serialize access
due to concurrent update", which could then be dealt with accordingly.

As it stands now we have a bastardization of the two transaction levels.

Robert Treat