Article ID: 122917, created on Sep 17, 2014, last review on Feb 4, 2016

  • Applies to:
  • Business Automation

Symptoms

An error "canceling statement due to statement timeout" occurs in PBA when generating a report/viewing some kind of information in PBA GUI.

Cause

The table being quired is too big, therefore the query takes more time for execution than specified in the DB_LOCK_WAIT_TIMEOUT parameter from the global.conf file (usr/local/bm/etc/ssm.conf/global.conf or .global.conf).

See KB article https://kb.parallels.com/en/11174 for instructions on how to investigate the "canceling statement due to statement timeout" problem in general.

Resolution

I. Database optimization

  1. Connect to the PBA database:

    psql -U $DB_USER -h $DB_HOST $DB_NAME
    

    Example:

    psql -U pba -h 172.16.32.53 pba
    
  2. Check how much time does the quiry takes for its execution:

    pba=> \timing
    Timing is on.
    pba=> quiry
    Time: 63.717 ms
    
  3. Determine how much space the problem database table TblName takes up on the disk. Make sure the disk is large enough:

    SELECT pg_size_pretty(pg_total_relation_size('"TblName"')) As fullTableSize;
    

    Example:

    pba=> SELECT pg_size_pretty(pg_total_relation_size('"Scheduler"')) As fullTableSize;
    fulltablesize
    ---------------
    43 MB
    (1 row)
    
  4. Reindex the problem table TblName:

    pba=> REINDEX TABLE "TblName";
    

    Example:

    pba=> REINDEX TABLE "Scheduler";
    REINDEX
    
  5. Additionally you may check the other tables which are big:

    pba=> select relname, relpages / 128 || ' Mb' as size from pg_class order by relpages desc limit 10;
    

    and reindex/cluster them

  6. Check the final size of the table. Make sure it has decreased significantly:

    SELECT pg_size_pretty(pg_total_relation_size('"TblName"')) As fullTableSize;
    

    Example:

    pba=> SELECT pg_size_pretty(pg_total_relation_size('"Scheduler"')) As fullTableSize;
    fulltablesize
    ---------------
    20 MB
    (1 row)
    
  7. Check the problem query performance again. It should be much faster now.

Known issues

Cannot open Event Manager screens - kb #115879

Search Words

order provisioning fails

Google and Domain order not provisioning

ODBC timeout while generating billing flatfile at production

canceling statement due to statement timeout

Renew Domain Subscription

business automation users list timeout

198398b282069eaf2d94a6af87dcb3ff caea8340e2d186a540518d08602aa065 e12cea1d47a3125d335d68e6d4e15e07

Email subscription for changes to this article
Save as PDF