From john.lister-ps@kickstone.com Tue Mar 30 07:58:06 2010 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id B45D263293E for ; Tue, 30 Mar 2010 04:58:06 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 38264-05 for ; Tue, 30 Mar 2010 07:57:56 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.kickstone.com (87-194-183-70.dsl.cnl.uk.net [87.194.183.70]) by mail.postgresql.org (Postfix) with ESMTP id 320BB6334CF for ; Tue, 30 Mar 2010 04:57:56 -0300 (ADT) Received: from 87-194-183-69.dsl.cnl.uk.net ([87.194.183.69]:2658 helo=MADRAS2) by mail.kickstone.com with esmtpa (Exim 4.69) (envelope-from ) id 1NwWKD-0002tX-Ul for pgsql-admin@postgresql.org; Tue, 30 Mar 2010 08:57:25 +0100 Message-ID: From: "John Lister" To: Subject: Strange deletion problem Date: Tue, 30 Mar 2010 08:57:52 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_00C6_01CACFE7.14D35670" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5843 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.009 tagged_above=-10 required=5 tests=BAYES_05=-1.11, HTML_MESSAGE=0.001, RDNS_DYNAMIC=0.1 X-Spam-Level: X-Archive-Number: 201003/234 X-Sequence-Number: 34033 This is a multi-part message in MIME format. ------=_NextPart_000_00C6_01CACFE7.14D35670 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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=3D? and product_id=3D? 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? Thanks John -- Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/ ------=_NextPart_000_00C6_01CACFE7.14D35670 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi, I have a table which is constantly = updated=20 through out the day with no problems, I'm running Postgresql = 8.3.8 in=20 ubuntu karmic. However, within the last week for some reason overnight = it is=20 being emptied and I can't work out why. I've set = log_min_duration_statement to 0=20 so that postgresql dumps out every query made to the db but it hasn't = brought=20 anything up. During the period between from when I know the table is ok = to the=20 next morning when it is empty I can see entries in the logs = for the=20 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=20 the table (I've looked for the obvious deletion and trunctate=20 statements).
 
The table is modified using JDBC = prepared=20 statements so I see 3 entries for each of the expected delete statements = (parse,=20 bind, execute) and the statement is as follows:
 
delete from product_list where = retailer_id=3D? and=20 product_id=3D?
 
 
Therefore I have a few = questions:
- Is there any other statements that = could be=20 causing the rows to be removed that I've missed
- Is there anything that could be = deleting 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?
 
Thanks
 
John
--
 
Got needs? Get Goblin'! - http://www.pricegoblin.co.uk/<= BR>
------=_NextPart_000_00C6_01CACFE7.14D35670-- From mabewlun@gmail.com Tue Mar 30 08:24:19 2010 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-- From julius@nsoft.lt Tue Mar 30 08:29:27 2010 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 8C6766325D0 for ; Tue, 30 Mar 2010 05:29:27 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 44437-06 for ; Tue, 30 Mar 2010 08:29:16 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.csc.lt (mail.csc.lt [193.138.191.5]) by mail.postgresql.org (Postfix) with ESMTP id 5FCD662DBD0 for ; Tue, 30 Mar 2010 05:29:15 -0300 (ADT) Received: (qmail 15667 invoked by uid 210); 30 Mar 2010 08:29:12 -0000 Received: from mail.nsoft.lt by mail (envelope-from , uid 201) with qmail-scanner-2.05st (clamdscan: 0.95.3/10658. spamassassin: 3.2.1. perlscan: 2.05st. Clear:RC:1(77.240.249.53):. Processed in 0.023221 secs); 30 Mar 2010 08:29:12 -0000 Received: from mail.nsoft.lt (77.240.249.53) by mail.csc.lt with SMTP; 30 Mar 2010 08:29:12 -0000 Received: from [192.168.16.103] (unknown [77.240.249.53]) (using TLSv1 with cipher DHE-RSA-CAMELLIA256-SHA (256/256 bits)) (No client certificate requested) by mail.nsoft.lt (Postfix) with ESMTPS id 044C58E04F for ; Tue, 30 Mar 2010 11:29:08 +0300 (EEST) Message-ID: <4BB1B64B.3080109@nsoft.lt> Date: Tue, 30 Mar 2010 11:28:59 +0300 From: Julius Tuskenis Organization: UAB nSoft User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; lt; rv:1.9.1.8) Gecko/20100227 Lightning/1.0b1 Thunderbird/3.0.3 MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Re: Strange deletion problem References: In-Reply-To: Content-Type: multipart/alternative; boundary="------------020201030100030601040108" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.002 tagged_above=-10 required=5 tests=BAYES_50=0.001, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201003/236 X-Sequence-Number: 34035 This is a multi-part message in MIME format. --------------020201030100030601040108 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit hello, John 2010.03.30 10:57, John Lister rašė: > - Is there any other statements that could be causing the rows to be > removed that I've missed Please see |TRUNCATE.| > - Is there anything that could be deleting them without generating a > log entry for the statement? Don't think so... > - Is it possible that data corruption of either the index/table is > making my delete statements removed more rows? Maybe you have foreign key with "On delete cascade" ? > - Is it a possibly bug? I don't know of such bug, but I'm no bug expert as well. I guess everything is possible. -- Julius Tuskenis Programavimo skyriaus vadovas UAB nSoft mob. +37068233050 --------------020201030100030601040108 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit hello, John

