From: Selva manickaraja <mavles78@gmail.com>
To: pgsql-admin@postgresql.org
Subject: PostgreSQL logs filling up file system due to duplicate key error
Date: Sat, 28 May 2011 21:19:28 +0800
Message-ID: <BANLkTimQ_mqPq0iFc-Xqzubzz81AtvGrLA@mail.gmail.com> (raw)
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: mavles78@gmail.com
Subject: Re: PostgreSQL logs filling up file system due to duplicate key error
In-Reply-To: <BANLkTimQ_mqPq0iFc-Xqzubzz81AtvGrLA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox