general description:
--------------------
I've a problem in PG 7.2. If you update rows which are included in plpgsql
RECORD , updated rows are again added to the RECORD, so you will get into
infinite loop.
details:
--------
- 7.2 problem
- problem in PLPGSQL
- problem arise only if count in RECORD exceed some number of rows
- problem arise only if RECORD is not sorted
question:
---------
Is it feature or bug ?
example:
--------
If you execute this script, you will enter to infinite loop. If you add
"order by a" it will work fine.
------------<< cut here << ---------------------
create sequence tmp_seq;
create table tmp_test ( a int4 default nextval('tmp_seq'), b int4 );
insert into tmp_test (b) values( 1 );
insert into tmp_test (b) values( 1 );
insert into tmp_test (b) values( 1 );
insert into tmp_test (b) select b from tmp_test;
insert into tmp_test (b) select b from tmp_test;
insert into tmp_test (b) select b from tmp_test;
insert into tmp_test (b) select b from tmp_test;
insert into tmp_test (b) select b from tmp_test;
select count(*) from tmp_test;
drop function ftmp_test();
create function ftmp_test() RETURNS varchar AS'
DECLARE
_grp varchar;
sql varchar;
R RECORD;
i integer;
BEGIN
i = 0;
FOR R IN select * from tmp_test
LOOP
i = i + 1;
if i%100 = 0 then
raise notice ''% - val: %'', i, R.a;
end if;
UPDATE tmp_test SET a=1000 WHERE a = R.a;
END LOOP;
RETURN '''';
END;' LANGUAGE 'plpgsql';
select ftmp_test();
drop table tmp_test;
------------<< cut here << --------------------
Vaclav Kulakovsky
DEFINITY Systems, s.r.o.
Tyrsova 2071
25601 Benesov
Czech Republic
Tel: +420 301 727975,724456
Fax: +420 301 724456
vaclav.kulakovsky@definity.cz
http://www.definity.cz
