Skip site navigation (1) Skip section navigation (2)

Bug #629: "for ... select ... loop" repeat first 9 rows

From: pgsql-bugs(at)postgresql(dot)org
To: pgsql-bugs(at)postgresql(dot)org
Subject: Bug #629: "for ... select ... loop" repeat first 9 rows
Date: 2002-04-04 17:48:16
Message-ID: 20020404174816.D8A66475EA1@postgresql.org (view raw or flat)
Thread:
Lists: pgsql-bugs
Michael V. Reztsov (ximeric(at)mail(dot)ru) reports a bug with a severity of 2
The lower the number the more severe it is.

Short Description
"for ... select ... loop" repeat first 9 rows

Long Description
Hello!
Sorry for bad English.

PostgreSQL 7.2, Redhat Linux 7.1

Problem:
In pl/pgsql, when table has 9 or less records, iterating through simple
"select * from table" with simultaneously update gives normal results.
But if table has 10 and more records, iterating repeat first 9 rows.
If "select" statement contains "order by", iterating gives normal results
with any rows amount.

Test:
1. createdb testdb
2. psql testdb
3.      \i bugtest.pgsql       -- example code
4.      SELECT bugtest();
5.      INSERT INTO groups VALUES ('group10');
6.      SELECT bugtest();
7. In "bugtest" function, comment first "for ... loop" line, uncomment second "for ... loop" line
8.      \i bugtest.pgsql
9.      SELECT bugtest();
10.     INSERT INTO groups VALUES ('group10');
11.     SELECT bugtest();


Sample Code
DROP TABLE groups;
CREATE TABLE groups (
    groupname text,
    rel_id integer
);

COPY "groups"  FROM stdin;
group01 \N
group02 \N
group03 \N
group04 \N
group05 \N
group06 \N
group07 \N
group08 \N
group09 \N
\.

DROP FUNCTION bugtest();
CREATE FUNCTION bugtest() RETURNS BOOLEAN AS '
DECLARE
    _group RECORD;
    _count INTEGER;
    i INTEGER := 0;
BEGIN
    SELECT count(*) into _count from groups;
    FOR _group IN SELECT * FROM groups LOOP     -- buggy without "ORDER BY"
    --FOR _group IN SELECT * FROM groups ORDER BY groupname LOOP     -- normal
        i := i + 1;
        raise notice ''N. = %, name = %'', i, _group.groupname;
        UPDATE groups SET rel_id = 1 WHERE groupname = _group.groupname;
    END LOOP;
    RETURN i = _count;
END;
' LANGUAGE 'PLPGSQL';


No file was uploaded with this report


Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2002-04-04 19:33:50
Subject: Re: Bug #629: "for ... select ... loop" repeat first 9 rows
Previous:From: Stephan SzaboDate: 2002-04-04 16:37:21
Subject: Re: a problem with case in psql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group