I have two routine postgresql maintenance tasks that require deletion of aged rows from certain tables of my web application.
Both take ~1 hr to complete, respectively.
Currently, each of these maintenance tasks is represented by a script, that is called at a designated time of day via crontab. I try to schedule both scripts with at least a ~2hr gap between them, since I don't want there to be any overlap between the two tasks.
My question is - can I just merge the two scripts into one, like so:
#!/bin/sh dbname="dbname" username="myuser" psql -d $dbname -U $username << EOF # task 1 delete from links_publicreply WHERE "submitted_on" < now() - interval '14 days' AND id NOT IN (SELECT latest_reply_id FROM links_link WHERE la$ # task 2 begin; DELETE FROM links_vote WHERE link_id IN (SELECT id FROM links_link WHERE id NOT IN (select answer_to_id from links_publicreply) AND "submitted$ DELETE FROM links_photoobjectsubscription WHERE which_link_id IN (SELECT id FROM links_link WHERE id NOT IN (select answer_to_id from links_pu$ DELETE FROM links_link WHERE id NOT IN (select answer_to_id from links_publicreply) AND "submitted_on" < now() - interval '1 hour'; commit; EOF
Notice how the deletion tasks are called one after another. This way, I can be certain task 2 only kicks off once task 1 is complete. I want to confirm my understanding is correct, or whether there are any caveats I need to keep in mind. Please advise.