Pg-promise: Connection is released before Task is done

[hawkeye64]

I am following the chained task as per example here: http://vitaly-t.github.io/pg-promise/Task.html

Using Database.tx

It does the first 2 tasks fine, but when it gets to the 3rd task, the database connection has been lost because the commit was done after the second one.

Expected behavior

Not to lose the connection

Actual behavior

Connection is lost

Steps to reproduce

See example above, but add more chained tasks

Environment

• Version of pg-promise: v10.5.6
• OS type (Linux/Windows/Mac): Ubuntu 18.04
• Version of Node.js: v12.16.0

---

09:29:31 tx/start
09:29:31 tx: begin

09:29:31 tx: UPDATE ivthvr.rule_trip_wire_one_direction set min_delay_between_alarms_enabled=true, min_delay_between_alarms='00:03:00', enabled=true, generate_alarm_message_active=false, description='', generate_associated_camera_snapshot=false, generate_associated_camera_video=false, camera_id=1, graduate_direction_enabled=false, graduate_direction=null, min_object_size_enabled=true, min_object_width_primary=null, min_object_height_primary=null, min_object_width_secondary=null, min_object_height_secondary=null, max_object_size_enabled=false, max_object_width_primary=null, max_object_height_primary=null, max_object_width_secondary=null, max_object_height_secondary=null, one_way_roi='0.53834808259587,0.522779416584726,0.771386430678466,0.56997705670272' WHERE rule_id=15 returning rule_id

09:29:31 tx: INSERT INTO ivthvr.rule_schedule (rule_id, enabled, day_of_week ,start_time,stop_time) VALUES (15, true, 'Monday', '12:00:00', '24:00:00')

09:29:31 tx: commit

09:29:31 tx: UPDATE ivthvr.rule_schedule SET enabled=true, day_of_week='Monday', start_time='00:00:00', stop_time='12:00:00' WHERE rule_schedule_id=2

09:29:31 tx/end; duration: .046, success: true
09:29:31.146: Disconnecting from database: ivtdb
09:29:31 disconnect(webaccess@ivtdb)

09:29:31 error: Querying against a released or lost connection.
tx: UPDATE ivthvr.rule_schedule SET enabled=true, day_of_week='Monday', start_time='00:00:00', stop_time='12:00:00' WHERE rule_schedule_id=2

Stack:
at promise (/home/intelliview/git/System_Console/projects/web/src/node_modules/pg-promise/lib/promise-parser.js:30:20)
at Task.$query (/home/intelliview/git/System_Console/projects/web/src/node_modules/pg-promise/lib/query.js:145:12)
at Task. (/home/intelliview/git/System_Console/projects/web/src/node_modules/pg-promise/lib/query.js:263:23)
at Task.query (/home/intelliview/git/System_Console/projects/web/src/node_modules/pg-promise/lib/task.js:117:34)
at /home/intelliview/git/System_Console/projects/web/src/packages/shareable/lib/queries/rules/updateRuleTripWireOneDirection.js:118:15
at Array.forEach ()
at updateSchedules (/home/intelliview/git/System_Console/projects/web/src/packages/shareable/lib/queries/rules/updateRuleTripWireOneDirection.js:112:17)

---

The documentation is a little confusing.

Here it says: When executing more than one request at a time, one should allocate and release the connection only once...

And on the same page: Each task/transaction manages the connection automatically.

I feel like I am missing something important here.

and one more: When executed on the root Database object, the connection is allocated from the pool, and once the method's callback has finished, the connection is released back to the pool.

Also, wanted to thank you for an excellent interface to PostgreSQL and any insights you may have.

[papawou]

It is not related to pg-promise, your code doesn't wait the end of the function
Are you sure the promise returned by db.tx is handled correctly ?

thisFunctionReturnAPromise() {
  return db.tx(t => {
    return t.one('SELECT 1')
      .then(res => t.one('SELECT 2'))
      .then(res => t.one('SELECT 3'))
  })
}

thisFunctionReturnAPromise()
  .then(res => /*res=3*/)

[hawkeye64]

Yes, it was exactly as you have above.
In the end, I rewrote the code the bundle all remaining queries into one and use that on the second query which is working fine.

[vitaly-t]

Querying against a released or lost connection is usually issued when you forget to promise-chain queries inside a task or transaction.

The new async syntax is much easier to use in that regard:

await db.tx(async t => {
    const a = await t.any('SELECT 1');
    const b = await t.any('SELECT 2');
    const c = await t.any('SELECT 3');
    return {a, b, c}; // if your transaction returns data
});

[hawkeye64]

Thanks for the info @vitaly-t