PostgreSQL存储函数中DELETE过期vehicle_data语句失效问题

问题原因 #

PostgreSQL中NOW()/CURRENT_TIMESTAMP返回的是事务开始时间，在整个事务执行期间保持固定不变。你的store_vehicle_data函数运行在事务中，test_store_vehicle_data又循环调用它15次（同属一个事务），导致函数内的NOW()始终是事务启动时的时间点——如果事务启动时部分记录还未到过期时间（或因容器时间同步问题导致时间判断偏差），DELETE语句就无法正确识别过期数据。

而手动执行DELETE时是单独事务，NOW()是实时时间，所以能正常删除过期记录。

解决方案 #

1. 替换NOW()为clock_timestamp() #

使用clock_timestamp()获取实时时间（每次调用都会更新），代替事务固定的NOW()，确保过期判断准确：

修改store_vehicle_data函数中的删除语句和插入语句：

CREATE OR REPLACE FUNCTION store_vehicle_data(
    _container_id BIGINT,
    _osm_node_ids BIGINT[],
    _customer_id INTEGER,
    _use_case_id INTEGER,
    _node_limit INTEGER,
    _retention_time INTERVAL
)
RETURNS BOOLEAN AS $$
DECLARE
    _osm_node_id BIGINT;
    _row_count INTEGER;
    _should_send_pull_container BOOLEAN := TRUE;
BEGIN
    -- 使用clock_timestamp()获取实时时间判断过期
    DELETE FROM vehicle_data
    WHERE clock_timestamp() > expires_at;

    -- Insert new records
    FOREACH _osm_node_id IN ARRAY _osm_node_ids LOOP
        BEGIN
            INSERT INTO vehicle_data (
                osm_node_id,
                customer_id, 
                use_case_id, 
                container_id, 
                expires_at
            ) VALUES (
                _osm_node_id, 
                _customer_id, 
                _use_case_id, 
                _container_id,
                clock_timestamp() + _retention_time -- 实时时间加留存期
            );
        EXCEPTION WHEN foreign_key_violation THEN
            RAISE EXCEPTION 'Invalid customer_id % or use_case_id % for osm_node_id % container_id: %',
                _customer_id, _use_case_id, _osm_node_id, _container_id;
        END;

        -- Check if the number of records exceeds the node limit
        SELECT COUNT(*)
        INTO STRICT _row_count
        FROM vehicle_data
        WHERE osm_node_id = _osm_node_id
        AND customer_id = _customer_id
        AND use_case_id = _use_case_id;

        IF _row_count > _node_limit THEN
            _should_send_pull_container := FALSE;
        END IF;
    END LOOP;

    RETURN _should_send_pull_container;
END;
$$ LANGUAGE plpgsql;

2. 添加expires_at索引优化删除性能 #

每次全表扫描删除过期数据会影响性能，给expires_at字段添加索引：

CREATE INDEX idx_vehicle_data_expires_at ON vehicle_data (expires_at);

3. 优化计数逻辑（可选） #

当前每次插入后执行COUNT(*)会重复扫描表，可改为先查询当前计数，插入后自增，减少IO消耗：

-- 替换原计数逻辑
SELECT COUNT(*) INTO _row_count
FROM vehicle_data
WHERE osm_node_id = _osm_node_id
AND customer_id = _customer_id
AND use_case_id = _use_case_id;

-- 插入操作
INSERT INTO vehicle_data (...) VALUES (...);

_row_count := _row_count + 1;

IF _row_count > _node_limit THEN
    _should_send_pull_container := FALSE;
END IF;

验证修改 #

重新执行冒烟测试，Test2将能正确删除过期数据，返回预期的10个TRUE和5个FALSE。

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