CODEWITHSUNDEEP
pythonsqldjangopostgresqldjango-settings
user2880391
user2880391

Reputation: 2791

Django - PostgreSQL set statement_timeout

I'm using Django 1.10 and PostgreSQL DB. I'm trying to figure out whether I can set statement_timeout from Django. Seems like I can't do it the same way as for connect_timeout (in settings.py):

DATABASES[DEFAULT]['OPTIONS'] = {
    'connect_timeout': float(os.getenv('DEFAULT_DB_OPTIONS_TIMEOUT', 5))
}

I saw something like this, but I can't find a way to verify it actually works:

DATABASES[DEFAULT]['OPTIONS'] = {
    'options': '-d statement_timeout=700'
}

I know I can set it directly from the DB like:

set statement_timeout=5000

but I'm looking for a way to do it from Django.

Upvotes: 8

Views: 9296

Answers (2)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

I use Django 3.2.16 on Windows 11 then, I set '-c statement_timeout=5000' in OPTIONS and 'SET statement_timeout = 5000;' with cursor.execute() in settings.py as shown below:

# "settings.py"

from django.db import connection

# ...

DATABASES = {
    'default':{
        'ENGINE':'django.db.backends.postgresql',
        'NAME':'postgres',
        'USER':'postgres',
        'PASSWORD':'admin',
        'HOST':'localhost',
        'PORT':'5432',
        # 'ATOMIC_REQUESTS': True,
    },
    'OPTIONS': {
        'options': '-c statement_timeout=5000' # Here
     }
}

cursor = connection.cursor()
cursor.execute('SET statement_timeout = 5000;') # Here

Then, test view with cursor.execute('SELECT pg_sleep(10);') below runs without any errors because statement_timeout = 5000 in settings.py doesn't work in test view:

# "views.py"

from django.db import connection
from django.http import HttpResponse

def test(request):
    cursor = connection.cursor()
    cursor.execute('SELECT pg_sleep(10);')
    
    return HttpResponse("Test")

But, when setting statement_timeout = 5000 in test view as shown below:

# "views.py"

from django.db import connection
from django.http import HttpResponse

def test(request):
    cursor = connection.cursor()
    cursor.execute('SET statement_timeout = 5000;')
    cursor.execute('SELECT pg_sleep(10);')
    
    return HttpResponse("Test")

The error below occurs because statement_timeout = 5000 in test view works properly. *cursor.execute('SET statement_timeout = 5000;') only applies to test view rather than other views so you need to call cursor.execute('SET statement_timeout = 5000;') in each view if you want to apply cursor.execute('SET statement_timeout = 5000;') to each view:

django.db.utils.OperationalError: canceling statement due to statement timeout

Upvotes: 2

expz
expz

Reputation: 1698

There is no other "Django" way of doing this. The Django way is to use your settings.py like you indicate, except your example is not quite right. It should be:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        ...
        'OPTIONS': {
            'options': '-c statement_timeout=5000',
            ...
        }
    }
}

The 'OPTIONS' dictionary is for arguments to the database connection string. The list of Postgres arguments can be found here. The options connection string argument allows you to specify command-line arguments for postgres. The -c option is explained here.

Note that the timeout is measured in milliseconds, so the 5000 above means 5 seconds.

Upvotes: 17

Related Questions