Export of large amount of packages will result in errors

[hstct]

Version

• Katello 4.12
• pulpcore 3.39.11
• pulp_rpm 3.23.3
• pulp_deb 3.0.2

Describe the bug
Exporting data (in this case Content Views in Katello) with large amount of packages will fail:

Error: sending query and params failed: number of parameters must be between 0 and 65535

There are actually at least two different issues both of which will throw the same error:

1. If the Content View has more than 65535 packages the export task will fail (usually pretty quickly).
2. If the Content View has less than 65535 but more than around half of that (tested with ~40k packages) the task will actually succeed but fail in writing the data with the same error. This will take long since the export task is actually completed.

Debian and RPM content are both affected by this.

To Reproduce
Steps to reproduce the behavior:

• Create a Content View with more than 65535 packages (or with more than half that but lower than 65535)
• Export the Content View via:
  hammer content-export complete version --chunk-size-gb=40 --content-view="CV_NAME" --organization-id=1 --version=1.0

Expected behavior
The export to be successful.

Additional context
We suspect that this has something to do with the upgrade to Django 4.2 or more specifically with the upgrade from psycopg2 to psycopg3. We found an issue that parallels our findings psycopg/psycopg#620

We can also confirm that on older systems that still use older pulpcore versions (pre django 4.2) the export still works on these types of Content Views.

For more context here are the pulp tasks:

1. For Content Views with more than 65535 packages

