Hi Tobias.

This is already implemented in Postgres.js and is called Pipelining, so yes - definitely possible 😉

Now when using Postgres.js you need to use sql.begin() (see the docs) for transactions, since if you're not doing that you don't have any assurance that your queries will run on the same connection, and consequently inside your transaction. The reason your code above works is only because you've set max: 1.

To achieve pipelining with transactions you simply return an array with the "unawaited" queries like this:

const result = await sql.begin(sql => [
 sql`select 1 as a`,
 sql`select 2 as a`,
 sql`select 3 as a`,
 sql`select 4 as a`,
 sql`select 5 as a`,
 sql`select 6 as a`
])

In v1 there is no limit to the number of pipelined queries per connection, but through testing I've found pipelining benefits max out around about 100 pipelined queries (of course different depending on the connection), but since a broken connection will throw all those queries I think it's a good tradeoff to set a boundary on the amount of queries pipelined, so in v3 - 100 is the default, but can be configured using max_pipeline.

hmm, the pipeling does for queries within this transaction what independent queries can do without pipeline. But there might be some more optimizations potential.

please give me some time and keep this issue open. I want to do some test and study the code.

No problem :) A possible optimization in v3 is allowing multiple full transactions to be pipelined, but to be honest a lot of real world scenarios can do that be using CTE's instead which in any case I can think of is a better way of going about it anyway.

Also, I read your comment to the article linked, and I think it's telling of a footgun in Postgres.js that you tried to run transactions like you did. I would love your insight to improve either the documentation, or if there should be a mechanism built in to prevent users of the library to try and do transactions like that which could be very dangerous?

So,... this is the second time I start writing:

I often see the pattern of processes that can be triggered from API or message-system, that looks like this:
(Usually, this is decorated with more function calls and OOP patterns, but it boil down to this. )

async function(args){
 await sql.begin(t => {
 const items = await sql`select * from items where id in (${args})`;
 const relatedItems = await sql`select * from items where id in (${items.map(i=>i.relatedId)})`;
 // some processing, sorting, mapping filtering ....
 await sql`update ...`;
 await sql`update ...`;
 });
}

Here is a timeline for this process. We have only one connection and it is called twice.

b=begin
s=select
u=update
c=commit/rollback
p=business logic processing
[1-9]=waiting / network roundtrip
-> b 1 s 2 s 2 p u 3 u 4 c 5 b 1 s 2 s 2 p u 3 u 4 c

All we talk about is how to remove as many numbers as possible (remove as much waiting time as possible.)

1. waiting after begin. We have to await here. Because the db can return the error 0b000, invalid_transaction_initiation. If we already started pipelining (not await) the following statements could run without transaction. Something like a connection error would be ok because no other sql would be send and the transaction would rollback in db. But if the error is db internal like (I don't know) transaction quota or max memory.

Because of this, that we do not want to pipeline queries that are supposed to run in a transaction, to run without transaction, we have to await the begin statement. And that, makes the "concurrent transaction on a single connection" impossible.

If the postgres server allow us to send a flag with each query of (execute only in transaction), then it would be possible.

2. The number 2 waiting blocks are logically needed by the application. maybe the developer could try to use a join or something.

3. This waiting times with a 3, It is the developers decision. He can also wrap them into a Promise.all to pipelining the updates.

4. This library has to wait for number four,because after some updates, the app could continue with more logic within the transaction.

5. The break between the transactions. I think we can get rid of this. In v2 I saw how to do it by calling the next function, in v3 it is more difficult.

pipelining as of today

The pipelining with 5 queries causes the following timeline today:

-> b 1 uuuuu 4 c 5

We have to await the number 1, I think there is potential to avoid number 4 and 5 awaiting.
number 5 awaiting is as described above.

for number for awaiting, I made a change rewrite...TobiasNickel:rewrite

This change is immediately pipelining the commit command. This is ok, because the db will automatically turn this into a rollback if needed. The application is going to receive the first error from Promise.all.

conclusion

I have not been able to bring a revolution with single connection concurrent transaction.

But I hope this post is valuable for you.

Postgres protocol, supports pipeline but does not support multiplexing

Interesting topic. I've also just recently suggested to implement a .pipeline function in node-postgres.