CURRENT_TIMESTAMP::DATE与CURRENT_DATE的差异及索引失效问题

原函数与索引定义 #

CREATE OR REPLACE FUNCTION GetCurrentDate()
  RETURNS DATE
AS $p$
BEGIN
    RETURN  CURRENT_TIMESTAMP::DATE;
END;
$p$ LANGUAGE plpgsql IMMUTABLE SECURITY DEFINER;

CREATE UNIQUE INDEX PersonPhoness_UK_PhoneNo_CountryID 
    ON person_phones (phone_no, country_id)
    WHERE   GetCurrentDate() 
        BETWEEN effective_from_date AND effective_till_date;

问题原因分析 #

• IMMUTABLE函数的错误标记：PostgreSQL中IMMUTABLE函数要求输入相同则输出永远固定，完全不依赖时间、事务状态等外部变量。但你的函数内部使用了CURRENT_TIMESTAMP——这个函数属于STABLE级别（同一事务内返回固定值，不同时间/事务返回不同值），将包含它的函数标记为IMMUTABLE违反了PostgreSQL的函数稳定性规则。

• 索引条件的固化逻辑：当PostgreSQL识别到索引WHERE条件中有IMMUTABLE函数时，会在索引创建的瞬间计算出函数结果，并将这个固定值永久写入索引的过滤规则。比如你在5月20日创建索引，函数返回2024-05-20，那索引的条件就变成了2024-05-20 BETWEEN effective_from_date AND effective_till_date。后续插入新行时，哪怕日期已经到了5月21日，索引依然用旧日期判断，而你新增行的effective_from_date是当天的CURRENT_DATE，旧日期不在新行的区间内，索引就不会触发唯一约束检查，导致重复数据漏判。

• 修改为CURRENT_DATE后的正常逻辑：当你把函数改成返回CURRENT_DATE，如果同步将函数的稳定性标记从IMMUTABLE改为STABLE（这是正确做法），PostgreSQL会在每次插入/修改行时重新计算函数值，用当前日期匹配行的时间区间，从而正确触发唯一约束，捕获重复数据。即使你没修改标记，CURRENT_DATE的语义更直接，PostgreSQL可能在部分场景下绕过错误的IMMUTABLE标记，但这属于侥幸，依赖时间的函数必须标记为STABLE。

正确的函数定义 #

CREATE OR REPLACE FUNCTION GetCurrentDate()
  RETURNS DATE
AS $p$
BEGIN
    RETURN CURRENT_DATE;
END;
$p$ LANGUAGE plpgsql STABLE SECURITY DEFINER;

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