You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@airflow.apache.org by "claude89757 (via GitHub)" <gi...@apache.org> on 2025/01/18 18:51:40 UTC
[D] Error on Inserting Session Data: psycopg2.errors.NumericValueOutOfRange: integer out of range [airflow]
GitHub user claude89757 created a discussion: Error on Inserting Session Data: psycopg2.errors.NumericValueOutOfRange: integer out of range
### 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=> \d session
Table "public.session"
Column | Type | Modifiers
------------+-----------------------------+------------------------------------------------------
id | integer | not null default nextval('session_id_seq'::regclass)
session_id | character varying(255) |
data | bytea |
expiry | timestamp without time zone |
Indexes:
"session_pkey" PRIMARY KEY, btree (id)
"session_session_id_key" UNIQUE CONSTRAINT, btree (session_id)
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 "<string>", 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?
- [x] Yes I am willing to submit a PR!
### Code of Conduct
- [x] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
GitHub link: https://github.com/apache/airflow/discussions/45782
----
This is an automatically sent email for commits@airflow.apache.org.
To unsubscribe, please send an email to: commits-unsubscribe@airflow.apache.org
Re: [D] Error on Inserting Session Data: psycopg2.errors.NumericValueOutOfRange: integer out of range [airflow]
Posted by "claude89757 (via GitHub)" <gi...@apache.org>.
GitHub user claude89757 closed a discussion: Error on Inserting Session Data: psycopg2.errors.NumericValueOutOfRange: integer out of range
### 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=> \d session
Table "public.session"
Column | Type | Modifiers
------------+-----------------------------+------------------------------------------------------
id | integer | not null default nextval('session_id_seq'::regclass)
session_id | character varying(255) |
data | bytea |
expiry | timestamp without time zone |
Indexes:
"session_pkey" PRIMARY KEY, btree (id)
"session_session_id_key" UNIQUE CONSTRAINT, btree (session_id)
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 "<string>", 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?
- [x] Yes I am willing to submit a PR!
### Code of Conduct
- [x] I agree to follow this project's [Code of Conduct](https://github.com/apache/airflow/blob/main/CODE_OF_CONDUCT.md)
GitHub link: https://github.com/apache/airflow/discussions/45782
----
This is an automatically sent email for commits@airflow.apache.org.
To unsubscribe, please send an email to: commits-unsubscribe@airflow.apache.org
Re: [D] Error on Inserting Session Data: psycopg2.errors.NumericValueOutOfRange: integer out of range [airflow]
Posted by "potiuk (via GitHub)" <gi...@apache.org>.
GitHub user potiuk added a comment to the discussion: Error on Inserting Session Data: psycopg2.errors.NumericValueOutOfRange: integer out of range
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.
GitHub link: https://github.com/apache/airflow/discussions/45782#discussioncomment-11876950
----
This is an automatically sent email for commits@airflow.apache.org.
To unsubscribe, please send an email to: commits-unsubscribe@airflow.apache.org
Re: [D] Error on Inserting Session Data: psycopg2.errors.NumericValueOutOfRange: integer out of range [airflow]
Posted by "claude89757 (via GitHub)" <gi...@apache.org>.
GitHub user claude89757 added a comment to the discussion: Error on Inserting Session Data: psycopg2.errors.NumericValueOutOfRange: integer out of range
thanks for your reply，i will check my airflow api logs in webserver and comeback to here late
GitHub link: https://github.com/apache/airflow/discussions/45782#discussioncomment-11877046
----
This is an automatically sent email for commits@airflow.apache.org.
To unsubscribe, please send an email to: commits-unsubscribe@airflow.apache.org
Re: [D] Error on Inserting Session Data: psycopg2.errors.NumericValueOutOfRange: integer out of range [airflow]
Posted by "claude89757 (via GitHub)" <gi...@apache.org>.
GitHub user claude89757 added a comment to the discussion: Error on Inserting Session Data: psycopg2.errors.NumericValueOutOfRange: integer out of range
The final optimization plan requires optimizing the SQL command used to initialize Airflow.
GitHub link: https://github.com/apache/airflow/discussions/45782#discussioncomment-11876949
----
This is an automatically sent email for commits@airflow.apache.org.
To unsubscribe, please send an email to: commits-unsubscribe@airflow.apache.org
Re: [D] Error on Inserting Session Data: psycopg2.errors.NumericValueOutOfRange: integer out of range [airflow]
Posted by "claude89757 (via GitHub)" <gi...@apache.org>.
GitHub user claude89757 added a comment to the discussion: Error on Inserting Session Data: psycopg2.errors.NumericValueOutOfRange: integer out of range
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
GitHub link: https://github.com/apache/airflow/discussions/45782#discussioncomment-11886207
----
This is an automatically sent email for commits@airflow.apache.org.
To unsubscribe, please send an email to: commits-unsubscribe@airflow.apache.org
Re: [D] Error on Inserting Session Data: psycopg2.errors.NumericValueOutOfRange: integer out of range [airflow]
Posted by "claude89757 (via GitHub)" <gi...@apache.org>.
GitHub user claude89757 closed the discussion with a comment: Error on Inserting Session Data: psycopg2.errors.NumericValueOutOfRange: integer out of range
redirect to https://github.com/apache/airflow/discussions/45794
GitHub link: https://github.com/apache/airflow/discussions/45782#discussioncomment-11886558
----
This is an automatically sent email for commits@airflow.apache.org.
To unsubscribe, please send an email to: commits-unsubscribe@airflow.apache.org
Re: [D] Error on Inserting Session Data: psycopg2.errors.NumericValueOutOfRange: integer out of range [airflow]
Posted by "claude89757 (via GitHub)" <gi...@apache.org>.
GitHub user claude89757 added a comment to the discussion: Error on Inserting Session Data: psycopg2.errors.NumericValueOutOfRange: integer out of range
### Fix for `airflow-web` Issue
1. **Check the Maximum ID**:
- Run the query:
`SELECT MAX(id) FROM session;`
Result: `2147483647`
2. **Delete Recent Entries**:
- Execute the query:
`DELETE FROM session WHERE id IN (SELECT id FROM session ORDER BY id DESC LIMIT 1000000);`
3. **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.
GitHub link: https://github.com/apache/airflow/discussions/45782#discussioncomment-11876948
----
This is an automatically sent email for commits@airflow.apache.org.
To unsubscribe, please send an email to: commits-unsubscribe@airflow.apache.org
Re: [D] Error on Inserting Session Data: psycopg2.errors.NumericValueOutOfRange: integer out of range [airflow]
Posted by "potiuk (via GitHub)" <gi...@apache.org>.
GitHub user potiuk added a comment to the discussion: Error on Inserting Session Data: psycopg2.errors.NumericValueOutOfRange: integer out of range
Converting to a discussion if more discussion is needed.
GitHub link: https://github.com/apache/airflow/discussions/45782#discussioncomment-11876954
----
This is an automatically sent email for commits@airflow.apache.org.
To unsubscribe, please send an email to: commits-unsubscribe@airflow.apache.org