duplicate key value violates unique constraint "pg_type_typname_nsp_index"

[Klaas-]

Please confirm the following

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

Bug Summary

Hi,
it seems there is a regression in 24.0.0.
I have multiple schedules (for the same template with different limits set) that run at the same time. This used to work fine until the recent 24.0.0 update.

Now I seem to get an error:

Traceback (most recent call last):
  File "/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
  File "/var/lib/awx/venv/awx/lib64/python3.9/site-packages/psycopg/cursor.py", line 723, in execute
    raise ex.with_traceback(None)
psycopg.errors.UniqueViolation: duplicate key value violates unique constraint "pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(main_jobevent_20240320_22, 2200) already exists.

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

Traceback (most recent call last):
  File "/var/lib/awx/venv/awx/lib64/python3.9/site-packages/awx/main/tasks/jobs.py", line 499, in run
    self.pre_run_hook(self.instance, private_data_dir)
  File "/var/lib/awx/venv/awx/lib64/python3.9/site-packages/awx/main/tasks/jobs.py", line 1066, in pre_run_hook
    super(RunJob, self).pre_run_hook(job, private_data_dir)
  File "/var/lib/awx/venv/awx/lib64/python3.9/site-packages/awx/main/tasks/jobs.py", line 427, in pre_run_hook
    create_partition(instance.event_class._meta.db_table, start=instance.created)
  File "/var/lib/awx/venv/awx/lib64/python3.9/site-packages/awx/main/utils/common.py", line 1154, in create_partition
    cursor.execute(
  File "/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/var/lib/awx/venv/awx/lib64/python3.9/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.9/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
  File "/var/lib/awx/venv/awx/lib64/python3.9/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.9/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
  File "/var/lib/awx/venv/awx/lib64/python3.9/site-packages/psycopg/cursor.py", line 723, in execute
    raise ex.with_traceback(None)
django.db.utils.IntegrityError: duplicate key value violates unique constraint "pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(main_jobevent_20240320_22, 2200) already exists.

Is this a regression from the changes in #14910 ?

I will try to work around the issue by putting the schedules a minute apart.

Greetings
Klaas

AWX version

24.0.0

Select the relevant components

• UI
• UI (tech preview)
• API
• Docs
• Collection
• CLI
• Other

Installation method

kubernetes

Modifications

no

Ansible version

awx-ee 24.0.0

Operating system

RHEL8

Web browser

Firefox

Steps to reproduce

Have two schedules that start at the same time with the same template (job needs to allow concurrent runs). I am not sure if the "same template" is important, but in my usecase it's always the same template

Expected results

Works

Actual results

Traceback (most recent call last):
  File "/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
  File "/var/lib/awx/venv/awx/lib64/python3.9/site-packages/psycopg/cursor.py", line 723, in execute
    raise ex.with_traceback(None)
psycopg.errors.UniqueViolation: duplicate key value violates unique constraint "pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(main_jobevent_20240321_13, 2200) already exists.

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

Traceback (most recent call last):
  File "/var/lib/awx/venv/awx/lib64/python3.9/site-packages/awx/main/tasks/jobs.py", line 499, in run
    self.pre_run_hook(self.instance, private_data_dir)
  File "/var/lib/awx/venv/awx/lib64/python3.9/site-packages/awx/main/tasks/jobs.py", line 1066, in pre_run_hook
    super(RunJob, self).pre_run_hook(job, private_data_dir)
  File "/var/lib/awx/venv/awx/lib64/python3.9/site-packages/awx/main/tasks/jobs.py", line 427, in pre_run_hook
    create_partition(instance.event_class._meta.db_table, start=instance.created)
  File "/var/lib/awx/venv/awx/lib64/python3.9/site-packages/awx/main/utils/common.py", line 1154, in create_partition
    cursor.execute(
  File "/var/lib/awx/venv/awx/lib64/python3.9/site-packages/django/db/backends/utils.py", line 67, in execute
    return self._execute_with_wrappers(
  File "/var/lib/awx/venv/awx/lib64/python3.9/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.9/site-packages/django/db/backends/utils.py", line 89, in _execute
    return self.cursor.execute(sql, params)
  File "/var/lib/awx/venv/awx/lib64/python3.9/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.9/site-packages/django/db/backends/utils.py", line 87, in _execute
    return self.cursor.execute(sql)
  File "/var/lib/awx/venv/awx/lib64/python3.9/site-packages/psycopg/cursor.py", line 723, in execute
    raise ex.with_traceback(None)
django.db.utils.IntegrityError: duplicate key value violates unique constraint "pg_type_typname_nsp_index"
DETAIL:  Key (typname, typnamespace)=(main_jobevent_20240321_13, 2200) already exists.

Additional information

No response

[AlekseiSaff]

Have the same issue

[cfiehe]

We are also affected by this regression.

[Nenodema]

Duplicate 😅 of #14563

[Klaas-]

We can work in #14563 -- but from my understanding #14563 should have been fixed with #14910 which in turn introduced this here -- but I'll defer to awx devs which issue to close :D

[dmzoneill]

@Klaas- @Nenodema @cfiehe @AlekseiSaff
Can you provide more details of your template/inventory/project/playbook.
The more information, the better.

I set up a basic job template and schedule d4 concurrent tasks at the same time, and I cannot reproduce this issue.

thank you

[AlekseiSaff]

@Klaas- @Nenodema @cfiehe @AlekseiSaff Can you provide more details of your template/inventory/project/playbook. The more information, the better.

I set up a basic job template and schedule d4 concurrent tasks at the same time, and I cannot reproduce this issue.

thank you

what kind of information you'd like to have? I'm happy to share. But that issue is not always possible to replicate. Let's say I do have an every hour task with the same scheduale. And I can see that issue once/twice in about 2-3 days only.

[Klaas-]

@Klaas- @Nenodema @cfiehe @AlekseiSaff Can you provide more details of your template/inventory/project/playbook. The more information, the better.

I set up a basic job template and schedule d4 concurrent tasks at the same time, and I cannot reproduce this issue.

Hi,
so in my dev environment I can't reproduce it at will either, but I think this is already fixed by #15000

[dmzoneill]

@Klaas-
This seems to be the case.
https://github.com/ansible/awx/pull/15000/files

[dmzoneill]

Closing, please reopen if difficulty persists. Thanks all

[Klaas-]

Just as a follow up: 24.1.0 seems to fix the issue for me, I had a good chance of that error happening when scheduling two jobs at the same time ~5 minutes into the future. I've ran it 10 times and it worked fine, on 24.0.0 this had failed at a rate of like 40%. I'll keep testing it a few times more, but from my point of view #15000 fixes it.