CODEWITHSUNDEEP
google-cloud-platformgoogle-cloud-sqlgoogle-cloud-run
Kirk
Kirk

Reputation: 66

Reconnect to Cloud Postgresql on Cloud Run invocation

I'm able to connect to Cloud Postgresql from Cloud Run using unix sockets and can successfully query the database. However, if I close the connection and try to open a new connection, the whole process hangs and eventually the Cloud Run invocation times out (at 900 seconds as expected).

The details: I'm using the Dart postgres-dart library to connect to the database. I have a wrapper around that library that implements connection pooling. Locally, everything works fine: the application can create a connection, query the database, close the connection, do some work with the data, create a new connection, and write some result back to the database, and close the connection again.

However, on Cloud Run, the application creates the first connection, queries the database, closes the connection, does work on the data, and attempts to create a new connection. This is where everything hangs and eventually the Cloud Run invocation times out.

There are no errors thrown and no other indication, that I can find, that something has gone wrong.

Is it possible that Cloud Run only provides a connection once and it has to stay open and be reused for the entire invocation? Given the async nature of Dart, it would be nice to create connections depending on the work that needs to be done and not block on the database calls. It also seems like a wasted resource if the connection stays open depending on the nature of the workload. I am aware that I could limit the number of Cloud Run instances to limit the database connections, but that doesn't help the resource tie-up if there is a long-ish running process. But maybe I'm missing something in how Cloud Run is expected to work.

Other than that, I'm stumped because it works fine outside of Cloud Run and there is error messaging regarding what's happening. Any help confirming how the Cloud Run connection works or how the problem can be diagnosed on Cloud Run is appreciated.

Edit

I created a simplified Cloud Run endpoint, which exhibits the same issue. The library is one that I wrote, called stanza, on top of postgres-dart.

This example is extremely simple -- it just tries to do the same thing twice but the exact same code doesn't run the second time...

Future<Response> socketTest(Request req) async {

  final keys = Keys();

  final testTable = TestTable.$table;

  final select = SelectQuery(testTable)
    ..selectFields([testTable.textField, testTable.intField])
    ..orderBy(testTable.id)
    ..limit(1);

  final creds = PostgresCredentials(keys.runDbHost, keys.runDbPort, keys.dbDatabase, keys.dbUser, keys.dbPassword);

  final stanza = Stanza.unix(creds, maxConnections: 5);

  final c1 = await stanza.connection();
  final r1 = await c1.execute<TestTable>(select);
  final out1 = r1.first.value;
  print('${out1.textField} | ${out1.intField}'); // works :)

  final c2 = await stanza.connection();
  final r2 = await c2.execute<TestTable>(select); // hangs :(
  final out2 = r2.first.value; // never reaches here
  print('${out2.textField} | ${out2.intField}');

  return req.response.send.code(200);

}

Cloud Run is configured with the Postgresql instance as a connection, the service account has Cloud SQL Client rights, and the necessary environment variables are added. The socket location is /cloudsql/PROJECT:REGION:INSTANCE/.s.PGSQL.5432

My next step is to try this connection with less layers of abstraction and see if I can get it to work. However, this code works fine when run in the same container locally.

Upvotes: 1

Views: 422

Answers (1)

Harif Velarde
Harif Velarde

Reputation: 753

According with the code you shared c1 connection never is closed, so the first connection remains and c2 is unable to work. Is always a good practice close connection when you finish to use it, I think could be something like this.

final c1 = await stanza.connection();
final r1 = await c1.execute<TestTable>(select);
final out1 = r1.first.value;
print('${out1.textField} | ${out1.intField}'); // works :)
c1 = await stanza.connection().close();

I suggest you take a look at this document

Upvotes: 0

Related Questions