Andrew Edson wrote: > I've been given a file to maintain, the purpose of which is to purge the > database of records more than two years old. (Database setup is pg 8.1.3) > > The program (written in perl) enters postgres as the user 'postgres', and > is supposed to select foreign-key records from all tables that link together > with a table which has a delete_dt field in it, so long as the delete_dt > value (timestamp with time zone) is more than two years old. It then, within > a running loop, is supposed to table-by-table delete all records where the > value of the key in question matches the returned value. Delete command is > simply 'DELETE FROM [table] WHERE [key] = [result variable]'. Result > variables are set as [Variable] = $result[x] where $result is the return from > the select and x is the relative location of the value in question within the > select. > > The program can apparently enter the database quite nicely, because it's > capable of running the initial select statement and receiving results; a set > of print statements to the log file that the program creates reveals that > it's getting the information. However, it seems to be unable to delete > records; going into the database after the program finishes running and > running the select statement from within the program yields exactly the same > records as doing so before the delete program runs. > > Does anyone know of anything in Postgres that might be causing this unusual > behavior? Or should I check the perl mailing lists instead?
I imagine that the person writing that perl app was careful enough to wrap the delete statements into a transaction and to not commit that until he was certain about the results. I expect there's a commented-out commit statement near the end, or there is an argument to tell the script to commit the transaction. -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster