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:


set -e
set -u

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