changeset 77049308ed5d in trytond:default
details: https://hg.tryton.org/trytond?cmd=changeset;node=77049308ed5d
description:
Replace advisory lock by SKIP LOCKED in queue pulling
It is simpler and closer to standard SQL.
issue8910
review288701002
diffstat:
CHANGELOG | 1 +
trytond/backend/database.py | 5 +++++
trytond/backend/postgresql/database.py | 23 ++++++++++++++++++++++-
trytond/ir/queue.py | 31 ++++++++++++++++++-------------
4 files changed, 46 insertions(+), 14 deletions(-)
diffs (139 lines):
diff -r b108dd82df17 -r 77049308ed5d CHANGELOG
--- a/CHANGELOG Sat Dec 28 18:00:01 2019 +0100
+++ b/CHANGELOG Sat Dec 28 18:05:25 2019 +0100
@@ -1,3 +1,4 @@
+* Replace advisory lock by SKIP LOCKED in queue pulling
* Load WSGI middleware from configuration
* Add help text to Dict keys
* Update button_action with returned values
diff -r b108dd82df17 -r 77049308ed5d trytond/backend/database.py
--- a/trytond/backend/database.py Sat Dec 28 18:00:01 2019 +0100
+++ b/trytond/backend/database.py Sat Dec 28 18:05:25 2019 +0100
@@ -1,6 +1,7 @@
# This file is part of Tryton. The COPYRIGHT file at the top level of
# this repository contains the full copyright notices and license terms.
from collections import namedtuple
+from sql import For
DatabaseIntegrityError = None
DatabaseOperationalError = None
@@ -164,6 +165,10 @@
"Return if database supports FOR UPDATE/SHARE clause in SELECT."
return False
+ def get_select_for_skip_locked(self):
+ "Return For class with skip locked"
+ return For
+
def has_window_functions(self):
"Return if database supports window functions."
return False
diff -r b108dd82df17 -r 77049308ed5d trytond/backend/postgresql/database.py
--- a/trytond/backend/postgresql/database.py Sat Dec 28 18:00:01 2019 +0100
+++ b/trytond/backend/postgresql/database.py Sat Dec 28 18:05:25 2019 +0100
@@ -30,7 +30,7 @@
from psycopg2 import OperationalError as DatabaseOperationalError
from psycopg2.extras import register_default_json, register_default_jsonb
-from sql import Flavor, Cast
+from sql import Flavor, Cast, For
from sql.functions import Function
from sql.operators import BinaryOperator
@@ -67,6 +67,12 @@
cursor.execute(self, sql, args)
+class ForSkipLocked(For):
+ def __str__(self):
+ assert not self.nowait, "Can not use both NO WAIT and SKIP LOCKED"
+ return super().__str__() + (' SKIP LOCKED' if not self.nowait else '')
+
+
class Unaccent(Function):
__slots__ = ()
_function = 'unaccent'
@@ -132,6 +138,7 @@
_search_path = None
_current_user = None
_has_returning = None
+ _has_select_for_skip_locked = None
_has_unaccent = {}
flavor = Flavor(ilike=True)
@@ -413,6 +420,20 @@
def has_select_for(self):
return True
+ def get_select_for_skip_locked(self):
+ if self._has_select_for_skip_locked is None:
+ connection = self.get_connection()
+ try:
+ # SKIP LOCKED clause is available since PostgreSQL 9.5
+ self._has_select_for_skip_locked = (
+ self.get_version(connection) >= (9, 5))
+ finally:
+ self.put_connection(connection)
+ if self._has_select_for_skip_locked:
+ return ForSkipLocked
+ else:
+ return For
+
def has_window_functions(self):
return True
diff -r b108dd82df17 -r 77049308ed5d trytond/ir/queue.py
--- a/trytond/ir/queue.py Sat Dec 28 18:00:01 2019 +0100
+++ b/trytond/ir/queue.py Sat Dec 28 18:05:25 2019 +0100
@@ -93,15 +93,20 @@
order_by=[
queue.scheduled_at.nulls_first,
queue.expected_at.nulls_first]))
- selected = With('id', query=candidates.select(
- candidates.id,
- where=((candidates.scheduled_at <= CurrentTimestamp())
- | (candidates.scheduled_at == Null))
- & database.lock_id(candidates.id),
- order_by=[
- candidates.scheduled_at.nulls_first,
- candidates.expected_at.nulls_first],
- limit=1))
+ selected = queue.select(
+ queue.id,
+ where=((queue.name == name) if name else Literal(True))
+ & (queue.dequeued_at == Null)
+ & ((queue.scheduled_at <= CurrentTimestamp())
+ | (queue.scheduled_at == Null)),
+ order_by=[
+ queue.scheduled_at.nulls_first,
+ queue.expected_at.nulls_first],
+ limit=1)
+ if database.has_select_for():
+ For = database.get_select_for_skip_locked()
+ selected.for_ = For('UPDATE')
+
next_timeout = With('seconds', query=candidates.select(
Min(Extract('second',
candidates.scheduled_at - CurrentTimestamp())
@@ -111,8 +116,8 @@
task_id, seconds = None, None
if database.has_returning():
query = queue.update([queue.dequeued_at], [CurrentTimestamp()],
- where=queue.id == selected.select(selected.id),
- with_=[candidates, selected, next_timeout],
+ where=queue.id.in_(selected),
+ with_=[candidates, next_timeout],
returning=[
queue.id, next_timeout.select(next_timeout.seconds)])
cursor.execute(*query)
@@ -121,8 +126,8 @@
task_id, seconds = row
else:
query = queue.select(queue.id,
- where=queue.id == selected.select(selected.id),
- with_=[candidates, selected])
+ where=queue.id.in_(selected),
+ with_=[candidates])
cursor.execute(*query)
row = cursor.fetchone()
if row: