From: | Vaclav Kulakovsky <vaclav(dot)kulakovsky(at)definity(dot)cz> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Postgres 7.2 - Updating rows in cursor problem |
Date: | 2002-02-13 08:47:49 |
Message-ID: | Pine.WNT.4.44.0202130924320.4620-100000@bnw02n |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general pgsql-hackers pgsql-patches |
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(dot)kulakovsky(at)definity(dot)cz
http://www.definity.cz
From | Date | Subject | |
---|---|---|---|
Next Message | Jean-Michel POURE | 2002-02-13 09:39:35 | ALTER / DROP information for pgAdmin2 |
Previous Message | Martijn van Oosterhout | 2002-02-13 08:35:57 | Re: Database Performance? |
From | Date | Subject | |
---|---|---|---|
Next Message | Hiroshi Inoue | 2002-02-13 08:48:33 | Re: alter table drop column status |
Previous Message | Dave Page | 2002-02-13 08:30:37 | Re: alter table drop column status |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2002-02-13 19:47:24 | Re: [GENERAL] Postgres 7.2 - Updating rows in cursor problem |
Previous Message | Fernando Nasser | 2002-02-12 16:19:40 | Allow arbitrary levels of analyze/rewriting |