I've written a 2nd gen Google Cloud Function in Python that pulls data from an external API and should write it to a PostgreSQL database hosted in Google Cloud SQL. When I try to run the function, it times out without changing anything in the database. I added some print() statements to see where it was failing. The sqlalchemy "engine.create()" seems to go through without issue, but it seems to not get past the first session.execute(stmt). Running the same function locally (while connecting to the cloud db) using function-framework works without issue, however. I'm using the Python cloud.sql.connector library (with the pg8000 driver) to connect to the database.
I'm honestly at a loss to explain why this won't work, or what the issue could even be. I've tried using a service account for the Function that has the global Owner role; the database credentials work when I'm running it locally, as well as in another cloud function; I've tried running the function with curl -H "Authorization: Bearer $(gcloud auth print-identity-token)" https://FUNCTION_URL from the cloud shell, as well as with gcloud function call from my local machine, same result. I'd expect the function to either complete or crash, but instead it just sits there until it times out. The only differences that I perceive between this and another function that connects to the same instance is that this one is a 2nd gen Cloud Function and uses the connector library instead of unix sockets to connect to the database. Same project, same region, same db instance, same GCP service account, same SQL login.
Relevant (I think) bits of code; bit where engine is created:
connector = Connector()
def getconn() -> pg8000.dbapi.Connection:
conn: pg8000.dbapi.Connection = connector.connect(
instance_connection_name,
"pg8000",
user=username,
password=password,
db=database,
ip_type=IPTypes.PUBLIC,
)
return conn
print("creating db connection...")
# create a connection to the database
engine = create_engine("postgresql+pg8000://",
creator=getconn,
echo=False)
Session = sessionmaker(bind=engine)
session = Session()
print("db con ok")
The method that won't complete (never prints "ok"):
def wipe_table(name):
print(f"Wiping staging table {name}...")
stmt = text(f"TRUNCATE {name};")
session.execute(stmt)
print("ok")
This is most likely same issue as Cloud Function cannot connect to Cloud SQL with "upstream request timeout" (I recommend updating post with full Cloud Function code for allowing reproduction of error).
TLDR; the Cloud SQL Python Connector object or SQLAlchemy connection pool should be lazy initialized when used with Cloud Functions because it runs background tasks.
It turns out global variables that run background tasks can cause issues when run out side of the Cloud Function request context (because Cloud Functions only allocates compute during a request). So Cloud Functions recommends lazy initializing this type of global variable so that the variable is initialized within the request context.
Example of lazy initializing connection pool is as follows:
import functions_framework
import sqlalchemy
from google.cloud.sql.connector import Connector, IPTypes
import pg8000
def connect_to_instance() -> sqlalchemy.engine.base.Engine:
connector = Connector()
def getconn() -> pg8000.dbapi.Connection:
return connector.connect(
"...", # the PostgreSQL's instance connection name here
"pg8000",
user = "xyz",
password = 'supersecret',
db = "db_name",
ip_type = IPTypes.PUBLIC
)
return sqlalchemy.create_engine(
"postgresql+pg8000://",
creator = getconn,
)
# lazy initialization of global db
db = None
@functions_framework.http
def hello_http(request):
# lazy init within request context
global db
if not db:
db = connect_to_instance()
with db.connect() as conn:
# ... run queries
More details on this same issue can be found here: https://github.com/GoogleCloudPlatform/cloud-sql-python-connector/issues/830