Обсуждение: BUG #4836: Losing data
The following bug has been logged online: Bug reference: 4836 Logged by: Haitham Isac Email address: isac@unhcr.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@unhcr.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. =20 Regards. =20 Haitham >>> Tom Lane <tgl@sss.pgh.pa.us> 03/06/2009 17:01 >>> "Haitham Isac" <isac@unhcr.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 =3D 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