I said:
> Not necessarily. It looks to me like someone is forgetting to do a
> CommandCounterIncrement() between plpgsql statements.
It's worse than that: someone is caching an out-of-date command counter
value.
Load the attached variant of Tatsuo's script, and then do this:
regression=# SELECT myftest(1);
NOTICE: i 1 ctid (0,30) xmin 5687 xmax 0 cmin 2 cmax 0
NOTICE: i 2 ctid (0,31) xmin 5687 xmax 0 cmin 4 cmax 0myftest
--------- 0
(1 row)
regression=# SELECT myftest(1);
NOTICE: i 1 ctid (0,32) xmin 5688 xmax 0 cmin 1 cmax 0myftest
--------- 0
(1 row)
regression=#
Neat eh? What happened to the i=2 line? If you start a fresh backend,
the first execution of the function works.
regards, tom lane
DROP TABLE t1;
CREATE TABLE t1 (i INT PRIMARY KEY);
insert into t1 values(1);
DROP FUNCTION myftest(INT);
CREATE FUNCTION myftest(INT)
RETURNS INT
AS 'DECLARE myid INT;DECLARE rec RECORD;key ALIAS FOR $1;BEGIN UPDATE t1 SET i = 1 WHERE i = 1;INSERT INTO t1 VALUES
(2); FOR rec IN SELECT i,ctid,xmin,xmax,cmin,cmax from t1 LOOP RAISE NOTICE ''i % ctid % xmin % xmax % cmin % cmax
%'',rec.i,rec.ctid,rec.xmin,rec.xmax,rec.cmin,rec.cmax; END LOOP; SELECT INTO myid i FROM t1 WHERE i = (SELECT i
FROMt1 WHERE i = 1);DELETE FROM t1 WHERE i = 2; RETURN 0; END;'LANGUAGE 'plpgsql';