Deployment / Provisioning requests are stuck at 'In Progress' or 'Pending Approval' status
book
Article ID: 325893
calendar_today
Updated On:
Products
VMware Aria Suite
Issue/Introduction
Symptoms: In the vRealize Automation Requests page, multiple tasks get stuck in In Progress or Pending Approval status for a long time.
Environment
VMware vRealize Automation 7.x
Cause
This issue occurs when a task hangs up or when the backing infrastructure for that task is changed or removed.
Resolution
To resolve this issue, clean up the postgres database for those tasks.
Take a backup of the postgres database by running these commands: su -m -c "/opt/vmware/vpostgres/current/bin/pg_dump -Fc vcac > /tmp/vcac.sql" - postgres
Note: -Fc switch already provides a compressed file. No need to bzip.
Connect to the database by running these commands:
To ensure that the update is accurate, run all these cleanup scripts in a transaction: BEGIN; UPDATE comp_bprequest SET status = 'FAILED' WHERE external_request_id::uuid IN (SELECT id FROM cat_request WHERE STATE in ('IN_PROGRESS', 'PROVIDER_COMPLETED','PRE_APPROVED','SUBMITTED','PROVIDER_SEND_ERROR','POST_APPROVED') AND (DATE_PART('day', now() - lastupdated) * 24 + DATE_PART('hour', now() - lastupdated) > '8'));
UPDATE cat_request SET state = 'FAILED' WHERE id::uuid IN (SELECT id FROM cat_request WHERE STATE in ('IN_PROGRESS', 'PROVIDER_COMPLETED','PRE_APPROVED','SUBMITTED','PROVIDER_SEND_ERROR','POST_APPROVED') AND (DATE_PART('day', now() - lastupdated) * 24 + DATE_PART('hour', now() - lastupdated) > '8'));
UPDATE cat_resource SET status = 'DELETED' WHERE request_id in ( select id from cat_request where state in ( 'FAILED' ) AND type = 'CI' AND (DATE_PART('day', now() - lastupdated) * 24 + DATE_PART('hour', now() - lastupdated) > '8'));
Verify that updates are successful by running Select queries:
SELECT id, status FROM comp_bprequest WHERE external_request_id::uuid IN (SELECT id FROM cat_request WHERE STATE in ('IN_PROGRESS', 'PROVIDER_COMPLETED','PRE_APPROVED','SUBMITTED','PROVIDER_SEND_ERROR','POST_APPROVED') AND (DATE_PART('day', now() - lastupdated) * 24 + DATE_PART('hour', now() - lastupdated) > '8'));
SELECT count(*) FROM cat_request WHERE STATE in ('IN_PROGRESS', 'PROVIDER_COMPLETED','PRE_APPROVED','SUBMITTED','PROVIDER_SEND_ERROR','POST_APPROVED') AND (DATE_PART('day', now() - lastupdated) * 24 + DATE_PART('hour', now() - lastupdated) > '8');
SELECT count(*) FROM cat_resource WHERE request_id IN (SELECT id FROM cat_request WHERE STATE in ('IN_PROGRESS', 'PROVIDER_COMPLETED','PRE_APPROVED','SUBMITTED','PROVIDER_SEND_ERROR','POST_APPROVED') AND type = 'CI');
If count is 0 for all 3 of the select queries, then COMMIT else ROLLBACK;