BUG #4836: Losing data

The following bug has been logged online:

Bug reference: 4836
Logged by: Haitham Isac
Email address: isac(at)unhcr(dot)org
PostgreSQL version: 8.3.1
Operating system: Windows Server 2003 SP2
Description: Losing data
Details:

Dear PostgreSQL team,

We are UNHCR (United Nations High Commissioner For Refugees - Iraq
Operations Office in Amman) have interest in using PostgreSQL for couple of
databases in our office. All of these databases are using MS Access as FE
and PSQL as BE.

This system is up and running pretty well except for a problem we face on
occasional basis which is losing random records from random tables (i.e.
doesn't follow a particular theme). This is happening for records that had
been entered weeks ago and then all of a sudden they are not there any
more.

Could you please advise.

Many thanks

Haitham Isac
IT Assistant

"Haitham Isac" <isac(at)unhcr(dot)org> writes:
> PostgreSQL version: 8.3.1
> Operating system: Windows Server 2003 SP2

> This system is up and running pretty well except for a problem we face on
> occasional basis which is losing random records from random tables (i.e.
> doesn't follow a particular theme). This is happening for records that had
> been entered weeks ago and then all of a sudden they are not there any
> more.

Hmm. The first bit of advice is to update to 8.3.latest (currently 8.3.7).
However, there are no known data-losing bugs in 8.3.1 (unless you are
using GiST indexes), so that's more in the nature of good practice than
something I think is likely to fix this specific problem.

As for the specific problem, Postgres is not known for losing data, and
especially not for losing individual rows without any other sign of
database corruption. I don't want to say it couldn't be our bug, but
frankly it seems much more likely that your application is incorrectly
issuing delete commands against those rows. What I'd suggest is turning
on log_statement = all so you can track all commands issued to the
database for awhile, and then checking to see if there are any errant
DELETEs listed. If it *is* our bug the log might provide enough clues
for you to submit a reproducible test case, which is what we'd need to
fix the bug.

Also, to be blunt, if you're storing mission-critical data you should
think twice about doing it on Windows. That OS is not particularly
trustworthy --- especially not if you have any third-party antivirus
software installed --- and I don't have unlimited faith in our port to
it either. Again, switching OSes seems unlikely to solve your immediate
problem, but it might spare you a catastrophic failure somewhere down
the road.

regards, tom lane

Thanks a lot Tom, I'll start with upgrading to 8.3.7 and see how is t going to be.

Regards.

Haitham

>>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> 03/06/2009 17:01 >>>
"Haitham Isac" <isac(at)unhcr(dot)org> writes:
> PostgreSQL version: 8.3.1
> Operating system: Windows Server 2003 SP2

> This system is up and running pretty well except for a problem we face on
> occasional basis which is losing random records from random tables (i.e.
> doesn't follow a particular theme). This is happening for records that had
> been entered weeks ago and then all of a sudden they are not there any
> more.

Hmm. The first bit of advice is to update to 8.3.latest (currently 8.3.7).
However, there are no known data-losing bugs in 8.3.1 (unless you are
using GiST indexes), so that's more in the nature of good practice than
something I think is likely to fix this specific problem.

As for the specific problem, Postgres is not known for losing data, and
especially not for losing individual rows without any other sign of
database corruption. I don't want to say it couldn't be our bug, but
frankly it seems much more likely that your application is incorrectly
issuing delete commands against those rows. What I'd suggest is turning
on log_statement = all so you can track all commands issued to the
database for awhile, and then checking to see if there are any errant
DELETEs listed. If it *is* our bug the log might provide enough clues
for you to submit a reproducible test case, which is what we'd need to
fix the bug.

Also, to be blunt, if you're storing mission-critical data you should
think twice about doing it on Windows. That OS is not particularly
trustworthy --- especially not if you have any third-party antivirus
software installed --- and I don't have unlimited faith in our port to
it either. Again, switching OSes seems unlikely to solve your immediate
problem, but it might spare you a catastrophic failure somewhere down
the road.

regards, tom lane

Haitham Isac wrote:
> Thanks a lot Tom, I'll start with upgrading to 8.3.7 and see how is t going to be.

Just to re-iterate an important point, too: Antivirus software is KNOWN
to cause problems for PostgreSQL. See the archives. It is VERY strongly
recommended that antivirus software be uninstalled, not just disabled or
told to ignore PostgreSQL.

Well-designed, bug-free antivirus software shouldn't affect PostgreSQL,
but there doesn't seem to be much of that around.

In any case, you shouldn't really need antivirus software on a database
server.

--
Craig Ringer