2

I would like to confirm a point with PostgreSQL cursor.

I have to implement a select factory for huge result set query (more than 1Go of small-weight rows). Thus I have learn about cursor and I execute my query the following way:

I create a cursor:

DECLARE HashedCursorName CURSOR FOR SELECT * FROM BigResultSetQuery;

I repetitively fetch small amount of resultset:

FETCH 1000 FROM HashedCursorName;

When resultset is exhausted I close the cursor:

CLOSE HashedCursorName;

I need to address concurrency, I mean, many users must be able to use the the same query stored in API procedure simultaneously. When a user execute a stored query, he receives a new connection. I first have mangled my cursor name in API because I thought that if all select query are called StaticCursorName I would have a problem with two user tempting to create the same cursor or reading the same. Everything works as expected.

Then I tried to change the cursor name to a static name, and it still work. I am able to run multiple same query simultaneously. Then I wonder, is cursor bound to connection?, and then does PostgreSQL resolve concurrency by assigning cursor to session?

4

Yes, cursors are not shared across sessions, and their namespace is private to their session as well.

Moreover, if the cursor is not declared "holdable" trough the WITH HOLD clause, it's automatically closed at the end of the transaction.

The relationship between cursor lifespan and session is detailed in the docpage for CLOSE:

Every non-holdable open cursor is implicitly closed when a transaction is terminated by COMMIT or ROLLBACK. A holdable cursor is implicitly closed if the transaction that created it aborts via ROLLBACK. If the creating transaction successfully commits, the holdable cursor remains open until an explicit CLOSE is executed, or the client disconnects.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.