Is there a way on handling transactions in custom scripts?

[ifoughal]

I created a netbox custom script which iterates through the netbox devices given a certain pattern (approximately 4k devices), and then interrogates another service (Cisco NSO), to query certain operational data for reporting and compliance purposes.

The devices reporting is running with multi-threading, and it takes about 4 mins per device.

The issue I'm hitting currently is a scaling one, I've tried running around 100 worker threads, and unfortunately I'm hitting transaction issue, it seems that there's a limit on simultaneous transactions per script (not sure about the exact value).

Traceback of the error:

 Traceback (most recent call last):
  File "/opt/netbox/netbox/scripts/generate_report.py", line 402, in run
    generate_excel_report(
  File "/opt/netbox/netbox/scripts/generate_report.py", line 251, in generate_excel_report
    results = list(executor.map(lambda i_d: fetch_device_data(cls, i_d[1], split_interface_name, with_nso=with_nso, timeout=timeout, retry=retry, index=i_d[0]), enumerate(devices)))
              ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.11/concurrent/futures/_base.py", line 619, in result_iterator
    yield _result_or_cancel(fs.pop())
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.11/concurrent/futures/_base.py", line 317, in _result_or_cancel
    return fut.result(timeout)
           ^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.11/concurrent/futures/_base.py", line 449, in result
    return self.__get_result()
           ^^^^^^^^^^^^^^^^^^^
  File "/usr/lib/python3.11/concurrent/futures/_base.py", line 401, in __get_result
    raise self._exception
  File "/usr/lib/python3.11/concurrent/futures/thread.py", line 58, in run
    result = self.fn(*self.args, **self.kwargs)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/netbox/scripts/generate_report.py", line 251, in <lambda>
    results = list(executor.map(lambda i_d: fetch_device_data(cls, i_d[1], split_interface_name, with_nso=with_nso, timeout=timeout, retry=retry, index=i_d[0]), enumerate(devices)))
                                            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/netbox/scripts/generate_report.py", line 99, in fetch_device_data
    device_interfaces = list(Interface.objects.filter(device=device))
                        ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/models/query.py", line 398, in __iter__
    self._fetch_all()
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/models/query.py", line 1881, in _fetch_all
    self._result_cache = list(self._iterable_class(self))
                         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/models/query.py", line 91, in __iter__
    results = compiler.execute_sql(
              ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1560, in execute_sql
    cursor = self.connection.cursor()
             ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/backends/base/base.py", line 330, in cursor
    return self._cursor()
           ^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/backends/base/base.py", line 306, in _cursor
    self.ensure_connection()
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/backends/base/base.py", line 288, in ensure_connection
    with self.wrap_database_errors:
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/backends/base/base.py", line 289, in ensure_connection
    self.connect()
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/backends/base/base.py", line 270, in connect
    self.connection = self.get_new_connection(conn_params)
                      ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/utils/asyncio.py", line 26, in inner
    return func(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib/python3.11/site-packages/django/db/backends/postgresql/base.py", line 275, in get_new_connection
    connection = self.Database.connect(**conn_params)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/netbox/venv/lib/python3.11/site-packages/psycopg/connection.py", line 729, in connect
    raise ex.with_traceback(None)
django.db.utils.OperationalError: connection failed: FATAL:  sorry, too many clients already 

This traceback was triggers by the current device interfaces query: list(Interface.objects.filter(device=device)), this was run with 100 workers (1 woker per device).

Is there a way to circumvent this limitation? I know that when the job is run, there's a single transaction created for the whole job, but is there a way to create and close transactions on the fly on the same job?

[abhi1693]

This is not a transaction issue. Your db doesn't have enough connections to run the queries. You should look into scaling the db if for the amount of workers you are running.

[ifoughal]

That fixed it. Thank you.