Issue: [Support Request] Threadlocals and pooled connections
Author: alexlatchford
State: closed
Created: 2015-10-18 20:59:25

Hi Charles,

Not sure if this is the best place for support advice so feel free to direct me elsewhere.

I'm using `PooledPostgresqlExtDatabase` class with `threadlocals=True` and `max_connections=50`. We're hitting the max number of connections within ~24 hours and I think I've narrowed down the problem to some background processing threads we spin up. We spin up 1 time use threads for tasks that do some processing in the background for a few seconds after a HTTP request is complete. 

My question is once that thread completes the connection will be returned to the pool, since it's returned will it ever be picked up by any other thread? Or is it orphaned never to be used again?

If not picked up again, (which I'm assuming is the case), I'm guessing the best solution is to use a non-pooled connection for those tasks right.

Many thanks,
Alex


> My question is once that thread completes the connection will be returned to the pool, since it's returned will it ever be picked up by any other thread? Or is it orphaned never to be used again?

Are you calling `close()` from the thread, when it finishes?

Can you share some code?


I'm not explicitly calling it at the moment, looks like if you close it you get the desired behaviour.. I wrote up this testing script:

``` python
import threading
from time import sleep
from peewee import *
from playhouse.pool import PooledPostgresqlExtDatabase


db = PooledPostgresqlExtDatabase(None, max_connections=5, threadlocals=True, register_hstore=False)
db.init('peewee_test', host='localhost', user='postgres')


counter = 1
counter_lock = threading.Lock()


def num_connections(state):
    global counter

    with counter_lock:
        print counter, " State: ", state, " - Num conns: ", len(db._in_use), \
            " - Thread: ", threading.current_thread().name
        counter += 1


class MyModel(Model):
    name = CharField()

    class Meta:
        database = db

num_connections('start')

# MyModel.create_table()

# num_connections()

# MyModel.create(name="Rosie")

# num_connections()

# db.close()


def query():
    db.connect()
    num_connections('connect')

    MyModel.select().execute()

    db.close()
    num_connections('close')

for i in xrange(5):
    thread = threading.Thread(target=query)
    thread.start()

num_connections('interval')
sleep(1)

for i in xrange(5):
    thread = threading.Thread(target=query)
    thread.start()
```

If you comment out the explicit `connect` and `close` you should have some open connections left at the end but if you call close looks like it works as expected. I'll update my code to reflect and hopefully that'll do the job!


Expected output with `connect` and `close` in place (inside `query()`):

```
alexlatchford@Alexs-Air Desktop $ python peewee_test.py 
1  State:  start  - Num conns:  0  - Thread:  MainThread
2  State:  interval  - Num conns:  0  - Thread:  MainThread
3  State:  connect  - Num conns:  1  - Thread:  Thread-1
4  State:  connect  - Num conns:  2  - Thread:  Thread-2
5  State:  connect  - Num conns:  3  - Thread:  Thread-3
6  State:  connect  - Num conns:  4  - Thread:  Thread-4
7  State:  connect  - Num conns:  4  - Thread:  Thread-5
8  State:  close  - Num conns:  4  - Thread:  Thread-1
9  State:  close  - Num conns:  3  - Thread:  Thread-2
10  State:  close  - Num conns:  2  - Thread:  Thread-3
11  State:  close  - Num conns:  1  - Thread:  Thread-4
12  State:  close  - Num conns:  0  - Thread:  Thread-5
13  State:  connect  - Num conns:  2  - Thread:  Thread-6
14  State:  connect  - Num conns:  3  - Thread:  Thread-7
15  State:  connect  - Num conns:  4  - Thread:  Thread-8
16  State:  connect  - Num conns:  4  - Thread:  Thread-9
17  State:  close  - Num conns:  3  - Thread:  Thread-6
18  State:  connect  - Num conns:  3  - Thread:  Thread-10
19  State:  close  - Num conns:  3  - Thread:  Thread-7
20  State:  close  - Num conns:  1  - Thread:  Thread-8
21  State:  close  - Num conns:  1  - Thread:  Thread-9
22  State:  close  - Num conns:  0  - Thread:  Thread-10
```

And with them commented out I get:

```
alexlatchford@Alexs-Air Desktop $ python peewee_test.py 
1  State:  start  - Num conns:  0  - Thread:  MainThread
2  State:  connect  - Num conns:  0  - Thread:  Thread-1
3  State:  connect  - Num conns:  0  - Thread:  Thread-2
4  State:  connect  - Num conns:  0  - Thread:  Thread-3
5  State:  connect  - Num conns:  0  - Thread:  Thread-4
6  State:  connect  - Num conns:  0  - Thread:  Thread-5
7  State:  interval  - Num conns:  0  - Thread:  MainThread
8  State:  close  - Num conns:  1  - Thread:  Thread-1
9  State:  close  - Num conns:  2  - Thread:  Thread-2
10  State:  close  - Num conns:  2  - Thread:  Thread-3
11  State:  close  - Num conns:  2  - Thread:  Thread-4
12  State:  close  - Num conns:  2  - Thread:  Thread-5
13  State:  connect  - Num conns:  2  - Thread:  Thread-6
14  State:  connect  - Num conns:  2  - Thread:  Thread-7

15  State:  connect  - Num conns:  2  - Thread:  Thread-8
16  State:  connect  - Num conns:  2  - Thread:  Thread-9
17  State:  connect  - Num conns:  2  - Thread:  Thread-10
18  State:  close  - Num conns:  2  - Thread:  Thread-6
19  State:  close  - Num conns:  2  - Thread:  Thread-7
20  State:  close  - Num conns:  2  - Thread:  Thread-8
21  State:  close  - Num conns:  2  - Thread:  Thread-9
22  State:  close  - Num conns:  2  - Thread:  Thread-10
```


![conn1](https://cloud.githubusercontent.com/assets/119974/10566871/20a3b3b4-75b8-11e5-80f1-3fe6d3d28498.png)

![conn2](https://cloud.githubusercontent.com/assets/119974/10566872/20a3b134-75b8-11e5-9e49-11d492432cd3.png)


Yep missed that detail, we've got the connections being used all over the show so difficult to check everywhere they're being used but certainly will be more diligent in the future :)

Thanks for the taking the time to respond.