Spring Boot @Scheduled调用JPA仓库报PSQLException：relation不存在

在Spring Boot项目中，使用@Scheduled注解的方法内调用JPA Repository方法时，抛出PSQLException，提示relation "journey" does not exist，但相同的Repository方法在代码其他部分执行完全正常。

调度配置类 #

@Configuration
@EnableScheduling
public class SchedulingConfig {

    @Bean
    public TaskScheduler taskScheduler() {
        ScheduledThreadPoolExecutor executor = new ScheduledThreadPoolExecutor(1);
        executor.setThreadFactory(new ThreadFactoryBuilder().setNameFormat("Scheduled-%d").build());

        ConcurrentTaskScheduler taskScheduler = new ConcurrentTaskScheduler(executor);
        taskScheduler.setTaskDecorator(new MasterTaskDecorator());
        return taskScheduler;
    }

}

调度方法 #

@Scheduled(fixedRate = 60000, initialDelay = 60000)
public void scheduledRevalidationOfOpenJourney() {
    List<JourneyEntity> openJourneys = journeyRepository.findAllByState(JourneyState.OPEN);
    openJourneys.forEach(openJourney -> openJourney.getDatasets().forEach(validationService::removeRelatedViolation));
}

异常信息 #

2022-12-02 15:22:58.894 ERROR 32704 --- [   scheduling-1] o.h.engine.jdbc.spi.SqlExceptionHelper   : ERROR: relation "journey" does not exist
org.springframework.dao.InvalidDataAccessResourceUsageException: could not extract ResultSet; SQL [n/a]; nested exception is org.hibernate.exception.SQLGrammarException: could not extract ResultSet
2022-12-02 15:23:58.896 ERROR 32704 --- [   scheduling-1] o.s.s.s.TaskUtils$LoggingErrorHandler    : Unexpected error occurred in scheduled task
Caused by: org.hibernate.exception.SQLGrammarException: could not extract ResultSet
Caused by: org.postgresql.util.PSQLException: ERROR: relation "journey" does not exist

注：数据库中journey表确实存在。

核心问题在于定时任务线程的数据库上下文（如Schema）与业务线程不一致，或者自定义的MasterTaskDecorator未正确传递数据库连接的上下文信息：

• 业务线程可能默认使用了指定的自定义Schema，但定时任务线程未继承该上下文，导致Hibernate去查询默认Schema（如public）下的journey表，因此找不到对应表。
• MasterTaskDecorator如果用于处理上下文传递，可能存在逻辑缺陷，未将事务或Schema上下文绑定到定时任务线程。

方案1：修复TaskDecorator的上下文传递 #

修改MasterTaskDecorator，确保Spring的请求/事务上下文能传递到定时任务线程：

public class MasterTaskDecorator implements TaskDecorator {
    @Override
    public Runnable decorate(Runnable runnable) {
        // 捕获当前线程的请求上下文
        RequestAttributes attributes = RequestContextHolder.getRequestAttributes();
        return () -> {
            try {
                // 将上下文绑定到定时任务线程
                RequestContextHolder.setRequestAttributes(attributes);
                runnable.run();
            } finally {
                // 清理上下文，避免内存泄漏
                RequestContextHolder.resetRequestAttributes();
            }
        };
    }
}

如果是Schema指定问题，也可以在JourneyEntity上显式声明Schema：

@Entity
@Table(schema = "你的自定义Schema名称", name = "journey")
public class JourneyEntity {
    // ... 实体字段
}

方案2：给定时任务方法添加事务注解 #

在调度方法上添加@Transactional，确保Spring为定时任务线程创建正确的事务上下文，绑定对应数据库连接：

@Scheduled(fixedRate = 60000, initialDelay = 60000)
@Transactional
public void scheduledRevalidationOfOpenJourney() {
    List<JourneyEntity> openJourneys = journeyRepository.findAllByState(JourneyState.OPEN);
    openJourneys.forEach(openJourney -> openJourney.getDatasets().forEach(validationService::removeRelatedViolation));
}

注意：项目需已启用事务管理，即配置类上添加@EnableTransactionManagement注解。

方案3：确保Schema上下文正确传递（多租户/Schema切换场景） #

如果MasterTaskDecorator用于处理多租户或Schema切换，需确保它将当前线程的Schema信息绑定到定时任务线程：

public class MasterTaskDecorator implements TaskDecorator {
    // 假设用ThreadLocal存储当前Schema
    private final ThreadLocal<String> currentSchemaHolder = new ThreadLocal<>();

    @Override
    public Runnable decorate(Runnable runnable) {
        // 保存当前线程的Schema
        String currentSchema = currentSchemaHolder.get();
        return () -> {
            try {
                if (currentSchema != null) {
                    // 将Schema绑定到定时任务线程
                    currentSchemaHolder.set(currentSchema);
                    // 这里可添加切换Schema的逻辑，比如通过JDBC执行SET SCHEMA语句
                    // Connection connection = ...;
                    // connection.setSchema(currentSchema);
                }
                runnable.run();
            } finally {
                currentSchemaHolder.remove();
            }
        };
    }
}

内容的提问来源于stack exchange，提问作者soumik.saha.lion5