Re: Strange deletion problem

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.

regards
Szymon Guz