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
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
