PostgreSQL logs filling up file system due to duplicate key error
| От | Selva manickaraja |
|---|---|
| Тема | PostgreSQL logs filling up file system due to duplicate key error |
| Дата | |
| Msg-id | BANLkTimQ_mqPq0iFc-Xqzubzz81AtvGrLA@mail.gmail.com обсуждение исходный текст |
| Ответы |
Re: PostgreSQL logs filling up file system due to duplicate key error
(Scott Ribe <scott_ribe@elevated-dev.com>)
Re: PostgreSQL logs filling up file system due to duplicate key error (Jesper Krogh <jesper@krogh.cc>) |
| Список | 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
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
В списке pgsql-admin по дате отправления:
Следующее
От: Scott RibeДата:
Сообщение: Re: PostgreSQL logs filling up file system due to duplicate key error