Catching Exceptions In Transactions

In my previous post, I showed an example of creating a uuid:

enqueueDB :: Value -> DB PayloadId
enqueueDB value = $ do
pid <- liftIO randomIO
execute
[sql| INSERT INTO payloads (id, value)
VALUES (?, ?);
NOTIFY enqueue;
|]
(pid, value)
return $ PayloadId pid

The problem with this function is that it can fail if the uuid already exists. Ideally, we would like to catch this exception and retry.

However, catching exceptions inside transactions is tricky. We need a mechanism to rollback the transaction to the point of the exception handler and continue from that point. Luckily, PostgreSQL provides this functionality for us through SAVEPOINTs.

In fact, pg-transact's MonadCatch instance is implemented using this feature:

instance (MonadIO m, MonadMask m) => MonadCatch (DBT m) where
catch (DBT act) handler = DBT $ mask $ \restore -> do
conn <- ask
sp <- liftIO $ newSavepoint conn
let setup = catch (restore act) $ \e -> do
liftIO $ rollbackToSavepoint conn sp
unDBT $ handler e

cleanup = liftIO $
tryJust (guard . isClass25) (releaseSavepoint conn sp)
    setup `finally` cleanup

Now we can catch so we can write a proper enqueueDB method:

retryOnUniqueViolation :: MonadCatch m => m a -> m a
retryOnUniqueViolation act = try act >>= \case
Right x -> return x
Left e ->
if Simple.sqlState e == "23505" &&
"duplicate key" `BS.isPrefixOf` Simple.sqlErrorMsg e
then act
else throwM e
enqueueDB :: Value -> DB PayloadId
enqueueDB value = retryOnUniqueViolation $ do
pid <- liftIO randomIO
execute
[sql| INSERT INTO payloads (id, value)
VALUES (?, ?);
NOTIFY enqueue;
|]
(pid, value)
return $ PayloadId pid

SAVEPOINTs are not something I am super familiar with. I don’t really understand the performance implications, although my research suggests they do not have a large negative effect on performance. I would recommend some caution if one creates thousands of catches in the DB monad. Luckily, that is not the typical case.

Conclusion

Catching and throwing exceptions is a common occurrence. Doing so in database transactions is convenient. SAVEPOINTs are a powerful PostgreSQL feature and paired with MonadCatch gives us a natural interface for taking advantage of them.