Hi,
PostgreSQL version:
-------------------------------------------
8.0-beta1 (windows)installed as a service.
Server Platform:
--------------------------------------------
Windows XP professional SP1
Intel 2,4 Ghz/512 Mb
General test situation:
--------------------------------------------
A stresstest for my company's Client-Server product.
I simulates quite realisticly heavy use.
Roughly equal to : http://research.rem.nl/performance.html ,but with 1000
sessions.
The test completed, showing quite good performance, but errors where
reported about INSERT statements.
Initial setup
--------------------------------------------
Installation of PostgreSQL on WinXP: Flawless
Installation of Database (Tables/fk's/views/users etc): Flawless Connect
with regular Clients software: Flawless Insertion of lots of records from
production database: Flawless
Relevant database info:
--------------------------------------------
CREATE TABLE tbverantwoording
(
dnkey serial NOT NULL,
dnkeyaanstelling int4 NOT NULL,
dnjaarweek int4 NOT NULL,
dnkeyactiviteitenschema int4 NOT NULL,
dvmuteerder varchar(50),
dvrekeningnummer varchar(60),
dfuren_ma float8,
dfuren_di float8,
dfuren_wo float8,
dfuren_do float8,
dfuren_vr float8,
dfuren_za float8,
dfuren_zo float8,
dnkeycode1 int4,
dnkeycode2 int4,
dnkeycode3 int4,
dfaantal_in_ma float8,
dfaantal_in_di float8,
dfaantal_in_wo float8,
dfaantal_in_do float8,
dfaantal_in_vr float8,
dfaantal_in_za float8,
dfaantal_in_zo float8,
dfaantal_uit_ma float8,
dfaantal_uit_di float8,
dfaantal_uit_wo float8,
dfaantal_uit_do float8,
dfaantal_uit_vr float8,
dfaantal_uit_za float8,
dfaantal_uit_zo float8,
dvopmerkingen varchar(200),
CONSTRAINT pk_tbverantw PRIMARY KEY (dnkey),
CONSTRAINT fk_tbverantw_activschema FOREIGN KEY (dnkeyactiviteitenschema)
REFERENCES tbactiviteitenschema (dnkey) ON UPDATE NO ACTION ON DELETE NO
ACTION,
CONSTRAINT fk_tbverantw_code1 FOREIGN KEY (dnkeycode1) REFERENCES tbcode
(dnkey) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_tbverantw_code2 FOREIGN KEY (dnkeycode2) REFERENCES tbcode
(dnkey) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_tbverantw_code3 FOREIGN KEY (dnkeycode3) REFERENCES tbcode
(dnkey) ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT fk_tbverantw_weken FOREIGN KEY (dnkeyaanstelling, dnjaarweek)
REFERENCES tbweken (dnkeyaanstelling, dnjaarweek) ON UPDATE NO ACTION ON
DELETE NO ACTION
)=20
WITH OIDS;
The problem:
--------------------------------------------
These statements:
insert into "tbverantwoording" ("dnkeyaanstelling", "dnjaarweek",
"dnkeyactiviteitenschema") values (27, 200343, 1781); insert into
"tbverantwoording" ("dnkeyaanstelling", "dnjaarweek",
"dnkeyactiviteitenschema") values (27, 200343, 1780);
Reported these errors:
psql:sqlfiles/0.sql:241: ERROR: duplicate key violates unique constraint
"pk_tbverantw"
psql:sqlfiles/0.sql:223: ERROR: duplicate key violates unique constraint
"pk_tbverantw"
BUT ONLY DURING HIGH LOAD, 717 errors where reported, for 2000 inserts.
After the test i can run the INSERT queries, no problem. This did never
occur during any testing on PostgreSQL 7.1, 7.2(cygwin) 7.3 on linux. This
stresstest is a standard procedure for all our customers who use PostgreSQL
as their database. It seems to me the sequence for the SERIAL dnkey, primary
key constraint has a problem.
Greeting from Amsterdam, The Netherlands,
Joek Hondius