AWX task error: django.db.utils.ProgrammingError: trailing junk after numeric literal at or near 10m, LINE 1: SET idle_in_transaction_session_timeout = 500s

[jerryqzhang]

Please confirm the following

• [X] I agree to follow this project's code of conduct.
• [X] I have checked the current issues for duplicates.
• [X] I understand that AWX is open source software provided for free and that I might not receive a timely response.
• [X] I am NOT reporting a (potential) security vulnerability. (These should be emailed to security@ansible.com instead.)

Bug Summary

awx task error "django.db.utils.ProgrammingError: trailing junk after numeric literal at or near..." when pg config "idle_in_transaction_session_timeout"

AWX version

24.6.1

Select the relevant components

• [ ] UI
• [ ] UI (tech preview)
• [X] API
• [ ] Docs
• [ ] Collection
• [ ] CLI
• [ ] Other

Installation method

openshift

Modifications

no

Ansible version

No response

Operating system

No response

Web browser

Chrome

Steps to reproduce

1. openshift use awx operator 2.19.1
2. use default config install awxoperator and awx instance
3. exec pg container change pc config, set global idle_in_transaction_session_timeout to 600000 or other value, pg store will change the unit, restart the pg process
4. check the awx task log, find the error

Expected results

no error log

Actual results

many error log similar to " django.db.utils.ProgrammingError: trailing junk after numeric literal at or near "500s" LINE 1: SET idle_in_transaction_session_timeout = 500s"

Additional information

