TimescaleDB使用time_bucket不支持小于1天的时间间隔问题

在FastAPI中使用TimescaleDB的time_bucket函数时，设置如'15 minutes'这类小于1天的时间间隔会触发错误：interval must not have sub-day precision。当前使用TimescaleDB版本2.16.1，PostgreSQL版本14.12，需求是支持15分钟到1天的时间间隔以生成K线图，希望通过自动配置解决该问题，无需手动网页操作。

相关代码 #

1. FastAPI异步查询函数 #

async def get_candlestick_data(self, db: AsyncSession, market_name: str, resource_name: str, interval: str):
    sql = f"""
    SELECT 
        time_bucket('{interval}', timestamp) as period,
        FIRST(price, timestamp) as open,
        MAX(price) as high,
        MIN(price) as low,
        LAST(price, timestamp) as close,
        SUM(quantity) as volume
    FROM 
        resource_market_value
    WHERE
        market_name = :market_name AND resource_name = :resource_name
    GROUP BY 
        period
    ORDER BY 
        period;
    """
    result = await db.execute(text(sql), {'market_name': market_name, 'resource_name': resource_name})
    return result.fetchall()

2. SQLAlchemy模型 #

from sqlalchemy import DateTime
from sqlalchemy import Column, Integer, String, BigInteger
from common_modules.database.base import Base


class ResourceMarketValue(Base):
    __tablename__ = 'resource_market_value'
    id = Column(BigInteger, primary_key=True, autoincrement=True)
    market_name = Column(String, nullable=False)
    resource_name = Column(String, nullable=False)
    timestamp = Column(DateTime, nullable=False)
    price = Column(Integer, nullable=False)
    quantity = Column(Integer, nullable=False)

3. 创建超表语句 #

SELECT create_hypertable('resource_market_value', 'timestamp');

错误日志 #

2024-09-12 14:59:54.455 UTC [50740] ERROR:  interval must not have sub-day precision
2024-09-12 14:59:54.455 UTC [50740] STATEMENT:
                SELECT
                    time_bucket('15 minutes', timestamp) as period,
                    FIRST(price, timestamp) as open,
                    MAX(price) as high,
                    MIN(price) as low,
                    LAST(price, timestamp) as close,
                    SUM(quantity) as volume
                FROM
                    resource_market_value
                WHERE
                    market_name = $1 AND resource_name = $2
                GROUP BY
                    period
                ORDER BY
                    period;

2024-09-12 14:59:54.456 UTC [50741] ERROR:  could not map dynamic shared memory segment
2024-09-12 14:59:54.456 UTC [50742] ERROR:  could not map dynamic shared memory segment
2024-09-12 14:59:54.458 UTC [1] LOG:  background worker "parallel worker" (PID 50741) exited with exit code 1
2024-09-12 14:59:54.459 UTC [1] LOG:  background worker "parallel worker" (PID 50742) exited with exit code 1

解决方案 #

1. 调整超表的chunk_time_interval #

报错核心原因是创建超表时默认chunk_time_interval为1天，当time_bucket间隔小于该值时，并行聚合会触发限制。修改创建超表语句，显式设置更小的时间间隔（比如1小时，适配15分钟的聚合需求）：

SELECT create_hypertable('resource_market_value', 'timestamp', chunk_time_interval => INTERVAL '1 hour');

如果已创建超表，可通过以下语句修改现有超表的chunk间隔：

SELECT set_chunk_time_interval('resource_market_value', INTERVAL '1 hour');

2. 修复SQL注入风险 #

原代码用f-string直接拼接interval参数存在SQL注入风险，改为参数化查询：

async def get_candlestick_data(self, db: AsyncSession, market_name: str, resource_name: str, interval: str):
    sql = """
    SELECT 
        time_bucket(:interval, timestamp) as period,
        FIRST(price, timestamp) as open,
        MAX(price) as high,
        MIN(price) as low,
        LAST(price, timestamp) as close,
        SUM(quantity) as volume
    FROM 
        resource_market_value
    WHERE
        market_name = :market_name AND resource_name = :resource_name
    GROUP BY 
        period
    ORDER BY 
        period;
    """
    result = await db.execute(text(sql), {'market_name': market_name, 'resource_name': resource_name, 'interval': interval})
    return result.fetchall()

3. 可选：调整PostgreSQL动态共享内存配置 #

错误日志中出现的could not map dynamic shared memory segment可能影响并行查询，可修改PostgreSQL配置文件（postgresql.conf）中的相关参数：

dynamic_shared_memory_type = posix  # 或根据系统选择合适的类型
max_parallel_workers_per_gather = 4  # 调整并行worker数量

修改后重启PostgreSQL服务即可。

内容的提问来源于stack exchange，提问作者Matthieu Raynaud de Fitte