On 01/20/2015 06:53 AM, Thomas Lynch wrote:
According to the manual, http://docs.racket-lang.org/db/query-api.html#%28part._transactions%29, section 3.5, postgres transactions can be issued directly in SQL (so called 'unmanaged'). Here is a short code segment that fails doing unmanaged transactions (and nothing else). My question here is whether this code is doing unmanaged transactions correctly, and they are not working as advertised, or if there is something left out like an explicit reset to the connection after it throws an exception. For purposes of illustration, I run the following query here in the psql shell to show the the error message that it correctly issues: => insert into x_unique_counters values ('table_author','1'); ERROR: duplicate key value violates unique constraint "x_unique_counters_pkey" DETAIL: Key (name)=(table_author) already exists. Now the racket program is ready for this kind of problem, here 'the-query' is the same query just shown above, but this time issued from this code (begin (semaphore-wait (current-db-semaphore)) (query-exec pgc "begin") (begin0 (with-handlers ( [(lambda (v) #t) ; this handler catches anything (lambda (v) (query-exec pgc "rollback") (semaphore-post (current-db-semaphore)) (raise v) ) ] ) (query-exec pgc the-query) ; this throws an exception ) (db-exec "commit") (semaphore-post (current-db-semaphore)) )) ] )) So when query-exec throws an exception, the handler grabs it. Then the handler executes an SQL rollback to cancel the transaction. All appears to be good, but then bang! we get an exception in the handler while doing the rollback: >(db-exec* '("rollback")) query-exec: current transaction is invalid All attempts to do any further queries on the connection fail with the same error. It appears to be foo-bar and a new connection must be made before any further SQL can be issued against the database. Hmm, what is going on here? Unmanaged connections not working as advertised .. or some switch that must be flipped to tell the connection to work again?
That looks like a bug in the db library. It rejects all queries when the transaction is invalid, rather than allowing rollbacks through.
A workaround is to use (rollback-transaction pgc) in the exception handler. Despite what the docs say, it's okay to use rollback-transaction on a unmanaged transaction. I'll fix the docs.
Ryan ____________________ Racket Users list: http://lists.racket-lang.org/users
