<html>
<head>
<meta content="text/html; charset=windows-1252"
http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<div class="moz-cite-prefix">Hi Thomas,<br>
<br>
On 1/20/2015 6:53 AM, Thomas Lynch wrote:<br>
</div>
<blockquote
cite="mid:CAGxFmCN9Hk5Z5su=BnHxXRRU817jbGHB703ZQaXrWkUZmNNOvA@mail.gmail.com"
type="cite">
<div dir="ltr">
<div>For purposes of illustration, I run the following query
here in the psql shell to show the the error message that it
correctly issues:</div>
<div><br>
</div>
<blockquote style="margin:0px 0px 0px
40px;border:none;padding:0px">
<div>
<div>=> insert into x_unique_counters values
('table_author','1');</div>
</div>
<div>
<div>ERROR: duplicate key value violates unique constraint
"x_unique_counters_pkey"</div>
</div>
<div>
<div>DETAIL: Key (name)=(table_author) already exists.</div>
</div>
</blockquote>
<div><br>
</div>
<div>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</div>
<blockquote style="margin:0px 0px 0px
40px;border:none;padding:0px">
<div><br>
</div>
<div>
<div> (begin</div>
</div>
<div>
<div> (semaphore-wait (current-db-semaphore))</div>
</div>
<div>
<div> (query-exec pgc "begin")</div>
</div>
<div>
<div> (begin0</div>
</div>
<div>
<div> (with-handlers</div>
</div>
<div>
<div> (</div>
</div>
<div>
<div> [(lambda (v) #t) ; this handler
catches anything</div>
</div>
<div>
<div> (lambda (v)</div>
</div>
<div>
<div> (query-exec pgc "rollback")</div>
</div>
<div>
<div> (semaphore-post
(current-db-semaphore))</div>
</div>
<div>
<div> (raise v)</div>
</div>
<div>
<div> )</div>
</div>
<div>
<div> ]</div>
</div>
<div>
<div> )</div>
</div>
</blockquote>
<blockquote style="margin:0px 0px 0px
40px;border:none;padding:0px">
<div>
<div> (query-exec pgc the-query) ; this throws
an exception</div>
</div>
</blockquote>
<blockquote style="margin:0px 0px 0px
40px;border:none;padding:0px">
<div>
<div> )</div>
</div>
<div>
<div> (db-exec "commit")</div>
</div>
<div>
<div> (semaphore-post (current-db-semaphore))</div>
</div>
<div>
<div> ))</div>
</div>
<div>
<div> ]</div>
</div>
<div>
<div> ))</div>
</div>
</blockquote>
<div><br>
</div>
<div>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:</div>
<div><br>
</div>
<blockquote style="margin:0 0 0 40px;border:none;padding:0px">
<div>
<div>>(db-exec* '("rollback"))</div>
</div>
<div>
<div>query-exec: current transaction is invalid</div>
</div>
</blockquote>
<div><br>
</div>
<div>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.</div>
</div>
</blockquote>
<br>
<br>
Your code is fine but I believe you've bumped into a quirk of
transaction error handling in Postgresql: when an SQL exception is
raised, Postgresql aborts the current (sub-)transaction
automatically - so in your example the transaction already has been
rolled back and no longer exists when your code goes to do the
explicit rollback.<br>
<br>
If you want to keep the transaction open in the event of an error -
e.g., to preserve things that *did* work - you need to establish a
savepoint prior to executing the statement that might fail. The
savepoint opens an implicit sub-transaction that can fail
independently. If an error occurs, automatic rollback affects only
the subtransaction and returns to the last savepoint rather than
aborting the whole [outer] transaction. It is subtly different
from opening an explicit sub-transaction, however.<br>
see <a class="moz-txt-link-freetext" href="http://www.postgresql.org/docs/9.3/static/sql-savepoint.html">http://www.postgresql.org/docs/9.3/static/sql-savepoint.html</a><br>
<br>
Incidentally, Oracle and SQL Server do this also.<br>
<br>
Hope this helps.<br>
George<br>
<br>
</body>
</html>