loopbackio/loopback-connector-postgresql

Remaining connection slots are reserved for non-replication superuser connections (with no traffic)

vikramkh opened this issue · 1 comments

Please fill in the entire template below.

-->

Steps to reproduce

I have a very nested relation. I have code that starts at the parent relation, finds all child relations by using the hasManyRelationGetter and then iterates through all children and finds their subsequent children, through a series of promise.alls. It looks something like below:

parents = Promise.all(parents.map(parent=> { 
let children = parentRepo.children(parent.id).find(); 
children = await Promise.all(children.map(child => .....)
return parent
}

It's about seven layers deep. When I run it, it creates multiple connections with the postgres database for some reason (just one API call). Sometimes, just from running this one function alone (only one person making this API call), i get the error:

FATAL: remaining connection slots are reserved for non-replication superuser connections.

How can I stop this from happening. I'm in pre-prod, so theres hardly any traffic, but this one function creates so many connections. I have functions like this as well that I haven't tested, and I am worried they are all wrong. Is finding nested relations like this wrong?
My "bug report" is more of a concern that I am doing something wrong, as no one else seems to have this problem. Additionally, I am running on DigitalOcean which allows a maximum of 22 connections, (and at rest, my database only has 10 active connections) but one person making one API call shouldn't be creating so many connections. Am I doing something wrong? My database configuration looks like the following:
{
"name": "pgdb",
"connector": "postgresql",
"host": "DB_HOST",
"port": "DB_PORT",
"user": "DB_USER",
"password": "DB_PASSWORD",
"database": "pgdb",
"schema": "lbstarter"
}

My exact code is:

export class DailyCatalogController {
  constructor(
    @inject(SecurityBindings.USER)
    private readonly user: UserProfile,
    @repository(MenuRepository)
    public menuRepository: MenuRepository,
    @repository(MenuCategoryRepository)
    public menuCategoryRepository: MenuCategoryRepository,
    @repository(MenuItemRepository)
    public menuItemRepository: MenuItemRepository,
    @repository(MenuItemOptionRepository)
    public menuItemOptionRepository: MenuItemOptionRepository,
  ) {
@authenticate(Strategy.BEARER)
  @authorize(['*'])
  @get('/dailycatalogs/{id}/menus/{day}')
  async getMenus(
    @param.path.string('id') catalogId: typeof Catalog.prototype.id,
    @param.path.number('day') day: number,
  ): Promise<Menu[]> {
    const daysOfWeek = [0, 1, 2, 3, 4, 5, 6];
    if (!daysOfWeek.includes(day)) {
      throw new HttpErrors.BadRequest('Specified Day Not Found');
    }
    let catalog = await this.catalogRepository.findById(catalogId);
    if (!catalog) {
      throw new HttpErrors.NotFound('No catalog Found');
    }
    let menus = await this.catalogRepository
      .menus(catalogId)
      .find({where: {active: true}});
    menus = (
      await Promise.all(
        menus.map(async menu => {
          let menuHoursPromise = async (menu: Menu) =>
            await this.menuRepository
              .menuHours(menu.id)
              .find({where: {dayOfWeek: day}});
          let menuCategoriesPromise = async (menu: Menu) =>
            await this.menuRepository
              .menuCategories(menu.id)
              .find({where: {active: true}});
          let [hours, categories] = await Promise.all([
            menuHoursPromise(menu),
            menuCategoriesPromise(menu),
          ]);
          categories = (
            await Promise.all(
              categories.map(async category => {
                let menuItems = await this.menuCategoryRepository
                  .menuItems(category.id)
                  .find();
                menuItems = await Promise.all(
                  menuItems.map(async menuItem => {
                    let options = await this.menuItemRepository
                      .menuItemOptions(menuItem.id)
                      .find();
                    options = await Promise.all(
                      options.map(async option => {
                        option.menuItemOptionValues = await this.menuItemOptionRepository
                          .menuItemOptionValues(option.id)
                          .find();
                        return option;
                      }),
                    );
                    menuItem.menuItemOptions = options;
                    return menuItem;
                  }),
                );
                category.menuItems = menuItems;
                return category;
              }),
            )
          ).filter(category => {
            return category.menuItems && category.menuItems.length > 0;
          });
          menu.menuHours = hours;
          menu.menuCategories = categories;
          return menu;
        }),
      )
    ).filter(menu => {
      return (
        menu.menuCategories &&
        menu.menuHours &&
        menu.menuCategories.length > 0 &&
        menu.menuHours.length > 0
      );
    });
    return menus;
  }
}

darwin x64 10.15.3
├── @loopback/authentication@3.2.4
├── @loopback/authentication-passport@1.0.6
├── @loopback/boot@1.6.0
├── @loopback/build@2.1.0
├── @loopback/context@1.25.0
├── @loopback/core@1.12.0
├── @loopback/openapi-v3@1.10.3
├── @loopback/repository@1.16.0
├── @loopback/rest@1.25.0
├── @loopback/rest-explorer@1.4.6
├── @loopback/service-proxy@1.3.13
├── @loopback/tslint-config@2.1.0
├── loopback-connector@4.10.0
├── loopback-connector-kv-redis@3.0.2
├── loopback-connector-postgresql@3.8.1
├── loopback4-authentication@2.1.3
├── loopback4-authorization@2.3.2
├── loopback4-soft-delete@1.2.0

Realized this isn't a problem with the code, but my database setup