Transaction error patching a large ValueSet

[jmalobicky]

When issuing patch commands to update a very large ValueSet (12000+ procedure codes being input). the following error is occurring

{"level":"ERROR","timestamp":"2024-08-12T13:04:38.507Z","msg":"Transaction error","error":"error: bind message supplies 0 parameters, but prepared statement \"\" requires 65536","stack":["error: bind message supplies 0 parameters, but prepared statement \"\" requires 65536","    at medplum/node_modules/pg/lib/client.js:526:17","    at processTicksAndRejections (node:internal/process/task_queues:95:5)","    at SqlBuilder.execute (medplum/packages/server/src/fhir/sql.ts:358:22)","    at ValueSetElementTable.insertValuesForResource (medplum/packages/server/src/fhir/lookups/lookuptable.ts:142:5)","    at ValueSetElementTable.indexResource (medplum/packages/server/src/fhir/lookups/valuesetelement.ts:54:5)","    at Repository.writeLookupTables (medplum/packages/server/src/fhir/repo.ts:1525:7)","    at medplum/packages/server/src/fhir/repo.ts:758:7","    at Repository.withTransaction (medplum/packages/server/src/fhir/repo.ts:1939:22)","    at Repository.writeToDatabase (medplum/packages/server/src/fhir/repo.ts:755:5)","    at Repository.handleMaybeCacheOnly (medplum/packages/server/src/fhir/repo.ts:637:7)"]}

Snippet used to run a continuous patch

import { MedplumClient, sleep } from "@medplum/core";
import { ValueSet } from "@medplum/fhirtypes";
import { procedureCodes } from "./codes";

const medplum = new MedplumClient({
  baseUrl: "http://localhost:8103/",
});

const startClientLogin = async () => {
  const clientId = process.env.MEDPLUM_CLIENT_ID;
  const clientSecret = process.env.MEDPLUM_CLIENT_SECRET;
  if (!clientId || !clientSecret) {
    throw new Error("Missing clientId or clientSecret");
  }
  await medplum.startClientLogin(clientId, clientSecret);
};

startClientLogin();

(async () => {
  let valueSet = await medplum.searchOne(
    "ValueSet",
    "url=https://acme.com/fhir/ValueSet/procedure-codes"
  );

  // we need to boostrap at least one value within the `contains` array
  if (!valueSet) {
    valueSet = await medplum.createResource<ValueSet>({
      resourceType: "ValueSet",
      url: "https://acme.com/fhir/ValueSet/procedure-codes",
      status: "active",
      expansion: {
        timestamp: "2024-07-03T12:31:22.640442+00:00",
        offset: 0,
        total: 1,
        parameter: [
          {
            name: "version",
            valueUri: "http://loinc.org",
          },
        ],
        contains: [
          {
            system: "https://acme.com/fhir/ValueSet/procedure-codes",
            code: "0001F",
            display: "Heart failure composite",
          },
        ],
      },
    });
  }

  for (const code of procedureCodes) {
    console.log("Adding code: ", JSON.stringify(code, null, 2));
    // @ts-ignore
    const result = await medplum.patchResource("ValueSet", valueSet.id, [
      {
        op: "add",
        path: "/expansion/contains/-",
        value: code,
      },
    ]);

    console.log(result.meta?.versionId);
    // Generate random sleep time between 3 and 8 seconds to not overload
    const sleepTime = Math.floor(Math.random() * (8000 - 3000 + 1)) + 3000;
    await sleep(sleepTime);
  }
})();

Example of the procedureCodes array

export const procedureCodes = [
    {
      system: "https://acme.com/fhir/ValueSet/procedure-codes",
      code: "0005F",
      display: "Osteoarthritis composite",
    },
    {
      system: "https://acme.com/fhir/ValueSet/procedure-codes",
      code: "00100",
      display: "Anesth salivary gland",
    },
    {
      system: "https://acme.com/fhir/ValueSet/procedure-codes",
      code: "00102",
      display: "Anesth repair of cleft lip",
    },
    {
      system: "https://acme.com/fhir/ValueSet/procedure-codes",
      code: "00103",
      display: "Anesth blepharoplasty",
    },
...
]

[codyebberson]

12000+ procedure codes

@mattwiller - thoughts on this? Would this be better done with the CodeSystem/$import operation?

[mattwiller]

@codyebberson The error here is occurring in the legacy ValueSetElements table, not the CodeSystem; since we're already planning to remove that table, this issue should be resolved once that lookup table is no longer populated on write