---
pulp_tasks:
- pulp_href: "/pulp/api/v3/tasks/018f767f-1b93-72cb-aa23-32c9c2650ef9/"
  pulp_created: '2024-05-14T09:46:32.725+00:00'
  state: failed
  name: pulpcore.app.tasks.export.pulp_export
  logging_cid: f34cbfee-3afb-4f6a-aff6-83b83e586f0a
  created_by: "/pulp/api/v3/users/1/"
  started_at: '2024-05-14T09:46:33.085+00:00'
  finished_at: '2024-05-14T09:46:55.152+00:00'
  error:
    traceback: |2
        File "/usr/lib/python3.11/site-packages/pulpcore/tasking/tasks.py", line 61, in _execute_task
          result = func(*args, **kwargs)
                   ^^^^^^^^^^^^^^^^^^^^^
        File "/usr/lib/python3.11/site-packages/pulpcore/app/tasks/export.py", line 417, in pulp_export
          _do_export(pulp_exporter, tar, the_export)
        File "/usr/lib/python3.11/site-packages/pulpcore/app/tasks/export.py", line 512, in _do_export
          export_artifacts(the_export, Artifact.objects.filter(pk__in=artifact_pks))
        File "/usr/lib/python3.11/site-packages/pulpcore/app/importexport.py", line 133, in export_artifacts
          for artifact in pb.iter(artifacts.only("file").iterator()):
        File "/usr/lib/python3.11/site-packages/pulpcore/app/models/progress.py", line 296, in iter
          for x in iter:
        File "/usr/lib/python3.11/site-packages/django/db/models/query.py", line 516, in _iterator
          yield from iterable
        File "/usr/lib/python3.11/site-packages/django/db/models/query.py", line 91, in __iter__
          results = compiler.execute_sql(
                    ^^^^^^^^^^^^^^^^^^^^^
        File "/usr/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1562, in execute_sql
          cursor.execute(sql, params)
        File "/usr/lib/python3.11/site-packages/django/db/backends/utils.py", line 67, in execute
          return self._execute_with_wrappers(
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        File "/usr/lib/python3.11/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
          return executor(sql, params, many, context)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        File "/usr/lib/python3.11/site-packages/django/db/backends/utils.py", line 84, in _execute
          with self.db.wrap_database_errors:
        File "/usr/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
          raise dj_exc_value.with_traceback(traceback) from exc_value
        File "/usr/lib/python3.11/site-packages/django/db/backends/utils.py", line 89, in _execute
          return self.cursor.execute(sql, params)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        File "/usr/lib/python3.11/site-packages/psycopg/server_cursor.py", line 294, in execute
          raise ex.with_traceback(None)
    description: 'sending query and params failed: number of parameters must be between
      0 and 65535'
  worker: "/pulp/api/v3/workers/018f73c9-a49a-7797-a12a-836eb2e6a13f/"
  child_tasks: []
  progress_reports:
  - message: Exporting Artifacts
    code: export.artifacts
    state: failed
    total: 66040
    done: 0
  created_resources:
  - "/pulp/api/v3/exporters/core/pulp/018f767f-19d8-7c16-9aa4-be0917d2c868/exports/018f767f-1f26-75cc-921f-b43b68856d32/"
  reserved_resources_record:
  - "/pulp/api/v3/exporters/core/pulp/018f767f-19d8-7c16-9aa4-be0917d2c868/"
  - shared:/pulp/api/v3/repositories/rpm/rpm/018e4331-54d9-7791-b6cf-df2913e135b7/
  - shared:/pulp/api/v3/repositories/rpm/rpm/018e4331-3e03-756e-b478-d7118b396463/
  - shared:/pulp/api/v3/repositories/rpm/rpm/018e4331-81e1-723a-bc88-54750f778760/
  - shared:/pulp/api/v3/repositories/rpm/rpm/018e4331-6a8a-77d2-9bcc-7fea35a5e6fb/
  - shared:/pulp/api/v3/domains/018e42cc-5411-73e1-932c-cc158854d906/
task_groups: []
poll_attempts:
  total: 15
  failed: 1

2. For Content Views with around 40k packages:

---
pulp_tasks:
- pulp_href: "/pulp/api/v3/tasks/018f768b-022a-764f-a0e1-7ee4e6256654/"
  pulp_created: '2024-05-14T09:59:32.650+00:00'
  state: failed
  name: pulpcore.app.tasks.export.pulp_export
  logging_cid: e0774285-1b15-4539-a9dc-30f2eb8f1764
  created_by: "/pulp/api/v3/users/1/"
  started_at: '2024-05-14T09:59:32.861+00:00'
  finished_at: '2024-05-14T10:27:41.082+00:00'
  error:
    traceback: |2
        File "/usr/lib/python3.11/site-packages/pulpcore/tasking/tasks.py", line 61, in _execute_task
          result = func(*args, **kwargs)
                   ^^^^^^^^^^^^^^^^^^^^^
        File "/usr/lib/python3.11/site-packages/pulpcore/app/tasks/export.py", line 417, in pulp_export
          _do_export(pulp_exporter, tar, the_export)
        File "/usr/lib/python3.11/site-packages/pulpcore/app/tasks/export.py", line 517, in _do_export
          export_content(the_export, version)
        File "/usr/lib/python3.11/site-packages/pulpcore/app/importexport.py", line 185, in export_content
          _write_export(export.tarfile, resource, dest_dir)
        File "/usr/lib/python3.11/site-packages/pulpcore/app/importexport.py", line 59, in _write_export
          for item in resource.queryset.iterator(chunk_size=EXPORT_BATCH_SIZE):
        File "/usr/lib/python3.11/site-packages/django/db/models/query.py", line 516, in _iterator
          yield from iterable
        File "/usr/lib/python3.11/site-packages/django/db/models/query.py", line 91, in __iter__
          results = compiler.execute_sql(
                    ^^^^^^^^^^^^^^^^^^^^^
        File "/usr/lib/python3.11/site-packages/django/db/models/sql/compiler.py", line 1562, in execute_sql
          cursor.execute(sql, params)
        File "/usr/lib/python3.11/site-packages/django/db/backends/utils.py", line 67, in execute
          return self._execute_with_wrappers(
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        File "/usr/lib/python3.11/site-packages/django/db/backends/utils.py", line 80, in _execute_with_wrappers
          return executor(sql, params, many, context)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        File "/usr/lib/python3.11/site-packages/django/db/backends/utils.py", line 84, in _execute
          with self.db.wrap_database_errors:
        File "/usr/lib/python3.11/site-packages/django/db/utils.py", line 91, in __exit__
          raise dj_exc_value.with_traceback(traceback) from exc_value
        File "/usr/lib/python3.11/site-packages/django/db/backends/utils.py", line 89, in _execute
          return self.cursor.execute(sql, params)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
        File "/usr/lib/python3.11/site-packages/psycopg/server_cursor.py", line 294, in execute
          raise ex.with_traceback(None)
    description: 'sending query and params failed: number of parameters must be between
      0 and 65535'
  worker: "/pulp/api/v3/workers/018f73c9-accc-7c2e-ba0d-0c381a66be93/"
  child_tasks: []
  progress_reports:
  - message: Exporting Artifacts
    code: export.artifacts
    state: completed
    total: 48052
    done: 48052
  created_resources:
  - "/pulp/api/v3/exporters/core/pulp/018f768b-0112-7365-bfd8-e30a0cd8eeb6/exports/018f768b-0448-7bcd-abd8-052060d1d70d/"
  reserved_resources_record:
  - "/pulp/api/v3/exporters/core/pulp/018f768b-0112-7365-bfd8-e30a0cd8eeb6/"
  - shared:/pulp/api/v3/repositories/rpm/rpm/018e4331-81e1-723a-bc88-54750f778760/
  - shared:/pulp/api/v3/domains/018e42cc-5411-73e1-932c-cc158854d906/
task_groups: []
poll_attempts:
  total: 123
  failed: 1

[hstct]

Most likely the issue is that psycopg3 now uses server-side bindings instead of client-side which it apparently used it with the older versions. Resulting here in a limitation for SQL itself

Actually Django still uses the client-side cursors by default.

[quba42]

Just one more slight clarification of what was already said above: The bug triggers (in the first version) as soon as the export includes more than ~65k artifacts. This was observed for both deb and rpm content exports. In the Katello context, it is enough for all the repos in a content view combined to add up to those more than ~65k artifacts and then export that content view version.

The second version of the bug occurs if there are less than ~65k artifacts but more than ~65k content units. For deb content there tends to be roughly twice as much content as actual artifacts because for each .deb package there is also a "package release component" content. I suspect the window in export size to hit this second version of the problem is pretty small for rpm exports. Edit: Apparently @hstct also saw this happen with ~40k rpm content.

[hstct]

I suspect the window in export size to hit this second version of the problem is pretty small for rpm exports.

I ran into the same issue with rpm with about ~40k artifacts in the export so its still an issue here too

[quba42]

Does this issue mean we somehow need to batch the artifact_pks from the following querry?
https://github.com/pulp/pulpcore/blob/main/pulpcore/app/tasks/export.py#L512

[dralley]

What happens if you tweak the Artifacts query with .values_list("pk", flat=True)

[quba42]

@dralley We are trying out your suggestion (in a somewhat hackish way) here: https://github.com/ATIX-AG/pulpcore/blob/fix_large_exports/pulpcore/app/importexport.py#L118

See the diff of the change here.

This seems to get us through the first version of the problem (we will most likely still fail on the second version, but we now need to wait about an hour for our test run to fail 😉)

[ggainey]

A note to recreate this behavior w/out needing 65K large rpm/deb artifacts:

• clone github.com/pulp/pulp-fixtures and make the following change:

(master) ~/github/Pulp3/pulp-fixtures $ git diff
diff --git a/Makefile b/Makefile
index 00fc396..5c6b89d 100644
--- a/Makefile
+++ b/Makefile
@@ -337,7 +337,7 @@ fixtures/file-perf: fixtures
        # 100,000 files were exceeding Travis' 50 minute time limit
        # https://pulp.plan.io/issues/6104
        # file/gen-fixtures.sh $@ --number 100000 --file-size 50
-       file/gen-fixtures.sh $@ --number 20000 --file-size 50
+       file/gen-fixtures.sh $@ --number 100000 --file-size 10
 
 fixtures/file-dl-forward: fixtures
        file/gen-fixtures.sh $@ --number 750 --file-size 50

• Create the 100K repo with make fixtures/file-perf
• create a pulp-file remote and repo, sync, and export:

pulp file remote create --name many --url file://~/pulp-fixtures/fixtures/file-perf/PULP_MANIFEST --policy immediate
pulp file repository create --name many --remote many
pulp file repository sync --name many
pulp exporter pulp create --name many --path /tmp/exports/ --repository file:file:many
pulp export pulp run --exporter many
Started background task /pulp/api/v3/tasks/018f7d73-6885-7ae7-8589-d9eed0564e42/
....Error: Task /pulp/api/v3/tasks/018f7d73-6885-7ae7-8589-d9eed0564e42/ failed: 'sending query and params failed: number of parameters must be between 0 and 65535'

Much kinder on one's available disk space (repo is ~400Mb instead of gigabytes...)

[dralley]

Most likely the issue is that psycopg3 now uses server-side bindings instead of client-side which it apparently used it with the older versions. Resulting here in a limitation for SQL itself

Actually Django still uses the client-side cursors by default.

So we still don't know exactly what changed between Django 3 and Django 4 to break this?

[ipanova]

https://bugzilla.redhat.com/show_bug.cgi?id=2280849