Skip site navigation (1) Skip section navigation (2)

PostgreSQL logs filling up file system due to duplicate key error

From: Selva manickaraja <mavles78(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: PostgreSQL logs filling up file system due to duplicate key error
Date: 2011-05-28 13:19:28
Message-ID: BANLkTimQ_mqPq0iFc-Xqzubzz81AtvGrLA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-admin
 Dear All,

We have put PostgreSQL9.0 with replication as production database for the
past 2 months for our Conventional system. After a good 2 months, we are now
planning to migrate our Logistics system which is in Informix. During the
last 2 months we had an incident occuring twice on the 14th of April and
May. This 27 May this same incident happened. Now since it is our production
DB, we are abit worried.

A duplicate key violation occures in the one of these table. We are still
unsure why it happens as we tried simulating the scenario, but we do not hit
the same error. We are very sure it has to do with the application. But the
worrying part is, when we hit this error, PgSQL throws an error and doesn't
stop there. It keeps on repeating the error non-stop in the log. The worse
this is it doesn't stop until thousands of log files are created and fills
up the filesystem. Eventually this causes the DB to shut down automatically
cause the partition under "pgsql/data/" is filled up!!!!!

Though we know this is due to an application bug, the db should not be
repeating it non-stop until halting to an end. The listing below in a short
extract of the thousands of lines from thousands of log files. After the
duplicate key error, the following line keeps on repeating in an infinite
loop.

2011-05-27 10:28:33 MYT br np [local] [unknown] ERROR:  current transaction
is aborted, commands ignored until end of transaction block
2011-05-27 10:28:33 MYT br np [local] [unknown] STATEMENT:  insert into
cgo_dtl
values($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20)
2011-05-27 10:28:33 MYT br np [local] [unknown] ERROR:  current transaction
is aborted, commands ignored until end of transaction block
2011-05-27 10:28:33 MYT br np [local] [unknown] STATEMENT:  fetch next from
cu9

For your information our application is written in a 4GL like language
called Genero.

Please advise as to how we can solve this problem. We are worried that this
error could happen over the weekend and without our knowledge the db may
just show down again.

2011-05-27 09:23:20 MYT br np [local] [unknown] LOG:  unexpected EOF on
client connection
2011-05-27 09:31:11 MYT br np [local] [unknown] LOG:  unexpected EOF on
client connection
2011-05-27 10:14:18 MYT br np [local] [unknown] LOG:  unexpected EOF on
client connection
2011-05-27 10:28:33 MYT br np [local] [unknown] ERROR:  duplicate key value
violates unique constraint "cgo_dtl_voy_cd_key"
2011-05-27 10:28:33 MYT br np [local] [unknown] DETAIL:  Key (voy_cd, bl_no,
ln_no)=(XG7L   , NGPKS1014      , 1) already exists.
2011-05-27 10:28:33 MYT br np [local] [unknown] STATEMENT:  insert into
cgo_dtl
values($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20)
2011-05-27 10:28:33 MYT br np [local] [unknown] ERROR:  current transaction
is aborted, commands ignored until end of transaction block
2011-05-27 10:28:33 MYT br np [local] [unknown] STATEMENT:  fetch next from
cu9
2011-05-27 10:28:33 MYT br np [local] [unknown] ERROR:  current transaction
is aborted, commands ignored until end of transaction block
2011-05-27 10:28:33 MYT br np [local] [unknown] STATEMENT:  insert into
cgo_dtl
values($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20)
2011-05-27 10:28:33 MYT br np [local] [unknown] ERROR:  current transaction
is aborted, commands ignored until end of transaction block
2011-05-27 10:28:33 MYT br np [local] [unknown] STATEMENT:  fetch next from
cu9
2011-05-27 10:28:33 MYT br np [local] [unknown] ERROR:  current transaction
is aborted, commands ignored until end of transaction block
2011-05-27 10:28:33 MYT br np [local] [unknown] STATEMENT:  insert into
cgo_dtl
values($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20)
2011-05-27 10:28:33 MYT br np [local] [unknown] ERROR:  current transaction
is aborted, commands ignored until end of transaction block
2011-05-27 10:28:33 MYT br np [local] [unknown] STATEMENT:  fetch next from
cu9
2011-05-27 10:28:33 MYT br np [local] [unknown] ERROR:  current transaction
is aborted, commands ignored until end of transaction block
2011-05-27 10:28:33 MYT br np [local] [unknown] STATEMENT:  insert into
cgo_dtl
values($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20)
2011-05-27 10:28:33 MYT br np [local] [unknown] ERROR:  current transaction
is aborted, commands ignored until end of transaction block
2011-05-27 10:28:33 MYT br np [local] [unknown] STATEMENT:  fetch next from
cu9
2011-05-27 10:28:33 MYT br np [local] [unknown] ERROR:  current transaction
is aborted, commands ignored until end of transaction block
2011-05-27 10:28:33 MYT br np [local] [unknown] STATEMENT:  insert into
cgo_dtl
values($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12,$13,$14,$15,$16,$17,$18,$19,$20)
2011-05-27 10:28:33 MYT br np [local] [unknown] ERROR:  current transaction
is aborted, commands ignored until end of transaction block
2011-05-27 10:28:33 MYT br np [local] [unknown] STATEMENT:  fetch next from
cu9


You kind help and assistance highly appreciated.

Thank you.

Warmest Regards,

Selvam

Responses

pgsql-admin by date

Next:From: Scott RibeDate: 2011-05-28 13:23:57
Subject: Re: PostgreSQL logs filling up file system due to duplicate key error
Previous:From: M. D.Date: 2011-05-28 06:37:08
Subject: library problem with psql

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group