Symptoms
Resource types (remnants of no longer existing subscriptions) cannot be deleted from the panel, producing the following error:
Resource type #xxxxxxx has non zero usage and can not be removed
Cause
Some subscription uses resource that you try to delete. Upon deleting a resource from the panel, resource usage is being checked. You can see the following actions being logged to the /var/log/poa.debug.log upon deleting a resource type:
STMT [Con: 14146, 0x955c508 txn:5720] ' SELECT parent_id, system FROM resource_types WHERE rt_id = ? FOR UPDATE'($0 = xxxxxxxx)
STMT [Con: 14146, 0x955c508 txn:5720] ' SELECT curr_usage FROM subs_resources WHERE rt_id = ? AND curr_usage > 0 AND sub_id = 1'($0 = xxxxxxxxx)
[txn:5720 ResourceType_impl::removeResourceType] {module_id="ResourceManagement"; code="520"} Resource type #xxxxxxx has non zero usage and can not be removed.
Thus, "plesk" database contains "curr_usage" values that are greater than '0'.
Resolution
1) Prior to performing the below mentioned steps, please create a backup of the current 'plesk' database:
#pg_dump plesk > plesk_db_backup
2) Check subsciptions that use mentioned resource with the query - replace name with the actual restype_name you want to remove:
psql -Uplesk -h `hostname` plesk
plesk=> select * from subs_resources where rt_id in ( select rt_id from resource_types where restype_name like '%name%' or restype_name like '%name%');
-[ RECORD 1 ]--+------------------------------
rt_instance_id | 608
sub_id | 19
sub_limit | -1
curr_usage | 2
own_usage | 2
rt_id | 1000093
last_update | 2014-03-13 14:32:30.891859+07
path | x21x43x608
You will get list of subscriptions that use this resource as above. Check resource usage for subscriptions in PPA->Subscriptions->Subscription id (from previous query)->Resources.