Re: Strange deletion problem

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(at)edesix(dot)com>
To: "John Lister" <john(dot)lister-ps(at)kickstone(dot)com>
Cc: <pgsql-admin(at)postgresql(dot)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(dot)lister-ps(at)kickstone(dot)com> wrote:
>> 2010/3/30 John Lister <john(dot)lister-ps(at)kickstone(dot)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
>