node-postgres : query not executing in sequence

[pawarvijay]

need some assistance

i have kept insert & update code in 2 different files and based on condition always insert should execute first and then update.but somehow update executes first then insert

test.js : simplified code

pg = require('pg');
var uuid = require('node-uuid').v4;
var id = uuid().toString();
var conString = 'postgres://postgres:pass@127.0.0.1:5432/testdb';

// ------INSERT
pg.connect(conString, function(err, client, done) {

    console.log('Executing Insert query');

    client.query('insert into testdb (id,data,iscancelled) values ($1,$2,$3)',[id,{name:'tom'},'no'],  function(err, result) {

        done();

        if(err) { return console.error('error running query', err);  }

        console.log('finished executing Insert query');

    });
});

// ------UPDATE
pg.connect(conString, function(err, client, done) {

    console.log('Executing update query');

    client.query("update testdb set iscancelled = 'yes' where id = $1",[id],  function(err, result) {

        done();

        if(err) { return console.error('error running query', err);  }

        console.log('finished executing Update query');

    });
});

Output

tom@tom:~$node test.js
Executing Insert query
Executing update query
finished executing Update query //WHY UPDATE FINISHES FIRST
finished executing Insert query

Note

this problem can be easily solved by using async.But my insert code and update code are in different files and depending on some situation update code might execute.so don't want to use async

Problem

Even though Insert query goes to execute first why does update finishes first in output

am i missing any thing ..?

[pawarvijay]

I tried this

i tried to remove json datatype ,i thought json data parsing is taking time.

But its not

it gives same above unsequenced output

[NumminorihSF]

Your update and insert queries are run parallel. So, if PostgreSQL make update faster, update will return result before insert. If you want start executing update query only after finishing update - you should set connection pool capacity to 1.

pg.defaults.poolSize = 1

but you should do this before any pg.connect()

[pawarvijay]

@NumminorihSF thanks for reply
yes this solved my problem

but but but

i just tested as one user

• setting client pool capacity to 1
• this will block my other users from using postgresl as my one client is already busy in serving one
• so there will be notable delay i think.
• as no new client will be created for other users,and they will have to wait till one client finishes serving

this will be bad for scaling app

According to pooling in postgresql

The connect method retrieves a Client from the client pool, or if all pooled clients are busy and the pool is not full, the connect method will create a new client passing its first argument directly to the Client constructor

[NumminorihSF]

In other way, you can get client from pool in function, that execute theese both, and pass pg.client into them

[pawarvijay]

@NumminorihSF this i what i landedup with

1. below i have not use client from pg.connect but created a new pg.Client for each query.
   Do i have to use client from pg.connect into pg.Client while pg.Client gets created....?_
2. do i still have to use pg.defaults.poolSize = 1;

do you mean like this

var pg = require('pg');
pg.defaults.poolSize = 1;

var uuid = require('node-uuid').v4;
var id = uuid().toString();
var conString = 'postgres://postgres:passpass@127.0.0.1:5432/cqrs';

pg.connect(conString, function(err, client, done) {

    var client1 = new pg.Client(conString);

    client1.connect(function(err) {
        if(err) {
            return console.error('could not connect to postgres', err);
        }
        console.log('Executing Insert query');
        client.query('insert into testdb (id,data,iscancelled) values ($1,$2,$3)',[id,'hello','no'],  function(err, result) {
            if(err) {
                return console.error('error running query', err);
            }
            console.log('finished executing Insert query');

            client1.end();

            done();
        });
    });
});



pg.connect(conString, function(err, client, done) {

    var client2 = new pg.Client(conString);

    client2.connect(function(err) {

        console.log('Executing Insert query');

        client.query("update testdb set iscancelled = 'yes' where id = $1",[id],  function(err, result) {

            console.log('finished executing Update query');
            client2.end();
            done();
        });
    });

});

OUTPUT

with pg.defaults.poolSize = 1;

Executing Insert query
finished executing Insert query
Executing Insert query
finished executing Update query

without pg.defaults.poolSize = 1;

Executing Insert query
Executing Insert query
finished executing Update query
finished executing Insert query

am i missing any thing or its the other way .?

[NumminorihSF]

I mean something like this:

insert-file:

module.exports.insert = function(client, callback){
  client.query('insert ...', [...], callback);
};

update-file:

module.exports.update= function(client, callback){
  client.query('update...', [...], callback);
};

master-file:

pg.connect(conString, function(err, cli, done){
  require('insert-file').insert(cli, function(err, result){
    //some-doing or error-handle
    require('update-file').update(cli, function(err, result){
      //some another work
      done(err);
    });
  });
});

So you can use any value of pg.defaults.poolSize

[pawarvijay]

@NumminorihSF 👍