ОБНОВЛЕНИЕ.. ОГРАНИЧЕНИЕ 1 с SqlAlchemy и PostgreSQL

Можно ли с помощью 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: невозможно вызвать Query.update() при вызове limit()

Это имеет смысл, поскольку UPDATE ... LIMIT 1 — это функция только для MySQL (решение приведено здесь)

Но как мне сделать то же самое с PostgreSQL? Возможно, используя подход с подзапросом?


python postgresql sqlalchemy sql-update sql-limit
person kolypto    schedule 19.09.2014    source источник
comment
Лучшее решение зависит от того, должна ли каждая параллельная транзакция обновлять одну и ту же первую строку в соответствии с ORDER BY или следующую. строка, еще не заблокированная, или одиночная, случайная/произвольная строка, соответствующая некоторым критериям.   -  person Erwin Brandstetter    schedule 21.09.2014

Ответы (3)


arrow_upward
8
arrow_downward

рецепт подзапроса — правильный способ сделать это, теперь нам нужно только построить этот запрос с помощью 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
)

Наслаждаться!

person kolypto    schedule 19.09.2014
comment
... и если вы пытаетесь использовать его для организации очередей, помните о параллелизме. Это не атомарно, поэтому несколько сеансов могут захватывать и обновлять одну и ту же строку. - person Craig Ringer; 20.09.2014
comment
Это немного прискорбно, потому что во многих RDBM подзапрос напрямую не нужен. Есть ли способ заставить SQLAlchemy ограничить обновление? - person Ken Kinder; 14.01.2020

arrow_upward
2
arrow_downward

Добавлять

WHERE analyzed <> :analyzed

чтобы одна и та же строка не обновлялась несколько раз. Или же

WHERE analyzed IS DISTINCT FROM :analyzed

если допустимы значения NULL. Добавьте то же условие во внешний UPDATE, что почти всегда в любом случае является хорошей идеей для избегайте пустых обновлений.

Параллельные транзакции, заблокированные блокировкой ROW SHARE из FOR UPDATE, пробуждаются, как только завершается первая транзакция. Поскольку измененная строка больше не соответствует условию WHERE, подзапрос не возвращает строку и ничего не происходит.

В то время как более поздние транзакции блокируют новую строку для обновления...

Вы можете использовать рекомендательные блокировки для всегда обновлять следующую разблокированную строку без ожидания. Я добавил больше в связанный ответ:

Или рассмотрите возможность PGQ для реализации очереди.

person Erwin Brandstetter    schedule 21.09.2014

arrow_upward
0
arrow_downward

моя БД не будет работать с ограничением внутри подзапроса, поэтому я использовал что-то вроде этого:

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()
person Alon Gouldman    schedule 27.10.2020