[racket] racket/db exception handling and unmanaged transactions with postgres

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