Fix "conn pool exhausted" issue. (GoogleCloudPlatform#2040)

jhm180 · Ace Nassri · commit 695595a84dd9 · 2019-03-13T10:54:02.000-07:00
Updated code to return connection to pool before returning a value from the "With" block. When I tried running demo code in a gcloud function without using .putconn() to return the connection to the pool, 3 out of every 4 function invocations failed and returned error "connection pool exhausted" while fourth invocation worked successfully. Note that creating a cursor object using a With statement doesn't automatically return a connection to the pool after the With statement executes - psycopg/psycopg2#367
diff --git a/functions/sql/postgres_sample.py b/functions/sql/postgres_sample.py
@@ -61,8 +61,10 @@ def postgres_demo(request):
 
     # Remember to close SQL resources declared while running this function.
     # Keep any declared in global scope (e.g. pg_pool) for later reuse.
-    with pg_pool.getconn().cursor() as cursor:
+    with pg_pool.getconn() as conn:
+        cursor = conn.cursor()
         cursor.execute('SELECT NOW() as now')
         results = cursor.fetchone()
+        pg_pool.putconn(conn)
         return str(results[0])
 # [END functions_sql_postgres]
