I have a bash script that creates new partitions for the tables in the database. I'm currently having a problem with locked tables - when this script runs and tries to create partitions for a table that is being used otherwise, it gets stuck. All I need is to find a way to stop the script after a certain amount of time when the table is locked, but so far I haven't succeeded.
This is the code block where the problem occurs:
>&3 2>&4 psql -h $host -d $database -U $database_user -t -e -c "ALTER TABLE ${schema}.${table} SPLIT DEFAULT PARTITION START (date '${partition_date_to_create}') INCLUSIVE END (date '${exclusive_date}') EXCLUSIVE INTO (PARTITION \""${partition_name}"\", PARTITION ${default_partition_name})"
It's part of this block:
do
partition_name="${partition_date_to_create//-/}"
if [ -n "${existent_partition[*]}" ] && [[ "${existent_partition[*]}" == *"$partition_name"* ]]; then
log_info "Table ${table} already has partition '${partition_name}'. Skipping creation."
continue
fi
log_info "Creating partition '${partition_name}' for table ${table}..."
exclusive_date=$(date --date="${partition_date_to_create} +1 day" +%F)
>&3 2>&4 psql -h $host -d $database -U $database_user -t -e -c "ALTER TABLE ${schema}.${table} SPLIT DEFAULT PARTITION START (date '${partition_date_to_create}') INCLUSIVE END (date '${exclusive_date}') EXCLUSIVE INTO (PARTITION \""${partition_name}"\", PARTITION ${default_partition_name})"
if [ "$?" -eq 0 ]; then
log_info "Created partition '${partition_name}' for table ${table}."
else
log_error "Error creating partition '${partition_name}' for table ${table}."
fi
done
What I have tried so far:
- Set the variable
ON_ERROR_STOPtoON; - Configure
LOCK_TIMEOUT; - Configure
STATEMENT_TIMEOUT; - Disable the autocommit feature and commit manually.
Can anyone offer me an insight on what I'm doing wrong?
Top comments (2)
That is kinda never going to work.
Have you tried to lock the table first and then doing the partition?
postgresql.org/docs/9.4/sql-lock.html
LOCK with the NOWAIT option.
Of course this won't block but the table won't be partitioned, so you may need to put some control mechanism in place. (Aka put it into a loop).
Said so, locks are fundamental in postgres, I would suggest you to refresh how and why they work.
Thanks for replying. I'm actually fairly new to postgres, so I'm struggling a with some concepts. I actually solved the problem by setting a timeout for the Linux command, not for the query, this way I'll not interfere with the locked table.