pg-cursor timeouts

[alesmenzel]

Hi, I think this is a bug within the pg-cursor library. I believe that when the operation between reading from the cursor takes more than X s, the cursor times out.

Here is the error I am getting:

error: canceling statement due to statement timeout
    at Connection.parseE (<path>/node_modules/pg/lib/connection.js:614:13)
    at Connection.parseMessage (<path>/node_modules/pg/lib/connection.js:413:19)
    at TLSSocket.<anonymous> (<path>/node_modules/pg/lib/connection.js:129:22)
    at TLSSocket.emit (events.js:321:20)
    at addChunk (_stream_readable.js:294:12)
    at readableAddChunk (_stream_readable.js:275:11)
    at TLSSocket.Readable.push (_stream_readable.js:209:10)
    at TLSWrap.onStreamRead (internal/stream_base_commons.js:186:23) {
  name: 'error',
  length: 109,
  severity: 'ERROR',
  code: '57014',
  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: '2985',
  routine: 'ProcessInterrupts'
}

This is a pseudo reproduction code:

async function * Iterator () {
  const cursor = await client.query(new Cursor(query, replacements))
  const cursorP = {
    read: util.promisify(cursor.read).bind(cursor),
    close: util.promisify(cursor.close).bind(cursor),
  }
  while (true) {
    const rows = await cursorP.read(size)
    if (!rows.length) {
      await cursorP.close()
      client.release()
      return
    }
    yield rows
  }
}

const iterable = await Iterator()
for await (const x of iterable) {
  // do stuff
  await doStomething(x);
}

[brianc]

I think postgres is killing your cursor because it isn't finishing reading within the timeout interval. I am pretty sure this is how postgres works. When you configure a statement timeout postgres starts a timer as soon as the query is received. Once it reaches the count down it kills the statement if it's still running. We don't set statement timeout automatically in this library so somewhere else in your app this is configured & being set. In this case you'll likely need to bump the statement timeout up to some magnitude larger than the work you're doing will take.

[alesmenzel]

Thanks, so just to clarify, you think it is timeout on the reading query? Because it should not matter how long the operation takes since some rows have already been read, no?

Sample demo:
Let's say there is 1000 rows in a table, I create a cursor then take 200 rows.
Now it does some other work for more than the pg statement timeout.
Should it really error out on timeout since I am not actually doing any query that would take for more than the statement timeout?

-> read 200 (SELECT * FROM X LIMIT 200 OFFSET 0)
-> wait for any amount of seconds
-> read 200 (SELECT * FROM X LIMIT 200 OFFSET 200)
-> wait for any amount of seconds
-> read 200 (SELECT * FROM X LIMIT 200 OFFSET 400)
-> wait for any amount of seconds
...
-> read 200 (SELECT * FROM X LIMIT 200 OFFSET 1000)
-> no rows returned, end the iteration

[brianc]

Yeah....I think statement_timeout is used to cancel long running queries from the backend because even if your cursor is "paused" while your app processes the data that's come in, the client is still taking up a "slot" within postgres and the query is considered to be "active." Postgres can comfortably handle a few hundred connections, but they're not a really light-weight resource from the server's point of view and so statement timeout is supposed to help make sure nothing hangs out for too long running a query...as long as your cursor hasn't finished reading, it's still connected and has a query dispatched, and the timeout is still ticking. Similarly if you set a statement timeout of 1 second and do select pg_sleep(5) the query will be killed after 1 second even though it's doing nothing but sleeping.

Your example you listed here:

-> read 200 (SELECT * FROM X LIMIT 200 OFFSET 0)
-> wait for any amount of seconds
-> read 200 (SELECT * FROM X LIMIT 200 OFFSET 200)
-> wait for any amount of seconds
-> read 200 (SELECT * FROM X LIMIT 200 OFFSET 400)
-> wait for any amount of seconds
...
-> read 200 (SELECT * FROM X LIMIT 200 OFFSET 1000)
-> no rows returned, end the iteration

those are all separate queries - a cursor doesn't work that way - it actually dispatches a single query and throttles the amount of results returned from the result set by only reading n rows out at a time...but any resources used to create that result set (such as temp tables or anything else which may have been created in the query) are still held, where as if you do kinda more old school "pagination" queries w/ limit/offset each one of those queries is discrete.

I think yer best bet here is to relax your statement_timeout for the cursor. Is there a way you can do that? The postgres docs don't recommend configuring one globally specifically because it can cause problems like this.

[alesmenzel]

Thanks for the explanation.

[brianc]

no prob! lmk if you have any more issues, always happy to take a look. :)

…

On Tue, Apr 28, 2020 at 11:45 AM Aleš Menzel ***@***.***> wrote: Thanks for the explanation. — You are receiving this because you modified the open/close state. Reply to this email directly, view it on GitHub <#2183 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAAMHINL2HE7XLM2DJGFNN3RO4BY5ANCNFSM4MS2DYDA> .