HTTP Status 500 – Internal Server Error
Type Exception Report

Message compliance-0.000-22.000.sql : SqlExecutor.execute(); bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: column "ownerentityid" of relation "signedsnapshot" already exists

Description The server encountered an unexpected condition that prevented it from fulfilling the request.

Exception

org.labkey.api.util.ConfigurationException: compliance-0.000-22.000.sql : SqlExecutor.execute(); bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: column "ownerentityid" of relation "signedsnapshot" already exists
	org.labkey.api.util.ExceptionUtil.renderErrorPage(ExceptionUtil.java:1036)
	org.labkey.api.util.ExceptionUtil.handleException(ExceptionUtil.java:953)
	org.labkey.api.util.ExceptionUtil.handleException(ExceptionUtil.java:681)
	org.labkey.api.security.AuthFilter.doFilter(AuthFilter.java:96)
	org.labkey.core.filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:120)
Root Cause

org.labkey.api.data.SqlScriptRunner$SqlScriptException: compliance-0.000-22.000.sql : SqlExecutor.execute(); bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: column "ownerentityid" of relation "signedsnapshot" already exists
	org.labkey.api.data.SqlScriptManager.runScript(SqlScriptManager.java:210)
	org.labkey.api.data.SqlScriptRunner.runScripts(SqlScriptRunner.java:84)
	org.labkey.api.module.DefaultModule.versionUpdate(DefaultModule.java:319)
	org.labkey.api.module.ModuleUpgrader.upgrade(ModuleUpgrader.java:98)
	org.labkey.api.module.ModuleUpgrader.lambda$upgrade$0(ModuleUpgrader.java:110)
	java.base/java.lang.Thread.run(Thread.java:833)
Root Cause

org.springframework.jdbc.BadSqlGrammarException: SqlExecutor.execute(); bad SQL grammar []; nested exception is org.postgresql.util.PSQLException: ERROR: column "ownerentityid" of relation "signedsnapshot" already exists
	org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.doTranslate(SQLStateSQLExceptionTranslator.java:101)
	org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:70)
	org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
	org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:79)
	org.labkey.api.data.ExceptionFramework$1.translate(ExceptionFramework.java:44)
	org.labkey.api.data.SqlExecutor.execute(SqlExecutor.java:120)
	org.labkey.api.data.SqlExecutor.execute(SqlExecutor.java:74)
	org.labkey.api.data.SqlExecutor.execute(SqlExecutor.java:69)
	org.labkey.api.data.SqlScriptExecutor$Block.execute(SqlScriptExecutor.java:163)
	java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
	org.labkey.api.data.SqlScriptExecutor.execute(SqlScriptExecutor.java:103)
	org.labkey.api.data.dialect.SqlDialect.runSql(SqlDialect.java:593)
	org.labkey.api.data.SqlScriptManager.runScript(SqlScriptManager.java:205)
	org.labkey.api.data.SqlScriptRunner.runScripts(SqlScriptRunner.java:84)
	org.labkey.api.module.DefaultModule.versionUpdate(DefaultModule.java:319)
	org.labkey.api.module.ModuleUpgrader.upgrade(ModuleUpgrader.java:98)
	org.labkey.api.module.ModuleUpgrader.lambda$upgrade$0(ModuleUpgrader.java:110)
	java.base/java.lang.Thread.run(Thread.java:833)
Root Cause

org.postgresql.util.PSQLException: ERROR: column "ownerentityid" of relation "signedsnapshot" already exists
	org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
	org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
	org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
	org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:496)
	org.postgresql.jdbc.PgStatement.execute(PgStatement.java:413)
	org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:333)
	org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:319)
	org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:295)
	org.postgresql.jdbc.PgStatement.execute(PgStatement.java:290)
	org.apache.tomcat.dbcp.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:181)
	org.apache.tomcat.dbcp.dbcp2.DelegatingStatement.execute(DelegatingStatement.java:181)
	org.labkey.api.data.dialect.StatementWrapper.execute(StatementWrapper.java:2052)
	org.labkey.api.data.SqlExecutor$NormalStatementExecutor.execute(SqlExecutor.java:147)
	org.labkey.api.data.SqlExecutor$NormalStatementExecutor.execute(SqlExecutor.java:135)
	org.labkey.api.data.SqlExecutor.execute(SqlExecutor.java:113)
	org.labkey.api.data.SqlExecutor.execute(SqlExecutor.java:74)
	org.labkey.api.data.SqlExecutor.execute(SqlExecutor.java:69)
	org.labkey.api.data.SqlScriptExecutor$Block.execute(SqlScriptExecutor.java:163)
	java.base/java.util.ArrayList.forEach(ArrayList.java:1511)
	org.labkey.api.data.SqlScriptExecutor.execute(SqlScriptExecutor.java:103)
	org.labkey.api.data.dialect.SqlDialect.runSql(SqlDialect.java:593)
	org.labkey.api.data.SqlScriptManager.runScript(SqlScriptManager.java:205)
	org.labkey.api.data.SqlScriptRunner.runScripts(SqlScriptRunner.java:84)
	org.labkey.api.module.DefaultModule.versionUpdate(DefaultModule.java:319)
	org.labkey.api.module.ModuleUpgrader.upgrade(ModuleUpgrader.java:98)
	org.labkey.api.module.ModuleUpgrader.lambda$upgrade$0(ModuleUpgrader.java:110)
	java.base/java.lang.Thread.run(Thread.java:833)
Note The full stack trace of the root cause is available in the server logs.



----------- Excerpt from log file -------------
ERROR Table                    2023-04-06T10:40:27,466           Module Upgrade : SQL  [367745]
    DO $$
    BEGIN

        IF (SELECT NOT EXISTS (SELECT 1 FROM pg_namespace WHERE nspname = 'compliance')) THEN

            CREATE SCHEMA compliance;

            CREATE TABLE compliance.TermsOfUse
            (
                RowId SERIAL NOT NULL,
                Container ENTITYID NOT NULL,

                Activity VARCHAR(100),
                IRB VARCHAR(100),
                PHI BOOLEAN NOT NULL DEFAULT false,
                Term TEXT NOT NULL,
                SortOrder DOUBLE PRECISION NOT NULL,

                CreatedBy USERID,
                Created TIMESTAMP,
                ModifiedBy USERID,
                Modified TIMESTAMP,

                CONSTRAINT PK_TermsOfUse PRIMARY KEY (RowId)
            );

            CREATE TABLE compliance.SignedSnapshot
            (
                RowId SERIAL NOT NULL,
                Container ENTITYID NOT NULL,
                SchemaName VARCHAR(200),
                TableName VARCHAR(200),
                SignedFileName VARCHAR(195),
                Rows INTEGER,
                FileSize BIGINT,
                Reason TEXT,
                SignedBy USERID,
                Signed TIMESTAMP,
                EntityId ENTITYID,
                Hash VARCHAR(200),

                CONSTRAINT PK_SignedSnapshot PRIMARY KEY (RowId)
            );

        END IF;

    END
    $$;



    ALTER TABLE compliance.SignedSnapshot ADD COLUMN OwnerEntityId ENTITYID;