[GENERAL] 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.
Re: [GENERAL] 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 [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
Re: [GENERAL] Curious situation - running program cannot seem to delete records.
On 2/28/07, Andrew Edson [EMAIL PROTECTED] 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? Thank you for your consideration. -- No need to miss a message. Get email on-the-go http://us.rd.yahoo.com/evt=43910/*http://mobile.yahoo.com/mail%0A with Yahoo! Mail for Mobile. Get started.http://us.rd.yahoo.com/evt=43910/*http://mobile.yahoo.com/mail%0A Is the program committing the change?
Re: [GENERAL] 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. 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. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Curious situation - running program cannot seem to delete records.
Andrew Edson a écrit : Does anyone know of anything in Postgres that might be causing this unusual behavior? Or should I check the perl mailing lists instead? Maybe you are beginning a transaction and that you are committing afterwards ? -- Arnaud ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
