<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>