11

I set up a postgresql server using Heroku in addition to my python bot which also runs on heroku but the bot fails to connect to the database

I secured that the password username etc. where correct.

This is the method used to connect:

async def create_db_pool():
    bot.pg_con = await asyncpg.create_pool(database="dbname", 
    user="username", 
    password="dbpw")

And this is how i run it:

bot.loop.run_until_complete(create_db_pool())

It is expected to access the database and write and read data instead i receive following error:

asyncpg.exceptions.ConnectionDoesNotExistError: connection was closed in the middle of operation
Task was destroyed but it is pending!
task: <Task pending coro=<chng_pr() running at I:/Python/HardCoreDisBot/Commands.py:38> wait_for=<Future pending cb=[<TaskWakeupMethWrapper object at 0x000002571E9B1978>()]>>
scottlittle
  • 18,866
  • 8
  • 51
  • 70
Akira
  • 111
  • 1
  • 3
  • I'm not sure about your "closed in the middle" error, but you shouldn't be hard-coding database credentials. Sensitive information doesn't belong in your code. On Heroku, you can [attach one database to multiple apps](https://devcenter.heroku.com/articles/heroku-postgresql#sharing-heroku-postgres-between-applications) and then use the environment variable to connect as usual. – ChrisGPT was on strike Jul 07 '19 at 14:37
  • @Chris sorry i am not aware on how to do this – Akira Jul 07 '19 at 15:30
  • Start by following the link in my previous comment and reading what you find there. – ChrisGPT was on strike Jul 07 '19 at 16:17
  • 1
    The code seems ok (so far), you need to show a little bit more (how you create `bot`, which SQL you run, closing connections) – Beppe C Nov 01 '20 at 16:26
  • you can user threading lock each thread should take the lock and close it after finishing... – Omar Kamoon Nov 07 '20 at 11:49

2 Answers2

2

So I looked at your problem and found a thread that had a similar problem and they seemed to find a work around by putting max_inactive_connection_lifetime in their code like this. this is the link to the thread.

async def create_db_pool():
    bot.pg_con = await asyncpg.create_pool(database="dbname", 
    user="username", 
    password="dbpw",
    max_inactive_connection_lifetime=3)
Ceres
  • 2,498
  • 1
  • 10
  • 28
Oliver Hnat
  • 797
  • 4
  • 19
0

I have the same issue with Starlette.

From what I've found, the issue here is that asyncpg doesn't share the same async loop.

I've followed the suggestion of asyncpg.create_pool(..., loop=asyncio.get_event_loop()) and although I haven't gotten rid of the issue itself, at least after a refresh the connection is re-established.

It seems like a still active issue, since the time I'm writing this: https://github.com/MagicStack/asyncpg/issues/309

Sanic users faced a similar situation https://github.com/sanic-org/sanic/issues/152

EDIT

This worked for me for the time being: https://magicstack.github.io/asyncpg/current/usage.html#connection-pools

async def handle(request):
"""Handle incoming requests."""
pool = request.app['pool']
power = int(request.match_info.get('power', 10))

# Take a connection from the pool.
async with pool.acquire() as connection:
    # Open a transaction.
    async with connection.transaction():
        # Run the query passing the request argument.
        result = await connection.fetchval('select 2 ^ $1', power)
        return web.Response(
            text="2 ^ {} is {}".format(power, result))
Lopofsky
  • 518
  • 6
  • 15