Blogs

Issue:
Deletion of the one row of data stored in the PS_INSTALLATION table can result in severe downtime to the entire PeopleSoft environment and significant data corruption due to the loss of sequential counters used in the Person data model as well other transactional data.
 

Cause:
Aside from the accidental deletion through direct SQL tools or erroneous applications, this is typically caused by improper and over-use of this table as a search record or for inner joins to limit the results of certain queries. As delivered, the PS_INSTALLATION table is used in several components to bypass the search functionality and take the user directly into a component. If all used fields are limited to level zero (0), there should be no harm in using this table in this fashion since the Component Processer prevents deletion of data in level zero.
The risk is typically amplified if the component is customized (by junior developers) by placing fields from the Installation table into a grid or scroll area in level one, two or below. Since row insertion and deletion is allowed at this level, the Installation table is placed in the mercy of user interaction. If insertion/deletion (+/-) are enabled at that level, user action can result in deleting the one row of data from the Installation table. In a less obvious scenario, the developer could have placed DeleteRowset() or equivalent functions in PeopleCode to remove data based on various user activities that can also result in the same damaging results.
Troubleshooting and Resolution:
This issue is typically difficult to isolate since the direct results are not immediately realized. Usually the warning signing are observed in other parts of the application when other components fail to open or when someone observes that new EMPLID is reset to zero. These symptoms do not directly point to data corruption in the Installation table. A common area where this issue can be manifested is in the SSR_CLSRCH_ENTRY page.
To make the situation worse, the row of data could have been re-inserted into the table incidentally as another side effect of the over-use of this table in other components. Only when querying the counters, it can be concluded that the data has been deleted and re-inserted.
The following SQL query can be used to identify all pages where fields from the Installation table are used in other than level zero:
Select * from pspnlfield where recname = 'INSTALLATION' and occurslevel > 0;
Pages and Components identified in the above query should be assessed and associated PeopleCode should be reviewed to ensure that un-intentional deletion of this table can occur.
 

Conclusion:
This issue is to a large extent a manifestation of the improper and liberal use of a critically important table such as PS_Installation. In most use cases (delivered or custom), a developer needs a table that will always have only one row of data to limit search results when a component is opened. Instead of developing and maintaining a custom table, view or additional logic that can use core RDMS functionality, the Installation table is used since it is readily available. Here are some practical suggestions:
·         Avoid using the Installation table in development objects unless its use is essentially needed.
·         Fields from the Installation table should never be placed in other than level zero especially when row insertion or deletion can be performed implicitly.
·         When performing data insertion or deletion through PeopleCode, ensure that all tables in the associated data set are intended part of the rowset.