100% CPU at concurent access - Mailing list pgsql-sql

Hi there,

I'm trying to solve the concurrent access in my database, but I found some 
problems.

I use "PostgreSQL 8.2.4 on i686-pc-mingw32, compiled by GCC gcc.exe (GCC) 
3.4.2 (mingw-special)";

I create the following scenario:
- use an empty database
- create the following table:
               CREATE TABLE "TestTable"               (                 "ID" integer NOT NULL,
CONSTRAINT"TestTable_pkey" PRIMARY KEY ("ID")               )               WITHOUT OIDS;               ALTER TABLE
"TestTable"OWNER TO postgres;
 

- add a row in the table with
               INSERT INTO "TestTable" VALUES ( 1000 );

- create the following functions:
               CREATE OR REPLACE FUNCTION "TestProcInner"()                 RETURNS integer AS               $BODY$
         DECLARE                       Loops int4 = 10;               BEGIN                       FOR i IN 0..Loops
LOOP                              RAISE NOTICE '%',i;                               UPDATE "TestTable" SET "ID" = i;
                          PERFORM pg_sleep(1);                       END LOOP;
 
                       RAISE NOTICE 'SUCCEEDED';                       RETURN 0;
               EXCEPTION                   WHEN serialization_failure THEN                       RAISE NOTICE
'FAILED';
                       RETURN 1;               END;               $BODY$                 LANGUAGE 'plpgsql' VOLATILE;
           ALTER FUNCTION "TestProcInner"() OWNER TO postgres;
 
       and
               CREATE OR REPLACE FUNCTION "TestProcOuter"()                 RETURNS integer AS               $BODY$
         DECLARE                       Loops int4 := 1;               BEGIN                       LOOP
            RAISE NOTICE 'TestProcOuter: % loop', Loops;                               IF 0 = "TestProcInner"() THEN
                                  EXIT; -- LOOP                               END IF;
Loops= Loops + 1;                       END LOOP;
 
                       RETURN 0;               END;               $BODY$                 LANGUAGE 'plpgsql' VOLATILE;
           ALTER FUNCTION "TestProcOuter"() OWNER TO postgres;
 


I use the following procedure to check when the concurrency access occures:
- open two query windows in pgAdmin
- add the following script in the both windows:
               BEGIN TRANSACTION;               SELECT "TestProcInner"();               COMMIT;

- run the script in the 1st window
- run the script in the 2nd window
- check the results:       - the script in the 1st window commit the transaction               and write the log
message'SUCCEEDED'       - the script from the 2nd window catch an exception               and write the log message
'FAILED'


Then I try to use the following procedure to catch the concurrency access       occurence and retry until both scripts
succeed:
- open two query windows in pgAdmin
- add the following script in the both windows:
               BEGIN TRANSACTION;               SELECT "TestProcOuter"();               COMMIT;

- run the script in the 1st window
- run the script in the 2nd window
- the Postgres begins to CONSUME 100% CPU, and LOCKS until I cancel the 
connection       from other pgAdmin session
- after a few second the first window finishes with 'SUCCEEDED'
- the second window writes:
               ERROR: canceling statement due to user request

Could somebody tell me why the procedure doesn't work and the CPU is used 
100%, please ?

TIA,
Sabin