Pg-promise: Initializing connections [question]

[elmigranto]

First of all, thanks for a nice lib :)

I'd like to run some SQL on every created connection before pool considers it as ready. Specifically, I need some SET commands executed before any other query.

Haven't found a way to do that, can you help? Thanks.

[vitaly-t]

function setupFreshConnection(client) {
    var sets = [
        'SET bla-1',
        'SET bla-2',
        'SET bla-3'
    ];
    client.query(sets.join(';'));
}

var pgOptions = {
    connect: (client, dc, isFresh) => {
        if (isFresh) {
            setupFreshConnection(client);
        }
    }
};

var pgp = require('pg-promise')(pgOptions);

Avoid executing multiple queries at that point, for performance sake, that's why we join them into just one query there.

[elmigranto]

Since connect is an event handler, would it be possible for queries to be executed before setupFreshConnection runs? I guess not, since EventEmitter#emit is usually done on the same tick as IO callback? Though I am a bit concerned about suppressing errors part.

[vitaly-t]

would it be possible for queries to be executed before setupFreshConnection runs

Executed against what? The connection just has been allocated there.

Though I am a bit concerned about surpressing errors part.

What we do there is basically a hack, it was never meant to be a feature, the event is provided primarily for logging. That's why you cannot throw any error there. But then again, you should not execute there any queries, apart from simple SET-s, those should not error out.

P.S. You can, of course, provide your own try/catch in there, just in case ;)

[vitaly-t]

@elmigranto I trust the question has been answered then ;)

[elmigranto]

The connection just has been allocated there.

From sources, looks like event is "emitted" after promise is resolved but I tried to actually run it and it seems to work in correct order, though. Not sure how that happens, tried to figure out proper order of stuff but got a bit lost, maybe later :)

And for errors, I could always process.reallyExit(1).

So yeah, thanks for a prompt reply :)

[vitaly-t]

This approach guarantees execution of queries before anything else for fresh connections :wink:

[danihodovic]

This approach guarantees execution of queries before anything else for fresh connections :wink:

That doesn't seem to be the case in the example below:

$ cat pg-promise-example.js
'use strict';

const config = require('./config/config.js');

const options = {
  connect: (client, dc, isFresh) => {
    setTimeout(() => console.log('Initializing now'), 10000);
  }
};
const pgp = require('pg-promise')(options);
const db = pgp(config);

db.any('select 1').then(() => console.log('selected 1'));

Outputs:

$ node pg-promise-example.js
selected 1
Initializing now

The use case I need this for is initializing custom type parsers before performing any queries in my application. Specifically I need to execute the query below before any point queries happen:

  const customPoint = require('./type-parsers/custom-point');
  const oidSQL = `SELECT oid FROM pg_type WHERE typname = 'geometry'`;
  const { rows } = yield client.query(oidSQL);
  pgp.pg.types.setTypeParser(rows[0].oid, customPoint);

I tried putting this code in the connect callback, but as seen in the simple example above, the connect callback isn't blocking, so other queries may execute before it.

Is there any other workaround for executing arbitrary blocking queries upon initialization? I don't want my users to accidentally get invalid point data if they happen to execute a query before the custom types initialize.

[vitaly-t]

@danihodovic You can't use timeout in that case, that's what breaks your sequence.

The use case I need this for is initializing custom type parsers before performing any queries in my application

So what's stopping you? Just initialize them in the beginning, before doing any queries. I don't understand why you are first executing queries, and only then set up type parsers.

[danihodovic]

Since the function is asynchronous without a done() callback the setup logic may take longer than initial queries. If you have a long running query in the connect callback other queries might execute before this is done (as simulated by the timeout).

[elmigranto]

I would think queries run in order on connection without multiplexing. E.g. running this will always return 1, 2, 3 in order.

const result = await Promise.all([
  conn.one('select 1;'), // no waiting
  conn.one('select 2;'), // queue next query right away
  conn.one('select 3;'), // it probably is buffered
]);

However, you can't run initialization that depends on query results. You'd have to wrap the library in some way.

---

Once again: I haven't checked sources, so this is just a guess.

[vitaly-t]

Since the function is asynchronous

Queries execute in the same order in which they get into the IO. The one in onConnect will always be the first one.

@elmigranto you should never use any Promise.all like that ;)

[danihodovic]

Ah, so there is a FIFO mechanism when storing pending queries. Neat!

Thanks @vitaly-t and @elmigranto !

[vitaly-t]

It depends on the execution pattern, really. If you want to be sure your queries in onConnect are executed first always, you should do something like this:

connect: (client, dc, isFresh) => {
   if(isFresh) {
       client.query('SET 1...;SET 2...;ETC.'); // do fail-proof concatenated queries only!!!! to guarantee their all preceed everything else.
   }
}

[danihodovic]

My concrete use case is to set a custom point type parser before any other queries are executed.

const init = Bluebird.coroutine(function * (client) {
  // Oids varies so we need to query it instead of hardcoding it.
  const oidSQL = `SELECT oid FROM pg_type WHERE typname = 'geometry'`;
  const { rows } = yield client.query(oidSQL);
  pgp.pg.types.setTypeParser(rows[0].oid, point);
});

const options = {
  promiseLib: Bluebird,
  connect: (client, dc, isFresh) => {
    if (isFresh) {
      init(client);
    }
  }
};

Is the point parser guaranteed to be set-up before any other query executes?

[vitaly-t]

1) You can NOT use ES6 generators within connect
2) You should call setTypeParser in the beginning, not during queries.

[danihodovic]

1. You can use ES6 generators within connect

Could you provide an example based on the above code?

1. You should call setTypeParser in the beginning, not during queries.

This is not possible in my use case since the oidc number varies between our Postgres instances. So it has to be queried for dynamically and set after the query is executed.

[vitaly-t]

Version 8.4.0 affects the functionality described here.