first transaction blocking DB (SAVEPOINT and conne

Question

I can shorten my post if necessary

I use a pg-promise transaction to insert a 'device' and all of its part (like system, disk, ...). The transaction works... but only the first time. After it my server can no more interact with the DB (neither insert nor select).

Here is a pg-monitor output with these steps

Thu Jul 11 2019 14:26:57 GMT+0200 (GMT+02:00) : server is listening on 9000
14:27:11 connect(hidden@hidden); useCount: 0
14:27:11 insert into "public"."roles"("name") values('Test') RETURNING *
14:27:11 disconnect(hidden@hidden)
14:27:15 connect(hidden@hidden); useCount: 1
14:27:15 insert into "public"."roles"("name") values('Test2') RETURNING *
14:27:15 disconnect(hidden@hidden)
14:27:18 connect(hidden@hidden); useCount: 2
14:27:18 tx(Insert-New-Device)/start
14:27:18 tx(Insert-New-Device): begin
14:27:18 tx(Insert-New-Device): insert into "public"."devices"("smanufacturer") values('HP') RETURNING *
14:27:18 tx(Insert-New-Device): insert into "public"."systems"("deviceid","distributionid","archid","smanufacturer") values(15,3,2,'Microsoft Corporation') RETURNING *
14:27:18 tx(Insert-New-Device): commit
14:27:18 tx(Insert-New-Device)/end; duration: .046, success: true
14:27:18 disconnect(hidden@hidden)
14:27:20 connect(hidden@hidden); useCount: 3
14:27:20 tx(Insert-New-Device)/start
14:27:20 tx(Insert-New-Device): savepoint level_1
14:27:20 error: SAVEPOINT can only be used in transaction blocks
         tx(Insert-New-Device): savepoint level_1
14:27:20 tx(Insert-New-Device)/end; duration: .011, success: false
14:27:20 disconnect(hidden@hidden)

errors

1. devices.add throw

   Error: SAVEPOINT can only be used in transaction blocks

2. roles.add throw

   Error: Querying against a released or lost connection

EDIT : found the problem

The problem is in my repositories. In pg-promise-demo, each repos export classes so the DB initializaion use the new key word in the extend event to create them. My repos are not classes. I tried to change them into classes and it works

Before (don't works)

./db/repos/devices.js

'use strict';

var Database = null, pgp = null, Collections = null;

async function add(params) {
  return Database.tx('Insert-New-Device', async t => {
    let system = null;

    const query = pgp.helpers.insert(params.data.device, Collections.insert) + " RETURNING *";
    let device = await t.one(query);

    // if a system is present, insert with diviceId and return
    if(params.data.system) {
      params.data.system.deviceid = device.deviceid;
      system = await t.systems.InsertOne(params);
    }

    return {device, system};
  })
  .catch(ex => {
    throw ex;
  });
}

function createColumnsets() { /* hidden for brevity */ }

// rpc methods
const expose = {
  'devices.insert': add
}

const DevicesRepository = {
  expose,        // expose methods as "rpc methods"
  InsertOne: add // internal use (by another repo for example : Database.devices.InsertOne())
};

module.exports = (db, pgpLib) => {
  Database = db;
  pgp = pgpLib;
  Collections = createColumnsets();

  return DevicesRepository;
}

./db/index.js.js

'use strict';

const promise = require('bluebird');

const repos = {
  Roles: require('./repos/roles'),
  Systems: require('./repos/systems'),
  Devices: require('./repos/devices')
}
const config = require('./conf');

const initOptions = {
    promiseLib: promise,
    extend(obj, dc) {
        obj.roles = repos.Roles(obj, pgp);
        obj.systems = repos.Systems(obj, pgp);
        obj.devices = repos.Devices(obj, pgp);
    }
};

const pgp = require('pg-promise')(initOptions);
const monitor = require('pg-monitor');
monitor.attach(initOptions);
const db = pgp(config);

const methods = Object.assign({}, db.roles.expose, db.systems.expose, db.devices.expose );

module.exports = {
  methods
}

Now (works without errors)

devices.js

'use strict';

class RolesRepository {
  constructor(db, pgp) {
    this.Database = db;
    this.pgp = pgp;

    this.Collections = createColumnsets(pgp);

    this.expose = {
      'roles.insert': this.InsertOne.bind(this)
    }
  }

  makeInsertQuery(role) {
    return this.pgp.helpers.insert(role, this.Collections.insert);
  }

  async InsertOne(params) {
    let query = this.makeInsertQuery(params.data);
    if(params.return) query += " RETURNING *";

    return this.Database.any(query)
                    .then(data => { return data; })
                    .catch(ex => { throw ex; });
  }
}

function createColumnsets(pgp) { /* hidden for brevity */ }

module.exports = RolesRepository

./db/index.js

'use strict';
const promise = require('bluebird');

//const repos = require('./repos'); // ./repos/index.js
const repos = {
  Roles: require('./roles'),
  Systems: require('./systems'),
  Devices: require('./devices'),
};
const config = { /* hidden */ };

const initOptions = {
    promiseLib: promise,
    extend(obj, dc) {
        obj.roles = new repos.Roles(obj, pgp);
        obj.systems = new repos.Systems(obj, pgp);
        obj.devices = new repos.Devices(obj, pgp);
    }
};

const pgp = require('pg-promise')(initOptions);
const monitor = require('pg-monitor');
monitor.attach(initOptions);

const db = pgp(config);

// expose db methods as rpc call
const methods = Object.assign({},
    db.roles.expose,
    db.systems.expose,
    db.devices.expose,
);

module.exports = {
  methods
}

Answer 1

I don't believe that what you are showing is a complete code, because the type of issue you are having is impossible within the pg-promise transaction level. It cannot execute a SAVEPOINT outside of transaction.

There is however, one way to hack it, which may break the library this way, and I highly suspect that this what you did, somehow...

When we execute a task with method task, or transaction via method tx, the method creates a temporary connection context, which it gives you as the callback parameter to execute queries.

It is done this way, so that when the callback finishes, the context is destroyed automatically. And if somehow you expose that context outside the callback function, and start executing queries against it, you break the connection context logic.

One way you can break it, is by executing an asynchronous function that uses the context, and not finishing it by the time the callback is done. Then you can have that type of error - Querying against a released or lost connection, which tells you that the context is gone/released, and you are still trying to execute queries against it.

You need to make sure that you do not use the connection context outside the callback function, as it is not usable there, and the result of using it this way can be unpredictable.