While trying to remove a few stuck requests pending approval I found the VMware KB 2114385 is wrong. The State needs to be changed.

From https://kb.vmware.com/s/article/2114385

 

Correct commands below

#SSH to vRA node

#Backup postgres DB

service vcac-server stop

su -m -c “/opt/vmware/vpostgres/current/bin/pg_dumpall -c -f /PATH/vcac.sql” postgres

bzip2 -z /PATH/vcac.sql

service vcac-server start

#Connect to the database by running these commands:

/opt/vmware/vpostgres/current/bin/psql vcac postgres

\connect vcac;

\x

#Check for total count of hung requests.

select count(*) from cat_request where state = ‘PENDING_PRE_APPROVAL’;

#Run these cleanup scripts

BEGIN;

UPDATE comp_bprequest

SET status = ‘FAILED’

WHERE external_request_id::uuid IN

(SELECT id

FROM cat_request

WHERE STATE in (‘PENDING_PRE_APPROVAL’)

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 (‘PENDING_PRE_APPROVAL’)

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 (‘PENDING_PRE_APPROVAL’)

AND (DATE_PART(‘day’, now() – lastupdated) * 24 + DATE_PART(‘hour’, now() – lastupdated) > ‘8’));

SELECT count(*)

FROM cat_request

WHERE STATE in (‘PENDING_PRE_APPROVAL’)

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 (‘PENDING_PRE_APPROVAL’) AND type = ‘CI’);

#If count is 0 for all 3 of the select queries, then COMMIT; else  ROLLBACK;