Changes in BMC Helix Innovation Suite to support PostgreSQL database - Documentation for AR System 21.3

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:

Summary
Laptop issues
[QA] email issues
QA environment upgrade

In Microsoft SQL Server with default settings, sorting on the Summary field in Ascending order sorts and displays the data as follows:

Summary
[QA] email issues
Laptop issues
QA environment upgrade

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:

Fix the form properties or workflow that might be impacted because of a change in the sorting behavior.
Identify specific form fields and raise a request with Support team to change collation at the database column level.

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.	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.

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.

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. PostgreSQL does not allow null byte ('\0') in a string on char/text/varchar fields. If you try to store a string containing null bytes, you receive an error.	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.

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.
PostgreSQL does not allow null byte ('\0') in a string on char/text/varchar fields. If you try to store a string containing null bytes, you receive an error.

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.

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

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.

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. Open link .

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. If Direct SQL is the only option, change the SQL to make sure it works for all supported database types. For example, use a T table or simple view updates instead of join view updates. Any use of direct SQL in a workflow must be compliant with all supported database types so that any change in the database does not affect the workflow.

Best practice to improve overall DB performance

During the migration from Microsoft SQL Server to PostgreSQL, the index definitions in the BMC Helix Innovation Suite and applications that performed well with the same volume of data are not utilized by the query optimizer in PostgreSQL. To address this issue, for any custom index or integrations, you must create new index definitions during the UAT or go-live activities. However, out-of-the-box applications and their index definitions will perform correctly.