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.
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.
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.
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.
- In the header of Dev Studio, click .
- 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
- In the Owning Ruleset field, enter: Pega-DecisionEngine.
- In the Setting Purpose field, enter: decision/datasets/db/useMergeStatementForUpdates.
- Click Create and open.
- In the Value field, enter: false.
- 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.