Question
LTIMindtree
SA
Last activity: 6 Sep 2022 8:21 EDT
How to RESUME the Upgrade (Major / Minor / Patch) If failed during SQL Statements Execution
Issue Description:
Below error is one of the sample issue during the Upgrade script execution and is common to occur while executing the SQL statements. During the such cases i.e..
Issue Description:
Below error is one of the sample issue during the Upgrade script execution and is common to occur while executing the SQL statements. During the such cases i.e..
2022-09-05 13:29:19,950 ( SQLLoaderInternal) WARNING - Statement: ALTER TABLE pega_cs_data.pr_data [java] ADD CONSTRAINT "pr_data_pk" PRIMARY KEY [java] ( [java] "pzinskey" [java] ) [java] 2022-09-05 13:29:19,951 ( SQLLoaderInternal) WARNING - [java] ******************************************************************************** [java] [java] Encountered a SQL Exception, clean schema before running again. [java] [java] ******************************************************************************** [java] Exception in thread "main" com.pega.pegarules.install.SQLLoaderException: Error executing SQL statements [java] at com.pega.pegarules.install.SQLLoader.load(SQLLoader.java:81) [java] at com.pega.pegarules.install.SQLLoader.main(SQLLoader.java:66) [java] Caused by: org.postgresql.util.PSQLException: ERROR: could not create unique index "pr_data_pk" [java] Detail: Key (pzinskey)=(DATA-ADMIN-JOBSCHEDULER-EXECUTION PZRULEQUEUEITEMPROCESSOR) is duplicated. [java] at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552) [java] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284) [java] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322) [java] at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481) [java] at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401) [java] at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322) [java] at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308) [java] at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284) [java] at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:279) [java] at com.pega.pegarules.install.SQLLoaderInternal.readAndExecuteSQLStatements(SQLLoaderInternal.java:160) [java] at com.pega.pegarules.install.SQLLoaderInternal.load(SQLLoaderInternal.java:119) [java] at com.pega.pegarules.install.SQLLoader.loadInner(SQLLoader.java:99) [java] at com.pega.pegarules.install.SQLLoader.load(SQLLoader.java:78) [java] ...
Accepted Solution
Updated: 6 Sep 2022 8:21 EDT
LTIMindtree
SA
Error Description:
org.postgresql.util.PSQLException: ERROR: could not create unique index "pr_sys_serviceregistry_pk" [java] Detail: Key (pysessionid)=(97bbb1a9-27e8-45fd-935c-69f2b2aafa30) is duplicated. [java] at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552) [java] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284) [java] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322) [java] at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481) [java] at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401) [java] at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322) [java] at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308) [java] at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284) [java] at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:279) [java] at com.pega.pegarules.install.SQLLoaderInternal.readAndExecuteSQLStatements(SQLLoaderInternal.java:160) [java] at com.pega.pegarules.install.SQLLoaderInternal.load(SQLLoaderInternal.java:119) [java] at com.pega.pegarules.install.SQLLoader.loadInner(SQLLoader.java:99) [java] at com.pega.pegarules.install.SQLLoader.load(SQLLoader.java:78) [java] at com.pega.pegarules.install.SQLLoader.main(SQLLoader.java:66) [java] 2022-09-05 13:50:18,274 ( SQLLoaderInternal) WARNING - Statement: ALTER TABLE pega_cs_data.pr_sys_serviceregistry [java] ADD CONSTRAINT "pr_sys_serviceregistry_pk" PRIMARY KEY [java] ( [java] "pysessionid" [java] ) [java] 2022-09-05 13:50:18,275 ( SQLLoaderInternal) WARNING - [java] ******************************************************************************** [java] [java] Encountered a SQL Exception, clean schema before running again. [java] [java] ******************************************************************************** [java] Exception in thread "main" com.pega.pegarules.install.SQLLoaderException: Error executing SQL statements [java] at com.pega.pegarules.install.SQLLoader.load(SQLLoader.java:81) [java] at com.pega.pegarules.install.SQLLoader.main(SQLLoader.java:66) [java] Caused by: org.postgresql.util.PSQLException: ERROR: could not create unique index "pr_sys_serviceregistry_pk" [java] Detail: Key (pysessionid)=(97bbb1a9-27e8-45fd-935c-69f2b2aafa30) is duplicated. [java] at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2552) [java] at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2284) [java] at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:322) [java] at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:481) [java] at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:401) [java] at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:322) [java] at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:308) [java] at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:284) [java] at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:279) [java] at com.pega.pegarules.install.SQLLoaderInternal.readAndExecuteSQLStatements(SQLLoaderInternal.java:160) [java] at com.pega.pegarules.install.SQLLoaderInternal.load(SQLLoaderInternal.java:119) [java] at com.pega.pegarules.install.SQLLoader.loadInner(SQLLoader.java:99) [java] at com.pega.pegarules.install.SQLLoader.load(SQLLoader.java:78) [java] ... 1 more
[java] 2022-09-05 13:50:18,272 ( SQLLoaderInternal) INFO - Failed to execute SQL in file postgres16.sql.
==================================================================================================
From the above statement, it's evident that, Issue will occur when some primary key constraints or Indexes found duplicates. In such cases, to RESUME the upgrade process, We need to do the following.
From the downloaded Pega Platform, in the Path <Drive-Path>\117260_Pega8.6.5\schema\generated\postgres\update\statements, SQL Statements will be availlable which will be used in the Upgrade script execution. From the found error during the script exection, We need to identify the SQL file or step where the execution failed to determine that all the previous ones executed successfully i.e.. the above issue occured during the execution of postgreSQL.16 file and all previous statements (postgreSQL-1 - 16) can be removed from the folder and invoke the script execution again to fix the issue.