Error: "Executor doesn't accept tasks"

[jimmyff]

Hi, I'm seeing periodic errors in my logs: Executor doesn't accept tasks.. Looking through the code it looks like this happens when the database is in closing state.

I'm wondering if I', using pgpool incorrectly which is why I'm seeing this error. I'm using this package in a number of cloud functions (not on production yet so just test traffic). At the start of each cloud function a new instance of PgPool is created, the body of the cloud function happens in a try/catch block and then .close() is called in the finally block on the pgpool instance.

Does it sound like I've implemented this package correctly and if so is there any reason I'd be running in to this error?

Thanks

[isoos]

@jimmyff: PgPool is designed to be used in a long-lived server processes. There is always a chance that it has a bug, so could you please share the exception stack traces?

If you always create -> use -> close for a short-lived ssessions, it won't provide too much benefit over using only a single connection. However, if there are no leftover queries, it shouldn't throw on/after closing it.

[jimmyff]

Apologies for the delay getting you this:

2021-10-26 17:00:59.386 BST

Exception: Executor doesn't accept tasks. 

#0      _Executor.scheduleTask (package:executor/src/executor_impl.dart:51)
#1      PgPool._withConnection (package:postgres_pool/postgres_pool.dart:393)
#2      PgPool.run.<anonymous closure> (package:postgres_pool/postgres_pool.dart:322)
#3      PgPool.run.<anonymous closure> (package:postgres_pool/postgres_pool.dart:321)
#4      RetryOptions.retry (package:retry/retry.dart:131)
#5      PgPool.run (package:postgres_pool/postgres_pool.dart:320)
#6      PgPool.mappedResultsQuery (package:postgres_pool/postgres_pool.dart:618)
#7      function (package:profile_updated/functions.dart:50)
<asynchronous suspension>
#8      _VoidJsonWithContextFunctionTarget.handler (package:functions_framework/src/targets/json_targets.dart:150)
<asynchronous suspension>
#9      cloudLoggingMiddleware.hostedLoggingMiddleware.<anonymous closure>.<anonymous closure> (package:functions_framework/src/logging.dart:171)
<asynchronous suspension>

My code is structured to close the connections like so:

  final db = await pgPoolDi(); // returns an instance of [PgPool]

  try {
    // ... do stuff here

  } catch (e, s) {
    context.logger.error(e);
    context.logger.error(s);
    rethrow;
  } finally {
    await db.close();
  }
}

I know that using pgpool on short lived processes isn't what it's designed for. I had this same issue when just using package:postgres Not sure if you remember but this was the original issue:
stablekernel/postgresql-dart#104

I'm thinking this could be just the original issue again?

I don't see why there should be any issue with or without pgpool, i'm closing each connection in that finally block!

Don't suppose you have any thoughts on it? Thanks

[isoos]

Looks like the PgPool was closed, but after that PgPool.mappedResultsQuery was called again. Could it be that some async tasks try to run something on it, even after the finally block runs?

[jimmyff]

Hmm, I only call mappedResultsQuery once and it happens in that try block. Also this seems to get the cloud run instance in to a state whereby any subsequent requests to it will immediately fail with the same error.

This makes me think that somehow the connections aren't closing and PostgreSQL instance (on cloud sql) is throwing the error and it's manifesting the exception in mappedResultsQuery as this is the first query executed? I had the same issue with package: postgres which makes me suspect that this could be an issue with that rather than anything to do with pgpool.

[isoos]

Does the same happen if you connect to a local postgres instance?

[jimmyff]

I don't have a local instance to test on, I could set one up though?

I can still connect to the postgres host using something like pgadmin and execute queries even whilst it's throwing this error whenever pubsub attempts to call the cloudrun function.

[isoos]

Yeah, a local docker instance with postgres is always good :)
Also, if you do it with https://pub.dev/documentation/docker_process/latest/containers_postgres/startPostgres.html we can build an easily accessible unit test around it.
(or any other docker package would work)

[jimmyff]

Hey @isoos I did have a play around with using a postgres docker container local but it was proving pretty difficult to get the extensions setup correctly! I need to come back to this at some point...

I got to the bottom of the issue by using postgres package rather than pgpool which then gave the error PostgreSQLSeverity.error 0A000: source for a multiple-column UPDATE item must be a sub-SELECT or ROW() expression which was then a trivial fix. Not sure why the exception wasn't bubbling up correctly and manifesting itself as Executor doesn't accept tasks?

Happy to close this, thanks for your help!

[isoos]

Not sure why the exception wasn't bubbling up correctly and manifesting itself as Executor doesn't accept tasks?

That's a good question, I've seen a similar one before, and I'd love to debug it a bit more. Do you happen to have the docker + exception setup in a small reproducible example project that you can share?

[isoos]

package:postgres got a fix for better surfacing exceptions: isoos/postgresql-dart#15
This may also help here.