Received: from maia.hub.org (maia-3.hub.org [200.46.204.243]) by mail.postgresql.org (Postfix) with ESMTP id 87956B5DBD5 for ; Sat, 28 May 2011 10:19:37 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.243]) (amavisd-maia, port 10024) with ESMTP id 84215-05 for ; Sat, 28 May 2011 13:19:29 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-yx0-f174.google.com (mail-yx0-f174.google.com [209.85.213.174]) by mail.postgresql.org (Postfix) with ESMTP id 1D8BFB5D80A for ; Sat, 28 May 2011 10:19:28 -0300 (ADT) Received: by yxs7 with SMTP id 7so1005669yxs.19 for ; Sat, 28 May 2011 06:19:29 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:date:message-id:subject:from:to :content-type; bh=WCMfiKEITEZTWmo/FRAR4a0wlPUZ2fnR4HKmKayhdBo=; b=usHy3fCaIFpwA/6XEV1N4YRi50fQ1n3n/9PD/WIumimlSSE/sPGSqIKBlDgbZQlOXy ENDW83r/9xUMSSYiGDEcBmOP6ifqxGUg4vJHvF/qycsW2pobI175WG17/KAPVGu4k8Vy k+s5WMKTADkktXOEy2SWudzIQw4wNKvPnSfIk= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:date:message-id:subject:from:to:content-type; b=tRgmhaqC0MTaEB+JZq/+V5J+5o+bJbipS+NtTjyiG2BDfCfe2Mh2nADdvf5WUH63oz 9+BYt68L62MMkSP2aZ8P5c6Sx3FZ8qst2+UXliyHT7824cNVAQpB+MSuDDIxnJDgO8IA SucFOtA0WBRoc0oK1Pf77HA/Lq9Z9moo0wyGk= MIME-Version: 1.0 Received: by 10.236.157.1 with SMTP id n1mr1861659yhk.180.1306588769029; Sat, 28 May 2011 06:19:29 -0700 (PDT) Received: by 10.236.108.139 with HTTP; Sat, 28 May 2011 06:19:28 -0700 (PDT) Date: Sat, 28 May 2011 21:19:28 +0800 Message-ID: Subject: PostgreSQL logs filling up file system due to duplicate key error From: Selva manickaraja To: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary=20cf30426fc672100204a455e787 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.334 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_ENVFROM_END_DIGIT=1.553, FREEMAIL_FROM=0.001, HTML_MESSAGE=0.001, RFC_ABUSE_POST=0.001, T_TO_NO_BRKTS_FREEMAIL=0.01 X-Spam-Level: X-Archive-Number: 201105/174 X-Sequence-Number: 37611 --20cf30426fc672100204a455e787 Content-Type: text/plain; charset=ISO-8859-1 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 --20cf30426fc672100204a455e787 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable
Dear All,

We have put PostgreSQ= L9.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 s= ame incident happened. Now since it is our production DB, we are abit worri= ed.

A duplicate key violation occures in the one of these table. We are sti= ll 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. Bu= t the worrying part is, when we hit this error, PgSQL throws an error and d= oesn'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 cre= ated and fills up the filesystem. Eventually this causes the DB to shut dow= n 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 d= uplicate key error, the following line keeps on repeating in an infinite lo= op.

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

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


Yo= u kind help and assistance highly appreciated.

Thank you.

War= mest Regards,

Selvam
--20cf30426fc672100204a455e787--