[nominatim-web] Don't use psycopg2's connection pooling

[Bartłomiej Piotrowski <bpiotrowski src gnome org>]

commit 4236d537697e143befe04c087a0fb543d1e96f24
Author: Bartłomiej Piotrowski <bpiotrowski gnome org>
Date:   Fri Apr 2 14:43:00 2021 +0200

    Don't use psycopg2's connection pooling

 app/main.py | 88 ++++++++++++++++++++++++++++---------------------------------
 1 file changed, 41 insertions(+), 47 deletions(-)
---
diff --git a/app/main.py b/app/main.py
index 473cad5..e5f37e9 100644
--- a/app/main.py
+++ b/app/main.py
@@ -20,23 +20,22 @@ settings = Settings()
 redis_conn = redis.Redis(
     host=settings.redis_host, port=settings.redis_port, decode_responses=True
 )
-postgres = psycopg2.pool.SimpleConnectionPool(1, 10, settings.postgres_url)
+postgres = psycopg2.connect(settings.postgres_url)
 
 app = FastAPI()
 
 
 @app.on_event("startup")
 def initialize():
-    with postgres.getconn() as conn:
-        with conn.cursor() as cur:
-            cur.execute(
-                "CREATE TABLE IF NOT EXISTS search ( key VARCHAR(255) UNIQUE NOT NULL, created_at TIMESTAMP 
NOT NULL, value text);"
-            )
+    with postgres.cursor() as cur:
+        cur.execute(
+            "CREATE TABLE IF NOT EXISTS search ( key VARCHAR(255) UNIQUE NOT NULL, created_at TIMESTAMP NOT 
NULL, value text);"
+        )
 
-            cur.execute(
-                "CREATE TABLE IF NOT EXISTS reverse ( key VARCHAR(255) UNIQUE NOT NULL, created_at TIMESTAMP 
NOT NULL, value text);"
-            )
-        conn.commit()
+        cur.execute(
+            "CREATE TABLE IF NOT EXISTS reverse ( key VARCHAR(255) UNIQUE NOT NULL, created_at TIMESTAMP NOT 
NULL, value text);"
+        )
+        postgres.commit()
 
 
 @app.get("/status")
@@ -65,25 +64,23 @@ def search(request: Request):
     if resp := redis_conn.get(key):
         return Response(content=resp, media_type="application/json")
 
-    with postgres.getconn() as conn:
-        with conn.cursor() as cur:
-            cur.execute("SELECT * FROM search WHERE key = %s", (key,))
-            if resp := cur.fetchone():
-                redis_conn.setex(key, 600, resp[2])
-                return Response(content=resp[2], media_type="application/json")
+    with postgres.cursor() as cur:
+        cur.execute("SELECT * FROM search WHERE key = %s", (key,))
+        if resp := cur.fetchone():
+            redis_conn.setex(key, 600, resp[2])
+            return Response(content=resp[2], media_type="application/json")
 
     r = requests.get(f"{settings.api_url}/search", params=request.query_params)
     redis_conn.setex(key, 600, r.text)
 
     if r.status_code == 200:
-        with postgres.getconn() as conn:
-            with conn.cursor() as cur:
-                now = datetime.datetime.now()
-                cur.execute(
-                    "INSERT INTO search(key,created_at,value) VALUES (%s, %s, %s)",
-                    (key, now, r.text),
-                )
-            conn.commit()
+        with postgres.cursor() as cur:
+            now = datetime.datetime.now()
+            cur.execute(
+                "INSERT INTO search(key,created_at,value) VALUES (%s, %s, %s)",
+                (key, now, r.text),
+            )
+            postgres.commit()
 
     return Response(
         content=r.text, media_type="application/json", status_code=r.status_code
@@ -100,12 +97,11 @@ def reverse(request: Request):
     if resp := redis_conn.get(key):
         return Response(content=resp, media_type="application/json")
 
-    with postgres.getconn() as conn:
-        with conn.cursor() as cur:
-            cur.execute("SELECT * FROM reverse WHERE key = %s", (key,))
-            if resp := cur.fetchone():
-                redis_conn.setex(key, 600, resp[2])
-                return Response(content=resp[2], media_type="application/json")
+    with postgres.cursor() as cur:
+        cur.execute("SELECT * FROM reverse WHERE key = %s", (key,))
+        if resp := cur.fetchone():
+            redis_conn.setex(key, 600, resp[2])
+            return Response(content=resp[2], media_type="application/json")
 
     query_params = {
         "lat": lat,
@@ -119,14 +115,13 @@ def reverse(request: Request):
     redis_conn.setex(key, 600, r.text)
 
     if r.status_code == 200:
-        with postgres.getconn() as conn:
-            with conn.cursor() as cur:
-                now = datetime.datetime.now()
-                cur.execute(
-                    "INSERT INTO reverse(key,created_at,value) VALUES (%s, %s, %s)",
-                    (key, now, r.text),
-                )
-            conn.commit()
+        with postgres.cursor() as cur:
+            now = datetime.datetime.now()
+            cur.execute(
+                "INSERT INTO reverse(key,created_at,value) VALUES (%s, %s, %s)",
+                (key, now, r.text),
+            )
+            postgres.commit()
 
     return Response(
         content=r.text, media_type="application/json", status_code=r.status_code
@@ -135,14 +130,13 @@ def reverse(request: Request):
 
 @app.get("/expire")
 def expire():
-    limit = datetime.datetime.now() - datetime.timedelta(days=30)
-
-    with postgres.getconn() as conn:
-        with conn.cursor() as cur:
-            cur.execute("DELETE FROM search WHERE created_at < %s", (limit,))
-            search = cur.rowcount
-            cur.execute("DELETE FROM reverse WHERE created_at < %s", (limit,))
-            reverse = cur.rowcount
-        conn.commit()
+    limit = datetime.datetime.now() - datetime.timedelta(seconds=30)
+
+    with postgres.cursor() as cur:
+        cur.execute("DELETE FROM search WHERE created_at < %s", (limit,))
+        search = cur.rowcount
+        cur.execute("DELETE FROM reverse WHERE created_at < %s", (limit,))
+        reverse = cur.rowcount
+        postgres.commit()
 
     return {"search": search, "reverse": reverse}