Style Book cannot be created or updated after upgrading to Update 4

Issue

• I get the following error message when a new Style Book is created or an existing one is edited:

  ERROR [http-nio-8080-exec-6][JDBCExceptionReporter:234] Batch entry 0 insert into StyleBookEntryVersion (mvccVersion, ctCollectionId, version, uuid_, styleBookEntryId, groupId, companyId, userId, userName, createDate, modifiedDate, defaultStyleBookEntry, frontendTokensValues, name, previewFileEntryId, styleBookEntryKey, styleBookEntryVersionId) values (0, 0, 1, 'c4f994f5-3beb-7efd-cdd1-ec0bdbb1ff9c', 102, 37347, 37307, 37354, 'Test Test', '2022-07-25 14:20:50.144+00', NULL, 'FALSE', '', 'TestNew', 0, 'testnew', 101) was aborted: ERROR: column "mvccversion" of relation "stylebookentryversion" does not exist_ Position: 36 Call getNextException to see other errors in the batch. [Sanitized]
  ERROR [http-nio-8080-exec-6][JDBCExceptionReporter:234] ERROR: column "mvccversion" of relation "stylebookentryversion" does not exist_ Position: 36 [Sanitized]
  ERROR [http-nio-8080-exec-6][PortletServlet:119] javax.portlet.PortletException: org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update
  javax.portlet.PortletException: org.hibernate.exception.SQLGrammarException: Could not execute JDBC batch update

Environment

• DXP 7.4 Update 4

Resolution

• This error is related to LPS-127285, where a fix was released for the root cause and this was backported to our branch under LPS-154911. However, this backport was only a partial one.
• As an alternative solution, the attached script: postgres-upgrade-sql-for-LPS-154911-dxp-4-7310.groovy could be run in order to fix the indexes.

Running (Groovy) Scripts in the Script Console

First and foremost, Liferay advises not to change the database directly. But if you must, please do so by running scripts in Server Administration > Script Console.

Before running any scripts, make sure to follow the rules below in order to minimize and/or prevent any potential issues that originate from modifying the database:

• Create a backup of your database.
  Doing so ensures that you can restore your backup in case you run into any issues.
• Test scripts in a
  • vanilla environment.
    Doing so ensures that the script itself works in a basic test environment and excluded any issues that originate from a customized environment.
  • lower/test environment.
    Doing so ensures that the script will work as expected with some test data in your environment.
  • copy of your production environment
    1. create a copy of your production database
    2. create a copy of your production Liferay bundle
    3. connect the copied database
    4. test the script
• Back up your production environment.
  Creating a backup of your production environment - your Liferay bundle(s) and your database -  would help to restore it in case something goes wrong. Before running the script in production, make sure to decide the steps in advance that are needed to restore the data or the environment.

Additional Information

• Running Scripts From the Script Console