Allow manual commit of transaction

When working with a middleware styled HTTP server like express.js or koa.js. I find it is often convenient to setup the transactions (i.e. start the transaction, set role, set search_path etc..) inside a middleware function like the following. This avoids code duplication and minimise the risk of data leak caused by forget setting the role in a route handler function)

async function setupdb(ctx, next) {
   await pg.begin(async (sql) => {
       await sql`set role to admin`;
       ctx.state.db = sql; // pass the transaction to the route handler via ctx 
       await next() // we query the database inside the handler, but only at the beginning of its execution. then the control is handed off to some other things which doesn't need the database at all.
   })
}

However, if the event handler includes some kind of long running process the transaction will remain open, possibly indefinitely. I have a use case where the API endpoint is a HTTP2 event-stream, in which case the await next() in theory can last forever. In those cases, it is desirable to manually commit the transaction inside the route handler function to avoid long running transaction.

I understand that there is nothing stops me from run a commit inside the transaction handler, but currently postgres.js will always try to commit again when the transaction handler function exits and we will get a 25P01 no transaction in progress warning from the database. A more important problem I see is that I believe the connection won't be released back to pool until the transaction handler exit.

Is there anyway to workaround this problem? Or do you think we can somehow support this kind of manual commit within a transaction? Thanks

porsager / postgres

Allow manual commit of transaction #700