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

p.s. here is my as-transaction macro for wrapping code in transaction
blocks.  The idea here is to make sure transactions begin and
commit/rollback is paired even in the presence of exceptions.  This macro
also deals with sharing the connection between threads, thus the
semaphore.  The db calls can be replaced with query calls simply enough to
use the racket/db library directly.  I don't mind doing a little
computation that otherwise might be outside the block within the
transaction block because the network delays of the query will dominate the
harmonic mean of the total performance.


 Perhaps someone will find this useful:

;;--------------------------------------------------------------------------------
;;  a transaction environment
;;
;;    sets semaphore so no other transaction environment will use the db
connection
;;    starts a transaction, and rolls it back if there is an exception,
otherwise closes it
;;
;;  (as-transaction body ...)
;;
  (define-syntax (as-transaction stx)
    (syntax-case stx ()
      [(as-transaction body ...)
        #`(begin
            (semaphore-wait (current-db-semaphore))
            (db-begin)
            (begin0
             (with-handlers
               (
                 [(lambda (v) #t) ; this handler catches anything
                   (lambda (v)
                     (db-rollback)
                     (semaphore-post (current-db-semaphore))
                     (raise v)
                     )
                   ]
                 )
               body ...
               )
              (db-commit)
              (semaphore-post (current-db-semaphore))
            ))
        ]
      ))





On Wed, Jan 21, 2015 at 1:29 PM, Thomas Lynch <
thomas.lynch at reasoningtechnology.com> wrote:

> Thankyou Ryan.  I also changed to the managed transaction approach and it
> is working well.  It is a great library, and simple to use.
>
> IMHO the library should not do a rollback or require one after an
> exception, as a query failure is information that can be acted on, i.e. a
> program might use the information that the query failed to take different
> actions within the same transaction - as George mentions above.  I don't
> know if the server side is good with that, seems it would be.  .. but you
> already knew that and well I'm not doing it here so the issue is only moot
> for us.  Right now I'm glad to just not lock up the connection.  Thanks.
>
> I can imagine cases of a racket program relaying canned SQL that has
> 'unmanaged' transactions embedded in it.  Perhaps this SQL is just taken
> from files used in other applications before, perhaps from a cut and paste
> job into racket, or perhaps the SQL comes in real time from libraries,
> especially foreign libraries.  Perhaps such transactions could even be
> embedded in server side psql programs.  Don't know if if this latter case
> matters for racket/db library as it would all happen server side.
>
> Now what would be really nice is server side racket!  Let me send lambdas
> over there to later invoked multiple times with operands to perform query
> get, react, insert code locally instead of dumping that across the slow
> network layer of the OSI stack.   .. this sort of thing keeps me up nights
> ;-)
>
> Thomas
>
>
>
>
>
>
>
> On Wed, Jan 21, 2015 at 1:04 AM, Ryan Culpepper <ryanc at ccs.neu.edu> wrote:
>
>> 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
>>
>>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.racket-lang.org/users/archive/attachments/20150121/dc97a923/attachment.html>