Pg-promise: Code example for using sequence for a large amount of UPDATE calls?

[AriaFallah]

I was looking through the learn by example section to figure out how to properly do a lot of update calls...around 20k updates.

In a section called massive you have

function source(index) {   
    // create and return a promise object dynamically,
    // based on the index passed;
    if (index < 100000) {
        return this.any('insert into test(name) values($1)', 'name-' + index);
    }
    // returning or resolving with undefined ends the sequence;
    // throwing an error will result in a reject;
}

db.tx(function (t) {
    // t = this;
    return this.sequence(source);
})
    .then(function (data) {
        // success;
    })
    .catch(function (error) {
        // error;
    });

But source is only a single query isn't it?

Lets say I have some data in the format

[{ colToUpdate: 'newVal' }, { colToUpdate: 'newVal' }]

Would I be doing something like sequence inside a for loop? I'm not sure I understand how to use it properly.

Also if you have better alternatives I'm open to using them.

[vitaly-t]

As per the sequence API, source is to generate and return new queries, till you got all of them.

But source is only a single query isn't it?

Not, it is not, see the API for the method.

[AriaFallah]

So would I be calling sequence multiple times? Or would I be returning an array of queries from source?

[vitaly-t]

So would I be calling sequence multiple times? Or would I be returning an array of queries from source?

Neither. You call sequence only once, as shown in the example. And source doesn't return an array of queries, it is to return one query at a time, given the index parameter.

sequence calls source repeatably, till one of the calls returns nothing (undefined), which then means the end of the sequence, as per the example.

[AriaFallah]

Okay so this is where my confusion comes in.

If source only returns a single query, and you only call sequence once, how does sequence execute multiple queries?

[vitaly-t]

I explained this in my previous post.

sequence calls source repeatably, till one of the calls returns nothing (undefined), which then means the end of the sequence, as per the example.

[AriaFallah]

So given what I know now, my call would look something like this

const data = [{ id: 1, colToUpdate: 'newVal' }, { id:2, colToUpdate: 'newVal' }];
function source(index) {
    const row = data[index];
    // create and return a promise object dynamically,
    // based on the index passed;
    if (index < 100000) {
        return this.any('UPDATE table SET colToUpdate=${colToUpdate} WHERE id=${id}', row);
    }
    // returning or resolving with undefined ends the sequence;
    // throwing an error will result in a reject;
}

db.tx(function (t) {
    // t = this;
    return this.sequence(source);
})
    .then(function (data) {
        // success;
    })
    .catch(function (error) {
        // error;
    });

I have two questions about this.

1. When does the this.any update call return undefined?
2. Eventually, in this case, when index = 2, it'll be out of bounds, and it'll return an error right? Should I opt to use { limit: data.length } to avoid this?

[vitaly-t]

Just change source to this:

function source(index) {
    if (index < data.length) {
        return this.any('UPDATE table SET colToUpdate = ${colToUpdate} WHERE id = ${id}', data[index]);
    }
}

and you are done.

[vitaly-t]

P.S. Setting { limit: data.length } and then skipping if (index < data.length) would produce the same result ;)

[vitaly-t]

Just for completeness here, and to show how flexible method sequence is, for future references.

Its optional parameter dest can also be used for resolving promises/queries:

const data = [{id: 1, colToUpdate: 'newVal'}, {id: 2, colToUpdate: 'newVal'}];

function dest(_, row) {
    return this.none('UPDATE table SET colToUpdate = ${colToUpdate} WHERE id = ${id}', row);
}

db.tx(t=>t.sequence(i=>data[i], dest, data.length))
    .then(data=> {
        // success;
    })
    .catch(error=> {
        // error;
    });

[ferlopezm94]

Thanks for the examples! Just one doubt: inside source, index will always start at 0? I couldn't find this information in the API, maybe it's obvious but I just want to be sure about it.

[vitaly-t]

index will always start at 0?

It does. Documentation: http://vitaly-t.github.io/spex/global.html#sequence

But first, see this: http://stackoverflow.com/questions/39119922/postgresql-multi-row-updates-in-node-js

It is a lot more efficient that way :wink:

[ferlopezm94]

Hi @vitaly-t, what would be the difference between using the dest function as you showed above and using source? Also in what situation could we use both source and dest? I'm a little confused since I can't see the different usage with your example.

Also, is it ok to use a sequence inside a batch? Or do I have to use it separately?

[vitaly-t]

Example update for version 6.9.0 and later of pg-promise:

const data = [{id: 1, colToUpdate: 'newVal'}, {id: 2, colToUpdate: 'newVal'}];

function dest(_, row) {
    return this.none('UPDATE table SET colToUpdate = ${colToUpdate} WHERE id = ${id}', row);
}

db.tx(t=>t.sequence(i=>data[i], {dest: dest, limit: data.length}))
    .then(data=> {
        // success;
    })
    .catch(error=> {
        // error;
    });

i.e. parameters for sequence changed.