与Hypertable关联时PostgreSQL查询结果不一致问题排查

执行以下查询时出现异常：两次执行期间表内数据完全未修改，但有时能查到行数据，有时返回空结果。其中steam."TopWishlistHistory"是基于Timescale扩展创建的Hypertable表。

原异常查询语句 #

SELECT thw."Added" FROM steam."TopWishlistHistory" thw
LEFT JOIN steam."GameCompany" gc ON gc."SteamGameId" = thw."SteamGameId" 
WHERE thw."Added" = (SELECT MAX(aa."Added") FROM steam."TopWishlistHistory" aa)

涉及的表结构 #

steam."TopWishlistHistory"表 #

CREATE TABLE IF NOT EXISTS steam."TopWishlistHistory"
(
    "Id" integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    "SteamGameId" integer NOT NULL,
    "Position" smallint,
    "Added" timestamp without time zone NOT NULL,
    CONSTRAINT steamgame_id_fk FOREIGN KEY ("SteamGameId")
        REFERENCES steam."Games" ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

steam."GameCompany"表 #

CREATE TABLE IF NOT EXISTS steam."GameCompany"
(
    "Id" integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
    "SteamGameId" integer NOT NULL,
    "CompanyId" integer NOT NULL,
    "Type" integer NOT NULL,
    "Added" timestamp(6) without time zone,
    CONSTRAINT "GameCompany_pkey" PRIMARY KEY ("Id"),
    CONSTRAINT sdfadsfdgddf FOREIGN KEY ("CompanyId")
        REFERENCES dbo."Company" ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT sdfsfdgddf FOREIGN KEY ("SteamGameId")
        REFERENCES steam."Games" ("Id") MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

可行解决方案：用CTE改写查询 #

改用CTE写法后，查询可稳定返回预期结果：

with t as (SELECT * FROM steam."TopWishlistHistory")     
SELECT t."Added" FROM t       
JOIN steam."GameCompany" gc ON gc."SteamGameId" = t."SteamGameId"  
WHERE t."Added" = (SELECT MAX("Added") FROM t)

问题原因分析 #

这种异常大概率是Timescale Hypertable的查询优化器在处理直接子查询时的执行计划偏差导致：

• 原查询中，主查询对Hypertable的扫描和子查询取MAX("Added")的扫描未同步，比如Hypertable的分块并行扫描时出现了数据视图不一致；
• CTE写法会提前物化临时数据集t，主查询和子查询都基于这个统一的数据快照计算，从而避免了扫描不一致的问题。

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