Thread: Curious situation - running program cannot seem to delete records.
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?
Thank you for your consideration.
No need to miss a message. Get email on-the-go
with Yahoo! Mail for Mobile. Get started.
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 //
On 2/28/07, Andrew Edson <cheighlund@yahoo.com> wrote:
Is the program committing the change?
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?Thank you for your consideration.
No need to miss a message. Get email on-the-go
with Yahoo! Mail for Mobile. Get started.
Is the program committing the change?
Re: Curious situation - running program cannot seem to delete records.
From
btober@ct.metrocast.net
Date:
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. Why don't you use ON DELETE CASCADE foreign key constraints? Let the data base handle all this work rather than maintaining a complicated external Perl script which might get out if sync with the data base as data base design changes are implemented. You don't even need Perl at all, actually: Have cron fire a very simple shell command using psql to invoke the delete command against the primary key table (the one with the delete_dt field), and let the foreign key constraints take care of, ... well..., the foreign key references.
Andrew Edson a écrit : > Does anyone know of anything in Postgres that might be causing this unusual behavior? Or should I check the perl mailinglists instead? Maybe you are beginning a transaction and that you are committing afterwards ? -- Arnaud