Pg-promise performance boost : Multiple inserts with multiple update parameters

Question

I am implementing Vitaly's pg-promise performance patterns, as advised here and there.

Here is my code :

for (var i=0;i<chunkedData.length;i++){

    var insertData = chunkedData[i].map(function (d) {
        return {
            application_id: d.application_id,
            country_id: d.country_id,
            collection_id: collectionId
        };
    });

    // Would need to make a loop here, and thus turning the result into an array
    var updateData = {
        application_id: chunkedData[i][j].application_id,
        country_id: chunkedData[i][j].country_id,
        collection_id: collectionId
    };

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

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

My problem is that insertData is an Array of Objects, and the library's insert helper builds an insert request using that Array, as specified in pg-promise API. Whereas updateData must be a simple Object.

I would like that when :

ON CONFLICT ON CONSTRAINT constraintName DO UPDATE

is triggered, the update values match the corresponding object in 'insertData' array.

How can I work around that problem ?

I've tried to put everything in a loop, but it leaks memory like crazy, and well, I lose the benefits of the pattern...

EDIT :

I want my query to be the equivalent of :

 var inserts = data.map(entry => {
    return t.none(" INSERT INTO application_average_ranking (application_id,country_id,collection_id) VALUES ($1,$2,$3)" +
                 " ON CONFLICT ON CONSTRAINT application_average_ranking_application_id_country_id_colle_key" +
                 " DO UPDATE SET country_id=$2,collection_id=$3",
                 [entry.application_id,entry.country_id,collectionId]
    );
});

In that case when Update is called, the parameters refer to values originally proposed for insertion.

Can you provide a pure SQL example of the resulting query that you want, so we can understand the logic of it better? I don't understand what the final query should even look like, to be able to advise you. — vitaly-t, May 18 '16 at 13:21

vitaly-t · Accepted Answer · 2017-11-15T12:35:15.797

Your task requires a static SQL to implement that kind of logic, by using EXCLUDED as the table reference with rows excluded due to the conflict:

var sqlConflict = " ON CONFLICT ON CONSTRAINT" +
    " application_average_ranking_application_id_country_id_colle_key" +
    " DO UPDATE SET application_id = excluded.application_id" +
    " country_id = excluded.country_id, collection_id = excluded.collection_id";

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

var query = h.insert(insertData, cs) + sqlConflict;

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

UPDATE

And in case your static list of excluded fields is too long and you want to simplify it, you can can always rely on flexibility of the helpers methods:

// or pull them from an object using `Object.keys(obj)`:
var cols = ['application_id', 'country_id', 'collection_id'];

var sets = pgp.helpers.sets({}, cols.map(c=> ({
    name: c, mod: '^', def: 'excluded.' + pgp.as.name(c)
})));

console.log(sets);
//=> "application_id"=excluded."application_id","country_id"=excluded."country_id",
//   "collection_id"=excluded."collection_id"

// or its simple JavaScript equivalent:
var sets = cols.map(c=> {
    var name = pgp.as.name(c);
    return name + '=excluded.' + name;
}).join();

UPDATE

With version 7.3.0 of the library and later, you should use method assignColumns to generate all of the excluded sets, like this:

cs.assignColumns({from: 'EXCLUDED'})
//=> "application_id"=EXCLUDED."application_id","country_id"=EXCLUDED."country_id","collection_id"=EXCLUDED."collection_id"

or, if you want to skip application_id, then you can do:

cs.assignColumns({from: 'EXCLUDED', skip: 'application_id'})
//=> "country_id"=EXCLUDED."country_id","collection_id"=EXCLUDED."collection_id"

See ColumnSet.assignColumns

Thank you for providing the full related code. Cfrei's answer had put me on the good track though. — Standaa - Remember Monica, May 18 '16 at 14:30
Well, I gave you the complete example, he cannot beat that ;) :) — vitaly-t, May 18 '16 at 14:34
@Stanislasdrg added an example of handling long lists of static excluded fields that you can append to your static SQL instead of typing them manually ;) — vitaly-t, May 19 '16 at 17:12

score 1 · Answer 2 · answered May 18 '16 at 11:50

1

Don't use h.sets(). Just write the conflict_action yourself. Handbook says

The SET and WHERE clauses in ON CONFLICT DO UPDATE have access to the existing row using the table's name (or an alias), and to rows proposed for insertion using the special excluded table.

Postgres - Insert

answered May 18 '16 at 11:50

CFrei

3,552
1
15
29

Thank you, it is yet another useful Postgres detail that I discover. My bad for not reading the Insert documentation before. – Standaa - Remember Monica May 18 '16 at 14:23

Pg-promise performance boost : Multiple inserts with multiple update parameters

2 Answers2