From: | Tatsuo Ishii <t-ishii(at)sra(dot)co(dot)jp> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | PL/pgSQL bug? |
Date: | 2001-08-10 06:20:45 |
Message-ID: | 20010810152045A.t-ishii@sra.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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.
--
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';
From | Date | Subject | |
---|---|---|---|
Next Message | Anatoly K. Lasareff | 2001-08-10 06:21:19 | Re: OpenFTS (Open Source Full Text Search engine) pre-announce |
Previous Message | Thomas Lockhart | 2001-08-10 04:19:13 | Re: contrib/postgis spatial extensions |