CODEWITHSUNDEEP
postgresqldatabase-cursornode-postgres
Daniel A. White
Daniel A. White

Reputation: 191037

Are PostgreSQL's cursors per connection or something else?

I'm using node-postgres (pg) and its pg-cursor extension. I noticed when reusing the same connection between multiple cursors, it locks due to what I would assume that a single connection is only able to serve only one cursor/result set at a time.

Some pseudo code

const client = new pg.Client();
const parentAsyncGenerator = getParentAsyncGenerator(client); // wrap a cursor's result

for await (const parent of parentAsyncGenerator) {
  const childAsyncGenerator = await getChildAsyncGenerator(client, parent); // blocks here
  for await (const child of childAsyncGenerator) { // or here
    // magic here
  }
}

I'm not overly concerned about performance here, I just want to know if my assumption is correct or not.

Using separate pool clients work.

Is it true that one connection can manage one cursor at a given time?

Upvotes: 0

Views: 341

Answers (1)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 658472

Is it true that one connection can manage one cursor at a given time?

No. The same connection (= same session in Postgres) can handle multiple cursors. Cursor names must be distinct, though. Cursors occupy resources, and can also hold row locks. So, obviously, you want to CLOSE them, as soon as they are not needed any more.

Cursors are closed at the end of a session in any case. So the same cursor (in plain SQL or in PL/pgSQL) can only be accessed from the same connection (same session). That's the answer to the question in the title, which differs from the question in the body!

Upvotes: 1

Related Questions