How to set query_timeout in relation to statement_timeout?

Question

We can set 2 timeouts for the Client:

• statement_timeout: number of milliseconds before a statement in query will time out, default is no timeout
• query_timeout number of milliseconds before a query call will timeout, default is no timeout

I understand it like this:

• the statement_timeout will be passed to the database (see postgres-docs:statement_timeout) and when a statement takes longer than this, the database will abort the query and return an error
• the database does not know anything about the query_timeout. This is handled by the drive (node-postgres). When this timeout is reached, node-postgres will just stop listening for a response, but the database may still be executing the query

Question 1 Should we set the query timeout slightly longer than the statement timeout?

I think so because then:

1. when a query really takes too long, the db will abort the query and return the error to the client
2. when the application does not get any response from the server within the query-timeout the application will throw a timeout error
   Question 2: what could be the reason for this? e.g. problems with the TCP/IP connection?

Transactions

What is the situation when we use transactions?
e.g. when we look at the example from the docs:

  try {
    await client.query('BEGIN')
    const queryText = 'INSERT INTO users(name) VALUES($1) RETURNING id'
    const res = await client.query(queryText, ['brianc'])
    const insertPhotoText = 'INSERT INTO photos(user_id, photo_url) VALUES ($1, $2)'
    const insertPhotoValues = [res.rows[0].id, 's3.bucket.foo']
    await client.query(insertPhotoText, insertPhotoValues)
    await client.query('COMMIT')
  } catch (e) {
    await client.query('ROLLBACK')
    throw e
  }

So in the happy path, we call query() 5 times: and the query/statement timouts are applied per query() call.
Question 3 So also the BEGIN/COMMIT queries have the same timeout a the INSERT queries, right?

Shrovetide

see related question in node-postgres – Shrovetide

Answer 1

After some tests using pg-promise, I came to this conclusion: the query-timeout should be slightly longer than the statement-timeout

Details

query-timeout only

• query-timeout = 10sec
• statement-timeout = undefined

In a transaction we send pgsleep(60) (sleep for 60 seconds)

Then this will happen:

• the BEGIN statement is sent to the db
• then pgsleep(60) is sent to the db
• after 10 seconds we reach the query-timeout: node-postgres will inform pg-promise
• pg-promise will send ROLLBACK
  • since the connection is still executing the sleep-60, the rollback will have to wait
  • after another 10 seconds pg-promise will return a Query read timeout error
• the database will continue to execute the sleep statement for the remaining time

statement-timeout only

• query-timeout = undefined
• statement-timeout = 10sec

In a transaction we send pgsleep(60) (sleep for 60 seconds)

Then this will happen:

• the BEGIN statement is sent to the db
• then pgsleep(60) is sent to the db
• after 10 seconds the database will abort the sleep, rollback the transaction and send back an error to the client: code 57014: canceling statement due to statement timeout
• the application can handle the error, the connection is released

So we should prefer to get a statement-timeout and set the query-timeout slightly longer, in case that the database cannot send back the error (I guess this could be the case when there are e.g. network issues)

Shrovetide