Closing django ORM connections in a multi-threaded environment

Question

I have below code in standalone script which is using django orm (outside django) with multithreading.

import threading 

MAX_THREADS = 30
semaphore = threading.Semaphore(value=MAX_THREADS)

books = Books.objects.all()
for book in books:
    book_id = book.id
    t = threading.Thread(target=process_book, args=[book_id])
    t.start()
    threads.append(t)

    for t in threads:
        t.join()

def process_book(book_id):
    semaphore.acquire()
    book = Books.objects.get(id=book_id)
    # Do some time taking stuff here
    book.save()
    semaphore.release()

Once the number of threads reaches MAX_CLIENT_CONN setting of postgres (which is 100 by default), I start getting following error with further calls:

operationalError at FATAL: remaining connection slots are reserved for non-replication superuser connections

Researching this I got to solutions of using a database pooler like pgbouncer, However that only stalls the new connections until connections are available but again after django's query wat timeout I hit

OperationalError at / query_wait_timeout server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

I understand that this is happening because the threads are not closing the db connections they are making but I am not sure how to even close the orm call connections? Is there something I could be doing differently in above code flow to reduce the number of connections?

I need to do a get on individual instances in order to update them because .update() or .save() wont work on queryset items.

Guiana Annimali · Answer 1 · 2022-01-04T01:13:37.187

0

this update the field in all the books on the database

for book in books:
    Books.objects.filter(id=book.id).bulk_update(book, ['field to update'])

update each single book

def process_book(book_id):
    semaphore.acquire()
    book = get_object_or_404(Books, id=book_id).update(field)   # Do some time taking stuff here

    semaphore.release()

edited Jan 04 '22 at 01:13

answered Jan 04 '22 at 01:07

Guiana Annimali

64
7

Didn't quite get it. How is this reducing number of connections? – MohitC Jan 04 '22 at 05:12

score 0 · Answer 2 · answered Sep 20 '22 at 19:59

Just close the database connections at the end of your threads

from django import db

def process_book(book_id):
    semaphore.acquire()
    book = Books.objects.get(id=book_id)
    # Do some time taking stuff here
    book.save()
    semaphore.release()
    db.connections.close_all()

Closing django ORM connections in a multi-threaded environment

2 Answers2