def _insert_events(
self,
c: cursor,
stored_events: List[StoredEvent],
**kwargs: Any,
) -> None:
# Acquire "EXCLUSIVE" table lock, to serialize inserts so that
# insertion of notification IDs is monotonic for notification log
# readers. We want concurrent transactions to commit inserted
# SERIAL values in order, and by locking the table for writes,
# it can be guaranteed. The EXCLUSIVE lock mode does not block
# the ACCESS SHARE lock which is acquired during SELECT statements,
# so the table can be read concurrently. However INSERT normally
# just acquires ROW EXCLUSIVE locks, which risks interleaving of
# many inserts in one transaction with many insert in another
# transaction. Since one transaction will commit before another,
# the possibility arises for readers that are tailing a notification
# log to miss items inserted later but with lower notification IDs.
# https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES
# https://www.postgresql.org/docs/9.1/sql-lock.html
# https://stackoverflow.com/questions/45866187/guarantee-monotonicity-of
# -postgresql-serial-column-values-by-commit-order
if len(stored_events) > 1:
lock_sqls = [c.mogrify(s) for s in self.lock_statements]
else:
lock_sqls = []
page_size = 500
batches = []
params = []
counter = 0
for stored_event in stored_events:
counter += 1
params.append(
(
stored_event.originator_id,
stored_event.originator_version,
stored_event.topic,
stored_event.state,
)
)
if counter == page_size:
batches.append(params)
params = []
counter = 0
if params:
batches.append(params)
for params in batches:
sqls = [
c.mogrify(
f"EXECUTE {self.insert_events_statement_name}(%s, %s, %s, %s)", args
)
for args in params
]
c.execute(b";".join(chain(lock_sqls, sqls)))
lock_sqls = []
def _insert_events(
self,
c: cursor,
stored_events: List[StoredEvent],
**kwargs: Any,
) -> None:
# Acquire "EXCLUSIVE" table lock, to serialize inserts so that
# insertion of notification IDs is monotonic for notification log
# readers. We want concurrent transactions to commit inserted
# notification_id values in order, and by locking the table for writes,
# it can be guaranteed. The EXCLUSIVE lock mode does not block
# the ACCESS SHARE lock which is acquired during SELECT statements,
# so the table can be read concurrently. However INSERT normally
# just acquires ROW EXCLUSIVE locks, which risks interleaving of
# many inserts in one transaction with many insert in another
# transaction. Since one transaction will commit before another,
# the possibility arises for readers that are tailing a notification
# log to miss items inserted later but with lower notification IDs.
# https://www.postgresql.org/docs/current/explicit-locking.html#LOCKING-TABLES
# https://www.postgresql.org/docs/9.1/sql-lock.html
# https://stackoverflow.com/questions/45866187/guarantee-monotonicity-of
# -postgresql-serial-column-values-by-commit-order
len_stored_events = len(stored_events)
# Just don't do anything if there is nothing to do.
if len_stored_events == 0:
return
# Mogrify the table lock statements.
lock_sqls = (c.mogrify(s) for s in self.lock_statements)
# Prepare the commands before getting the table lock.
page_size = 500
pages = [
(c.mogrify(
f"EXECUTE {self.insert_events_statement_name}(%s, %s, %s, %s)",
(
stored_event.originator_id,
stored_event.originator_version,
stored_event.topic,
stored_event.state,
),
) for stored_event in page)
for page in (stored_events[ndx:min(ndx +
page_size, len_stored_events)]
for ndx in range(0, len_stored_events, page_size))
]
commands = [
b";".join(page)
for page in chain([chain(lock_sqls, pages[0])], pages[1:])
]
# Execute the commands.
for command in commands:
c.execute(command)
def get_movies_by_ids(ids: List[str], cursor: _cursor) -> List[dict]:
"""
Retrieves full movies data.
"""
logger.debug(f"Looking for {len(ids)} movies")
args = ",".join(cursor.mogrify("%s", (_id, )).decode() for _id in ids)
cursor.execute(f"""
SELECT
fw.id as fw_id,
fw.title,
fw.description,
fw.rating,
fw.created_at,
fw.updated_at,
array_agg(g.name) as genres,
array_agg(p.full_name) as names,
array_agg(pfw.role) as roles,
array_agg(p.id) as persons_ids
FROM content.film_work fw
LEFT JOIN content.person_film_work pfw ON pfw.film_work_id = fw.id
LEFT JOIN content.person p ON p.id = pfw.person_id
LEFT JOIN content.genre_film_work gfw ON gfw.film_work_id = fw.id
LEFT JOIN content.genre g ON g.id = gfw.genre_id
WHERE fw.id IN ({args})
GROUP BY fw_id;
""")
movies = cursor.fetchall()
logger.debug(f"Found {len(movies)} movies by ids")
return movies
def _fetch_movies_by_persons(cursor: _cursor, persons: List[dict],
updated_after: datetime.datetime):
"""
Extracts movies where provided persons participate.
Also filters movies by updated_at.
"""
args = ",".join(
cursor.mogrify("%s", (person["id"], )).decode() for person in persons)
cursor.execute(
f"""
SELECT fw.id, fw.updated_at
FROM content.film_work fw
LEFT JOIN content.person_film_work pfw ON pfw.film_work_id = fw.id
WHERE updated_at > %s AND pfw.person_id IN ({args})
ORDER BY fw.updated_at
LIMIT {CONFIG.FETCH_FROM_PG_BY};
""", (updated_after, ))
linked_movies = cursor.fetchall()
logger.debug(f"Fetched {len(linked_movies)} linked movies")
return linked_movies
def _fetch_movies_by_genres(
cursor: _cursor, genres: List[dict],
movie_updated_after: datetime.datetime) -> List[dict]:
"""
Returns all movies related to provided genres list.
Also filters movies by provided updated_at field.
"""
args = ",".join(
cursor.mogrify("%s", (genre["id"], )).decode() for genre in genres)
cursor.execute(
f"""
SELECT fw.id, fw.updated_at
FROM content.film_work fw
LEFT JOIN content.genre_film_work gfw ON gfw.film_work_id = fw.id
WHERE updated_at > %s AND gfw.genre_id IN ({args})
ORDER BY fw.updated_at
LIMIT {CONFIG.FETCH_FROM_PG_BY};
""", (movie_updated_after, ))
linked_movies = cursor.fetchall()
logger.debug(f"Fetched {len(linked_movies)} linked movies")
return linked_movies