Обсуждение: Disappearing table - suggestions on possible causes appreciated.

In response to Andrew Edson <cheighlund@yahoo.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
ourdatabase.  Although the database is replicated by Slony, this particular table I do not think is.  Servers running
thedb 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
databasehas mysteriously lost all of its data.  When this happens, the programs that require it (most of our
client-sidestuff) 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
timesor 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'shappened more than once to any given server, and I have no clue where to begin looking for the cause.  Any
suggestionswill 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

Andrew Edson <cheighlund@yahoo.com> writes:
>   Does anyone have any suggestions as to what could be causing this
>   single table to lose its data?

I wonder if Slony is doing it to you --- somehow deciding it needs to
"replicate" that table from somewhere else.  Might be worth inquiring
on the slony lists if anyone's seen such a thing.

            regards, tom lane

On Fri, Jun 08, 2007 at 07:39:26AM -0700, Andrew Edson wrote:
>   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.

Well, a couple possibilities:

1.    Are you vacuuming correctly?  If you had xid wraparound, your
data could disappear.

2.    Malice or pilot error: are you sure nobody has issued
TRUNCATE on the table?

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
Users never remark, "Wow, this software may be buggy and hard
to use, but at least there is a lot of code underneath."
        --Damien Katz

On Fri, Jun 08, 2007 at 11:11:05AM -0400, Tom Lane wrote:
>
> I wonder if Slony is doing it to you --- somehow deciding it needs to
> "replicate" that table from somewhere else.  Might be worth inquiring
> on the slony lists if anyone's seen such a thing.

The only way Slony would do that is in the event that the table were
added to a set.  In that case, it will indeed truncate the table and
try to load the data from the origin.  So that is a possibility I
didn't list in my other mail.  If the table is large, the load could
take some time, during which you'd not be able to see anything in the
table.  When you went to insert to the table, though, you'd get
errors, so unless you're dropping the table and rebuilding from
scratch, I'd expect you to get an error when you reloaded the data.

A

--
Andrew Sullivan  | ajs@crankycanuck.ca
Everything that happens in the world happens at some place.
        --Jane Jacobs