<div dir="ltr">Thankyou Ryan. I also changed to the managed transaction approach and it is working well. It is a great library, and simple to use.<div><br></div><div>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.<br><div><br></div><div>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.</div><div><br></div><div>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 ;-)</div><div><br></div><div>Thomas</div><div><br></div><div><br></div><div><br><br></div><div><br></div><div><br></div></div></div><div class="gmail_extra"><br><div class="gmail_quote">On Wed, Jan 21, 2015 at 1:04 AM, Ryan Culpepper <span dir="ltr"><<a href="mailto:ryanc@ccs.neu.edu" target="_blank">ryanc@ccs.neu.edu</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex"><div class="HOEnZb"><div class="h5">On 01/20/2015 06:53 AM, Thomas Lynch wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<br>
According to the manual,<br>
<a href="http://docs.racket-lang.org/db/query-api.html#%28part._transactions%29" target="_blank">http://docs.racket-lang.org/<u></u>db/query-api.html#%28part._<u></u>transactions%29</a>,<br>
section<br>
3.5, postgres transactions can be issued directly in SQL (so called<br>
'unmanaged'). Here is a short code segment that fails doing unmanaged<br>
transactions (and nothing else).<br>
<br>
My question here is whether this code is doing unmanaged transactions<br>
correctly, and they are not working as advertised, or if there is<br>
something left out like an explicit reset to the connection after it<br>
throws an exception.<br>
<br>
For purposes of illustration, I run the following query here in the psql<br>
shell to show the the error message that it correctly issues:<br>
<br>
=> insert into x_unique_counters values ('table_author','1');<br>
ERROR: duplicate key value violates unique constraint<br>
"x_unique_counters_pkey"<br>
DETAIL: Key (name)=(table_author) already exists.<br>
<br>
<br>
Now the racket program is ready for this kind of problem, here<br>
'the-query' is the same query just shown above, but this time issued<br>
from this code<br>
<br>
<br>
(begin<br>
(semaphore-wait (current-db-semaphore))<br>
(query-exec pgc "begin")<br>
(begin0<br>
(with-handlers<br>
(<br>
[(lambda (v) #t) ; this handler catches anything<br>
(lambda (v)<br>
(query-exec pgc "rollback")<br>
(semaphore-post (current-db-semaphore))<br>
(raise v)<br>
)<br>
]<br>
)<br>
<br>
(query-exec pgc the-query) ; this throws an exception<br>
<br>
)<br>
(db-exec "commit")<br>
(semaphore-post (current-db-semaphore))<br>
))<br>
]<br>
))<br>
<br>
<br>
So when query-exec throws an exception, the handler grabs it. Then the<br>
handler executes an SQL rollback to cancel the transaction. All appears<br>
to be good, but then bang! we get an exception in the handler while<br>
doing the rollback:<br>
<br>
>(db-exec* '("rollback"))<br>
query-exec: current transaction is invalid<br>
<br>
<br>
All attempts to do any further queries on the connection fail with the<br>
same error. It appears to be foo-bar and a new connection must be made<br>
before any further SQL can be issued against the database.<br>
<br>
Hmm, what is going on here? Unmanaged connections not working as<br>
advertised .. or some switch that must be flipped to tell the connection<br>
to work again?<br>
</blockquote>
<br></div></div>
That looks like a bug in the db library. It rejects all queries when the transaction is invalid, rather than allowing rollbacks through.<br>
<br>
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.<span class="HOEnZb"><font color="#888888"><br>
<br>
Ryan<br>
<br>
</font></span></blockquote></div><br></div>