Changes in BMC Helix Innovation Suite to support PostgreSQL database
Advantages of moving to PostgreSQL
In your BMC Helix SaaS environments, data is moved from Microsoft SQL Server to PostgreSQL database for all BMC co-located data centers for operational efficiency and the ability to use vendor-specific functionality. We have not made structural changes to the DB schema, but because of the move to PostgreSQL database, there are changes in the upgrade process that result in some changes to the DB structure.
Changes in BMC Helix Innovation Suite to support the move to PostgreSQL database
- Use of CITEXT datatype for case insensitivity handling—PostgreSQL supports a special datatype called citext for case insensitive matching. If the db case sensitivity is false, the server appends ::citxet to the string columns and to the string literal values in Where clauses for case insensitivity matching.
citext does not support limiting the size of the column itself as compared to Microsoft SQL Server or Oracle. The product adds explicit DB constraints in PostgreSQL to limit the size of the columns. - Use of CASCADE DELETE for dropping views—PostgreSQL does not support DROP COLUMN, DROP VIEW, or ALTER COLUMN statements if there are dependent views on top of that column view.
You must drop the column by using the CASCADE DELETE statement, which deletes the dependent views. Numerous metadata operations in AR System require that a column be dropped or altered or that a view be dropped. Because of this strict restriction, the AR Server places the dependent view definition in memory and then issues the drop column or view command with the cascade option to delete the column or views. After the view is re-created, the stored dependent views are also re-created. - Use of savepoints in transactions—AR System uses the PostgreSQL savepoint functionality to ignore a few errors in between transactions for cases, such as creating user friendly views, and so on. If it fails to create the view, ignore the failure and make the transaction successful. Without savepoints, even though errors are ignored, PostgreSQL fails the transaction and rolls back the transaction.
- Use of limit and offset to fetch records in chunks—Microsoft SQL and Oracle have a certain logic to fetch records in chunks for getListEntry, getListValuesFromMultiSchemaEntries APIs. For PostgreSQL, instead of applying custom logic to do the chunking, we use a limit and offset clause provided by PostgreSQL to implement chunking.
- Escape LIKE patterns—For the LIKE operator to match patterns that contain special characters, such as % or [ as literals, we need to escape the pattern by using backslash (\).
- Update SQL changes—PostgreSQL does not allow prefixing a table name to a column name that is getting set in an update command.
Do not include the table's name in the specification of a target column — for example, UPDATE tab SET tab.col = 1 is invalid. Use UPDATE tab set col = 1 instead. - String concatenation—Use || operator for string concatenation.
Limiting integrations and customizations at the database level
In addition to PostgreSQL, BMC Helix Innovation Suite platform and applications support other databases, such as Oracle and SQL Server. BMC provides you with the option of moving to other databases based on factors, such as data centers and the public cloud on which the applications run. However, we strongly recommend moving to PostgreSQL database. BMC Helix Innovation Suite platform and applications will continue to function normally after the upgrade without additional configurations as long you follow best practices for external integrations.
BMC plans to restrict your database access in the cloud. This access restriction will happen over a period of time.
Certain types of workflow actions will be restricted, such as direct SQL actions. Access to databases that use ODBC will be deprecated and no custom DB constructs will be allowed for triggers, indexes, and stored procedures.
We recommend that you start planning and transitioning your integrations and customizations to any one of the following supported integrations:
- APIs (REST API, SOAP Web Services, or RPC)
- Workflow actions
- Integration templates available through BMC Helix iPaaS
- Integration connectors
Changes to DB structure due to changes in the upgrade process
Starting with version 21.x, during the upgrade process, a fresh installation of platform and applications is performed, and then your customization and data are migrated from the previous system to your new system. If your customizations involve direct SQL Sever, the upgrade process might result in the following important changes:
- The T table names will change. If the integrations assumed a certain physical table name for a form, those names will no longer be valid. For example, if a Helpdesk form name is T2100, after the upgrade, it might be T2900.
- Entry Ids or values of field ID1 (Request ID field) might change for certain records depending on whether a conflict occurs during upgrade.
- Individual customizations that worked in the past might not work in the same way after the upgrade. In most cases, BMC will facilitate the required fixes, but some customer involvement might be needed.
Differences in behavior of applications when using PostgreSQL versus Microsoft SQL Server
This section describes some changes in the behavior of your applications, primarily due to DB collation differences, when you move the database engine from Microsoft SQL Server to PostgreSQL database.
Sorting data containing special characters
If the data being sorted contains special characters, such as hyphens, brackets, and so on, such data is sorted differently in different databases.
For example, the ticket summary contains the following data:
Summary |
|---|
Laptop issues |
[QA] email issues |
QA environment upgrade |
The following table describes the differences in behavior of BMC Helix IT Service Management applications when you are using PostgreSQL and MS SQL Server, the limitations, and the workaround:
Using PostgreSQL | Using Microsoft SQL Server | Limitation or customer experience after upgrading to version 21.x and later | Resolution/Workaround | ||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
In PostgreSQL with default settings, sorting on the Summary field in Ascending order sorts and displays the data as follows:
| In Microsoft SQL Server with default settings, sorting on the Summary field in Ascending order sorts and displays the data as follows:
| The data can be sorted differently on the UI or workflow after upgrading to version 21.x and later. | If data sorting is not acceptable for business reasons, use one of the following options to resolve the issue:
|
Accent sensitive searches
Search results based on accented characters works differently in Microsoft SQL Server and PostgreSQL. If searches use qualifications (WHERE clause in database) that rely on accent characters, the search results might be different in Microsoft SQL and PostgreSQL.
The following table describes the differences in behavior of BMC Helix IT Service Management applications when you are using PostgreSQL and MS SQL Server, the limitations, and the workaround:
Using PostgreSQL | Using Microsoft SQL Server | Limitation or customer experience after upgrading to version 21.x and later | Resolution/Workaround |
|---|---|---|---|
PostgreSQL database does not support accent insensitive collation searches. Therefore, similar sounding words, such as “èvan” and “evan” are considered as different strings. Best practice: We recommend that you use FTS indexes so that accent insensitive setting of databases are not used. Use the Full-Text-Accent-Chars-Ascii-Convert parameter through the Centralized configuration to enable accent-insensitive searches for Elastic. For more information, see Configuration-settings-E-M. | Microsoft SQL Server supports accent insensitive collation in searches. Therefore, similar sounding words, such as “èvan” and “evan” are treated as the same string. | Accent sensitive searches will not produce expected results as explained in the PostgreSQL example. | If it is important to treat accented strings in the same manner as non-accented strings in searches, enable FTS indexes on those fields. FTS indexes have a configuration to enable accent insensitive searches. |
Handling ASCII NULL characters
An example of handling ASCII NULL characters is as follows:
You run a UDM job to sync LDAP user data into BMC Helix system. One of the fields in LDAP user records is binary, which contains an ASCII NUL character. This LDAP binary field is mapped to one of the character fields on the CTM:LoadPeople form.
The following table describes the differences in behavior of BMC Helix IT Service Management applications when using PostgreSQL and MS SQL Server, the limitations, and the workaround:
Using PostgreSQL | Using Microsoft SQL Server | Limitation or customer experience after upgrading to version 21.x and later | Resolution/Workaround |
|---|---|---|---|
ASCII NULL characters are represented as \0. | Microsoft SQL Server and other databases allow null byte ('\0') in a string on char/text/varchar fields. | ASCII NULL characters will not get loaded from external data entry. Any existing data containing such characters will be lost during migration. | Such characters cannot be loaded in the target PostgreSQL database. Modify the calling program/workflow to stop sending this character. If there was ASCII NULL character pre-upgrade, remove it from the source and then perform migration. |
Attachment size difference
When you upload files to the attachment fields in a server, the maximum file size limit is different for Microsoft SQL Server and PostgreSQL databases.
The following table describes the differences in behavior of BMC Helix IT Service Management applications when using PostgreSQL and MS SQL Server, the limitations, and the workaround:
Using PostgreSQL | Using Microsoft SQL Server | Limitation or customer experience after upgrading to version 21.x and later | Resolution/Workaround |
|---|---|---|---|
In a PostgreSQL database, the limit for storage in a row is 1 GB. Typically, the file size should be less than 1 GB. | In a Microsoft SQL Server database, the limit for storage of files is 2 GB. | Attachments or data with cumulative size of 1 GB will not be accepted in the system. Attachments that are larger than 1 GB at source before migration will be lost in migration. | Do not upload attachments that are larger than 1 GB. BMC will provide a list of entries that might contain attachments that are larger than 1 GB. You must download such attachments from the source system prior to migration and upload them elsewhere, such as internal FTP site or OneDrive, and provide links in the ticket for end users. |
Searching for backslash (\) character
Searching for backslash (\) character in Microsoft SQL Server does not return correct results unless it is escaped, because \ is a default escape character in PostgreSQL.
The following table describes the differences in behavior of BMC Helix IT Service Management applications when using PostgreSQL and SQL Server, the limitations, and the workaround:
Using PostgreSQL | Using Microsoft SQL Server | Limitation or customer experience after upgrading to version 21.x and later | Resolution/Workaround |
|---|---|---|---|
If the data contains a single backslash character, for example (onbmc\user), and it needs to be used in a WHERE clause of a query, it has to be escaped in PostgreSQL. Example: SELECT name FROM table WHERE login = ‘onbmc\\user’ will return the result as expected. | If the data contains single backslash character, for example, (onbmc\user), and it needs to be used in a WHERE clause of a query, it can be used as it is in Microsoft SQL Server. Example: SELECT name FROM table WHERE login = ‘onbmc\user’ will return the result as expected. | If there are custom workflows that use direct SQL and make use of the backslash character, it will not return the expected results after migration. | Edit the custom workflows to make use of the correct qualification based on database type. Use $DATABASE$ keyword in the workflow qualification to ensure foolproof behavior that is independent of the database. |
Creating or modifying data on a View form fails in PostgreSQL
Creating or modifying data on a View form fails if you use a View form that fetches data from a Database View. This issue occurs in PostgreSQL database when the Database view is not automatically updatable. See the information on Updatable Views in the PostgreSQL online documentation..
The following table describes the differences in behavior of BMC Helix IT Service Management applications when using PostgreSQL and SQL Server, the limitations, and the workaround:
Using PostgreSQL | Using Microsoft SQL Server | Limitation or customer experience after upgrading to version 21.x and later | Resolution/Workaround |
|---|---|---|---|
Most Database views are not updatable by default. Views with joins or those using aggregate functions are not updatable in PostgreSQL. | Many views may be updatable by default because SQL Server Database Engine automatically finds and maps the update to underlying tables. | Direct SQLs from workflows that were directly updating the database views might fail due to the PostgreSQL limitation. | Change the logic in the workflow to use better constructs, such as Set Field or Push Field action, wherever possible. |
Differences in syntax of database functions in MS SQL Server and PostgreSQL
The following table shows syntax differences between MS SQL and PostgreSQL related to certain computational operations. These operations are datatype specific and are accomplished by DB specific functions, which are different for each database. These functions are used in Smart Reports, ODBC integrations, custom database views, and so on.
Database functions | Supported in MS SQL | Supported in PostgreSQL |
|---|---|---|
CAST | Yes | Yes
For more information about these functions, see the PostgreSQL documentation. |
CONVERT | Yes | Yes
For more information about these functions, see the PostgreSQL documentation. |
ROW_NUMBER | Yes | No |
RANK | Yes | No |
DENSE_RANK | Yes | No |
NTILE | Yes | No |
CHARINDEX | Yes | Yes
For more information about this function, see the PostgreSQL documentation. |
CONCAT | Yes | Yes |
DATALENGTH | Yes | Yes
For more information about this function, see the PostgreSQL documentation. |
LEFT | Yes | Yes |
LEN | Yes | Yes |
LOWER | Yes | Yes |
LTRIM | Yes | Yes |
REPLACE | Yes | Yes |
REPLICATE | Yes | Yes
For more information about this function, see the PostgreSQL documentation. |
REVERSE | Yes | Yes |
RIGHT | Yes | Yes |
RTRIM | Yes | Yes |
SPACE | Yes | No |
STR | Yes |
For more information about these functions, see the PostgreSQL documentation. |
SUBSTRING | Yes |
For more information about this function, see the PostgreSQL documentation. |
TRANSLATE | Yes | Yes |
TRIM | Yes | Yes |
UPPER | Yes | Yes |
DATEADD | Yes | Yes |
DATEDIFF | Yes | Yes |
DATENAME | Yes | Yes |
DATEPART | Yes | Yes |
DAY | Yes | Yes |
MONTH | Yes | Yes |
YEAR | Yes | Yes |
QUARTER | Yes | Yes |
STUFF | Yes | No |
PATINDEX | Yes | Yes
For more information about this function, see the PostgreSQL documentation. |
LEAD | Yes | Yes |
LAG | Yes | Yes |
INSTR | Yes | Yes
For more information about this function, see the PostgreSQL documentation. |
LPAD | Yes | No |
MONTHS_BETWEEN | Yes | Yes
For more information about this function, see the PostgreSQL documentation. |
ADD_MONTHS | Yes | No |
LAST_DAY | Yes | No |
EXTRACT | Yes | No |
NEXT_DAY | Yes | No |
TO_NUMBER | Yes | Yes |
Concat_ws | Yes | Yes |
COALESCE | Yes | Yes |
EOMONTH | Yes | Yes |
CEILING | Yes | Yes |
FLOOR | Yes | Yes |