Error: "ERROR: canceling statement due to statement timeout" viewing a specific storage policies Storage Containers

Products

• VMware Cloud Director

Issue/Introduction

• Unable to list Storage Containers for a specific storage policy.
• Able to view Storage Containers via: Provider Portal > Infrastructure resources > Storage Container 
• storageContainers API takes long time to respond:
  
  e.g. https://<host>/cloudapi/1.0.0/storageContainers?page=1&pageSize=15&filterEncoded=true&filter=storagePolicyId==urn:vcloud:storagepolicy:########-####-####-####-############&sortAsc=name&links=true
  
  
• Below error can be seen in the /opt/vmware/vcloud-container/logs/vcloud-container-debug.log:
  
  2026-06-10 09:22:15,532 | ERROR    | pool-jetty-404            | DefaultExceptionMapper         | Internal server exception | requestId=########-####-####-####-############,request=GET https://<VCD>/cloudapi/1.0.0/storageContainers,requestTime=######,remoteAddress=###.###.##.##:#####,userAgent=Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 ...,accept=application/json;version 40.0.0-alpha
org.hibernate.exception.GenericJDBCException: could not execute query
        at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
        at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
        at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)

Environment

VMware Cloud Director 10.6.1.2

Cause

The issue occurs due to a query taking a long time to return ui_dstore_storage_class_view.

Resolution

To workaround this issue, follow the below steps:

1. Take a backup of the Database: Backup and Restore of Your VMware Cloud Director Appliance
2. SSH to the Primary cell as root.
3. Connect to the database running the command below:

   sudo -i -u postgres psql vcloud OR db

4. Run the below query to Create/update a view:
   
   CREATE OR REPLACE VIEW ui_dstore_storage_class_view AS
  WITH
    cte_pvdc_sclass_scontainer as (
      SELECT
        vc_id,
        storage_profile_moref,
        storage_profile_name,
        datastore_id,
        moref
      FROM pvdc_sclass_scontainer
    ),
    cte_scontain_sprofile_req as (
      SELECT
        vc_id,
        ds_moref,
        storage_profile_name,
        SUM(storage_used) AS storage_used
      FROM dgp_view_scontain_sprofile_req
      GROUP BY vc_id, ds_moref, storage_profile_name
    )
  SELECT
    pspsc.vc_id AS vc_id,
    vc.name AS vc_name,
    scn.sclass_id AS storage_class_id,
    scn.sclass_name AS storage_class_name,
    pspsc.storage_profile_moref AS storage_class_moref,
    spsc_metrics.storage_container_id AS storage_container_id,
    spsc_metrics.storage_container_name AS storage_container_name,
    pspsc.moref AS storage_container_moref,
    COALESCE(spsc_metrics.used_mb, 0) AS used_mb,
    COALESCE(spsc_metrics.total_mb, 0) AS total_mb,
    SUM(COALESCE(dsrv.storage_used, 0)) AS requested_mb,
    COALESCE(spsc_metrics.provisioned_mb, 0) AS provisioned_mb
  FROM
    cte_pvdc_sclass_scontainer pspsc
  LEFT JOIN sprofile_scontainer_metrics spsc_metrics
    ON pspsc.storage_profile_name = spsc_metrics.storage_profile_name
      AND pspsc.datastore_id = spsc_metrics.storage_container_id
  LEFT JOIN cte_scontain_sprofile_req dsrv
    ON pspsc.vc_id = dsrv.vc_id AND spsc_metrics.scontainer_moref = dsrv.ds_moref
      AND pspsc.storage_profile_name = dsrv.storage_profile_name
  LEFT JOIN sfp_view_sclass_name scn
    ON pspsc.storage_profile_name = scn.sclass_name
  LEFT JOIN virtual_center vc
    ON pspsc.vc_id = vc.id
  GROUP BY pspsc.vc_id, vc.name, scn.sclass_id, scn.sclass_name, pspsc.storage_profile_moref,
      spsc_metrics.storage_container_id, spsc_metrics.storage_container_name,
      pspsc.moref, spsc_metrics.used_mb, spsc_metrics.total_mb, dsrv.storage_used,
      spsc_metrics.provisioned_mb;
   
   
5. This should create a view and output should be like: CREATE VIEW
6. Now verify the performance on UI to confirm if you are now able to list Storage Containers for a Storage Policy.