Kysely•4mo ago

Any limits on insertValues(array)?

I am seeding data in migrations and received error when trying to insert over 35.000 rows at once.

const rows = [ // there are 35.000 objects here]
await db
  .insertInto('tableName')
  .values(rows)
  .execute();

const rows = [ // there are 35.000 objects here]
await db
  .insertInto('tableName')
  .values(rows)
  .execute();

but if I do the loop it all works without any problems.

for (const row of rows) {
  await db
    .insertInto('tableName')
    .values(row)
    .execute();
}

for (const row of rows) {
  await db
    .insertInto('tableName')
    .values(row)
    .execute();
}

Failed to migrate error: bind message has 39901 parameter formats but 0 parameters
    at /project-folder/node_modules/pg/lib/client.js:526:17
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async PostgresConnection.executeQuery (/project-folder/node_modules/kysely/dist/cjs/dialect/postgres/postgres-driver.js:72:28)
    at async /project-folder/node_modules/kysely/dist/cjs/query-executor/query-executor-base.js:37:28
    at async SingleConnectionProvider.#run (/project-folder/node_modules/kysely/dist/cjs/driver/single-connection-provider.js:27:16)
    at PostgresConnection.executeQuery (/project-folder/node_modules/kysely/dist/cjs/dialect/postgres/postgres-driver.js:91:69)
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async /project-folder/node_modules/kysely/dist/cjs/query-executor/query-executor-base.js:37:28
    at async SingleConnectionProvider.#run (/project-folder/node_modules/kysely/dist/cjs/driver/single-connection-provider.js:27:16) {
  length: 122,
  severity: 'ERROR',
  code: '08P01',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'postgres.c',
  line: '1709',
  routine: 'exec_bind_message'
}
node:internal/errors:863
  const err = new Error(message);

Failed to migrate error: bind message has 39901 parameter formats but 0 parameters
    at /project-folder/node_modules/pg/lib/client.js:526:17
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async PostgresConnection.executeQuery (/project-folder/node_modules/kysely/dist/cjs/dialect/postgres/postgres-driver.js:72:28)
    at async /project-folder/node_modules/kysely/dist/cjs/query-executor/query-executor-base.js:37:28
    at async SingleConnectionProvider.#run (/project-folder/node_modules/kysely/dist/cjs/driver/single-connection-provider.js:27:16)
    at PostgresConnection.executeQuery (/project-folder/node_modules/kysely/dist/cjs/dialect/postgres/postgres-driver.js:91:69)
    at processTicksAndRejections (node:internal/process/task_queues:95:5)
    at async /project-folder/node_modules/kysely/dist/cjs/query-executor/query-executor-base.js:37:28
    at async SingleConnectionProvider.#run (/project-folder/node_modules/kysely/dist/cjs/driver/single-connection-provider.js:27:16) {
  length: 122,
  severity: 'ERROR',
  code: '08P01',
  detail: undefined,
  hint: undefined,
  position: undefined,
  internalPosition: undefined,
  internalQuery: undefined,
  where: undefined,
  schema: undefined,
  table: undefined,
  column: undefined,
  dataType: undefined,
  constraint: undefined,
  file: 'postgres.c',
  line: '1709',
  routine: 'exec_bind_message'
}
node:internal/errors:863
  const err = new Error(message);

Solution:

Just insert in chunks

Jump to solution

3 Replies

mike•4mo ago

Oh, I probably found the problem. The table includes a generated (stored) column and it’s not happening quickly enough and that’s why it fails. Difficult to confirm but it looks like that.

koskimas•4mo ago

Databases have limits to how many parameters a query can have. You are probably hitting that

Solution

koskimas•4mo ago

Just insert in chunks

Gaming

Programming

Any limits on insertValues(array)?