Re: PL/pgSQL bug?

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 0
myftest
---------
0
(1 row)

regression=# SELECT myftest(1);
NOTICE: i 1 ctid (0,32) xmin 5688 xmax 0 cmin 1 cmax 0
myftest
---------
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 FROM t1 WHERE i = 1);
DELETE FROM t1 WHERE i = 2;
RETURN 0;
END;
'
LANGUAGE 'plpgsql';