Odd Behavior After Multiple Deletes

Dear Sirs,

We are observing an odd phenomena with PostgresSQL, which is no doubt due to
our using the database improperly. That database in question is PostgresSQL
Version 8.7.1 and runs on a machine installed with Server 2003. The DELETE
commands were being issued both from a Windows XP using PostgresSQL Version
9.1 and at times, from the server itself.

1. We have a table that receives a load of data daily from an older
system. Every night, we delete some of the rows on the table using a DELETE
command from PgAdminIII, perform a VACUUM Full on that table, and then
reload the data using a batch file that contains a series of INSERTS. We run
the batch file from the command line prompt. The table seems fine
afterwards, and the data is correct. We then we turn off the system for the
night.

2. First thing next morning, we look at the data and verify that it
is the same as it was the night before. Suddenly, as the day goes by, the
older, deleted data appears to “bleed” back into the table, creating
duplicate rows. Users are entering new data to that table during the day, as
well as looking at the data we loaded the night before, but none of the
duplicate rows are coming from user input, only the system itself. What are
we doing wrong?

We have not tried dropping the table and starting over, as that would entail
reloading much older data. I think that you will probably say, “Get your
versions in sync right away!” It´s just that the duplication of data occurs
only in this one file where all the deletes are issued every night.

Sincerely,

Chaya Gilburt