Skip to main content


         This documentation site is for previous versions. Visit our new documentation site for current releases.      
 

Failure or performance issues when saving records to the database

Updated on July 5, 2022

By default, Pega Platform uses the SQL merge statement to write records to the database. In some cases, when this logic is used, a data flow run might fail or take a long time to complete.

Condition

When running a batch data flow, you might experience a failure with an error message similar to this one:

ERROR: number of columns (1844) exceeds limit (1664)

You might also encounter performance issues when a batch data flow runs slowly.

Cause

When the data flow destination is configured to save records to a Database Table data set by inserting new records and overwriting existing records (as shown in the following figure), the default logic is to use the SQL merge statement in the Pega Platform database layer.

Save options for a Database Table data set used in a data flow destination
A Customer Table data set is selected as the destination. The option to insert new and overwrite existing records is selected.

In most scenarios, the merge statement ensures fast processing of records. However, in some cases, it can cause poor performance, and with older Postgres versions, it can cause failure. If you encounter such problems, you can configure the system to use the delete and insert logic (used in earlier Pega Platform versions) instead of the merge statement.

The decision/datasets/db/useMergeStatementForUpdates dynamic system setting (DSS) controls which logic is used. When this DSS is undefined, its default value is true, which means that the merge statement is used. To use the delete and insert logic, create this DSS and set it to false.

Solution

  1. In the header of Dev Studio, click CreateSysAdminDynamic System Settings.
  2. In the Dynamic System Settings short description field, briefly define this setting's function.
    For example: Indicates whether the merge statement is used to save data
  3. In the Owning Ruleset field, enter: Pega-DecisionEngine.
  4. In the Setting Purpose field, enter: decision/datasets/db/useMergeStatementForUpdates.
  5. Click Create and open.
  6. In the Value field, enter: false.
  7. Click Save.
    Result: When saving new records and overwriting existing records in the database, the system now uses the delete and insert logic instead of the merge statement.
  • Previous topic Exceeded threshold for error count in real-time data flow run
  • Next topic No data flow service nodes are available

Have a question? Get answers now.

Visit the Support Center to ask questions, engage in discussions, share ideas, and help others.

Did you find this content helpful?

Want to help us improve this content?

We'd prefer it if you saw us at our best.

Pega.com is not optimized for Internet Explorer. For the optimal experience, please use:

Close Deprecation Notice
Contact us