Thread: Sequence problem in stresstest on 8.0-beta1 (windows)

Sequence problem in stresstest on 8.0-beta1 (windows)

From
"J. Hondius"
Date:
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

Re: Sequence problem in stresstest on 8.0-beta1 (windows)

From
Tom Lane
Date:
"J. Hondius" <jhondius@rem.nl> writes:
> 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.

I couldn't replicate any problem.  I put 5000 copies of those two
statements into a file and set up half a dozen psql sessions
concurrently reading the file.  No errors.

If you can provide a complete, reproducible test case, I'll be glad to
look at it, but there's not enough information here to do anything ...

            regards, tom lane

Re: Sequence problem in stresstest on 8.0-beta1 (windows)

From
"J. Hondius"
Date:
Hi Tom,

Ehr...
The problem must have been the heat...
I forgot to set the sequences to the right values after inserting the data.
This is something that is done automatically in our testprocedure, but in
this windows test i forgot.

Did it the right way now, the errors are history.

-- sigh --

BTW: We (and the sales guys) are absolutely delighted with the arrival of
the native windows version!

Greetings, Joek
=20

-----Oorspronkelijk bericht-----
Van: Tom Lane [mailto:tgl@sss.pgh.pa.us]=20
Verzonden: zaterdag 14 augustus 2004 2:13
Aan: J. Hondius
CC: pgsql-bugs@postgresql.org
Onderwerp: Re: [BUGS] Sequence problem in stresstest on 8.0-beta1 (windows)


"J. Hondius" <jhondius@rem.nl> writes:
> These statements:
> insert into "tbverantwoording"  ("dnkeyaanstelling", "dnjaarweek",
> "dnkeyactiviteitenschema") values  (27, 200343, 1781); insert into=20
> "tbverantwoording"  ("dnkeyaanstelling", "dnjaarweek",
> "dnkeyactiviteitenschema") values  (27, 200343, 1780);

> Reported these errors:
> psql:sqlfiles/0.sql:241: ERROR:  duplicate key violates unique=20
> constraint "pk_tbverantw"
> psql:sqlfiles/0.sql:223: ERROR:  duplicate key violates unique=20
> constraint "pk_tbverantw"

> BUT ONLY DURING HIGH LOAD, 717 errors where reported, for 2000=20
> inserts.

I couldn't replicate any problem.  I put 5000 copies of those two statements
into a file and set up half a dozen psql sessions concurrently reading the
file.  No errors.

If you can provide a complete, reproducible test case, I'll be glad to look
at it, but there's not enough information here to do anything ...

            regards, tom lane