Revisit PostgreSQL connection code

[jsstevenson]

I suspect that we could be taking greater advantage of asyncpg (not that I understand how).

[jsstevenson]

Noticed in #240 that multiple async.run calls in a single docstring were raising an asyncpg error related to resource use. Not sure if this is intractible or something about how we're using the connection pool.

______________________ [doctest] cool_seq_tool.mappers.mane_transcript.MANETranscript.g_to_mane_c ______________________
1102         ...     ))
1103         >>> type(result)
1104         <class 'cool_seq_tool.mappers.mane_transcript.CdnaRepresentation'>
1105         >>> result.status
1106         <TranscriptPriority.MANE_SELECT: 'mane_select'>
1107
1108         Locating a MANE transcript requires a ``gene`` symbol argument -- if none is
1109         given, this method will only lift over to genomic coordinates on GRCh38.
1110
1111         >>> with asyncio.Runner() as runner:
UNEXPECTED EXCEPTION: InterfaceError('cannot perform operation: another operation is in progress')
Traceback (most recent call last):
  File "/Users/jss009/code/cool-seq-tool/src/cool_seq_tool/sources/uta_database.py", line 177, in _execute_query
    async with connection.transaction():
  File "/Users/jss009/code/cool-seq-tool/venv/lib/python3.11/site-packages/asyncpg/transaction.py", line 68, in __aenter__
    await self.start()
  File "/Users/jss009/code/cool-seq-tool/venv/lib/python3.11/site-packages/asyncpg/transaction.py", line 146, in start
    await self._connection.execute(query)
  File "/Users/jss009/code/cool-seq-tool/venv/lib/python3.11/site-packages/asyncpg/connection.py", line 350, in execute
    result = await self._protocol.query(query, timeout)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "asyncpg/protocol/protocol.pyx", line 365, in query
  File "asyncpg/protocol/protocol.pyx", line 778, in asyncpg.protocol.protocol.BaseProtocol._new_waiter
  File "/Users/jss009/.pyenv/versions/3.11.0/lib/python3.11/asyncio/base_events.py", line 724, in call_later
    timer = self.call_at(self.time() + delay, callback, *args,
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/jss009/.pyenv/versions/3.11.0/lib/python3.11/asyncio/base_events.py", line 737, in call_at
    self._check_closed()
  File "/Users/jss009/.pyenv/versions/3.11.0/lib/python3.11/asyncio/base_events.py", line 519, in _check_closed
    raise RuntimeError('Event loop is closed')
RuntimeError: Event loop is closed
During handling of the above exception, another exception occurred:
Traceback (most recent call last):
  File "/Users/jss009/.pyenv/versions/3.11.0/lib/python3.11/doctest.py", line 1350, in __run
    exec(compile(example.source, filename, "single",
  File "<doctest cool_seq_tool.mappers.mane_transcript.MANETranscript.g_to_mane_c[6]>", line 2, in <module>
  File "/Users/jss009/.pyenv/versions/3.11.0/lib/python3.11/asyncio/runners.py", line 118, in run
    return self._loop.run_until_complete(task)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/jss009/.pyenv/versions/3.11.0/lib/python3.11/asyncio/base_events.py", line 650, in run_until_complete
    return future.result()
           ^^^^^^^^^^^^^^^
  File "/Users/jss009/code/cool-seq-tool/src/cool_seq_tool/mappers/mane_transcript.py", line 1139, in g_to_mane_c
    grch38 = await self.g_to_grch38(ac, start_pos, end_pos)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/jss009/code/cool-seq-tool/src/cool_seq_tool/mappers/mane_transcript.py", line 1016, in g_to_grch38
    descr = await self.uta_db.get_chr_assembly(ac)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/jss009/code/cool-seq-tool/src/cool_seq_tool/sources/uta_database.py", line 997, in get_chr_assembly
    descr = await self.get_ac_descr(ac)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/jss009/code/cool-seq-tool/src/cool_seq_tool/sources/uta_database.py", line 591, in get_ac_descr
    result = await self.execute_query(query)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/jss009/code/cool-seq-tool/src/cool_seq_tool/sources/uta_database.py", line 184, in execute_query
    result = await _execute_query(query)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/Users/jss009/code/cool-seq-tool/src/cool_seq_tool/sources/uta_database.py", line 176, in _execute_query
    async with self._connection_pool.acquire() as connection:
  File "/Users/jss009/code/cool-seq-tool/venv/lib/python3.11/site-packages/asyncpg/pool.py", line 219, in release
    raise ex
  File "/Users/jss009/code/cool-seq-tool/venv/lib/python3.11/site-packages/asyncpg/pool.py", line 209, in release
    await self._con.reset(timeout=budget)
  File "/Users/jss009/code/cool-seq-tool/venv/lib/python3.11/site-packages/asyncpg/connection.py", line 1500, in reset
    await self.execute(reset_query, timeout=timeout)
  File "/Users/jss009/code/cool-seq-tool/venv/lib/python3.11/site-packages/asyncpg/connection.py", line 350, in execute
    result = await self._protocol.query(query, timeout)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "asyncpg/protocol/protocol.pyx", line 359, in query
  File "asyncpg/protocol/protocol.pyx", line 744, in asyncpg.protocol.protocol.BaseProtocol._check_state
asyncpg.exceptions._base.InterfaceError: cannot perform operation: another operation is in progress
/Users/jss009/code/cool-seq-tool/src/cool_seq_tool/mappers/mane_transcript.py:1111: UnexpectedException

[github-actions]

This issue is stale because it has been open 45 days with no activity. Please make a comment for triaging or closing the issue.