Differences in behavior of applications when using PostgreSQL and other databases - Documentation for BMC Helix IT Service Management Deployment 21.3

CaseUsing PostgreSQL Using Microsoft SQL Server

Limitation / customer experience after upgrading from version earlier than 21.x to version to 21.x and later

Resolution / Workaround Sorting data containing special characters in ticket summary

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

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.

PostgreSQL database does not support accent insensitive collation searches.

Hence, similar sounding words, such as “èvan” and “evan” are considered as different strings. 

Best practice: We recommend you to use FTS indexes so that accent insensitive setting of databases are not used.

Microsoft SQL Server supports accent insensitive collation in searches.

Hence, 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 NUL charactersYou run a UDM job to sync LDAP user data into 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.ASCII NUL characters are represented as \o.
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 character cannot be loaded in the target PostgreSQL database, so modify the calling program/workflow to stop sending this character.

If there was ASCII NULL character pre-upgrade, fix it in source to remove it and then perform migration.

Attachment size differenceWhen you are uploading files to the attachment fields in a server, maximum file size limit is different for files for Microsoft SQL Server and PostgreSQL databases.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.

Search for backslash (\) characterSearching for backslash (\) character in Microsoft SQL Server does not return correct results unless it is escaped, because \ is a default escape character in PostgreSQL.

If the data contains single backslash character for example. (onbmc\user) and it needs to be used in 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 the WHERE clause of a query, it can be used as it is in MS SQL.

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.

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

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.