Postgres 7.2 - Updating rows in cursor problem
От | Vaclav Kulakovsky |
---|---|
Тема | Postgres 7.2 - Updating rows in cursor problem |
Дата | |
Msg-id | Pine.WNT.4.44.0202130924320.4620-100000@bnw02n обсуждение исходный текст |
Список | pgsql-general |
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
В списке pgsql-general по дате отправления: