SQLAlchemy timed out error in plugin

[howawong]

Writing a chat bot with the following python code,

from skygear.transmitter.encoding import deserialize_record
from skygear.action import push_users
from skygear.container import SkygearContainer
from skygear.options import options as skyoptions
from skygear.utils.context import current_user_id
from chat.decorators import *
from chat.message import Message
from skygear.models import Record, RecordID
from skygear.options import options as skyoptions
from skygear.container import SkygearContainer
from chat.database import Database
import uuid

bot_owner_id = "570fe85a-0cbd-40dc-b8e5-4d1d98fc4f8d"

class ChatBotMessage(Message):
    @classmethod
    def _get_database(cls):
        container = SkygearContainer(api_key=skyoptions.masterkey,
                                     user_id=bot_owner_id)
        return Database(container, cls.database_id)


@after_message_sent
def after_message_sent_hook(message, conversation, participants):
    message = deserialize_record(message)
    print(bot_owner_id)
    print(message['body'])
    print(message.owner_id)
    if message['body'] is not None and message.owner_id != bot_owner_id:
        print('creating message')
        new_record = Record(RecordID('message', str(uuid.uuid4())), bot_owner_id, None)
        new_message = ChatBotMessage.from_record(new_record)
        new_message['conversation'] = message['conversation']
        new_message['body'] = 'Hello!'
        new_message.save()

However, error sometimes occurs,

2018-02-15 02:25:18,450 ERROR [skygear.transmitter.common:57][Thread-52] Error occurred processing request
Traceback (most recent call last):
  File "/root/.local/lib/python3.6/site-packages/skygear/transmitter/common.py", line 40, in wrapper
    return dict(result=f(self, *args, **kwargs))
  File "/root/.local/lib/python3.6/site-packages/skygear/transmitter/common.py", line 102, in call_func
    return self.hook(obj, param)
  File "/root/.local/lib/python3.6/site-packages/skygear/transmitter/common.py", line 180, in hook
    returned = func(record, original_record, conn)
  File "/root/.local/lib/python3.6/site-packages/skygear/decorators.py", line 85, in hook_func
    func(record, original_record, db)
  File "/root/.local/lib/python3.6/site-packages/chat/message_handlers.py", line 290, in message_after_save_handler
    return handle_message_after_save(record, original_record, conn)
  File "/root/.local/lib/python3.6/site-packages/chat/message_handlers.py", line 165, in handle_message_after_save
    message.notifyParticipants(event_type)
  File "/root/.local/lib/python3.6/site-packages/chat/message.py", line 106, in notifyParticipants
    self)
  File "/root/.local/lib/python3.6/site-packages/chat/pubsub.py", line 27, in _publish_record_event
    'record': serialize_record(record)
  File "/root/.local/lib/python3.6/site-packages/chat/pubsub.py", line 10, in _publish_event
    channel_name = _get_channel_by_user_id(user_id)
  File "/root/.local/lib/python3.6/site-packages/chat/utils.py", line 21, in _get_channel_by_user_id
    with db.conn() as conn:
  File "/usr/lib/python3.6/contextlib.py", line 81, in __enter__
    return next(self.gen)
  File "/root/.local/lib/python3.6/site-packages/skygear/utils/db.py", line 118, in conn
    with _get_engine().begin() as conn:
  File "/root/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 1992, in begin
    conn = self.contextual_connect(close_with_result=close_with_result)
  File "/root/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2123, in contextual_connect
    self._wrap_pool_connect(self.pool.connect, None),
  File "/root/.local/lib/python3.6/site-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
    return fn()
  File "/root/.local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 403, in connect
    return _ConnectionFairy._checkout(self)
  File "/root/.local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 782, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
  File "/root/.local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 532, in checkout
    rec = pool._do_get()
  File "/root/.local/lib/python3.6/site-packages/sqlalchemy/pool.py", line 1179, in _do_get
    (self.size(), self.overflow(), self._timeout), code="3o7r")
sqlalchemy.exc.TimeoutError: QueuePool limit of size 5 overflow 10 reached, connection timed out, timeout 30

Expected Results

No TimeoutError

[howawong]

After the discussion, setting max_overflow=-1 is not desirable which might overload the database. Therefore the alternatives are either

1. closing the conn before calling the hooks (which might be dangerous) in plugin side or
2. making send_action asynchronous.

[rickmak]

Another option will be calling send_action with the destinate lambda/handler is exist within the same plugin runtime. We will call the function directly.