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

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

On 2/28/07, Andrew Edson <cheighlund(at)yahoo(dot)com> 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?

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.