PAYATHON 2026

SQLAlchemy PostgreSQL JSONB 原子更新:|| 与 + 的差异

数据小陈

结论

如果第二种写法最终被 SQLAlchemy/PostgreSQL 编译成单条 UPDATE ... SET group_key = group_key || $1 WHERE ...,它在原子性和效率上与第一种写法基本一致。关键在于,标准 PostgreSQL 的 JSONB 合并操作符是 ||,+ 不是 JSONB 的原生合并操作符;除非你的 SQLAlchemy 类型或方言把 + 重载为 ||,否则会报 operator does not exist: jsonb + jsonb。所以更推荐显式写 op("||"),或者使用 SQLAlchemy 中明确的 JSONB 合并表达式。

原因

  • 整个操作就是一条 UPDATE。PostgreSQL 在行锁下读取当前行版本,计算 group_key || 新对象,再写回。不需要先 SELECT,也不会把“读-改-写”拆成应用层的多次往返。
  • 同一行的并发 UPDATE 会等待行锁。在 READ COMMITTED 下,后到的 UPDATE 通常会基于最新行版本重新计算 SET 表达式,因此不同 session_id 的并发合并一般不会互相覆盖。如果同一个 key 并发写入,后提交的结果会覆盖先提交的结果,这是业务语义决定的。
  • 如果最终 SQL 都是 group_key || 参数,cast 和 literal 的区别只在绑定参数与类型声明上,执行计划、I/O 和锁开销没有本质差别。显式写 || 更清楚。
  • 两种写法都应使用绑定参数,不要用字符串拼接 JSON。

解决步骤

  1. 先确认第二种方案实际编译出的 SQL。如果生成的是 group_key + :param,在标准 PostgreSQL 上不可执行;如果生成的是 group_key || :param,则可以执行。
  2. 优先使用显式的 op("||"),并用 cast(..., JSONB) 或 literal(..., JSONB) 把右侧对象声明为 JSONB。
  3. 执行后检查 rowcount。为 0 表示没有匹配到目标行;这条 UPDATE 不会自动插入。
  4. 是否在函数内 commit(),按项目的事务边界决定。

代码示例

from sqlalchemy import update, cast
from sqlalchemy.dialects.postgresql import JSONB

async def add_group_member_session_group_key(
    session: AsyncSession, group_id: int, user_id: int,
    user_session_id: uuid.UUID,
    session_group_key: tuple
) -> int:
    stmt = (
        update(GroupChatMember)
        .where(
            GroupChatMember.group_id == group_id,
            GroupChatMember.user_id == user_id,
        )
        .values(
            group_key=GroupChatMember.group_key.op("||")(
                cast({str(user_session_id): session_group_key}, JSONB)
            )
        )
    )
    result = await session.execute(stmt)
    await session.commit()
    return result.rowcount

如果使用 literal,仍建议显式保留 ||:

from sqlalchemy import literal

group_key=GroupChatMember.group_key.op("||")(
    literal({str(user_session_id): session_group_key}, JSONB)
)

注意事项

  1. JSONB 的键必须是字符串,str(user_session_id) 比直接传 UUID 更稳;值要能被 JSON 序列化,tuple 会变成数组。
  2. rowcount == 0 表示没有匹配到 group_id/user_id 的行;需要 upsert 时另写 INSERT ... ON CONFLICT。
  3. commit() 提交的是当前事务,不只这一条 UPDATE;如果上层已经管理事务,避免重复提交。
  4. 在 REPEATABLE READ/SERIALIZABLE 下,并发冲突可能抛序列化失败,需要按业务重试。
  5. 若同一 session_id 并发写入,|| 的右侧会覆盖左侧同名键;需要保留历史时改用数组或独立表。

备注:内容仅供参考。