Pg-promise performance boost : ON CONFLICT

Question

I'm trying to follow the performance pattern recommended by the pg-promise library author here.

Basically Vitaly recommends to do so with inserts :

var users = [['John', 23], ['Mike', 30], ['David', 18]];

// We can use Inserts as an inline function also:

db.none('INSERT INTO Users(name, age) VALUES $1', Inserts('$1, $2', users))
    .then(data=> {
        // OK, all records have been inserted
    })
    .catch(error=> {
        // Error, no records inserted
    });

Using the following helper function :

function Inserts(template, data) {
    if (!(this instanceof Inserts)) {
        return new Inserts(template, data);
    }
    this._rawDBType = true;
    this.formatDBType = function () {
        return data.map(d=>'(' + pgp.as.format(template, d) + ')').join(',');
    };
}

My question is, how would you go when there is a contraint on the insert ? My code :

db.tx(function (t) {
    return t.any("SELECT... ",[params])
        .then(function (data) {

          var requestParameters = [];

          async.each(data,function(entry){
            requestParameters.push(entry.application_id,entry.country_id,collectionId)
          });

          db.none(
            " INSERT INTO application_average_ranking (application_id,country_id,collection_id) VALUES ($1)" +
            " ON CONFLICT ON CONSTRAINT constraint_name" +
            " DO UPDATE SET country_id=$2,collection_id=$3",
            [Inserts('$1, $2, $3',requestParameters),entry.country_id,collectionId])

            .then(data=> {
              console.log('success');
            })
            .catch(error=> {
              console.log('insert error');
            });

        });

});

Obviously, I can't access the parameters because I'm out of the async loop.

I also tried to do something like this :

        db.none(
        " INSERT INTO application_average_ranking (application_id,country_id,collection_id) VALUES ($1)" +
        " ON CONFLICT ON CONSTRAINT constraint_name" +
        " DO UPDATE SET (application_id,country_id,collection_id) = $1",
        Inserts('$1, $2, $3',requestParameters));

But of course, it doesn't respect postgresql's standard.

Is there a way to achieve this ?

Thanks !

Answer 1

I wrote that example for Performance Boost article to generate simple-form multi-inserts, nothing more, which was sufficient for the article.

What you are trying to do here is a bit more complex, and obviously function Inserts(template, data) doesn't have that kind of logic.

I can't tell you from the top of my head what would it take to change it to allow for your kind of scenario, but it can get quite complicated, and perhaps not even worth doing at all.

Luckily, you don't have to. I have been asked time and again for certain formatting helpers, which I released just recently - helpers namespace. You need to update to the very latest version of the library (currently 4.1.9) to be able to use it the way you need.

Change your example to the following:

var h = pgp.helpers;
var cs = new h.ColumnSet(['?application_id', 'country_id', 'collection_id'],
    {table: 'application_average_ranking'});

db.tx(t => {
    return t.any("SELECT... ", [params])
        .then(function (data) {

            var insertData = data.map(d => {
                return {
                    application_id: d.application_id,
                    country_id: d.country_id,
                    collection_id: collectionId
                };
            });

            var updateData = {
                country_id: entry.country_id,
                collection_id: collectionId
            };

            var query = h.insert(insertData, cs) +
                " ON CONFLICT ON CONSTRAINT constraint_name DO UPDATE SET " +
                h.sets(updateData, cs);

            db.none(query)
                .then(data => {
                    console.log('success');
                })
                .catch(error => {
                    console.log('insert error');
                });
        });
});

and that should do it.

See also: ColumnSet.