Problem Note 70894: A SAS® Infrastructure for Risk Management tool fails with the error “column "source_federated_area_id" of relation "jobflow_instance" already exists”
 |  |  |  |  |
After installing a hot fix for the SAS Infrastructure for Risk Management (IRM) product, the documentation instructs you to run the post-installation task automation tool. This tool might fail to run with the following error:
java.sql.BatchUpdateException: Batch entry 0 ALTER TABLE JOBFLOW_INSTANCE ADD COLUMN SOURCE_FEDERATED_AREA_ID CHARACTER VARYING(32) was aborted: ERROR: column "source_federated_area_id" of relation "jobflow_instance" already exists Call getNextException to see other errors in the batch.
[…]
Error updating new SQL statements.
Could not update database.
Cause
Some ALTER TABLE statements need to be executed to add, remove, or update columns of different tables in the IRM database as part of various hot fixes that were released in the past. However, each ALTER TABLE statement must be executed only once to prevent SQL exceptions. If re-executed, these statements cause SQL exceptions, which prevents the hot fix post-installation steps from completing. As a result, the SAS IRM application malfunctions.
SAS IRM provides a Java-based post-installation task automation tool to execute these SQL statements. This tool maintains a table named HF_SQL with the columns SQL_STMT, RELEASE_VERSION, and APPLIED_COLUMN to check if a SQL statement has already been executed. Currently, the tool checks if the SQL statement has been executed in the current IRM release version. However, the tool should check across all SAS IRM releases, including the current and all previous releases, to ensure that the SQL statements are not executed again if they have already been applied. In this way, the tool can skip the SQL update if it has already been executed, avoiding unnecessary re-execution and ensuring a smooth post-installation process.
The error occurs when you complete steps similar to the following:
- Install SAS® Infrastructure for Risk Management 3.6.
- Apply a SAS Infrastructure for Risk Management 3.6 hot fix and run the SAS Infrastructure for Risk Management post-installation task automation tool.
Note that completing this step creates the "hf_sql" table in the IRM database with release_version='3.6'.
- Migrate to SAS® Infrastructure for Risk Management 3.7.
Note that this step preserves the SAS IRM database and "hf_sql" table.
- Apply a SAS Infrastructure for Risk Management 3.7 hot fix and run the SAS Infrastructure for Risk Management post-installation task automation tool.
Note that, in this case, the SAS Infrastructure for Risk Management post-installation task automation tool looks for statements that have been run against the current release. When the tool checks for SQL statements that have already been run, it won't find any. As a result, the tool tries to run all of the SQL statements again.
Troubleshooting
<SASHome>/SASWebInfrastructurePlatformDataServer/9.4/bin/pg_dump -h localhost -p 9432 -U irmadmin -t jobflow_instance "irmdb" > /tmp/jobflow_instance_out.sql
From the database table export of jobflow_instance, we can see that source_federated_area_id column already exists.
Workaround
To work around this issue, run the SAS Infrastructure for Risk Management post-installation task automation tool with the -nosql option to skip the SQL updates.
Solution
A code fix for the issue was included in SAS Infrastructure for Risk Management 3.7 Hot Fix M2Q003. Apply the latest SAS Infrastructure for Risk Management 3.7 Hot Fix to obtain the code fix.
Click the Hot Fix tab in this note to access the hot fix for this issue.
Additional Resources
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS Infrastructure for Risk Management | Microsoft® Windows® for x64 | 3.7 | 9.4 TS1M8 | ||
| Linux for x64 | 3.7 | 9.4 TS1M8 | ||||