Synchronize Portfolio - "Error tables can have at most 1600 columns"

Products

• Clarity PPM SaaS
• Clarity PPM On Premise

Issue/Introduction

Error "Synchronize Portfolio:  Unknown error" when trying to sync Portfolios.

The job fails with error: 
PSQLException: Error tables can have at most 1600 columns.  

In the bg-ca logs, an error is noted in relation to the table that causes the problem (different tables may be involved!):

• i.e. TEMP_PFM_CA_PROJECT
• TEMP_PFM_CA_IDEA
• etc.

Example error:

ALTER TABLE TEMP_PFM_CA_IDEA ADD TEST bigint NULL]
ERROR 2021-06-29 08:10:26,818 [Dispatch Synchronize portfolio investments : bg@XXXXX (tenant=clarity)] job.PFMSyncManager (clarity:admin:x__x-x-x-x-x:Synchronize portfolio investments) 
com.niku.dbtools.DBUpdateException: org.postgresql.util.PSQLException: ERROR: tables can have at most 1600 columns

Environment

Release : 15.9.2, 15.9.3

Component : CLARITY PORTFOLIOS

Database: Postgres

Cause

DE61489

• By design, Postgres has a limitation of 1600 columns for a database table.
• When a column is removed from a table, Postgres will still count the deleted columns toward the limitation count.
• The TEMP tables such as TEMP_PFM_CA_PROJECT, TEMP_PFM_CA_IDEA and others, have columns added and removed as a part of their general usage.

Workaround:

Once the limitation is reached, the only way to reset the count would be to perform the following:

1. Make a backup of the table from the database (Note: must be the same table identified in bg-ca.logs)
2. Drop the table.
3. Restore the previously backed up table.

The backup does not retain the older deleted column information, so the restore will not have that information either. 

Resolution

• Fixed in Release 16.0.0 
• Fixed in Release 15.9.3 Patch #1 (15.9.3.1) 

Additional Information

• Reference 16.0.0 Clarity Release Notes > Resolved Defects 
• Reference 15.9.3 Clarity and Jaspersoft Cumulative Patch README Files 
• See also: Clarity frequently reported issues