如何实现双向会话存在则更新、不存在则插入的消息功能？

1. 先给Conversations表加唯一约束（核心） #

要避免双向会话重复，首先在Conversations表上创建基于LEAST和GREATEST的唯一索引，确保不管两个用户ID的顺序如何，只会有一条会话记录：

CREATE UNIQUE INDEX idx_conversations_unique_pair 
ON conversations (LEAST(user_id1, user_id2), GREATEST(user_id1, user_id2));

同时建议插入会话时，主动把较小的ID放到user_id1、较大的ID放到user_id2，既配合索引生效，也让后续查询更直观。

2. 编写PL/pgSQL函数统一处理消息逻辑 #

触发器无生效大概率是因为会话存在性判断逻辑有漏洞，不如直接写一个可调用的函数，一站式处理会话创建/更新、消息插入、收件箱同步：

CREATE OR REPLACE FUNCTION send_message(
    p_sender_id INT,
    p_recipient_id INT,
    p_content TEXT
) RETURNS INT AS $$
DECLARE
    v_conversation_id INT;
BEGIN
    -- 检查是否存在双向会话
    SELECT id INTO v_conversation_id
    FROM conversations
    WHERE (user_id1 = p_sender_id AND user_id2 = p_recipient_id)
       OR (user_id1 = p_recipient_id AND user_id2 = p_sender_id);

    -- 会话不存在则创建
    IF v_conversation_id IS NULL THEN
        INSERT INTO conversations (user_id1, user_id2, created_at, last_message_at)
        VALUES (LEAST(p_sender_id, p_recipient_id), GREATEST(p_sender_id, p_recipient_id), NOW(), NOW())
        RETURNING id INTO v_conversation_id;

        -- 给双方初始化收件箱记录
        INSERT INTO inbox (user_id, conversation_id, unread_count)
        VALUES (p_sender_id, v_conversation_id, 0),
               (p_recipient_id, v_conversation_id, 1);
    ELSE
        -- 会话存在则更新最后消息时间
        UPDATE conversations
        SET last_message_at = NOW()
        WHERE id = v_conversation_id;

        -- 更新收件箱未读计数（收件人+1）
        UPDATE inbox
        SET unread_count = unread_count + 1
        WHERE user_id = p_recipient_id AND conversation_id = v_conversation_id;
    END IF;

    -- 插入消息记录
    INSERT INTO messages (conversation_id, sender_id, content, sent_at)
    VALUES (v_conversation_id, p_sender_id, p_content, NOW());

    RETURN v_conversation_id;
END;
$$ LANGUAGE plpgsql;

3. 查询无重复的用户会话列表 #

要获取某个用户的所有会话，关联会话、收件箱和用户表即可：

SELECT 
    c.id AS conversation_id,
    CASE 
        WHEN c.user_id1 = :current_user_id THEN u2.username 
        ELSE u1.username 
    END AS partner_username,
    c.last_message_at,
    i.unread_count
FROM conversations c
JOIN inbox i ON c.id = i.conversation_id
JOIN users u1 ON c.user_id1 = u1.id
JOIN users u2 ON c.user_id2 = u2.id
WHERE i.user_id = :current_user_id; -- 替换为当前用户ID

4. 触发器失效的常见排查点 #

如果坚持用触发器，检查以下几点：

• 触发时机：是否在插入消息前（BEFORE INSERT ON messages）触发，避免会话判断滞后
• 会话匹配逻辑：是否同时覆盖了(user_id1, user_id2)和(user_id2, user_id1)两种情况
• 异常处理：触发器内是否有未捕获的异常导致事务回滚
• 权限设置：触发器函数是否有操作Conversations、Inbox表的权限

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