Re: Curious situation - running program cannot seem to delete records.

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
alban(at)magproductions(dot)nl

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 //