Error on Inserting Session Data: psycopg2.errors.NumericValueOutOfRange: integer out of range #45782

claude89757 · 2025-01-15T02:37:01Z

claude89757
Jan 15, 2025

Apache Airflow version

2.10.4

If "Other Airflow 2 version" selected, which one?

No response

What happened?

airflow-web failed with error logs as follow：

[SQL: INSERT INTO session (session_id, data, expiry) VALUES (%(session_id)s, %(data)s, %(expiry)s) RETURNING session.id]
sqlalchemy.exc.DataError: (psycopg2.errors.NumericValueOutOfRange) integer out of range

airflow_2=> SELECT MAX(id) FROM session;
max

2147483647
(1 row)

airflow_2=> SELECT MAX(id) FROM session;
max

2147483647
(1 row)

What you think should happen instead?

No response

How to reproduce

running airflow for a long time with a lot of tasks

Operating System

centos

Versions of Apache Airflow Providers

No response

Deployment

Other Docker-based deployment

Deployment details

No response

Anything else?

[SQL: INSERT INTO session (session_id, data, expiry) VALUES (%(session_id)s, %(data)s, %(expiry)s) RETURNING session.id]
[parameters: {'session_id': 'b8a8eb6a-2f7a-496f-b409-1e2b43a06eb2', 'data': <psycopg2.extensions.Binary object at 0x7f153e16a100>, 'expiry': datetime.datetime(2025, 2, 14, 2, 19, 47, 825437, tzinfo=datetime.timezone.utc)}]
(Background on this error at: https://sqlalche.me/e/14/9h9h)
[2025-01-15T10:19:47.826+0800] {app.py:1744} ERROR - Exception on / [HEAD]
Traceback (most recent call last):
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
self.dialect.do_execute(
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.NumericValueOutOfRange: integer out of range

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/flask/app.py", line 2529, in wsgi_app
response = self.full_dispatch_request()
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/flask/app.py", line 1826, in full_dispatch_request
return self.finalize_request(rv)
^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/flask/app.py", line 1847, in finalize_request
response = self.process_response(response)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/flask/app.py", line 2344, in process_response
self.session_interface.save_session(self, ctx.session, response)
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/airflow/www/session.py", line 33, in save_session
return super().save_session(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/flask_session/sessions.py", line 568, in save_session
self.db.session.commit()
File "", line 2, in commit
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 1454, in commit
self._transaction.commit(_to_root=self.future)
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 832, in commit
self._prepare_impl()
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 811, in _prepare_impl
self.session.flush()
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 3449, in flush
self.flush(objects)
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 3588, in flush
with util.safe_reraise():
^^^^^^^^^^^^^^^^^^^
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 70, in exit
compat.raise(
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/sqlalchemy/util/compat.py", line 211, in raise
raise exception
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/sqlalchemy/orm/session.py", line 3549, in _flush
flush_context.execute()
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/sqlalchemy/orm/unitofwork.py", line 456, in execute
rec.execute(self)
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/sqlalchemy/orm/unitofwork.py", line 630, in execute
util.preloaded.orm_persistence.save_obj(
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 245, in save_obj
_emit_insert_statements(
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/sqlalchemy/orm/persistence.py", line 1238, in _emit_insert_statements
result = connection._execute_20(
^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1710, in _execute_20
return meth(self, args_10style, kwargs_10style, execution_options)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/sqlalchemy/sql/elements.py", line 334, in _execute_on_connection
return connection._execute_clauseelement(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1577, in _execute_clauseelement
ret = self._execute_context(
^^^^^^^^^^^^^^^^^^^^^^
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1953, in _execute_context
self.handle_dbapi_exception(
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2134, in handle_dbapi_exception
util.raise(
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/sqlalchemy/util/compat.py", line 211, in raise
raise exception
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 1910, in _execute_context
self.dialect.do_execute(
File "/opt/bitnami/airflow/venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 736, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.DataError: (psycopg2.errors.NumericValueOutOfRange) integer out of range

Are you willing to submit PR?

Yes I am willing to submit a PR!

Code of Conduct

I agree to follow this project's Code of Conduct

claude89757 · 2025-01-15T03:37:00Z

claude89757
Jan 15, 2025
Author

Fix for `airflow-web` Issue

Check the Maximum ID:
- Run the query:
  SELECT MAX(id) FROM session;
  Result: 2147483647
Delete Recent Entries:
- Execute the query:
  DELETE FROM session WHERE id IN (SELECT id FROM session ORDER BY id DESC LIMIT 1000000);
Verify the New Maximum ID:
- Run the query:
  SELECT MAX(id) FROM session;
  Result: 2146483647

This procedure temporarily resolves the issue with airflow-web by clearing a large number of session records.

0 replies

claude89757 · 2025-01-15T03:38:04Z

claude89757
Jan 15, 2025
Author

The final optimization plan requires optimizing the SQL command used to initialize Airflow.

0 replies

potiuk · 2025-01-18T18:50:11Z

potiuk
Jan 18, 2025
Collaborator

I think you have something seriously, serously wrong with your setup: having 2147483647 sessions means more than 2 billion(!) sessions. .

This is by no means possible to be generated by human traffic - this is likely something that is generated by some badly designed API calls - where each API call is logging in, running (or even not) a call and logging out immediately. Which means that you never reuse sessions to run multiple API calls (which I would expect you shoudl do if you want to use APIS).

Just to put it in perspective. There are 31 Million seconds in a year. So if you start running 10 API requessts /s with Airlfow doing login/logout you would need more than 6 years of continuous 10/req/s calls 24hrs/day 7 days a week to get to 2 billion sessions.

I think you should review what you are doing with your API calls, because you are doing something seriously wrong.

1 reply

claude89757 Jan 18, 2025
Author

thanks for your reply，i will check my airflow api logs in webserver and comeback to here late

potiuk · 2025-01-18T18:52:04Z

potiuk
Jan 18, 2025
Collaborator

Converting to a discussion if more discussion is needed.

0 replies

claude89757 · 2025-01-20T04:19:03Z

claude89757
Jan 20, 2025
Author

Through this issue, I believe there is a problem with the use of the auto-increment mechanism for the session table in Airflow's database. The specific reasons are as follows:

I already have a scheduled task to execute from airflow.utils.db_cleanup import run_cleanup to clean up the database cache.
However, this cache cleanup operation does not resolve the issue of the id in the session table continuously increasing and eventually exhausting.
I urge the Airflow project developers to address this issue, as it could affect the long-term stability of the Airflow project.

@potiuk

0 replies

claude89757 · 2025-01-20T05:44:45Z

claude89757
Jan 20, 2025
Author

redirect to #45794

0 replies

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Error on Inserting Session Data: psycopg2.errors.NumericValueOutOfRange: integer out of range #45782

{{title}}

{{editor}}'s edit

{{editor}}'s edit

Replies: 6 comments 1 reply

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

{{title}}

Select a reply

Error on Inserting Session Data: psycopg2.errors.NumericValueOutOfRange: integer out of range #45782

claude89757 Jan 15, 2025

Apache Airflow version

If "Other Airflow 2 version" selected, which one?

What happened?

airflow_2=> SELECT MAX(id) FROM session; max

airflow_2=> SELECT MAX(id) FROM session; max

What you think should happen instead?

How to reproduce

Operating System

Versions of Apache Airflow Providers

Deployment

Deployment details

Anything else?

Are you willing to submit PR?

Code of Conduct

Replies: 6 comments · 1 reply

claude89757 Jan 15, 2025 Author

Fix for airflow-web Issue

claude89757 Jan 15, 2025 Author

potiuk Jan 18, 2025 Collaborator

claude89757 Jan 18, 2025 Author

potiuk Jan 18, 2025 Collaborator

claude89757 Jan 20, 2025 Author

claude89757 Jan 20, 2025 Author

claude89757
Jan 15, 2025

airflow_2=> SELECT MAX(id) FROM session;
max

airflow_2=> SELECT MAX(id) FROM session;
max

Replies: 6 comments 1 reply

claude89757
Jan 15, 2025
Author

Fix for `airflow-web` Issue

claude89757
Jan 15, 2025
Author

potiuk
Jan 18, 2025
Collaborator

claude89757 Jan 18, 2025
Author

potiuk
Jan 18, 2025
Collaborator

claude89757
Jan 20, 2025
Author

claude89757
Jan 20, 2025
Author