JDBC执行PostgreSQL函数超时，pgAdmin执行正常求原因

背景与问题 #

我们有一个PostgreSQL函数，基于复杂查询向数据库插入数据。数据库为Azure单服务器上的PostgreSQL 11（16核、32GB内存），最近一次执行需插入6000行数据。

该函数的查询存在低效连接、排序/去重等可能导致内存占用过高的问题，这部分后续会处理，但当前核心问题是：

• 通过Spring Boot应用的JDBC执行该函数时，耗时极长（20分钟甚至无限期），应用使用Java Tomcat连接池，约有25个空闲连接被复用；
• 通过pgAdmin执行同一函数时，每次都能在约1分钟内完成，本地PostgreSQL实例执行也正常。

核心疑问：为什么执行时间差异极大？如果是内存/交换问题（比如work_mem过低），那不应该所有连接都受影响吗？

执行语句：SELECT * FROM create_change_reviews(10);

PostgreSQL函数代码 #

CREATE
    OR REPLACE FUNCTION create_change_reviews(rev integer)
    RETURNS TABLE
            (
                created  int,
                type     text,
                duration interval
            )
    LANGUAGE plpgsql
AS
$function$
DECLARE
    created_reviews
                    int;
    start_time
                    timestamp;
BEGIN
    SELECT clock_timestamp()
    INTO start_time;

    WITH
        relevant_documents as (
            SELECT DISTINCT
                mc.id           as mc_id,
                mc.oid          as mc_oid,
                mc.role_name    as importance_role
            FROM so_document_version dv
                     JOIN so_module m ON dv.id = m.document_version_id and m.import_id = rev
                     JOIN so_module_changedescription mc ON
                        m.id = mc.module_id AND mc.importance IN ('must know')
        ),
        changes_with_effect AS (
            SELECT DISTINCT
                u.id as user_id,
                rd.mc_oid as oid
            FROM relevant_documents rd
                     JOIN so_user u ON u.id > 0
                     JOIN so_user_role ur ON u.id = ur.user_id
                     JOIN so_role r ON ur.role_id = r.id AND rd.importance_role = r.name
                     JOIN so_effect_role er
                          ON per.role_name = rd.importance_role = r.name
                     JOIN so_module_change_effect mce ON mce.effect_id = er.effect_id AND rd.mc_id = mce.change_id
        ),
        changes_no_effect AS (
            SELECT DISTINCT
                u.id as user_id,
                rd.mc_oid as oid
            FROM relevant_documents rd
                     LEFT JOIN so_module_change_effect ce on rd.mc_id = ce.change_id
                     JOIN so_user u ON u.id > 0
                     JOIN so_user_role ur ON u.id = ur.user_id
                     JOIN so_role r ON ur.role_id = r.id AND rd.importance_role = r.name
            WHERE ce.effect_id is null
        ),
        all_changes AS (
            SELECT user_id, oid FROM changes_with_effect
            UNION
            SELECT user_id, oid FROM changes_no_effect
        )
    INSERT
    INTO so_module_change_review(id, read, user_id, change_oid, created, created_by, updated, updated_by)
    SELECT nextval('hibernate_sequence'),
           false,
           nuvr.user_id,
           nuvr.oid,
           current_timestamp,
           -1,
           current_timestamp,
           -1
    FROM (
             SELECT DISTINCT user_id, oid
             FROM all_changes ac
         ) nuvr ON CONFLICT DO NOTHING;

    GET DIAGNOSTICS created_reviews = ROW_COUNT;
    RAISE
        NOTICE 'Created % module reviews', created_reviews;

    RETURN QUERY SELECT created_reviews, 'module', clock_timestamp() - start_time;
END
$function$
;

Spring Batch Tasklet代码 #

public class CreateReviewsTask implements Tasklet {

    protected Logger logger = LoggerFactory.getLogger(this.getClass());

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public RepeatStatus execute(StepContribution contribution, ChunkContext chunkContext) throws Exception {
        SessionImpl session = unboxSession();
        Connection connection = session.connection();
        boolean prevAutoCommitValue = connection.getAutoCommit();
        connection.setAutoCommit(true); // disable transactions
        try {
            execute(connection, contribution, chunkContext);
        } catch (SQLException e) {
            logger.error("Database task failed", e);
            contribution.setExitStatus(ExitStatus.FAILED);
            throw e;
        } finally {
            connection.setAutoCommit(prevAutoCommitValue);
        }

        return null;
    }

    private SessionImpl unboxSession() {
        Object delegate = entityManager.getDelegate();
        if (delegate instanceof SessionImpl) {
            return (SessionImpl)delegate;
        }

        return (SessionImpl)entityManager.unwrap(Session.class);
    }

    protected void execute(Connection connection, StepContribution contribution, ChunkContext chunkContext) throws SQLException {
        long importId = Long.parseLong(chunkContext.getStepContext().getJobExecutionContext().get(IMPORT_ID).toString());
        try (PreparedStatement ps = connection.prepareStatement("SELECT * FROM create_change_reviews(" + importId + ");")) {
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    int creationCount = rs.getInt("created");
                    String type = rs.getString("type");
                    String duration = rs.getString("duration");
                    logger.info("Created {} {} reviews in {}", creationCount, type, duration);
                    contribution.incrementFilterCount(creationCount);
                    contribution.incrementWriteCount(creationCount);
                }
            }
        } catch (SQLException e) {
            logger.error("Error creating reviews", e);
            throw new ImportException("Error creating reviews. " + e.getMessage(), e);
        }
    }
}

附加信息 #

• 数据库资源占用低（CPU、内存、磁盘均无峰值）
• 若有遗漏的相关信息，请告知

可能的原因分析 #

• 事务与自动提交设置差异：Spring Boot代码中手动修改连接的autoCommit为true，而pgAdmin的默认事务设置不同。PostgreSQL函数本身在事务上下文执行，修改autoCommit可能导致事务行为异常，比如中间结果无法被优化，或触发不必要的事务边界，拖慢执行速度。
• 连接参数不一致：pgAdmin和Tomcat连接池的会话参数可能不同，比如会话级work_mem——pgAdmin可能设置了更高值，让排序/去重在内存完成；而Tomcat连接池沿用全局较低值，导致大量磁盘临时文件操作。此外jit、search_path等参数差异也可能导致查询计划不同。
• 连接池会话状态残留：Tomcat连接池复用的空闲连接可能残留之前的会话级设置（如临时修改的work_mem、事务隔离级别），导致当前函数使用不合适的参数执行；而pgAdmin每次使用新连接，参数为默认值，效率更高。
• JDBC驱动处理逻辑差异：JDBC驱动执行函数时的结果集处理方式可能与pgAdmin不同，比如executeQuery等待整个函数执行完成才返回结果，而pgAdmin可能采用更高效的处理逻辑，间接导致延迟。
• 隐性资源竞争：尽管数据库资源占用低，但Spring Boot连接可能持有隐性锁，或连接池内存在资源竞争，导致函数执行时出现无感知的等待。

内容的提问来源于stack exchange，提问作者dube