DEV Community

loading...

How can I stop a PostgreSQL query automatically when the table is locked?

barbaraips profile image Bárbara Perdigão ・1 min read

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_STOP to ON;
  • 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?

Discussion

pic
Editor guide
Collapse
siscia_ profile image
Simone Mosciatti

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.

Collapse
barbaraips profile image
Bárbara Perdigão Author

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.