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>
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 |