Re: Odd Behavior After Multiple Deletes

On Sunday, May 12, 2013 9:40 PM Chaya Gilburt wrote:
> Dear Sirs,

> We are observing an odd phenomena with PostgresSQL, which is no doubt =
due
to our using the database improperly. =A0That database in question is
PostgresSQL Version 8.7.1 and runs on a machine installed=20
> 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=20
> 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=20
> then we turn off the system for the night.

> 2. =A0First 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 =93bleed=94 back into the=20
> 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?

The system itself cannot insert rows into your tables. I could think of
below 2 reasons for seeing extra rows:

1. Delete has not happened appropriately
2. There are triggers defined on tables which could insert the extra =
rows
you are seeing.

Is the problem you described happen more than once? Could you form =
testcase
which can show such behavior?

With Regards,
Amit Kapila.