Обсуждение: Odd Behavior After Multiple Deletes
Dear Sirs, =20 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. =20 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. =20 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 =93bleed=94 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? =20 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, =93Get = your versions in sync right away!=94 It=B4s just that the duplication of data = occurs only in this one file where all the deletes are issued every night. =20 Sincerely, Chaya Gilburt
On 5/12/2013 9:10 AM, Chaya Gilburt wrote: > PostgresSQL Version 8.7.1 hopefully, you mean 8.1.7 ? there never was a 8.7 release, it went up to 8.4 then rolled over to 9.0 -- john r pierce 37N 122W somewhere on the middle of the left coast
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.
On Friday, May 24, 2013 6:04 AM Chaya Gilburt wrote: > Dear Amit Kapila, > > The apparent problem with duplicate rows appearing in the database was > solved. I forgot that I had modified a program to insert payments > directly > to the database. Not only that, but I also did not have the program > delete > the batch files used, only reopen them as an append. Every time the > batch > job was reused, the older inserts still inside the batch file were > re-inserted along with the new inserts. I am sorry to have wasted your > time, but glad that the problem was due to my own idiocy and not > Postgresql, > as we love using it. It is great that you have found the problem in your application and continuing to use PostgreSql. I have kept in cc to postgresql mailing chain, as I think it should be known to every one, so that any body faces similar problem in future can refer this mail chain. With Regards, Amit Kapila.