2010.03.30 10:57, John Lister rašė:
- Is there any other statements that could be causing the rows to be removed that I've missed
Please see TRUNCATE.
- Is there anything that could be deleting them without generating a log entry for the statement?
Don't think so...
- Is it possible that data corruption of either the index/table is making my delete statements removed more rows?
Maybe you have foreign key with "On delete cascade" ?
- Is it a possibly bug?
I don't know of such bug, but I'm no bug expert as well. I guess everything is possible.


-- 
Julius Tuskenis
Programavimo skyriaus vadovas
UAB nSoft
mob. +37068233050
--------------020201030100030601040108-- From john.lister-ps@kickstone.com Tue Mar 30 21:52:02 2010 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id C2033633AAC for ; Tue, 30 Mar 2010 18:52:02 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 87825-08 for ; Tue, 30 Mar 2010 21:51:51 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.kickstone.com (87-194-183-70.dsl.cnl.uk.net [87.194.183.70]) by mail.postgresql.org (Postfix) with ESMTP id DD3B9632815 for ; Tue, 30 Mar 2010 18:51:51 -0300 (ADT) Received: from 87-194-183-69.dsl.cnl.uk.net ([87.194.183.69]:4261 helo=MADRAS2) by mail.kickstone.com with esmtpa (Exim 4.69) (envelope-from ) id 1NwjLD-0003Xt-To; Tue, 30 Mar 2010 22:51:19 +0100 Message-ID: <437F03564D5C4DC1A5E5D466157A3E9A@squarepi.com> From: "John Lister" To: "Szymon Guz" Cc: References: Subject: Re: Strange deletion problem Date: Tue, 30 Mar 2010 22:51:46 +0100 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0022_01CAD05B.932BC100" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5843 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.498 tagged_above=-10 required=5 tests=BAYES_00=-2.599, HTML_MESSAGE=0.001, RDNS_DYNAMIC=0.1 X-Spam-Level: X-Archive-Number: 201003/262 X-Sequence-Number: 34061 This is a multi-part message in MIME format. ------=_NextPart_000_0022_01CAD05B.932BC100 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable 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=3D? and product_id=3D? 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. Cheers for replying, I've checked the config and nothing seems to be = amiss, as I'm running ubuntu the defaults seem to be to dump to stderr = and somehow this is redirect to the log file, there doesn't seem to be = any other log files used - although it is possible the ubuntu startup = scripts inject the logfile on startup? Unfortunately I can't restart the database easily, whatever changed = seemed to have happened on friday without a restart so I'm hoping I can = find and undo it... John ------=_NextPart_000_0022_01CAD05B.932BC100 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable =EF=BB=BF
 
