def get_session_lock(db: mediawords.db.DatabaseHandler, lock_type: str, lock_id: int, wait: bool = False) -> bool: """Get a postgres advisory lock with the lock_type and lock_id as the two keys. Arguments: db - db handle lock_type - must be in LOCK_TYPES dict above lock_id - id for the particular lock within the type wait - if true, block while waiting for the lock, else return false if the lock is not available Returns: True if the lock is available """ lock_type = str(decode_object_from_bytes_if_needed(lock_type)) if isinstance(lock_id, bytes): lock_id = decode_object_from_bytes_if_needed(lock_id) lock_id = int(lock_id) if isinstance(wait, bytes): wait = decode_object_from_bytes_if_needed(wait) wait = bool(wait) log.debug("trying for lock: %s, %d" % (lock_type, lock_id)) if lock_type not in LOCK_TYPES: raise McDBLocksException("lock type not in LOCK_TYPES: %s" % lock_type) lock_type_id = LOCK_TYPES[lock_type] if wait: db.query("select pg_advisory_lock(%(a)s, %(b)s)", {'a': lock_type_id, 'b': lock_id}) return True else: r = db.query("select pg_try_advisory_lock(%(a)s, %(b)s) as locked", {'a': lock_type_id, 'b': lock_id}).hash() return r['locked']
def release_session_lock(db: mediawords.db.DatabaseHandler, lock_type: str, lock_id: int) -> None: """Release the postgres advisory lock if it is held.""" lock_type = str(decode_object_from_bytes_if_needed(lock_type)) if isinstance(lock_id, bytes): lock_id = decode_object_from_bytes_if_needed(lock_id) lock_id = int(lock_id) if lock_type not in LOCK_TYPES: raise McDBLocksException("lock type not in LOCK_TYPES: %s" % lock_type) lock_type_id = LOCK_TYPES[lock_type] db.query("select pg_advisory_unlock(%(a)s, %(b)s)", {'a': lock_type_id, 'b': lock_id})
def release_session_lock(db: mediawords.db.DatabaseHandler, lock_type: str, lock_id: int) -> None: """Release the postgres advisory lock if it is held.""" lock_type = str(decode_object_from_bytes_if_needed(lock_type)) lock_id = int(decode_object_from_bytes_if_needed(lock_id)) if lock_type not in LOCK_TYPES: raise McDBLocksException("lock type not in LOCK_TYPES: %s" % lock_type) lock_type_id = LOCK_TYPES[lock_type] db.query("select pg_advisory_unlock(%(a)s, %(b)s)", { 'a': lock_type_id, 'b': lock_id })
def main(): db = mediawords.db.connect_to_db() counts = db.query(""" select class, count(*) as count, min(last_updated::date) as min_date, max(last_updated::date) as max_date from ( select *, rank() over ( partition by class, (args->>'media_id')::int order by last_updated desc ) as media_rank, args->>'media_id' as media_id from job_states ) q where media_rank = 1 and state in( 'queued', 'running') and last_updated < now() - interval '1 day' group by class order by class """).hashes() if len(counts) > 0: print("Long Running Jobs:\n") for count in counts: print("%s: %d (%s - %s)" % (count['class'], count['count'], count['min_date'], count['max_date']))
def fetch_posts_from_api( self, query: str, start_date: datetime, end_date: datetime, sample: Optional[int] = None, page_size: Optional[int] = None, ) -> list: """Return posts from a csv that are within the given date range.""" db = mediawords.db.connect_to_db() assert sample is None, "Sampling is not implemented." assert page_size is None, "Page size limiting is not supported." if self.mock_enabled: query = self._insert_mock_data(db, get_mock_data()) table = query if re.search(r'[^[a-z][A-Z][0-9]_]', table): raise McPostgresGenericDataException( f'illegal table name: {table}') posts = db.query( f""" select content, publish_date, author, post_id, channel from {table} where publish_date::timestamp between %(a)s and %(b)s """, { 'a': start_date, 'b': end_date }).hashes() return posts
def _get_topic_domain(db: DatabaseHandler, topic: dict, domain: str) -> dict: """Get a topic_domain.""" return db.query( 'select * from topic_domains where topics_id = %(a)s and domain = %(b)s', { 'a': topic['topics_id'], 'b': domain }).hash()
def get_session_lock(db: mediawords.db.DatabaseHandler, lock_type: str, lock_id: int, wait: bool = False) -> bool: """Get a postgres advisory lock with the lock_type and lock_id as the two keys. Arguments: db - db handle lock_type - must be in LOCK_TYPES dict above lock_id - id for the particular lock within the type wait - if true, block while waiting for the lock, else return false if the lock is not available Returns: True if the lock is available """ lock_type = str(decode_object_from_bytes_if_needed(lock_type)) if isinstance(lock_id, bytes): lock_id = decode_object_from_bytes_if_needed(lock_id) lock_id = int(lock_id) if isinstance(wait, bytes): wait = decode_object_from_bytes_if_needed(wait) wait = bool(wait) log.debug("trying for lock: %s, %d" % (lock_type, lock_id)) if lock_type not in LOCK_TYPES: raise McDBLocksException("lock type not in LOCK_TYPES: %s" % lock_type) lock_type_id = LOCK_TYPES[lock_type] if wait: db.query("select pg_advisory_lock(%(a)s, %(b)s)", { 'a': lock_type_id, 'b': lock_id }) return True else: r = db.query("select pg_try_advisory_lock(%(a)s, %(b)s) as locked", { 'a': lock_type_id, 'b': lock_id }).hash() return r['locked']
def _insert_mock_data(self, db: DatabaseHandler, data: list) -> str: """Insert the mock data into the test table and return the test table name.""" table = 'postgres_post_fetcher_test' db.query(f""" create table {table} ( id serial primary key, content text, publish_date text, author text, channel text, post_id text ) """) for d in data: db.create(table, d) return table
def _insert_mock_data(self, db: DatabaseHandler, data: list) -> str: """Insert the mock data into the test table and return the test table name.""" table = 'postgres_post_fetcher_test' db.query(f""" CREATE TABLE {table} ( id BIGSERIAL PRIMARY KEY, content TEXT NULL, publish_date TEXT NULL, author TEXT NULL, channel TEXT NULL, post_id TEXT NULL ) """) for d in data: db.create(table, d) return table
def list_session_locks(db: mediawords.db.DatabaseHandler, lock_type: str) -> list: """Return a list of all locked ids for the given lock_type.""" lock_type = str(decode_object_from_bytes_if_needed(lock_type)) if lock_type not in LOCK_TYPES: raise McDBLocksException("lock type not in LOCK_TYPES: %s" % lock_type) lock_type_id = LOCK_TYPES[lock_type] return db.query( "select objid from pg_locks where locktype = 'advisory' and classid = %(a)s", {'a': lock_type_id}).flat()
def _id_exists_in_db(db: db.DatabaseHandler, guid: str) -> bool: """Internal method to check if item exists in the database.""" guid_exists = db.query( "select 1 from stories s join media m using (media_id) where m.name = 'AP' and s.guid = %(a)s", { 'a': guid }).hash() if guid_exists: logger.debug( 'Story with guid: {} is already in the database -- skipping story.' ) return True return False
def list_session_locks(db: mediawords.db.DatabaseHandler, lock_type: str) -> list: """Return a list of all locked ids for the given lock_type.""" lock_type = str(decode_object_from_bytes_if_needed(lock_type)) if lock_type not in LOCK_TYPES: raise McDBLocksException("lock type not in LOCK_TYPES: %s" % lock_type) lock_type_id = LOCK_TYPES[lock_type] return db.query( "select objid from pg_locks where locktype = 'advisory' and classid = %(a)s", { 'a': lock_type_id }).flat()
def main(): if len(sys.argv) < 2: raise Error("usage: dump_topic_maps.py <snapshots_id>") snapshots_id = sys.argv[1] db = mediawords.db.connect_to_db() snapshot = db.require_by_id('snapshots', snapshots_id) timespan_maps = db.query( """ select t.*, tm.*, f.name focus_name, fs.name focal_set_name from timespans t join timespan_maps tm using ( timespans_id ) left join foci f using ( foci_id ) left join focal_sets fs using ( focal_sets_id ) where t.snapshots_id = %(a)s """, { 'a': snapshots_id }).hashes() for tm in timespan_maps: filename = "%s.%s" % (tm['options'].get('color_by', 'default'), tm['format']) directory = '%d/%s/%s/%s/%s' % ( snapshot['topics_id'], tm['focal_set_name'], tm['focus_name'], tm['period'], tm['start_date'][0:10], ) os.makedirs(directory, exist_ok=True) full_path = "%s/%s" % (directory, filename) log.warning("writing %s..." % full_path) f = open(full_path, 'wb') f.write(tm['content'])
def main(): db = mediawords.db.connect_to_db() counts = db.query(""" select class, count(*) as count, min(last_updated::date) as min_date, max(last_updated::date) as max_date from ( select *, rank() over ( partition by class, (args->>'media_id')::int order by last_updated desc ) as media_rank, args->>'media_id' as media_id from job_states ) q where media_rank = 1 and state in( 'queued', 'running') and last_updated < now() - interval '1 day' group by class order by class """).hashes() if len(counts) > 0: print("Long Running Jobs:\n") for count in counts: print("%s: %d (%s - %s)" % (count['class'], count['count'], count['min_date'], count['max_date']))
def main(): limit = sys.argv[1] if len(sys.argv) > 1 else 10000000 db = mediawords.db.connect_to_db() media = db.query( """ select m.*, mh.* from media m join media_health mh using ( media_id ) where dup_media_id is null order by m.media_id asc limit %(a)s """, { 'a': limit }).hashes() media_groups = {} num_media = len(media) for i, medium in enumerate(media): domain = mediawords.util.url.get_url_distinctive_domain(medium['url']) log.warning("%s [%d/%d]" % (domain, i, num_media)) if domain not in media_groups: media_groups[domain] = [] media_groups[domain].append(medium) medium['medium_domain'] = domain medium['dup_domain_matches'] = True dup_media = db.query( "select m.*, mh.* from media m join media_health mh using ( media_id ) where dup_media_id = %(a)s", { 'a': medium['media_id'] }).hashes() media_groups[domain].extend(dup_media) for dup_medium in dup_media: dup_domain = mediawords.util.url.get_url_distinctive_domain( dup_medium['url']) medium['medium_domain'] = dup_domain medium['dup_domain_matches'] = domain == dup_domain db.query("drop table if exists media_dups") db.query(""" create table media_dups ( domain text, media_id int ) """) fieldnames = \ 'domain media_id dup_media_id name url medium_domain dup_domain_matches num_stories num_sentences'.split() db.begin() media_dups_rows = [] for i, domain in enumerate(media_groups.keys()): log.warning("domain %s [%d/%d]" % (domain, i, len(media_groups.keys()))) media = media_groups[domain] if len(media) > 1: for m in media: db.query( """ insert into media_dups (domain, media_id) values (%(a)s, %(b)s) """, { 'a': domain, 'b': m['media_id'] }) db.commit()
def main(): db = mediawords.db.connect_to_db() waiting_topics = db.query(""" with topic_jobs as ( select *, ( args->>'topics_id' )::bigint topics_id from job_states where class like '%Topic%' and coalesce( message, '' ) not like '%is already running%' ), ranked_jobs as ( select *, rank() over ( partition by topics_id order by last_updated desc ) from topic_jobs ) select t.name, j.topics_id, j.state, j.class, j.last_updated, j.job_states_id, coalesce( j.message, '' ) as message, j.args->>'snapshots_id' snapshots_id, now() n, rank r from ranked_jobs j join topics t on ( t.topics_id = ( j.args->>'topics_id' )::bigint ) where last_updated < now() - '1 day'::interval and rank = 1 and j.state not in ( 'completed' ) and coalesce( j.message, '' ) not like 'canceled%' and coalesce( j.message, '' ) not like '%exceeds topic max stories%' and coalesce( j.message, '' ) not like '%eed_query returned more than%' and last_updated > now() - interval '180 days' order by topics_id desc """).hashes() log.info("waiting topics: %d" % len(waiting_topics)) hung_topics = filter(lambda x: x['state'] != 'error', waiting_topics) for topic in hung_topics: print( f"queueing topic: {topic['topics_id']}: {topic['name']} - {topic['state']} {topic['last_updated']}" ) topics_id = topic['topics_id'] snapshots_id = topic['snapshots_id'] queue_job(topic, snapshots_id) errored_topics = filter(lambda x: x['state'] == 'error', waiting_topics) for topic in errored_topics: topics_id = topic['topics_id'] snapshots_id = topic['snapshots_id'] print( f"{topics_id}: {topic['name']} - {topic['state']} {topic['last_updated']}" ) print(topic['snapshots_id']) print(f"\t{topic['message'][0:100]}") print( f"\thttps://topics.mediacloud.org/#/topics/{topics_id}/summary\n") while True: action = input( '(r)equeue, (d)elete fetch errors, (c)ancel, (i)gnore, or (f)ull message? ' ) if action == 'r': # requeue a spider job for the topic print('requeueing...') queue_job(topic, snapshots_id) break elif action == 'd': # delete all topic_fetch_url python errors -- do this if we know the cause of the errors # and want the topic to succeed any way rather than triggering a 'fetch error rate ... is greater' err print('deleting topic_fetch_url errors...') db.query( "DELETE FROM topic_fetch_urls WHERE topics_id = %(a)s AND state = 'python error'", {'a': topics_id}) elif action == 'c': # prepend the 'canceled: ' string to the start of the error message so that topic job will be # ignored by future runs of this script print('canceling...') db.update_by_id('job_states', topic['job_states_id'], {'message': f'canceled: {topic["message"]}'}) break elif action == 'i': # ignore this topic for this run only print('ignoring...') break elif action == 'f': jobs = db.query( """ SELECT * FROM job_states WHERE class LIKE '%Topic%' AND args->>'topics_id' = %(a)s::TEXT ORDER BY job_states_id """, { 'a': topics_id }).hashes() [ print( f"{job['last_updated']} {job['class']}\n{job['message']}\n****" ) for job in jobs ]
def main(): limit = sys.argv[1] if len(sys.argv) > 1 else 10000000 db = mediawords.db.connect_to_db() media = db.query(""" select m.*, mh.* from media m join media_health mh using ( media_id ) where dup_media_id is null order by m.media_id asc limit %(a)s """, {'a': limit}).hashes() media_groups = {} num_media = len(media) for i, medium in enumerate(media): domain = mediawords.util.url.get_url_distinctive_domain(medium['url']) log.warning("%s [%d/%d]" % (domain, i, num_media)) if domain not in media_groups: media_groups[domain] = [] media_groups[domain].append(medium) medium['medium_domain'] = domain medium['dup_domain_matches'] = True dup_media = db.query( "select m.*, mh.* from media m join media_health mh using ( media_id ) where dup_media_id = %(a)s", {'a': medium['media_id']} ).hashes() media_groups[domain].extend(dup_media) for dup_medium in dup_media: dup_domain = mediawords.util.url.get_url_distinctive_domain(dup_medium['url']) medium['medium_domain'] = dup_domain medium['dup_domain_matches'] = domain == dup_domain db.query("drop table if exists media_dups"); db.query( """ create table media_dups ( domain text, media_id int ) """) fieldnames = \ 'domain media_id dup_media_id name url medium_domain dup_domain_matches num_stories num_sentences'.split() db.begin() media_dups_rows = [] for i, domain in enumerate(media_groups.keys()): log.warning("domain %s [%d/%d]" %(domain, i, len(media_groups.keys()))) media = media_groups[domain] if len(media) > 1: for m in media: db.query( """ insert into media_dups (domain, media_id) values (%(a)s, %(b)s) """, {'a': domain, 'b': m['media_id']}) db.commit()
def _get_topic_domain(db: DatabaseHandler, topic: dict, domain: str) -> dict: """Get a topic_domain.""" return db.query( 'select * from topic_domains where topics_id = %(a)s and domain = %(b)s', {'a': topic['topics_id'], 'b': domain}).hash()