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.  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?

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.