PostgreSQL创建视图时ORDER BY失效致编号更新异常问题

1. 视图ORDER BY不约束UPDATE处理顺序：PostgreSQL里视图定义的ORDER BY仅在直接查询视图时生效，执行UPDATE操作时，数据库不会遵循视图的排序逻辑处理行，而是按内部优化后的顺序执行，导致create_sequence_number函数的调用顺序完全随机，生成的序号自然混乱。
2. RULE机制无顺序控制能力：你创建的rule_message只是简单将视图的UPDATE操作转发到基表，没有对行的处理顺序做任何约束，无法保证按预期的created_at顺序更新字段。
3. 自定义函数依赖的调用顺序不可控：如果create_sequence_number是依靠调用顺序生成递增/递减序列，而UPDATE的行处理顺序不确定，必然会出现序号错乱的情况。

不需要创建视图和RULE，直接用窗口函数精准控制排序逻辑，再执行更新操作：

WITH ranked_messages AS (
    SELECT 
        id,
        -- 按chat_id分组，created_at倒序生成序号（顶行值最大）
        ROW_NUMBER() OVER (PARTITION BY chat_id ORDER BY created_at DESC) AS target_number
    FROM _message
    WHERE chat_id = '27936' -- 如需处理所有chat_id，删除此条件
)
UPDATE _message m
SET number = rm.target_number
FROM ranked_messages rm
WHERE m.id = rm.id;

如果create_sequence_number需要生成特定规则的序列（比如不是简单行号），可以基于窗口函数的排序结果传递参数，示例如下：

WITH ranked_messages AS (
    SELECT 
        id,
        chat_id,
        ROW_NUMBER() OVER (PARTITION BY chat_id ORDER BY created_at DESC) AS seq
    FROM _message
    WHERE chat_id = '27936'
)
UPDATE _message m
SET number = create_sequence_number(m.chat_id::text, rm.seq) -- 假设函数支持接收序号参数
FROM ranked_messages rm
WHERE m.id = rm.id;

• 窗口函数ROW_NUMBER()可以精准控制每个chat_id分组内的行顺序，确保序号严格遵循created_at的排序规则（如需正序生成，将ORDER BY created_at DESC改为ASC即可）。
• 直接使用WITH子句+UPDATE的方式，比视图+RULE的方案更简洁可靠，从根源避免了视图排序不生效的问题。

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