The following bug has been logged on the website: Bug reference: 8448 Logged by: László Rózsahegyi Email address: laszlo.rozsahe...@rool.hu PostgreSQL version: 9.3.0 Operating system: windows 7 64 bit Description:
Looping through query results exits at 10th step when * query has for update clause, and * in loop body between 1 and 10 step update - at least one step - the locked record I tested it after a fresh and clean PostgreSQL install. The configuration files left unchanged. test code (bur_report.sql): set client_encoding='UTF-8'; /* looping through query results exits at 10th step under some conditions */ create database looptest; \c looptest create sequence id_seq start 100; create table test ( id bigint not null unique default nextval('id_seq') , code varchar(3) not null , note text ); insert into test (code) values ('HUN'), ('ENG'); create type t_10 as ( num integer , note text ); select n.id, g.i from test n , generate_series(1,15) g(i) where n.code = 'HUN' order by 2 ; /* The results are 15 rows */ create or replace function update10() returns setof t_10 language plpgsql volatile security definer as $BODY$ declare lRec record; lSor t_10; begin for lRec in select n.id, g.i from test n , generate_series(1,15) g(i) /* 15 > 10 */ where n.code = 'HUN' order by 2 for update of n /* bug part 1 */ loop lSor.num = lRec.i; lSor.note = lRec.id::text || '-' || lRec.i::text; /* exit loop after 10th step when update locked record in 1..10 step otherwise returns all 15 record (example condition is lRec.i > 10 ) */ if lRec.i = 1 then update test set note = lSor.note where id = lRec.id; /* bug part 2 */ end if; return next lSor; end loop; return; end; $BODY$ ; select * from update10(); /* The results are 10 records */ \c postgres drop database looptest; -- end code The last query results are 10 records. I expected 15 records, like the other query. I tested the code in windows 7 command prompt: C:\temp>"c:\Program Files\PostgreSQL\9.3\bin\psql.exe" -Upostgres -h127.0.0.1 -p5557 -f bug_report.sql Password for user postgres: SET CREATE DATABASE You are now connected to database "looptest" as user "postgres". CREATE SEQUENCE CREATE TABLE INSERT 0 2 CREATE TYPE id | i -----+---- 100 | 1 100 | 2 100 | 3 100 | 4 100 | 5 100 | 6 100 | 7 100 | 8 100 | 9 100 | 10 100 | 11 100 | 12 100 | 13 100 | 14 100 | 15 (15 rows) CREATE FUNCTION num | note -----+-------- 1 | 100-1 2 | 100-2 3 | 100-3 4 | 100-4 5 | 100-5 6 | 100-6 7 | 100-7 8 | 100-8 9 | 100-9 10 | 100-10 (10 rows) You are now connected to database "postgres" as user "postgres". DROP DATABASE C:\temp> -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs