BUG #8448: looping through query results exits at 10th step under some conditions - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      8448
Logged by:          László Rózsahegyi
Email address:      laszlo.rozsahegyi@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>