Stanislasdrg Stanislasdrg - 6 months ago 67
Node.js Question

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

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.

Answer

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