ODF-0290 while adding a new custom attribute in Clarity Studio

Products

• Clarity PPM On Premise

Issue/Introduction

A customer on 16.2.2 found the following error while adding a new custom attribute. 

ODF-0290: Your change to the attribute was canceled because a long-running query will block it.
The query initiated by the admin has been running for 53 seconds and is identified by 
database process id 4035765. You can wait and try again or contact an administrator to kill 
the long-running query before trying again.

To help us avoid this issue, could you please let us know how to check which query was running during the save process?

Environment

Clarity server 16.2.2 on PostgreSQL

Cause

DE78121 - This error message was part of a strategy adopted to indicate system deadlocks in Release 16.2.1 for a Postgres environment. 

It implements a methodology to inform the user that the change they are about to make will freeze up their system. Instead of blindly proceeding with the alter statement, we first check for any long-running queries involving the table being altered. If detected, we skip the alter and send back a message to the user. The user can wait for the long-running query to finish or have an administrator kill the query. Regardless, it will no longer let the alter proceed when a long-running query is detected.

Resolution

To find details on the long-running query, one could use the pg_stat_activity view and get details on the Database process ID.

The following query would be of help:

SELECT
       pid,
       backend_type,
       client_addr,
       usename,
       datname,
       application_name,
       state,
       wait_event_type ||'.'|| wait_event AS waiting,
       now() - coalesce(xact_start, query_start) AS age,
       query
FROM pg_stat_activity
WHERE (
   (clock_timestamp() - xact_start > '00:00:00.1') OR
   (clock_timestamp() - query_start > '00:00:00.1' AND state = 'idle in transaction (aborted)')
   ) AND pid != pg_backend_pid()
ORDER BY now() - coalesce(xact_start, query_start) DESC;

The pg_stat_activity view retrieves detailed information about the processes. After that, run the “pg_cancel_backend(pid)” and “pg_terminate_backend(pid)” commands to kill the selected processes