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

book

Article ID: 218063

calendar_today

Updated On:

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 : [email protected] (tenant=clarity)] job.PFMSyncManager (clarity:admin:21265500__1E4F9A7E-9ED3-480E-B55A-66EF5584EF66:Synchronize portfolio investments) 
com.niku.dbtools.DBUpdateException: org.postgresql.util.PSQLException: ERROR: tables can have at most 1600 columns

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. 

Environment

Release : 15.9.2, 15.9.3

Component : CLARITY PORTFOLIOS

Database: Postgres

Resolution

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

Additional Information