How to use bash to do a job in chunks using a control table
Let's pretend you have a job so large that you want to do it a bit at a time. You decide you can do your job an item at a time, and that will be easier on the database instead of doing one long-running query. You create a table called my_schema.items_to_process, which has the item_id of each item you want to process, and you add a column to that items_to_process table called done, which defaults to false. You can then use a script that gets each not-done item from items_to_process, processes it, then updates that item to done = true in items_to_process. A bash script to do that would look something like this:
#!/bin/bash set -e set -u PSQL="/u99/pgsql-9.1/bin/psql" DNL_TABLE="items_to_process" #DNL_TABLE="test" FETCH_QUERY="select item_id from my_schema.${DNL_TABLE} where done is false limit 1" process_item() { local item_id=$1 local dt=$(date) echo "[${dt}] processing item_id $item_id" $PSQL -X -U myuser -h myhost -c "insert into my_schema.thingies select thingie_id, salutation, name, ddr from thingies where item_id = $item_id and salutation like 'Mr.%'" mydb } item_id=$($PSQL -X -U myuser -h myhost -P t -P format=unaligned -c "${FETCH_QUERY}" mydb) dt=$(date) while [ -n "$item_id" ]; do process_item $item_id echo "[${dt}] marking item_id $item_id as done..." $PSQL -X -U myuser -h myhost -c "update my_schema.${DNL_TABLE} set done = true where item_id = $item_id" mydb item_id=$($PSQL -X -U myuser -h myhost -P t -P format=unaligned -c "${FETCH_QUERY}" mydb) dt=$(date) done