Re: Disappearing table - suggestions on possible causes appreciated.

In response to Andrew Edson <cheighlund(at)yahoo(dot)com>:

> The company I work for provides services for various offices around the country. In order to help keep our database straight, and allow several of our client-side programs to verify their location, we include a table called 'region' in our database. Although the database is replicated by Slony, this particular table I do not think is. Servers running the db are SuSE 9.3, running Postgresql 8.1.3, and I believe Slony 1.1.
>
> Recently, there have been incidents across a few of the offices where the region table in their local copy of the database has mysteriously lost all of its data. When this happens, the programs that require it (most of our client-side stuff) will not retrieve data related to those areas. I've already built an sql script to restore the table quickly, I'm just curious as to why this is happening. It's not really a common event, as it's only happened maybe five times or so across a few dozen servers and about three months. The most recent incident was brought to our attention today, which is why this is on my mind now.
>
> Does anyone have any suggestions as to what could be causing this single table to lose its data? I don't think it's happened more than once to any given server, and I have no clue where to begin looking for the cause. Any suggestions will be greatly appreciated.

Sounds like the problem is too erratic to be easily diagnosed. It also sounds
too dispersed to be related to a hardware problem or anything like that.

My first thought is that 8.1.3 is pretty old. Any reason not to upgrade to
8.1.9?

My second thought would be to enable statement logging on all the servers.
This will generate a lot of log activity, so make sure you have the disk
space available, and proper log rotation in place. I'm suggesting this
because my second guess on this problem would be that an application is
erroneously deleting the data. If that's the case, you should be able to
track it down next time it happens if you have statement logging enabled.

--
Bill Moran
http://www.potentialtech.com