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

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/42b2ba17/attachment-0001.html>