Can not use connection for other queries while cursor is open.

[erayhanoglu]

node-pg does not allow using connection while cursor is open. Here an example scenario which works with some other database libraries but with node-pg.

1. create connection > 2. start transaction > 3. create cursor > 4. execute update > 5.commit > 6.close cursor > 7. close connection.

application is getting locked in 4. step. it waits until cursor is closed. This limits usage.

[sdc395]

I've just encountered exactly this problem. I need to be able to use a second cursor within the scope of the first cursor and also insert new records within the scope of the first cursor. I'm still investigating the cause of the deadlock but, as far as I know, this is not a PostgreSQL limitation.

[sdc395]

OK, here's a workaround from a pg-promise point of view. To be honest, I'm not sure why I bothered with node-pg-cursor. Is there a problem with the following?

async function* iterate(tx: pgPromise.IBaseProtocol<any>, query: string, values?: any) {

    const name = v4();

    tx.none(`DECLARE "${name}" NO SCROLL CURSOR FOR ${query}`, values);

    try {

        while (true) {

            const record = await tx.oneOrNone(`FETCH 1 "${name}"`);

            if (isNil(record)) {

                break;
            }

            yield record;
        }
    }
    finally {

        tx.none(`CLOSE "${name}"`);
    }
}

v4 is from the uuid package. isNil is from lodash.