Trying to use Knex onConflict times out my Cloud Function

Question

I am trying to insert geoJSON data into a PostGIS instance on a regular schedule and there is usually duplicate data each time it runs. I am looping through this geoJSON data and trying to use Knex.js onConflict modifier to ignore when a duplicate key field is found but, it times out my cloud function.

  async function insertFeatures() {
    try {
      const results = await getGeoJSON();
      pool = pool || (await createPool());

      const st = knexPostgis(pool);
      for (const feature of results.features) {
        const { geometry, properties } = feature;
        const { region, date, type, name, url } = properties;
        const point = st.geomFromGeoJSON(geometry);

        await pool('observations').insert({ 
          region: region,
          url: url,
          date: date,
          name: name,
          type: type,
          geom: point,
        })
        .onConflict('url')
        .ignore()
      }
    } catch (error) {
      console.log(error)
      return res.status(500).json({
        message: error + "Poop"
      });
    }
  }

score 0 · Answer 1 · answered Dec 28 '22 at 10:45

The timeout error could be caused by a variety of reasons,either it could be transaction batch size your function is processing or connection pool size or database server limitations.Here in your cloud function, check whether when setting up the pool, knex allows us to optionally register afterCreate callback, if this callback is added it is getting positive that you make the call to the done callback that is passed as the last parameter to your registered callback or else no connection will be acquired leading to timeout.

Also one way to see what knex is doing internally is to set DEBUG=knex:* environment variable, before running the code so that knex outputs information about queries, transactions and pool connections while code executes.It is advised that you set batch sizes, connection pool size and connection limits from the database server to match the workload that you are pushing to the server, this ensures the basic timeout issues caused.
Also check for similar examples here:

Thank you. It is making the connection because without the .onConflict and .ignore I get the error of duplicate key value violates unique constraint. With onConflict and ignore in the code my entire cloud function times out. — jjhi11, Dec 28 '22 at 13:46
The quotations around url in .onConflict were causing it to hang. I now get a "do nothing - syntax error at or near" — jjhi11, Dec 28 '22 at 18:25
Please paste the whole error, it is a syntax related error ,so it would have details at the line mentioned in the error line, try to correct that. Check the following links: https://stackoverflow.com/questions/57021925/syntax-error-when-doing-postgres-like-through-knex-js https://stackoverflow.com/questions/57019985/syntax-error-in-create-function-statement-in-psql-using-knex-js https://stackoverflow.com/questions/53644505/insert-data-with-raw-sql-throw-syntax-error-at-or-near-1-error https://stackoverflow.com/questions/67003367/knex-query-syntax-error-near-select-in-the-join-method — Vaidehi Jamankar, Dec 29 '22 at 07:09

Trying to use Knex onConflict times out my Cloud Function

1 Answers1