Exiting with ctrl-c

[rkimoakbioinformatics]

Description

For example, if a script

conn = await aiosqlite.connect('db.sqlite')
cursor = await conn.cursor()
... some work with cursor ...

is interrupted with ctrl-c and it does not exit to the console. If conn and cursor are closed before ctrl-c or inside try-except catching ctrl-c, it does.

However, it can be tricky with multiple cursors and db connections in different parts of a program, to know and close them properly when interrupted with ctrl-c.

What would be the best way to handle KeyboardInterrupt and aiosqlite? Or, would it be possible that aiosqlite does not hang when interrupted before closing connections? aiosqlite3 does not show this behavior.

Details

• OS: Windows as well as WSL
• Python version: 3.7
• aiosqlite version: 0.15.0
• Can you repro on master? I don't know how to install aiosqlite with master since I don't see setup.py there.
• Can you repro in a clean virtualenv? Yes

[rkimoakbioinformatics]

Here is another example:

conn = await aiosqlite.connect('db.sqlite')
cursor = await conn.cursor()
cursor.execute(q) # q is a SQL command which runs long.

During the execution of q, ctrl-c stops the query but the script does not exit. ctrl-c again stops the script with the following message:

Exception ignored in: <module 'threading' from '/home/rick/miniconda3/envs/py37/lib/python3.7/threading.py'>
Traceback (most recent call last):
  File "/home/rick/miniconda3/envs/py37/lib/python3.7/threading.py", line 1307, in _shutdown
    lock.acquire()
KeyboardInterrupt

Surrounding cursor.execute with try-except did not catch ctrl-c during the execution of q.

[pfmoore]

The simplest solution may be to make the thread that implements a DB connection a daemon thread. Ctrl-C interrupts the main thread, but the process won't terminate until all (non-daemon) threads have completed. It's harder if you need to tidy up the connection thread on Ctrl-C, but given that the program is exiting anyway, this might not be necessary.

[rkimoakbioinformatics]

Thanks. We ended up with just making sure all db connections get to be closed. As long as there was no open connection, the program exited cleanly.

[pfmoore]

That doesn't help in cases where (for whatever reason) the application doesn't manage to close all connections. I would prefer it if the issue was handled in the library itself. Can this issue be reopened please?

[rkimoakbioinformatics]

Oh, sure.

[cjrh]

I have a working example of CTRL-C, but it requires #89 and #90 . I'll show some code then explain what it does, and discuss why it's not so simple for the library to handle everything internally.

import asyncio, aiorun, aiosqlite

async def conn(db):
    sql = ''' select sf.name, dd.value, count(*)
        from document_data dd inner join structured_field sf on dd.field_id = sf.id
        group by sf.name, dd.value
        limit 10 '''
    try:
        print('running query...')
        async with db.execute(sql) as cursor:
            async for row in cursor:
                print('<row>')
        print('query completed')
    except asyncio.CancelledError:   # <1>
        print('interrupting the DB connection')
        await db.interrupt()

    print('leaving conn')

async def main():
    dbname = 'storage.db'
    try:
        async with aiosqlite.connect(dbname) as db:
            print('connected')
            await conn(db)
    except aiosqlite.OperationalError:
        print('query was cancelled')
    else:
        asyncio.get_running_loop().stop()

if __name__ == '__main__':
    aiorun.run(main())

My sqlite DB storage.db is ~ 6 GB, and the query above takes tens of seconds to run. Uninterrupted successful output looks like this:

$ python main.py 
connected
running query...
<row>
<row>
<row>
<row>
<row>
<row>
<row>
<row>
<row>
<row>
query completed
leaving conn
$ 

This is output if you press CTRL-C while the query is running:

$ python main.py 
connected
running query...
^CStopping the loop
interrupting the DB connection
leaving conn
query was cancelled
$ 

• You must override the default signal handlers, KeyboardInterrupt just doesn't work properly. (I am not smart enough to get it work reliably for some reason)
• Your signal handler must do task cancellation (asyncio tasks); and then in an exception handler for CancelledError, you have to call the interrupt() API from sqlite, which is also provided by aiosqlite and the code example above uses that.
• The above is using my tiny library aiorun. This is very similar to (and predates) the stdlib asyncio.run, so it handles shutdown and task cancellation, but I set up signal handlers differently. I have some support for Windows CTRL-BREAK, for example. You don't need to use aiorun specifically if you prefer not to, but you could look at it's code to how the signal handlers work.
• The exception handler at <1> could perhaps be moved into aiosqlite, but the CancelledError should still be raised out so that caller code can handle it (as a shutdown signal); that's not the tricky bit though. The tricky bit is that the sqlite.OperationalError that is raised as a consequence, is raised in the Connection.close() task. I don't know whether we'd want that exception to be absorbed, or re-raised. In my example above, our application code catches it, and we have the opportunity to check for it.

I guess what I'm trying to say is, I'm not sure whether handling the interrupt should be moved into the internals of aiosqlite or not. I think I would prefer not, but even though I'm a heavy user of sqlite I've never had to use the interrupt API, so I'm not sure.

The code example above was run on Python 3.8.5. But again note that it does depends on #89 and #90 to be able to handle the consequences of interrupt.

[rkimoakbioinformatics]

Thanks. The way the example code handles the CTRL-C would be perfect for my use case.

[cjrh]

@rkiminsilico are you running on Windows or Linux? (or mac?)

[rkimoakbioinformatics]

The package (open-cravat) is run on all three platforms (Windows, Mac, and Linux).

[cjrh]

Oops, yes I see you mentioned windows in the initial report. aiorun should work on Windows too, with respect to CTRL-C, but it hasn't been used as much on windows (as linux).