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