misc: limit query to recent sessions when checking for locked objects (#34097)

This commit is contained in:
Frédéric Péters 2019-06-18 10:33:38 +02:00
parent 4d1e32130a
commit 73958864d1
2 changed files with 40 additions and 7 deletions

View File

@ -14,6 +14,7 @@
# You should have received a copy of the GNU General Public License
# along with this program; if not, see <http://www.gnu.org/licenses/>.
import os
import time
import uuid
@ -72,12 +73,26 @@ class BasicSession(Session):
del self.visiting_objects[object_key]
self.visiting_objects[key] = current_timestamp
@classmethod
def select_recent(cls, seconds=30*60, **kwargs):
objects_dir = cls.get_objects_dir()
if not os.path.exists(objects_dir):
return []
ids = []
time_limit = time.time() - seconds
for filename in os.listdir(objects_dir):
if filename[0] == '.':
continue
if os.stat(os.path.join(objects_dir, filename)).st_mtime > time_limit:
ids.append(filename)
return cls.get_ids(ids, **kwargs)
@classmethod
def get_visited_objects(cls, exclude_user=None):
# return the list of visited objects
current_timestamp = time.time()
visited_objects = {}
for session in cls.select(ignore_errors=True):
for session in cls.select_recent(ignore_errors=True):
if session.user and session.user == exclude_user:
continue
visiting_objects = getattr(session, 'visiting_objects', None)
@ -90,7 +105,7 @@ class BasicSession(Session):
@classmethod
def get_sessions_with_visited_object(cls, object_key):
for session in cls.select(ignore_errors=True):
for session in cls.select_recent(ignore_errors=True):
visiting_objects = getattr(session, 'visiting_objects', None)
if not visiting_objects:
continue
@ -114,7 +129,7 @@ class BasicSession(Session):
if object_key in (getattr(self, 'visiting_objects', None) or {}):
del self.visiting_objects[object_key]
# and from others
for session in self.__class__.select(ignore_errors=True):
for session in self.__class__.select_recent(ignore_errors=True):
if session.id == self.id:
continue
visiting_objects = getattr(session, 'visiting_objects', None)

View File

@ -659,7 +659,14 @@ def do_session_table():
AND table_name = %s''', (table_name,))
existing_fields = set([x[0] for x in cur.fetchall()])
needed_fields = set(['id', 'session_data', 'name_identifier', 'visiting_objects_keys'])
needed_fields = set(['id', 'session_data', 'name_identifier',
'visiting_objects_keys', 'last_update_time'])
# migrations
if not 'last_update_time' in existing_fields:
cur.execute('''ALTER TABLE %s ADD COLUMN last_update_time timestamp DEFAULT NOW()''' % table_name)
cur.execute('''CREATE INDEX %s_ts ON %s (last_update_time)''' % (
table_name, table_name))
# delete obsolete fields
for field in (existing_fields - needed_fields):
@ -1819,6 +1826,12 @@ class Session(SqlMixin, wcs.sessions.BasicSession):
('session_data', 'bytea'),
]
@classmethod
@guard_postgres
def select_recent(cls, seconds=30*60, **kwargs):
clause = [GreaterOrEqual('last_update_time', datetime.datetime.now() - datetime.timedelta(seconds=seconds))]
return cls.select(clause=clause, **kwargs)
@guard_postgres
def store(self):
sql_dict = {
@ -1828,6 +1841,7 @@ class Session(SqlMixin, wcs.sessions.BasicSession):
# table, they are ignored when loading the data.
'name_identifier': self.name_identifier,
'visiting_objects_keys': self.visiting_objects.keys() if getattr(self, 'visiting_objects') else None,
'last_update_time': datetime.datetime.now(),
}
conn, cur = get_connection_and_cursor()
@ -1877,7 +1891,9 @@ class Session(SqlMixin, wcs.sessions.BasicSession):
sql_statement = '''SELECT %s
FROM %s
WHERE %%(value)s = ANY(visiting_objects_keys)''' % (
WHERE %%(value)s = ANY(visiting_objects_keys)
AND last_update_time > (now() - interval '30 minutes')
''' % (
', '.join([x[0] for x in cls._table_static_fields]
+ cls.get_data_fields()),
cls._table_name)
@ -2174,7 +2190,7 @@ def get_yearly_totals(period_start=None, period_end=None, criterias=None):
return result
SQL_LEVEL = 31
SQL_LEVEL = 32
def migrate_global_views(conn, cur):
cur.execute('''SELECT COUNT(*) FROM information_schema.tables
@ -2291,7 +2307,9 @@ def migrate():
# 24: add index on evolution(formdata_id)
for formdef in FormDef.select():
do_formdef_indexes(formdef, created=False, conn=conn, cur=cur)
if sql_level < 25:
if sql_level < 32:
# 25: create session_table
# 32: add last_update_time column to session table
do_session_table()
if sql_level < 30:
# 30: actually remove evo.who on anonymised formdatas