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 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';