Sequence problem in stresstest on 8.0-beta1 (windows) - Mailing list pgsql-bugs

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