PG承诺的性能提升：在冲突中 [英] Pg-promise performance boost : ON CONFLICT

问题描述

我正在尝试遵循pg-promise库作者推荐的性能模式此处。

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

基本Vitaly建议使用插入内容：

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
    });

使用以下帮助函数：

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));

但是，当然，它不遵守PostgreSQL的标准。

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

有没有办法做到这一点？

Is there a way to achieve this ?

谢谢！

推荐答案

我为 Performance Boost 生成简单形式的多插入的文章，仅此而已，就足够了。

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.

幸运的是，您不必这样做。我一次又一次被问到某些格式化助手，我最近才发布了它们-助手名称空间。您需要更新到该库的最新版本（当前为4.1.9），以便能够按需要使用它。

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');
                });
        });
});

，应该这样做。

另请参见： ColumnSet 。

这篇关于PG承诺的性能提升：在冲突中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！