1

We are using Python 3.5, Django 1.10 and PostgreSql 9.6 DB

We needed some Auto-increment field for one of the object (Item), and couldn't achieve that using DB auto increment, since there is another dependency for the incrementation.
The field should be incremented for Item per Project (each Project can contain multiple items). We decided to use django-sequences package that basically creates another table (we're using the same DB as our models), and increments the field whenever asked to by locking the DB and increment the highest value according to relevant parameter (in our case the Project id). Seems like it works.
The problem occurs when there are multiple requests in parallel. Seems like the DB gets locked and we're starting to get 504 after few requests.
Why it happens and what could solve that?

This is the django-sequences use:

        if instance.identifier < 0:
        with transaction.atomic():
            instance.identifier = get_next_value(
                'project__' + str(instance.project.id) + '__item__identifier',
                initial_value=1,
                nowait=False
            )

Update: Seems like the 504 come from Apigee which is set to Timeout of 10 sec.

Cœur
  • 37,241
  • 25
  • 195
  • 267
user2880391
  • 2,683
  • 7
  • 38
  • 77
  • what's your db concurrency settings? how big is your connection pool? – Jason Jan 01 '18 at 14:36
  • connection pool is above 800 (max_connections). – user2880391 Jan 01 '18 at 14:52
  • And how many concurrent requests before you hit the 504? – Jason Jan 01 '18 at 15:52
  • What about using [SQL sequences](https://www.postgresql.org/docs/9.6/static/sql-createsequence.html)? Using application logic for something a database perfectly solves feels weired. If you really need to achieve one pk sequence per project you might create a sql sequence for each of your projects in the database. Read this question on how to use it: https://stackoverflow.com/questions/3379891/django-accessing-postgresql-sequence – dahrens Jan 01 '18 at 16:21

0 Answers0