Thread: Postgres 7.2 - Updating rows in cursor problem

Postgres 7.2 - Updating rows in cursor problem

From
Vaclav Kulakovsky
Date:
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