使用SqlAlchemy和PostgreSQL进行UPDATE LIMIT 1操作

Question

使用SqlAlchemy，是否可以构建一个仅更新第一个匹配行的查询？

在我的情况下，我需要更新最近的日志条目。

class Log(Base):
    __tablename__ = 'logs'
    id = Column(Integer, primary_key=True)
    #...
    analyzed = Column(Boolean)

session.query(Log)  \
    .order_by(Log.id.desc())  \
    .limit(1)  \
    .update({ 'analyzed': True })

这将导致:

InvalidRequestError: 当limit()已被调用时，无法调用Query.update()

这是有道理的，因为UPDATE ... LIMIT 1是仅适用于MySQL的功能（解决方案在这里给出）

但我该如何在PostgreSQL中实现相同的功能呢？可能需要使用子查询方法？

- kolypto

---

最佳解决方案取决于每个并发事务是否应该根据 ORDER BY 更新相同的第一行，还是更新下一个未锁定的行，或者单个匹配某些条件的随机/任意行。 - Erwin Brandstetter

Answer 1

子查询是正确的做法，现在我们只需要使用SqlAlchemy来构建这个查询。

让我们从子查询开始：

sq = ssn.query(Log.id)  \
    .order_by(Log.id.desc())  \
    .limit(1)  \
    .with_for_update()

现在将其与as_scalar()一起使用，使用update()文档中的示例：

from sqlalchemy import update

q = update(Log)  \
    .values({'analyzed': True})  \
    .where(Log.id == sq.as_scalar())

将查询打印出来以查看结果：

UPDATE logs 
SET analyzed=:analyzed 
WHERE logs.id = (
    SELECT logs.id 
    FROM logs ORDER BY logs.id DESC 
    LIMIT :param_1 
    FOR UPDATE
)

祝愉快！

- kolypto

---

如果您正在尝试将其用于排队，请考虑并发性。它不是原子的，因此多个会话可以抓取和更新相同的行。 - Craig Ringer

这有点不幸，因为在许多关系型数据库管理系统中并不直接需要子查询。有没有一种方法可以强制SQLAlchemy限制更新？ - Ken Kinder

Answer 2

添加

WHERE analyzed <> :analyzed

为了防止同一行被多次更新。或者。

WHERE analyzed IS DISTINCT FROM :analyzed

如果允许NULL值，请在外部的UPDATE中添加相同的条件。在任何情况下都是一个好主意，以避免空更新。

并发事务由于来自FOR UPDATE的ROW SHARE锁而被阻塞，一旦第一个事务完成，它们就会被唤醒。由于更改后的行不再满足WHERE条件，子查询不返回任何行，因此什么也不会发生。

虽然稍后的事务会锁定要更新的新行...

您可以使用咨询锁定，以便始终更新下一个未锁定的行而无需等待。我在链接的答案中添加了更多内容:

• Postgres UPDATE ... LIMIT 1

或者考虑PGQ来实现队列。

- Erwin Brandstetter

Answer 3

我的数据库无法在子查询中使用limit - 所以我最终使用了类似这样的语句:

log_id = session.query(Log.id)  \
    .order_by(Log.id.desc())  \
    .limit(1)
log_id = [log.id for log in log_id]
session.query(Log).filter(Log.id.in_(log_id)).delete()

- Alon Gouldman