Thread leak when used as an attached database

[ran404]

Hello,

I found an issue when using genomicsqlite as an attached database. The problem occurs when the following conditions are met:

1. the query on the attached database triggers decompression on a worker thread.
2. the database that is being attached to is changed.

This means that if a genomicsqlite db is used as a reference in a loop to process different databases, the thread count will keep going up, until it reaches the upper system limit (ulimit -a), by then it will hang (and no new process is allowed to start)

I have created the following unit test code which can reliably reproduce the bug.

import os
import shutil
import sqlite3
import tempfile
from unittest import TestCase

import genomicsqlite
import psutil


class GenomicSQLiteThreadTest(TestCase):
    def setUp(self):
        self.temp_dir = tempfile.mkdtemp()

    def tearDown(self):
        shutil.rmtree(self.temp_dir, ignore_errors=True)

    def test_no_native_threads_are_being_leaked(self):
        temp_ref_db_path = self.prepare_reference_genomicsqlite_db()

        current_process = psutil.Process()
        self.assertLess(current_process.num_threads(), 50)

        for idx in range(50):
            conn = sqlite3.connect(':memory:', isolation_level=None, uri=True)

            # Attach genomicsqlite db as ref
            attach_sql = genomicsqlite.attach_sql(conn, temp_ref_db_path, 'ref', immutable=True)
            conn.executescript(attach_sql)

            # This query needs to be "big" enough otherwise problem doesn't occur
            row_count = conn.execute("SELECT COUNT(*) FROM ref.test_table;").fetchone()[0]
            self.assertEqual(row_count, 10_000)

            conn.close()

            num_threads = current_process.num_threads()
            print(f'Iteration {idx}, number of threads: {num_threads}')

            # This blows up in less than 20 iterations
            self.assertLess(num_threads, 50)

    def prepare_reference_genomicsqlite_db(self):
        temp_ref_db_path = os.path.join(self.temp_dir, 'temp_ref.db')
        ref_conn = genomicsqlite.connect(temp_ref_db_path)
        ref_conn.execute("CREATE TABLE test_table (test_col INTEGER PRIMARY KEY);")

        # Populate some data
        ref_conn.executemany("INSERT INTO test_table VALUES (?);", ((n,) for n in range(10_000)))

        ref_conn.commit()
        ref_conn.close()
        return temp_ref_db_path

[mlin]

@ran404 Thanks for this. One thing I find so far is the issue may be specific to setting immutable=True on the attach command. I will find a solution, but taking that out may be a workaround in the meantime.

[ran404]

@mlin Thanks for looking into it. I've had a play with the params, and the immutable=True parameter does indeed seem to cause the issue.

FYI all of the following param combination seem to be okay so far:

1. genomicsqlite.attach_sql(conn, dbfile, schema_name)
2. genomicsqlite.attach_sql(conn, dbfile, schema_name, immutable=False)
3. genomicsqlite.attach_sql(conn, dbfile, schema_name, immutable=False, readonly=True)
4. genomicsqlite.attach_sql(conn, dbfile, schema_name, readonly=True)

But those with immutable=True are not okay:

1. genomicsqlite.attach_sql(conn, dbfile, schema_name, immutable=True)
2. genomicsqlite.attach_sql(conn, dbfile, schema_name, immutable=True, readonly=True)