Re: PL/pgSQL bug?

Tatsuo Ishii wrote:
> It seems that sometimes uncommitted data (dirty data?) could be seen
> in PL/pgSQL function.
>
> Below is a sample script to reproduce the problem: If you execute
> "SELECT myftest(1)" concurrently, you will see the subselect in the
> SELECT INTO... will produce:
>
> ERROR:  More than one tuple returned by a subselect used as an expression.
>
> This is odd, since the coulum i is a primary key, and should never has
> duplicate values.
>
> If you comment out the SELECT INTO... statement, you could see a line
> something like:
>
> NOTICE:  ctid (0,5) xmin 645188 xmax 645190 cmin 2 cmax 2
>
> This is odd too, since xmax > 0 or cmax > 0 should never happen with
> visible tuples, in my understanding.
>
> I see these in 7.0.3, 7.1.2 and current.
   If  that's  the  case,  it must be a general problem with SPI   that'll apply to any procedural  language  as  well
as user   defined C function using SPI.
 
   When  scans  or  functions  are  involved,  PL/pgSQL uses SPI   functionality to evaluate the expression.


Jan

> --
> Tatsuo Ishii
>
> ----------------------------------------------------------------------
> DROP TABLE t1;
> CREATE TABLE t1 (i INT PRIMARY KEY);
> 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;
>    SELECT INTO tid,myid ctid,i FROM t1 WHERE i = (SELECT i FROM t1 WHERE i = 1);
>    FOR rec IN SELECT ctid,xmin,xmax,cmin,cmax from t1 LOOP
>        RAISE NOTICE ''ctid % xmin % xmax % cmin % cmax %'', rec.ctid,rec.xmin,rec.xmax,rec.cmin,rec.cmax;
>    END LOOP;
>    RETURN 0;
>   END;
>  '
>  LANGUAGE 'plpgsql';
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>


--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com