2024-07-29 05:25:58,176 ERROR    [-] awx.main.dispatch Worker failed to run task awx.main.tasks.system.awx_periodic_scheduler(*[], **{}
 Traceback (most recent call last):
   File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/django/db/backends/utils.py", line 87, in _execute
     return self.cursor.execute(sql)
            ^^^^^^^^^^^^^^^^^^^^^^^^
   File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
     raise ex.with_traceback(None)
 psycopg.errors.SyntaxError: trailing junk after numeric literal at or near "500s"
 LINE 1: SET idle_in_transaction_session_timeout = 500s
                                                   ^
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
   File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/awx/main/dispatch/worker/task.py", line 103, in perform_work
     result = self.run_callable(body)
              ^^^^^^^^^^^^^^^^^^^^^^^
   File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/awx/main/dispatch/worker/task.py", line 78, in run_callable
     return _call(*args, **kwargs)
            ^^^^^^^^^^^^^^^^^^^^^^
   File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/awx/main/tasks/system.py", line 719, in awx_periodic_scheduler
     with advisory_lock('awx_periodic_scheduler_lock', lock_session_timeout_milliseconds=lock_session_timeout_milliseconds, wait=False) as acquired:
   File "/usr/lib64/python3.11/contextlib.py", line 144, in exit
     next(self.gen)
   File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/awx/main/utils/pglock.py", line 26, in advisory_lock
     cur.execute(f"SET idle_in_transaction_session_timeout = {idle_in_transaction_session_timeout}")
   File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/django/db/backends/utils.py", line 67, in execute
     return self._execute_with_wrappers(
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
     return executor(sql, params, many, context)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
   File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/django/db/backends/utils.py", line 84, in _execute
     with self.db.wrap_database_errors:
   File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/django/db/utils.py", line 91, in exit
     raise dj_exc_value.with_traceback(traceback) from exc_value
   File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/django/db/backends/utils.py", line 87, in _execute
     return self.cursor.execute(sql)
            ^^^^^^^^^^^^^^^^^^^^^^^^
   File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
     raise ex.with_traceback(None)
 django.db.utils.ProgrammingError: trailing junk after numeric literal at or near "500s"
 LINE 1: SET idle_in_transaction_session_timeout = 500s

[jerryqzhang]

It appears that version 24.6.0 does not have this issue, but version 24.6.1 does have this problem.

[nmoseman]

I am seeing this issue as well. I recently upgraded to 24.6.1.

I tried setting idle_in_transaction_session_timeout to different values in postgresql 15, but django doesn't seem to understand that postgres is returning a string, not a number, Even when I set it to a numerical manually postgres still wants to return a string with a time value (seconds, minutes, days, etc) on the end of it.

ALTER DATABASE awx SET idle_in_transaction_session_timeout=86400001;

To try to force it to return milliseconds... it still ends up giving me this error:

  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/awx/main/dispatch/worker/task.py", line 103, in perform_work
    result = self.run_callable(body)
             ^^^^^^^^^^^^^^^^^^^^^^^
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/awx/main/dispatch/worker/task.py", line 78, in run_callable
    return _call(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/awx/main/scheduler/tasks.py", line 30, in dependency_manager
    run_manager(DependencyManager, "dependency")
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/awx/main/scheduler/tasks.py", line 20, in run_manager
    manager().schedule()
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/awx/main/scheduler/task_manager.py", line 142, in schedule
    with advisory_lock(f"{self.prefix}_lock", lock_session_timeout_milliseconds=lock_session_timeout_milliseconds, wait=False) as acquired:
  File "/usr/lib64/python3.11/contextlib.py", line 144, in __exit__
    next(self.gen)
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/awx/main/utils/pglock.py", line 26, in advisory_lock
    cur.execute(f"SET idle_in_transaction_session_timeout = {idle_in_transaction_session_timeout}")
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
django.db.utils.ProgrammingError: trailing junk after numeric literal at or near "86400001m"
LINE 1: SET idle_in_transaction_session_timeout = 86400001ms

[Darwin014]

I have also updated to 24.6.1 and am encountering the same error logs.

2024-08-16 06:43:42,070 ERROR    [-] awx.main.dispatch Worker failed to run task awx.main.scheduler.tasks.dependency_manager(*[], **{}
Traceback (most recent call last):
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
psycopg.errors.SyntaxError: trailing junk after numeric literal at or near "1m"
LINE 1: SET idle_in_transaction_session_timeout = 1min
                                                  ^

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

Traceback (most recent call last):
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/awx/main/dispatch/worker/task.py", line 103, in perform_work
    result = self.run_callable(body)
             ^^^^^^^^^^^^^^^^^^^^^^^
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/awx/main/dispatch/worker/task.py", line 78, in run_callable
    return _call(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/awx/main/scheduler/tasks.py", line 30, in dependency_manager
    run_manager(DependencyManager, "dependency")
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/awx/main/scheduler/tasks.py", line 20, in run_manager
    manager().schedule()
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/awx/main/scheduler/task_manager.py", line 142, in schedule
    with advisory_lock(f"{self.prefix}_lock", lock_session_timeout_milliseconds=lock_session_timeout_milliseconds, wait=False) as acquired:
  File "/usr/lib64/python3.11/contextlib.py", line 144, in __exit__
    next(self.gen)
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/awx/main/utils/pglock.py", line 26, in advisory_lock
    cur.execute(f"SET idle_in_transaction_session_timeout = {idle_in_transaction_session_timeout}")
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
django.db.utils.ProgrammingError: trailing junk after numeric literal at or near "1m"
LINE 1: SET idle_in_transaction_session_timeout = 1min
                                                  ^

[Kardi5]

I have the same issue with version 24.6.1 and an AWS RDS PostgreSQL 15.8. Seems to be fixed but not released: https://github.com/ansible/awx/pull/15352/files 24.6.1 was released on July 2 and PR is from July 10

[71Imapla]

This may be the result of using Postgres 15: https://stackoverflow.com/questions/75282073/postgresql-15-syntax-sensitivity. I am using an external a Postgres 14 Database with no issues after upgrade.

[Kardi5]

Strange. I upgraded to PostgreSQL 15 because of this error. On AWS RDS 14.13 and 15.8 the statement SET idle_in_transaction_session_timeout = 1d will fail for me. While SET idle_in_transaction_session_timeout = '1d' works.

Are you sure you are using AWX version 24.6.1? Bug is not present in version 24.6.0

[john9x]

version 24.6.1 and an AWS RDS PostgreSQL 15.8

same issue

[m-shalenko]

The same issue

2024-09-22 17:05:23,037 ERROR    [e9efdfd5a55749bcaa5b167a702a01a7] awx.main.dispatch Worker failed to run task awx.main.scheduler.tasks.dependency_manager(*[], **{}
Traceback (most recent call last):
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
psycopg.errors.SyntaxError: trailing junk after numeric literal at or near "30s"
LINE 1: SET idle_in_transaction_session_timeout = 30s
                                                  ^

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

Traceback (most recent call last):
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/awx/main/dispatch/worker/task.py", line 103, in perform_work
    result = self.run_callable(body)
             ^^^^^^^^^^^^^^^^^^^^^^^
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/awx/main/dispatch/worker/task.py", line 78, in run_callable
    return _call(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/awx/main/scheduler/tasks.py", line 30, in dependency_manager
    run_manager(DependencyManager, "dependency")
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/awx/main/scheduler/tasks.py", line 20, in run_manager
    manager().schedule()
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/awx/main/scheduler/task_manager.py", line 142, in schedule
    with advisory_lock(f"{self.prefix}_lock", lock_session_timeout_milliseconds=lock_session_timeout_milliseconds, wait=False) as acquired:
  File "/usr/lib64/python3.11/contextlib.py", line 144, in __exit__
    next(self.gen)
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/awx/main/utils/pglock.py", line 26, in advisory_lock
    cur.execute(f"SET idle_in_transaction_session_timeout = {idle_in_transaction_session_timeout}")
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/django/db/backends/utils.py", line 84, in _execute
    with self.db.wrap_database_errors:
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/var/lib/awx/venv/awx/lib64/python3.11/site-packages/psycopg/cursor.py", line 732, in execute
    raise ex.with_traceback(None)
django.db.utils.ProgrammingError: trailing junk after numeric literal at or near "30s"
LINE 1: SET idle_in_transaction_session_timeout = 30s

Postgres: 16.3 AWX: 24.6.1 Operator: 2.19.1

[sorquan]

Still broken with pg15