Any suggestion on how I'd commit/rollback using a connection pool?

[vladiibine]

Hi,

If I want to commit/rollback a transaction, is there any clean way of doing that, when using a connection pool?

[UPDATE] after a few hours:

I found that this way works

conn = yield pool.getconn()
with pool.manage(conn):
    try:
        cursor = yield conn.execute('select 1')
    except MyException:
        yield conn.execute('ROLLBACK')
    .....

While this works, and it commits the work, I still am wondering:

• Could I still avoid executting a commit after exiting the managed context?
• Rolling back - I see indeed no rollback method on the connection
• After retrieving a cursor from yield pool.execute('select something') I'll get back a cursor. This is done in an async manner. However when I want to retrieve a huge set of data from the DB, is this done synchronously? Because as far as I see, I'm supposed to use the cursor in a synchronous way. Is there any way to get back the data in a manner like chunk = yield cursor.fetchmany(10000) and continue iterating like that?

Thanks a lot in advance. After figuring these things out, I'll try make some time to help you update the documentation with these issues.

[haizaar]

Could I still avoid executting a commit after exiting the managed context?

Nothing in momoko lib calls COMMIT for you. I'm not sure why you believe it happens (may be Postgres does it behind the scenes in some way - I'm not much familiar with this DB). BTW, there is Connection.transaction method, that executes a list of statements and commits/rolls back. Does it help you?

Rolling back - I see indeed no rollback method on the connection

Again, there are no helper methods like Connection.commit and Connection.rollback - you can call execute with appropriate statements or extend connection class to add this methods (currently it will requires monkey-patching the imported package, but I can extend Pool to accept ConnectionClass in constructor).

After retrieving a cursor from yield pool.execute('select something') I'll get back a cursor...

Are named cursors what you are after?

[haizaar]

Closing this discussion due to inactivity. Feel free to reopen.

[vladiibine]

Yeah, I'm still thinking about it. If I have anything interesting I'll let you know. Thanks for the support! :)