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

erayhanoglu commented 6 years ago

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.

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 commented 6 years ago

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 commented 6 years ago

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.

brianc / node-pg-cursor

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