Received: from maia.hub.org (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id EC3B0633958
	for <pgsql-admin-postgresql.org@mail.postgresql.org>;
	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
	<pgsql-admin-postgresql.org@mail.postgresql.org>;
	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 <pgsql-admin@postgresql.org>; 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 <john.lister-ps@kickstone.com>) id 1NwvH9-00042R-MY
	for pgsql-admin@postgresql.org; Wed, 31 Mar 2010 11:35:55 +0100
Message-ID: <BBD9E1D8D3904332BFC5E4B3F42634E7@squarepi.com>
From: "John Lister" <john.lister-ps@kickstone.com>
To: <pgsql-admin@postgresql.org>
References: <D29C98141027402A85C7801C69954C11@squarepi.com>
	<e4edc9361003300124x34ebfdafu797723911bf7ed14@mail.gmail.com>
	<437F03564D5C4DC1A5E5D466157A3E9A@squarepi.com>
	<bc14e6f2216e074b07ea78d561ab55f5@edesix.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" <robin@edesix.com>
To: "John Lister" <john.lister-ps@kickstone.com>
Cc: <pgsql-admin@postgresql.org>
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 '%<IDLE>%');"
>
> 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"
> <john.lister-ps@kickstone.com> wrote:
>> 2010/3/30 John Lister <john.lister-ps@kickstone.com>
>>
>>     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
>