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