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。
解决步骤
- 先确认第二种方案实际编译出的 SQL。如果生成的是
group_key + :param,在标准 PostgreSQL 上不可执行;如果生成的是group_key || :param,则可以执行。 - 优先使用显式的
op("||"),并用cast(..., JSONB)或literal(..., JSONB)把右侧对象声明为 JSONB。 - 执行后检查
rowcount。为 0 表示没有匹配到目标行;这条 UPDATE 不会自动插入。 - 是否在函数内
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)
)
注意事项
- JSONB 的键必须是字符串,
str(user_session_id)比直接传 UUID 更稳;值要能被 JSON 序列化,tuple 会变成数组。 rowcount == 0表示没有匹配到group_id/user_id的行;需要 upsert 时另写INSERT ... ON CONFLICT。commit()提交的是当前事务,不只这一条 UPDATE;如果上层已经管理事务,避免重复提交。- 在 REPEATABLE READ/SERIALIZABLE 下,并发冲突可能抛序列化失败,需要按业务重试。
- 若同一
session_id并发写入,||的右侧会覆盖左侧同名键;需要保留历史时改用数组或独立表。
备注:内容仅供参考。
