Where possible use a reset command against the DB to verify a pooled connection.

This is related to 0a29c2fafbfb7ed42c54208cea576c6b0d600d82, when getting a connection out of the pool we issue a simple query on that connection
to ensure that it is still valid. Several databases have reset commands for use with connection pools that also reset connection variables back to
their default states so that connections coming from the pool behave as if they were new connections. This patch uses db specific commands to do
this for
 * mssql - sp_reset_connection
 * postgres - "RESET ALL"

diff --git a/sql/pool.lisp b/sql/pool.lisp
index 7aa3c994b4a0f7a1ea7e45766dcc93457a0009e5..d1c0a230bc43255f8d9b3940286e1545c3e7d50d 100644 (file)
--- a/sql/pool.lisp
+++ b/sql/pool.lisp
@@ -32,15 +32,25 @@
          :initform (make-process-lock "Connection pool"))))
 
 (defun acquire-from-conn-pool (pool)
+  "Try to find a working database connection in the pool or create a new
+one if needed. This performs 1 query against the DB to ensure it's still
+valid. When possible (postgres, mssql) that query will be a reset
+command to put the connection back into its default state."
   (or (with-process-lock ((conn-pool-lock pool) "Acquire from pool")
         (when (plusp (length (free-connections pool)))
           (let ((pconn (vector-pop (free-connections pool))))
             ;; test if connection still valid.
            ;; (e.g. db reboot -> invalid connection )
            (handler-case
-               (case (database-type pconn)
-                 (:mysql 
+               (case (database-underlying-type pconn)
+                 (:postgresql
+                    (database-execute-command "RESET ALL" pconn))
+                 (:mysql
                     (database-query "SHOW ERRORS LIMIT 1" pconn nil nil))
+                 (:mssql
+                    ;; rpc escape sequence since this can't be called as a normal sp.
+                    ;;http://msdn.microsoft.com/en-us/library/aa198358%28SQL.80%29.aspx
+                    (database-execute-command "{rpc sp_reset_connection}" pconn))
                  (T
                     (database-query "SELECT 1;"  pconn '(integer) nil)))
              (sql-database-error (e)