Multiple cursors on single transaction not supported.

[McAronDev]

  await sql.begin((tx:any)=>{
    return Promise.all([
      tx`select 1 as cursor, x from generate_series(1,4) as x`.cursor(async (row: any[])=>{
        console.log('cursor1' , JSON.stringify(row));
        await new Promise(resolve=>setTimeout(resolve, 2000));
      }),
      tx`select 2 as cursor, x from generate_series(101,104) as x`.cursor(async (row: any[])=>{
        console.log('cursor2' , JSON.stringify(row));
        await new Promise(resolve=>setTimeout(resolve, 1000));
      })
    ]);
  });

This code returns unexpected console output:

cursor1 {"cursor":1,"x":1}
cursor1 {"cursor":2,"x":101}
cursor1 {"cursor":2,"x":102}
cursor1 {"cursor":2,"x":103}
cursor1 {"cursor":2,"x":104}

is it possible to support simultaneous reading for multiple cursors in single transaction?

[McAronDev]

Same behavior when using cursors without transactions but with limited pool size. When second cursor was created on same connection as first one reading from first cursor hangs, but first's cursor handler receives rows from second cursor.

[porsager]

Hi @McAronDev

That's really interesting, I haven't thought about that use case when implementing cursors, so I would have to give it some more thought.

I'm currently very hung up with work, so it might be a little while before I can find the time to look closer.

[pauldraper]

You can't run more than one query simultaneously in a transaction.

There is a bug here, but FYI, your first example can't ever really be done.

[McAronDev]

@pauldraper

You can't run more than one query simultaneously in a transaction.

Yes. But they should be queued. Since this lib does not provide separate cursor creation and it's fetching methods, i guess we should be able to reuse transaction while cursor is "paused" and until that all queries should be queued.

So in my example, there should be the following behavior:

1. cursor1 created and start fetching. cursor2 creation is queued.
   DECLARE cursor1 CURSOR FOR select 1 as cursor, x from generate_series(1,4) as x; FETCH 1 FROM cursor1;
2. received first row from cursor1. cursor1 paused for 2 seconds.
3. cursor2 created and start fetching.
   DECLARE cursor2 CURSOR FOR select 2 as cursor, x from generate_series(101,104) as x; FETCH 1 FROM cursor2;
4. received first row from cursor2. cursor2 paused for 1 second.
5. cursor2 resumed
   FETCH 1 FROM cursor2;
6. cursor2 received second row, and paused again for 1sec

... Following fetching order is not easy predictable=) but we should receive all rows from both cursors.

[pauldraper]

Sure, that makes sense.