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