Received: from maia.hub.org (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id C8B106325D0 for ; Tue, 30 Mar 2010 05:24:19 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 52376-06 for ; Tue, 30 Mar 2010 08:24:04 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-wy0-f174.google.com (mail-wy0-f174.google.com [74.125.82.174]) by mail.postgresql.org (Postfix) with ESMTP id A0B6962DBD0 for ; Tue, 30 Mar 2010 05:24:08 -0300 (ADT) Received: by wyb39 with SMTP id 39so1516912wyb.19 for ; Tue, 30 Mar 2010 01:24:07 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:received:message-id:subject:from:to:cc:content-type; bh=nZwW9UkCpkSDFZUmRvmYR2jRGRshC8IA6ChLQ3ifAN4=; b=Z/X7hVosxf/heVqhS8doVG4T82TsQwdeN1y2Lf0yZQvmpAUZC4hIDJnYcq7uSvj62j Vti6OUOGBHOPdPiaz3I1cdmO86KofyrK0rMql5jWGqt51Ezdor2W26DQOK+C/CUn2Kkg KxVJH2xP+ZG8yx9yvQ1dUmgIC3sSFyMBfjkO8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=GQ5k7YLOZYT4Q55v0rmeaw1Hr00BdInwC1C+oEmRDG4AmkQsRZmzmDQFWa+pceFDuv tPXARrEK+/L777M2zUB3phB2Q+JiFbedpfgrfimXXXHJjz5MNOQ7ynPV10o/PxO2+ii1 xwupDSXxUunr5rIDgKfMI45XDA667pWO+Rr24= MIME-Version: 1.0 Received: by 10.216.0.5 with HTTP; Tue, 30 Mar 2010 01:24:07 -0700 (PDT) In-Reply-To: References: Date: Tue, 30 Mar 2010 10:24:07 +0200 Received: by 10.216.86.132 with SMTP id w4mr83954wee.87.1269937447082; Tue, 30 Mar 2010 01:24:07 -0700 (PDT) Message-ID: Subject: Re: Strange deletion problem From: Szymon Guz To: John Lister Cc: pgsql-admin@postgresql.org Content-Type: multipart/alternative; boundary=0016e6d77ed36b9ca10483005a83 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.285 tagged_above=-10 required=5 tests=AWL=0.313, BAYES_00=-2.599, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/235 X-Sequence-Number: 34034 --0016e6d77ed36b9ca10483005a83 Content-Type: text/plain; charset=UTF-8 2010/3/30 John Lister > Hi, I have a table which is constantly updated through out the day with > no problems, I'm running Postgresql 8.3.8 in ubuntu karmic. However, within > the last week for some reason overnight it is being emptied and I can't work > out why. I've set log_min_duration_statement to 0 so that postgresql dumps > out every query made to the db but it hasn't brought anything up. During the > period between from when I know the table is ok to the next morning when > it is empty I can see entries in the logs for the expected 200 or so > deletions, but during this period approx 15k rows are removed. The odd thing > is that there is nothing else in the logs that references the table (I've > looked for the obvious deletion and trunctate statements). > > The table is modified using JDBC prepared statements so I see 3 entries for > each of the expected delete statements (parse, bind, execute) and the > statement is as follows: > > delete from product_list where retailer_id=? and product_id=? > > > Therefore I have a few questions: > - Is there any other statements that could be causing the rows to be > removed that I've missed > - Is there anything that could be deleting them without generating a log > entry for the statement? > - Is it possible that data corruption of either the index/table is making > my delete statements removed more rows? > - Is it a possibly bug? > > Hi, - maybe there are some other settings for this database (they are not stored in the file), check the pg_settings table in the database - maybe you're looking in a wrong log file - sometimes do (they change from time to time - depending on the configuration) - ensure that the logging is done to file and to THIS file, because there are more logging settings than just log_min_duration and sometimes it can be messed up - try to restart the database and see if there isn't any other file created as usually I observe that after deleting current log file, the database doesn't recreate while logging so the logs are not stored. regards Szymon Guz --0016e6d77ed36b9ca10483005a83 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

2010/3/30 John Lister = <john.lister-ps@kickston= e.com>
Hi, I have a table which is constantly= updated=20 through out the day with no problems, I'm=C2=A0running=C2=A0Postgresql = 8.3.8 in=20 ubuntu karmic. However, within the last week for some reason overnight it i= s=20 being emptied and I can't work out why. I've set log_min_duration_s= tatement to 0=20 so that postgresql dumps out every query made to the db but it hasn't b= rought=20 anything up. During the period between from when I know the table is ok to = the=20 next morning when it=C2=A0is empty=C2=A0I can see entries in the logs for t= he=20 expected 200 or so deletions, but during this period approx 15k rows are=20 removed. The odd thing is that there is nothing else in the logs that refer= ences=20 the table (I've looked for the obvious deletion and trunctate=20 statements).
=C2=A0
The table is modified using JDBC prepa= red=20 statements so I see 3 entries for each of the expected delete statements (p= arse,=20 bind, execute) and the statement=C2=A0is as follows:
=C2=A0
delete from product_list where retaile= r_id=3D? and=20 product_id=3D?
=C2=A0
=C2=A0
Therefore I have a few questions:
- Is there any other statements that c= ould be=20 causing the rows to be removed that I've missed
- Is there anything that could be dele= ting them=20 without generating a log entry for the statement?
- Is it possible that data corruption = of either the=20 index/table is making my delete statements removed more rows?
- Is it a possibly bug?
=C2=A0
=

Hi,
- maybe there are some other settings for= this database (they are not stored in the file), check the pg_settings tab= le in the database
- maybe you're looking in a wrong log file - sometimes do (they ch= ange from time to time - depending on the configuration)
- ensure= that the logging is done to file and to THIS file, because there are more = logging settings than just log_min_duration and sometimes it can be messed = up
- try to restart the database and see if there isn't any other fil= e created as usually I observe that after deleting current log file, the da= tabase doesn't recreate while logging so the logs are not stored.

regards
Szymon Guz
--0016e6d77ed36b9ca10483005a83--