Search path occasionally reverts to public for PG connection with custom schema defined

[shaheedazaad]

Package version

20.1.0

Describe the bug

I posted a discussion topic similar to this on the core repo, but I understand the problem a little better now.

At the moment, I am accessing my API from a single subdomain for testing. Each subdomain will have its own connection, but for now, I'm just using one connection for all requests.

I've set a custom search path representing the schema for this subdomain.

{
    client: 'pg',
    connection: {
      host:  env.get('PG_HOST'),
      port: env.get('PG_PORT'),
      user: env.get('PG_USER'),
      password: env.get('PG_PASSWORD'),
      database: env.get('PG_DB_NAME'),
      ssl: true,
    },
    searchPath: ['tenant_' + tenantInfo.tenantName],
  }

This works most of the time. Prettyprinting the queries shows that all queries go through this connection. However, the search path seems to randomly revert to public, resulting in a relation does not exist error.

This change seems to happen in-between queries, and is not related to any particular function in my api.

Checking the schema on this connection, using the below code, shows that it indeed does revert to public.

const schema = await db.connection('tenant_test').rawQuery('SELECT current_schema()')

Reproduction repo

No response

[shaheedazaad]

Did some more testing; this seems related to my using digitalocean's connection pooler rather than lucid/knex's. I guess I'm occasionally getting connections without the search path set when pooling this way.

[RomainLanz]

Hey @shaheedazaad! 👋🏻

How are you defining your searchPath? If you are mutating the global configuration, it is normal that you may encounter race conditioning between your requests.

Keep note that Node.js is processing multiple requests at the same time.

[shaheedazaad]

Hi @RomainLanz !

I'm defining separate connections for each search path, these are registered in a middleware based on the subdomain:
db.manager.add('tenant_' + tenantName, config)

I'm using the HttpContext to store the connection name and modifying the query client as suggested in the docs. In other words, I specify the connection for each query, which I think avoids the race condition problem - at least this is what my testing has shown so far.

The db emitter shows that the correct registered connection is always used, but the schema is reset to public. I don't actually have any other connections with the schema unset, or set to public.