Error handling and query results count.

[chainhead]

Hi, This pull request addresses the following:

• Error handling
• Query results count

Error handling

If the node encounters an error, then error is not available for catching by a Catch node. This is because, the node.error is not emitting the message as well. See discussion here.

Count of results

When sending multiple queries to this node, the result does not differentiate between the results from different queries. Specifically, it is hard to know, if a query returned any result at all. See discussion here.

Files changed

• postgres.js

Node output

With this change, msg.payload will not change. However, there is an additional field named as _queryCounts that is an array of counts of rows returned by each query.

Test

Import the following flow into the canvas. To test, click on the button of the Inject node to start the flow. You should see the response similar to one pasted below.

Flow with modified node

[
  {
    "id": "a5281d45.cd5f38",
    "type": "tab",
    "label": "Flow 1",
    "disabled": false,
    "info": ""
  },
  {
    "id": "c9fee356.432a48",
    "type": "inject",
    "z": "a5281d45.cd5f38",
    "name": "Test",
    "topic": "",
    "payload": "Test",
    "payloadType": "str",
    "repeat": "",
    "crontab": "",
    "once": false,
    "onceDelay": 0.1,
    "x": 110,
    "y": 280,
    "wires": [
      [
        "48df46a8.18f108"
      ]
    ]
  },
  {
    "id": "48df46a8.18f108",
    "type": "function",
    "z": "a5281d45.cd5f38",
    "name": "DemoQueries",
    "func": "var select1 = {\n    \"query\" : \"SELECT * FROM pg_tablespace;\",\n    \"params\" : [],\n    \"output\" : true\n};\nvar select2 = {\n    \"query\" : \"SELECT * FROM pg_tablespace where spcname='does not exist';\",\n    \"params\" : [],\n    \"output\" : true\n};\nvar select3 = {\n    \"query\" : \"SELECT * FROM pg_does_not_exist;\",\n    \"params\" : [],\n    \"output\" : true\n};\nvar select4 = {\n    \"query\" : \"SELECT CURRENT_TIMESTAMP;\",\n    \"params\" : [],\n    \"output\" : true\n};\n//\nmsg.payload = [select1, select2, select3, select4];\nreturn msg;",
    "outputs": 1,
    "noerr": 0,
    "x": 300,
    "y": 280,
    "wires": [
      [
        "602818ee.9007b"
      ]
    ]
  },
  {
    "id": "602818ee.9007b",
    "type": "postgres",
    "z": "a5281d45.cd5f38",
    "postgresdb": "bf82a294.d62fb8",
    "name": "DBQuery",
    "output": true,
    "outputs": 1,
    "x": 500,
    "y": 280,
    "wires": [
      [
        "81904e66.fba"
      ]
    ]
  },
  {
    "id": "81904e66.fba",
    "type": "debug",
    "z": "a5281d45.cd5f38",
    "name": "QueryOutput",
    "active": true,
    "tosidebar": true,
    "console": false,
    "tostatus": false,
    "complete": "true",
    "x": 690,
    "y": 280,
    "wires": []
  },
  {
    "id": "28e80b0.e6483f6",
    "type": "catch",
    "z": "a5281d45.cd5f38",
    "name": "CatchDbError",
    "scope": [
      "602818ee.9007b"
    ],
    "x": 110,
    "y": 380,
    "wires": [
      [
        "42209308.791e84"
      ]
    ]
  },
  {
    "id": "42209308.791e84",
    "type": "debug",
    "z": "a5281d45.cd5f38",
    "name": "Errors",
    "active": true,
    "tosidebar": true,
    "console": false,
    "tostatus": false,
    "complete": "true",
    "x": 670,
    "y": 380,
    "wires": []
  },
  {
    "id": "bf82a294.d62fb8",
    "type": "postgresdb",
    "z": "",
    "hostname": "localhost",
    "port": "5432",
    "db": "demo_db",
    "ssl": false
  }
]

Response

Query output

{
  "_msgid": "be29e1db.909d4",
  "topic": "",
  "payload": [
    {
      "spcname": "pg_default",
      "spcowner": 10,
      "spcacl": null,
      "spcoptions": null
    },
    {
      "spcname": "pg_global",
      "spcowner": 10,
      "spcacl": null,
      "spcoptions": null
    },
    {
      "spcname": "dvcdata",
      "spcowner": 16384,
      "spcacl": null,
      "spcoptions": null
    },
    {
      "spcname": "appdata",
      "spcowner": 16384,
      "spcacl": null,
      "spcoptions": null
    },
    {
      "current_timestamp": "2018-07-07T14:12:26.709Z"
    }
  ],
  "_queryCounts": [
    4,
    0,
    -1,
    1
  ]
}

Error output

{
  "_msgid": "be29e1db.909d4",
  "topic": "",
  "payload": [
    {
      "query": "SELECT * FROM pg_tablespace;",
      "params": [],
      "output": true
    },
    {
      "query": "SELECT * FROM pg_tablespace where spcname='does not exist';",
      "params": [],
      "output": true
    },
    {
      "query": "SELECT * FROM pg_does_not_exist;",
      "params": [],
      "output": true
    },
    {
      "query": "SELECT CURRENT_TIMESTAMP;",
      "params": [],
      "output": true
    }
  ],
  "_queryCounts": [
    4,
    0,
    -1
  ],
  "error": {
    "message": "error: relation \"pg_does_not_exist\" does not exist",
    "source": {
      "id": "602818ee.9007b",
      "type": "postgres",
      "name": "DBQuery",
      "count": 1
    },
    "stack": "error: relation \"pg_does_not_exist\" does not exist\n    at Connection.parseE (/home/ubuntu/node_modules/pg/lib/connection.js:553:11)\n    at Connection.parseMessage (/home/ubuntu/node_modules/pg/lib/connection.js:378:19)\n    at Socket.<anonymous> (/home/ubuntu/node_modules/pg/lib/connection.js:119:22)\n    at emitOne (events.js:116:13)\n    at Socket.emit (events.js:211:7)\n    at addChunk (_stream_readable.js:263:12)\n    at readableAddChunk (_stream_readable.js:250:11)\n    at Socket.Readable.push (_stream_readable.js:208:10)\n    at TCP.onread (net.js:597:20)"
  }
}

[BruceFletcher]

That's a really well done pull request, thanks for making it!

I might get a chance to test it out later tonight, or if not I'll definitely try to get to it in the next few days.

[chainhead]

Awesome @BruceFletcher !