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

[George Neuner]

Hi Thomas,

On 1/20/2015 6:53 AM, Thomas Lynch wrote:
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.


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.

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.
see  http://www.postgresql.org/docs/9.3/static/sql-savepoint.html

Incidentally, Oracle and SQL Server do this also.

Hope this helps.
George

____________________
  Racket Users list:
  http://lists.racket-lang.org/users