2010/3/30 John Lister <john.lister-ps@kickstone.com= >
Hi, I have a table which is = constantly updated=20 through out the day with no problems, = I'm running Postgresql 8.3.8=20 in ubuntu karmic. However, within the last week for some reason = overnight it=20 is being emptied and I can't work out why. I've set=20 log_min_duration_statement to 0 so that postgresql dumps out every = query=20 made to the db but it hasn't brought anything up. During the period = between=20 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=20 deletions, but during this period approx 15k rows are removed. The = odd thing=20 is that there is nothing else in the logs that references the table = (I've=20 looked for the obvious deletion and trunctate = statements).
 
The table is modified using JDBC = prepared=20 statements so I see 3 entries for each of the expected delete = statements=20 (parse, bind, execute) and the statement is as = follows:
 
delete from product_list where = retailer_id=3D?=20 and product_id=3D?
 
 
Therefore I have a few = questions:
- Is there any other statements = that could be=20 causing the rows to be removed that I've missed
- Is there anything that could be = deleting them=20 without generating a log entry for the statement?
- Is it possible that data = corruption of either=20 the index/table is making my delete statements removed more=20 rows?
- Is it a possibly = bug?
 

Hi,
- maybe there are some other settings for this database (they are = not=20 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=20 from time to time - depending on the configuration)
- ensure that the logging is done to file and to THIS file, = because there=20 are more logging settings than just log_min_duration and sometimes it = can be=20 messed up
- try to restart the database and see if there isn't any other = file=20 created as usually I observe that after deleting current log file, the = database doesn't recreate while logging so the logs are not=20 stored.

Cheers for replying, = I've checked the=20 config and nothing seems to be amiss, as I'm running ubuntu the defaults = seem to=20 be to dump to stderr and somehow this is redirect to the log file, there = doesn't=20 seem to be any other log files used - although it is possible the ubuntu = startup=20 scripts inject the logfile on startup?
Unfortunately I can't restart = the database=20 easily, whatever changed seemed to have happened on friday without a = restart so=20 I'm hoping I can find and undo it...
 
John
 
------=_NextPart_000_0022_01CAD05B.932BC100-- From robin@edesix.com Wed Mar 31 07:10:31 2010 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id B4F32633197 for ; Wed, 31 Mar 2010 04:10:31 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 84461-05 for ; Wed, 31 Mar 2010 07:10:21 +0000 (UTC) X-Greylist: delayed 00:09:09.317012 by SQLgrey-1.7.6 Received: from honeysuckle.london.02.net (honeysuckle.london.02.net [87.194.255.144]) by mail.postgresql.org (Postfix) with ESMTP id 1E114632644 for ; Wed, 31 Mar 2010 04:10:20 -0300 (ADT) Received: from lenin.edesix.com (93.97.174.104) by honeysuckle.london.02.net (8.5.016.1) id 4A23EDE6087D2F6A; Wed, 31 Mar 2010 08:00:39 +0100 Received: from localhost ([127.0.0.1] helo=mail.edesix.com) by lenin.edesix.com with esmtpsa (TLSv1:AES256-SHA:256) (Exim 4.69) (envelope-from ) id 1Nwruo-00025A-7y; Wed, 31 Mar 2010 08:00:38 +0100 MIME-Version: 1.0 Date: Wed, 31 Mar 2010 08:00:38 +0100 From: robin To: John Lister Cc: Subject: Re: Strange deletion problem In-Reply-To: <437F03564D5C4DC1A5E5D466157A3E9A@squarepi.com> References: <437F03564D5C4DC1A5E5D466157A3E9A@squarepi.com> Message-ID: X-Sender: robin@edesix.com User-Agent: RoundCube Webmail/0.3.1 Content-Transfer-Encoding: 8bit Content-Type: text/plain; charset="UTF-8" X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-2.599 tagged_above=-10 required=5 tests=BAYES_00=-2.599 X-Spam-Level: X-Archive-Number: 201003/267 X-Sequence-Number: 34066 You could create a statement level delete trigger on the relevant table, then get it to snapshot all the contents of the pg_stat_activity table (which will show all running queries) into some sort of log table. If you look at the plpgsql documentation (part of the postgresql manual for your release) there is a section on triggers and an example on maintaining a summary table; this is sort of the pattern you want to adopt, except that what you insert into the "summary" table will be something like: "insert into my_table_delete_log (select * from pg_stat_activity where current_query not like '%%');" If you want to run something similar at the row level instead, beware that you may then see it trigger 15K times and thus you could end up with 15K copies of the active queries ... You might also want to add a sequence column to your log table so you can be sure which order the queries were inserted, although if you're using statement level logging, you'll should see distinct query and backend start timestamps anyway. Just an idea - never tried it myself - so probably best to test it out on a test database of some sort first! Cheers, Robin On Tue, 30 Mar 2010 22:51:46 +0100, "John Lister" wrote: > 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. > > Cheers for replying, I've checked the config and nothing seems to be > amiss, as I'm running ubuntu the defaults seem to be to dump to stderr and > somehow this is redirect to the log file, there doesn't seem to be any > other log files used - although it is possible the ubuntu startup scripts > inject the logfile on startup? > Unfortunately I can't restart the database easily, whatever changed seemed > to have happened on friday without a restart so I'm hoping I can find and > undo it... > > John From john.lister-ps@kickstone.com Wed Mar 31 10:36:37 2010 Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id EC3B0633958 for ; Wed, 31 Mar 2010 07:36:37 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 19320-04 for ; Wed, 31 Mar 2010 10:36:27 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.kickstone.com (87-194-183-70.dsl.cnl.uk.net [87.194.183.70]) by mail.postgresql.org (Postfix) with ESMTP id 0AEE463378D for ; Wed, 31 Mar 2010 07:36:26 -0300 (ADT) Received: from 87-194-183-69.dsl.cnl.uk.net ([87.194.183.69]:4391 helo=MADRAS2) by mail.kickstone.com with esmtpa (Exim 4.69) (envelope-from ) id 1NwvH9-00042R-MY for pgsql-admin@postgresql.org; Wed, 31 Mar 2010 11:35:55 +0100 Message-ID: From: "John Lister" To: References: <437F03564D5C4DC1A5E5D466157A3E9A@squarepi.com> Subject: Re: Strange deletion problem Date: Wed, 31 Mar 2010 11:36:23 +0100 MIME-Version: 1.0 Content-Type: text/plain; format=flowed; charset="UTF-8"; reply-type=original Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2900.5843 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.102 tagged_above=-10 required=5 tests=BAYES_50=0.001, RDNS_DYNAMIC=0.1, STOX_REPLY_TYPE=0.001 X-Spam-Level: X-Archive-Number: 201003/280 X-Sequence-Number: 34079 Thanks to all that helped, I've eventually solved it, I set up a cron job to monitor the table every minute to help me narrow down the time frame when the event happened - the thought of scanning 20+Gb log files for the 6hr period it happened again didn't fill me with joy. I also added log_statement=all and changed log_prefix to give me a clue, additionally I added a trigger to log all changes to an audit_table in case I still missed it. I was using row level triggers as I didn't realise that postgresql had statement level ones, so you learn something new every day. It turns out one of the developers had for some insane reason put "delete from product_list" into an overnight batch job. Bizarrely this wasn't appearing in the logs when I used log_min_duration_statement=0, but log_statement=all, which is something else I've learnt. Right off to shoot the developer in the leg as we speak.. John ----- Original Message ----- From: "robin" To: "John Lister" Cc: Sent: Wednesday, March 31, 2010 8:00 AM Subject: Re: [ADMIN] Strange deletion problem > You could create a statement level delete trigger on the relevant table, > then get it to snapshot all the contents of the pg_stat_activity table > (which will show all running queries) into some sort of log table. > > If you look at the plpgsql documentation (part of the postgresql manual > for your release) there is a section on triggers and an example on > maintaining a summary table; this is sort of the pattern you want to > adopt, > except that what you insert into the "summary" table will be something > like: > > "insert into my_table_delete_log (select * from pg_stat_activity where > current_query not like '%%');" > > If you want to run something similar at the row level instead, beware that > you may then see it trigger 15K times and thus you could end up with 15K > copies of the active queries ... > > You might also want to add a sequence column to your log table so you can > be sure which order the queries were inserted, although if you're using > statement level logging, you'll should see distinct query and backend > start > timestamps anyway. > > Just an idea - never tried it myself - so probably best to test it out on > a test database of some sort first! > > Cheers, > Robin > > > On Tue, 30 Mar 2010 22:51:46 +0100, "John Lister" > wrote: >> 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. >> >> Cheers for replying, I've checked the config and nothing seems to be >> amiss, as I'm running ubuntu the defaults seem to be to dump to stderr > and >> somehow this is redirect to the log file, there doesn't seem to be any >> other log files used - although it is possible the ubuntu startup > scripts >> inject the logfile on startup? >> Unfortunately I can't restart the database easily, whatever changed > seemed >> to have happened on friday without a restart so I'm hoping I can find > and >> undo it... >> >> John >