Lambda + pg-promise + transaction = random timeout

Question

I've been struggling with this issue for the past 48 hours and after reading lots of answers here, blogs, articles, documentation, etc... I still cannot find a solution!

Basically, I have a lambda function with a 2-minute timeout. Based on logs and insights, it processes fine most of the requests, but it randomly fails with a "timed out" error when trying to execute the transaction below.

Lambda code (chopped for legibility):


import pgp from 'pg-promise'
import logger from '../lib/logger'

const Database = pgp()

const db = Database({
 connectionString: process.env.DATABASE_URL,
 max: 3,
 idleTimeoutMillis: 10000,
})

db.connect()
 .then(() => logger.info('Successfully connected to the PG database'))
 .catch(err => logger.error({ err }))

export const handler = async (event, context) => {
 logger.info('transaction start...')

 await db.tx(async tx => {
   await tx.none(
     `
     INSERT INTO...`,
     [someValue1, someValue2]
   )
   const updatedRow = await tx.one(
     `
     UPDATE Something...`,
     [somethingId]
   )
   return someFunction(updatedRow)
 })

 logger.info('transaction end...')
}

const someFunction = async (data) => {
 return db.task('someTask', async ctx => {
   const value = await ctx.oneOrNone(
     `SELECT * FROM Something...`,
     [data.id]
   )

   if (!value) {
     return
   }

   const doStuff = async (points) =>
     ctx.none(
       `UPDATE Something WHERE id =.....`,
       [points]
     )

   // increment points x miles
   if (data.condition1) {
     await doStuff(10)
   }

   if (data.condition2) {
     await doStuff(20)
   }

   if (data.condition3) {
     await doStuff(30)
   }
 })
}

I see that the transaction starts but never ends, so the function is inevitably killed by timeout.

I read the whole wiki in pg-promise and understood everything about tweaks, performance, good practices, etc. But still, something is very wrong.

You can see that I also changed the pool size and max timeout for experimenting, but it didn't fix the issue.

Any ideas?

Thanks!

vitaly-t · Answer 1 · 2020-05-19T20:13:58.570

0

Most likely you are running out of connections. You are not using them correctly, while at the same time you are setting a very low connection limit of 3.

The first issue, you are testing a connection by calling connect, without following it with done, which permanently occupies, and thus wastes your initial/primary connection. See the example here where we are releasing the connection after we have tested it.

The second problem - you are requesting a new connection (by calling .task on the root db level) while inside a transaction, which is bad for any environment, while particularly critical when you have very few connections available.

The task should be reusing connection of the current transaction, which means your someFunction should either require the connection context, or at least take it as optional parameter:

const someFunction = async (data, ctx) => {
 return (ctx || db).task('someTask', async tx => {
   const value = await tx.oneOrNone(

Task <-> Transaction interfaces in pg-promise can be fully inter-nested, you see, propagating the current connection through all levels.

Also, I suggest use of pg-monitor, for a good query+context visualization.

edited May 19 '20 at 20:13

answered May 19 '20 at 19:48

vitaly-t

24,279
15
116
138

Thanks, but I don't understand when you say that the task does not reuse the parent connection, as per stated in the docs: "Each task/transaction manages the connection automatically. When executed on the root Database object, the connection is allocated from the pool, and once the method's callback has finished, the connection is released back to the pool. However, when invoked inside another task or transaction, the method reuses the parent connection." So, if I'm inside a transaction, why does the task not know about it? – demian85 May 19 '20 at 20:46
And even if those things are wrong, why is the function timing out? The average function run time is ~3secs, but 120secs when timing out. Why am I running out of connections? I changed it to 3 max just for testing because the default setting does not work for me. How many connections should I open considering it is a lambda function meant to be reused by subsequent requests? – demian85 May 19 '20 at 20:51
`if I'm inside a transaction, why does the task not know about it?` because you execute them in a completely detached manner. The change I explained links task to the containing transaction, to make it aware of the available connection. `why is the function timing out?` - because your task is trying to allocate its own connection, while there is none left, so it gets stuck. – vitaly-t May 19 '20 at 20:53
Okay, so the docs are confusing. It makes you think that the task knows about the connection context, but you have to explicitly let it know by sending it as a parameter. It does not scale at all. How should I know that a certain function will be called inside a transaction? And still, it does not explain why I'm running out of connections. I will set the max at 99999, will it work then? Can we talk somewhere else more friendly, this is painful. I appreciate your help!! – demian85 May 19 '20 at 21:11
`you have to explicitly let it know by sending it as a parameter`. No, you don't, you just create tasks/transactions from the container, that's how connection can be inherited. `It does not scale at all` - what does that have to do with scaling? `How should I know that a certain function will be called inside a transaction` - read the API, you get all the details within task/transaction context `ctx`. – vitaly-t May 19 '20 at 21:31
`Can we talk somewhere else more friendly, this is painful` - both library and StackOverflow have chats. – vitaly-t May 19 '20 at 21:33
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/214216/discussion-between-vitaly-t-and-demian85). – vitaly-t May 19 '20 at 22:17

Lambda + pg-promise + transaction = random timeout

1 Answers1