refactor(web/panettone): Use postmodern connection pools r/3814

Instead of managing Postgres connections on our own, use the
`with-connection` postmodern function with pooling enabled as a route
decorator.

This should resolve at least some of the issues from b/113 with
leaking connections, and an unreported issue with connections being
reused while transactions are in progress.

Change-Id: I1ed68667a3240900de1ae69df37d2d3018caf204
Reviewed-on: https://cl.tvl.fyi/c/depot/+/5198
Tested-by: BuildkiteCI
Reviewed-by: eta <tvl@eta.st>
Autosubmit: tazjin <tazjin@tvl.su>

1 files changed, 19 insertions, 25 deletions

diff --git a/web/panettone/src/model.lisp b/web/panettone/src/model.lisp
index a3b75380c825..c54a0ae474bf 100644
--- a/web/panettone/src/model.lisp
+++ b/web/panettone/src/model.lisp
@@ -1,28 +1,24 @@
 (in-package :panettone.model)
 (declaim (optimize (safety 3)))
 
-(defun connect-postgres (&key
-                           (host (or (uiop:getenvp "PGHOST") "localhost"))
-                           (user (or (uiop:getenvp "PGUSER") "panettone"))
-                           (password (or (uiop:getenvp "PGPASSWORD") "password"))
-                           (database (or (uiop:getenvp "PGDATABASE") "panettone"))
-                           (port (or (integer-env "PGPORT") 5432)))
-  "Initialize the global postgresql connection for Panettone"
-  (postmodern:connect-toplevel database user password host :port port))
-
-(defun make-thread
-    (function &rest args)
-  "Make a new thread as per `BORDEAUX-THREADS:MAKE-THREAD' but with its own, new
-database connection."
-  (let ((spec `(,(or (uiop:getenvp "PGDATABASE") "panettone")
-                ,(or (uiop:getenvp "PGUSER") "panettone")
-                ,(or (uiop:getenvp "PGPASSWORD") "password")
-                ,(or (uiop:getenvp "PGHOST") "localhost")
-                :port ,(or (integer-env "PGPORT") 5432))))
-    (apply #'bt:make-thread
-           (lambda ()
-             (postmodern:call-with-connection spec function))
-           args)))
+(defvar *pg-spec* nil
+  "Connection spec for use with the with-connection macro. Needs to be
+initialised at launch time.")
+
+(defun make-pg-spec ()
+  "Construct the Postgres connection spec from the environment."
+  (list (or (uiop:getenvp "PGDATABASE") "panettone")
+        (or (uiop:getenvp "PGUSER") "panettone")
+        (or (uiop:getenvp "PGPASSWORD") "password")
+        (or (uiop:getenvp "PGHOST") "localhost")
+
+        :port (or (integer-env "PGPORT") 5432)
+        :application-name "panettone"
+        :pooled-p t))
+
+(defun prepare-db-connections ()
+  "Initialises the connection spec used for all Postgres connections."
+  (setq *pg-spec* (make-pg-spec)))
 
 ;;;
 ;;; Schema
@@ -268,7 +264,7 @@ type `ISSUE-NOT-FOUND'."
     (with-column-writers ('num_comments 'num-comments)
       (query-dao 'issue query status))))
 
-(defmethod num-comments ((issue-id integer))
+(defmethod count-comments ((issue-id integer))
   "Return the number of comments for the given ISSUE-ID."
   (query
    (:select (:count '*)
@@ -306,7 +302,6 @@ NOTE: This makes a database query, so be wary of N+1 queries"
      :where (:= 'issue-id issue-id))
     (:asc 'created-at))))
 
-
 ;;;
 ;;; Writing
 ;;;
@@ -414,7 +409,6 @@ explicitly subscribing to / unsubscribing from individual issues."
 
 
 (comment
- (connect-postgres)
  (ddl/init)
  (make-instance 'issue :subject "test")
  (create-issue :subject "test"