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

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

From
pgsql-bugs@postgresql.org
Date:
Michael V. Reztsov (ximeric@mail.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

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

From
Tom Lane
Date:
pgsql-bugs@postgresql.org writes:
> "for ... select ... loop" repeat first 9 rows

This is fixed in 7.2.1 --- at least, I don't see anything wrong
when trying your example.

            regards, tom lane