do query on new connection

[yawhide]

Hello,

I am trying to figure out how to handle my prepared statement already exists errors.

Is there a way to hook into the "new connection created" method and do something before calling its callback? It would be great if I could create some prepared statements, then run any queries. I noticed there is an option you can pass into pg-pool called onCreated but i dont see it being used anywhere in index.js.

any ideas how to go about solving my problem?

thanks

edit: i just noticed this PR https://github.com/brianc/node-pg-pool/pull/43, should I just fork this lib and use that until the PR is merged?

[bramkoot]

I'll try to add the tests this week so the PR can be merged. For the time being, you can use this monkey-patch (or indeed just fork):

const oldVersion = pg.Pool.prototype._create
pg.Pool.prototype._create = function (cb) {
    oldVersion.apply(this, [(err, client) => {
        if (err) return cb(err)

        // setup client
        client.query('SET search_path TO some_schema OR do any setup that you want')
            .then(() => cb(null, client))
            .catch(cb)
    }])
}

[yawhide]

thanks

[hello-josh]

Pool fires a few events you can use, no monkey patching required.

let cxn = new pg.Pool(config);
cxn.on('connect', client => {
  client.query('SET ROLE "my_role"'); // or whatever
});

[bramkoot]

I like the simplification, but unfortunately for my use case that doesn't work. The first query execution should wait for this setup to be done, and when you just respond to an event there's no way to guarantee the order of the setup query and the actual query.

[hello-josh]

That's true. I have some hackery where I am wrapping the library in a facade and use a promise to ensure my SET ROLE completed before any query executes. It's probably more complicated than the monkey patch solution

[brianc]

not sure if this helps entirely, but clients will queue queries internally and they dispatch them 1 at a time, so if you do this:

client.query('SELECT 1')
client.query('SELECT 2')

the first query will always be dispatched & result or error received before the 2nd query is sent off. Postgres doesn't support multiple in-flight queries at a time on a single connection.

[hello-josh]

@brianc that's a good point. Does the connect event happen asynchronously though where pool.query could execute before the connect event is handled?

[brianc]

I am pretty sure the pool emits connect before the first query is dispatched, but you might need to write a bit of code to verify.

Also if it helps in the next semver major which is coming sometime in the next week or two I'm upgrading the underlying pool and making lifecycle hooks a lot more of a first class citizen.

[hello-josh]

It looked like it does emit connect first, but I wasn't sure with the internals if a nextTick might throw off the ordering. I'm just getting in to node.js and modern javascript so I'm not 100% up to speed yet.

[brianc]

I feel you - yeah if it works that way there's no way the ordering will get thrown off: the queue is manually mantained inside the client in a strictly FIFO way.