Schema resets to public whenever a query fails

[ashking]

Here is how I'm creating a new conn poll

const connPool = new pg.Pool({
  user: 'dbuser',
  host: 'database.server.com',
  database: 'mydb',
  password: 'secretpassword',
  port: 3211,
})

Then set the search_path to a schema

connPool.query(`SET search_path TO airlines`, (err, res) => {
  console.log(err, res)
})

Now when I execute a query that does not fail, I sill get the schema that I had previously set when I run SELECT current_schema();

But when I deliberately run a query that fails (for instance a typo in "SEKECT..." instead of "SELECT...",
the schema is reset to "public" when I run SELECT current_schema();

This definitely looks like a bug and I'm not sure how to get around this. Any help is appreciated. Thanks.

Edit:
BTW, I'm currently using v7.4.1

[sehrope]

You can't use the generic pool.query(...) as there's no guarantee that multiple commands are being sent to the same underlying connection. If you're going to maintain state in a given connection then you'll need to use explicit checkout / checkin of connections.The connection could also have been closed and recycled. It may appear to work in a small example because the same connection will be reused but if you wait for the pool expiration to kick in then the next pool.query(...) will get a connection that doesn't have your search_path set.

Be easiest if you add a local wrapper in your app and use that everywhere. Something like:

export async function query(sql, params) {
    // Get a connection from the pool:
    let client = await pool.connect();
    try {
        // Run our custom SQL to set search_path
        await client.query('SET search_path TO airlines');
        // Run the actual SQL we want to execute
        const result = await client.query(sql, params);
        // This is done in two steps so that we can catch the error and discard the connection
        return result;
    } catch(err) {
        // Release the connection back to the pool
        client.release(err);
        client = null;
        // Throw the original error so the caller gets it
        throw err;
    } finally {
        if (client) {
            // Work completed successfully so release the connection back to the pool for reuse
            client.release();
        }
    }
}

There's also a connect event for the pool that you can use to run custom commands on a connection prior to it being made available to app code: https://node-postgres.com/api/pool#-code-pool-on-39-connect-39-client-client-gt-void-gt-void-code-

That has it's own issues though as it doesn't wait for the command to complete or even be successful. I think there's an open issue regarding fixing that (i.e. making it async and waiting for explicit complete). Until that's done I'd suggest manually managing it yourself.

[charmander]

See brianc/node-pg-pool#97 for more discussion on changing settings for all clients in a pool.