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
fromall tables that link together with a table which has a delete_dt field in it, so long as the delete_dt value
(timestampwith time zone) is more than two years old. It then, within a running loop, is supposed to table-by-table
deleteall 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
returnfrom 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
statementand receiving results; a set of print statements to the log file that the program creates reveals that it's
gettingthe information. However, it seems to be unable to delete records; going into the database after the program
finishesrunning and running the select statement from within the program yields exactly the same records as doing so
beforethe delete program runs.
>
> Does anyone know of anything in Postgres that might be causing this unusual behavior? Or should I check the perl
mailinglists 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@magproductions.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 //
