2010/3/30 John Lister <john.lister...@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
