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--