BUG #8448: looping through query results exits at 10th step under some conditions

From: laszlo(dot)rozsahegyi(at)rool(dot)hu
To: pgsql-bugs(at)postgresql(dot)org
Subject: BUG #8448: looping through query results exits at 10th step under some conditions
Date: 2013-09-12 14:41:01
Message-ID: E1VK84r-0004zz-33@wrigleys.postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 8448
Logged by: László Rózsahegyi
Email address: laszlo(dot)rozsahegyi(at)rool(dot)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>

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Kevin Grittner 2013-09-12 15:19:46 Re: BUG #8447: With table inheritance, indexes seems to be ignored when looking over indexed fields in base table
Previous Message stormbyte 2013-09-12 14:06:52 BUG #8447: With table inheritance, indexes seems to be ignored when looking over indexed fields in base table