Cannot connect to Postgres database

abesis · 13 November 2019 21:06

Hi,
I've been trying to communicate with Postgres database that runs locally on a Raspberry pi.
So far I think that I have tried every node available through the Palette, such as

node-red-contrib-postgres-variable
node-red-contrib-re-postgres
node-red-contrib-postgres-multi
Postgrestor
..and others

All of them fail with a TypeError and cause Node-RED to restart, the moment a query is sent to the database. Here is the log:

Welcome to Node-RED
===================
13 Nov 22:41:55 - [info] Node-RED version: v1.0.2
13 Nov 22:41:55 - [info] Node.js version: v10.17.0
13 Nov 22:41:55 - [info] Linux 4.19.75-v7l+ arm LE
13 Nov 22:41:56 - [info] Loading palette nodes
13 Nov 22:42:03 - [info] Dashboard version 2.17.1 started at /ui
13 Nov 22:42:03 - [info] Settings file : /home/pi/.node-red/settings.js
13 Nov 22:42:03 - [info] Context store : 'default' [module=memory]
13 Nov 22:42:03 - [info] User directory : /home/pi/.node-red
13 Nov 22:42:03 - [info] Server now running at http://127.0.0.1:1880/
13 Nov 22:42:03 - [info] Active project : ggln25wb
13 Nov 22:42:03 - [info] Flows file : /home/pi/.node-red/projects/ggln25wb/flow.json
13 Nov 22:42:04 - [info] Starting flows
13 Nov 22:42:05 - [info] Started flows
13 Nov 22:42:18 - [red] Uncaught Exception:
13 Nov 22:42:18 - TypeError: Second argument must be a string or a buffer.
at Connection.parseCString (/home/pi/.node-red/node_modules/pg/lib/connection.js:649:20)
at Connection.parseE (/home/pi/.node-red/node_modules/pg/lib/connection.js:548:30)
at Connection.parseMessage (/home/pi/.node-red/node_modules/pg/lib/connection.js:378:19)
at Socket. (/home/pi/.node-red/node_modules/pg/lib/connection.js:119:22)
at Socket.emit (events.js:198:13)
at addChunk (_stream_readable.js:287:12)
at readableAddChunk (_stream_readable.js:268:11)
at Socket.Readable.push (_stream_readable.js:223:10)
at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
nodered.service: Main process exited, code=exited, status=1/FAILURE
nodered.service: Failed with result 'exit-code'.
nodered.service: Service RestartSec=100ms expired, scheduling restart.
nodered.service: Scheduled restart job, restart counter is at 1.
Stopped Node-RED graphical event wiring tool.
Started Node-RED graphical event wiring tool.
13 Nov 22:42:20 - [info]
Welcome to Node-RED
===================
13 Nov 22:42:20 - [info] Node-RED version: v1.0.2
13 Nov 22:42:20 - [info] Node.js version: v10.17.0
13 Nov 22:42:20 - [info] Linux 4.19.75-v7l+ arm LE
13 Nov 22:42:22 - [info] Loading palette nodes
13 Nov 22:42:28 - [info] Dashboard version 2.17.1 started at /ui
13 Nov 22:42:29 - [info] Settings file : /home/pi/.node-red/settings.js
13 Nov 22:42:29 - [info] Context store : 'default' [module=memory]
13 Nov 22:42:29 - [info] User directory : /home/pi/.node-red
13 Nov 22:42:29 - [info] Server now running at http://127.0.0.1:1880/
13 Nov 22:42:29 - [info] Active project : ggln25wb
13 Nov 22:42:29 - [info] Flows file : /home/pi/.node-red/projects/ggln25wb/flow.json
13 Nov 22:42:29 - [info] Starting flows
13 Nov 22:42:30 - [info] Started flows

My flow is a simple one, with just one select statement that should return one row from the database:

Query:

msg.payload = 'SELECT * FROM table1 limit 1;';
return msg;

Connection settings:

Postgres node:

Am I missing something?
Using the same connection parameters, I can connect and select records without problems, using Pyhton. Also pgAdmin connects normally.

Colin · 13 November 2019 21:55

Which node are you using that takes a query in that form in the payload.
Did you try the example flow in the readme of node-red-contrib-re-postgres?

abesis · 13 November 2019 22:24

Yes, I've tried that too. And tried it again now.
This is the example from node-red-contrib-re-postgres:

The payload of the format query node is:

select * from table1 where field2 > $param1

which, after removing the optional parameters, eventually becomes a plain SQL query, as the one I used above. Have I misunderstood something here?

By the way, the flow now throws this error, without causing Node-RED to restart:

[error] [postgres:dcda0807.f56818] TypeError: pg.connect is not a function

Edit: I also now get this warning, that looks relevant:

DeprecationWarning: PG.connect is deprecated - please see the upgrade guide at https://node-postgres.com/guides/upgrading`

Colin · 14 November 2019 08:03

Export that flow and paste it here please.

abesis · 14 November 2019 15:58

Here is the flow:

[{"id":"4b9aa0ee.e9305","type":"inject","z":"d27b9737.cad368","name":"trigger","topic":"","payload":"","payloadType":"date","repeat":"","crontab":"00 12 * * *","once":false,"x":320,"y":2740,"wires":[["3f5acb02.90bd14"]]},{"id":"310442ef.1b4b7e","type":"postgres","z":"d27b9737.cad368","postgresdb":"a96a000f.57a5f","name":"","output":true,"perrow":false,"outputs":1,"x":740,"y":2740,"wires":[["e1035bbb.9ec088"]]},{"id":"e1035bbb.9ec088","type":"debug","z":"d27b9737.cad368","name":"query output","active":true,"console":"false","complete":"true","x":930,"y":2740,"wires":[]},{"id":"3f5acb02.90bd14","type":"function","z":"d27b9737.cad368","name":"setup params","func":"msg.payload = 'select * from mytable limit 1;';\nreturn msg;","outputs":1,"noerr":0,"x":540,"y":2740,"wires":[["310442ef.1b4b7e"]]},{"id":"a96a000f.57a5f","type":"postgresdb","z":"","hostname":"localhost","port":"5432","db":"mydb","ssl":false}]

And here is the result when I trigger it:

Welcome to Node-RED
===================
14 Nov 12:00:03 - [info] Node-RED version: v1.0.2
14 Nov 12:00:03 - [info] Node.js version: v10.17.0
14 Nov 12:00:03 - [info] Linux 4.19.75-v7l+ arm LE
14 Nov 12:00:04 - [info] Loading palette nodes
14 Nov 12:00:12 - [info] Dashboard version 2.17.1 started at /ui
14 Nov 12:00:13 - [info] Settings file : /home/pi/.node-red/settings.js
14 Nov 12:00:13 - [info] Context store : 'default' [module=memory]
14 Nov 12:00:13 - [info] User directory : /home/pi/.node-red
14 Nov 12:00:13 - [info] Server now running at http://127.0.0.1:1880/
14 Nov 12:00:13 - [info] Active project : ggln25_web
14 Nov 12:00:13 - [info] Flows file : /home/pi/.node-red/projects/ggln25_web/flow.json
14 Nov 12:00:13 - [info] Starting flows
14 Nov 12:00:14 - [info] Started flows
14 Nov 17:52:10 - [info] Stopping modified flows
14 Nov 17:52:10 - [info] Stopped modified flows
14 Nov 17:52:10 - [info] Starting modified flows
14 Nov 17:52:10 - [info] Started modified flows
(node:18315) DeprecationWarning: PG.connect is deprecated - please see the upgrade guide at Upgrading – node-postgres
14 Nov 17:52:41 - [red] Uncaught Exception:
14 Nov 17:52:41 - TypeError: Second argument must be a string or a buffer.
at indexOf (/home/pi/.node-red/node_modules/pg/lib/connection.js:19:21)
at Connection.parseCString (/home/pi/.node-red/node_modules/pg/lib/connection.js:663:13)
at Connection.parseE (/home/pi/.node-red/node_modules/pg/lib/connection.js:562:30)
at Connection.parseMessage (/home/pi/.node-red/node_modules/pg/lib/connection.js:391:17)
at Socket. (/home/pi/.node-red/node_modules/pg/lib/connection.js:129:22)
at Socket.emit (events.js:198:13)
at addChunk (_stream_readable.js:287:12)
at readableAddChunk (_stream_readable.js:268:11)
at Socket.Readable.push (_stream_readable.js:223:10)
at TCP.onStreamRead [as onread] (internal/stream_base_commons.js:94:17)
nodered.service: Main process exited, code=exited, status=1/FAILURE
nodered.service: Failed with result 'exit-code'.
nodered.service: Service RestartSec=100ms expired, scheduling restart.
nodered.service: Scheduled restart job, restart counter is at 3.
Stopped Node-RED graphical event wiring tool.
Started Node-RED graphical event wiring tool.
14 Nov 17:52:44 - [info]

Then Node-RED restarts.

Colin · 14 November 2019 21:42

You don't seem to have answered that question, and 'no' is not a valid answer, even if it is true
[Edit] My guess is that you need to have msg.queryParameters even if there are no properties being specified. But start with the demo flow, if that works then you can modify towards what you need and see at what point it fails.
There is an issue with the node that needs reporting though. Nothing you send to a node should crash node-red, a node should catch all exceptions inside itself to prevent that.

abesis · 16 November 2019 14:00

I confirm that I have tried the example in the readme without changing anything.
I still get the same DepricationWarning, TypeError and Node-RED restarts.

abesis · 16 November 2019 14:02

Can anyone with the following configuration confirm that they can work with the db?

Raspberry Pi 3
psql (11.5 (Raspbian 11.5-1+deb10u1))
Node-RED version: v1.0.2
Node.js  version: v10.17.0
Linux 4.19.75-v7l+ arm LE

Colin · 16 November 2019 15:16

In that case submit an issue against the node.

Topic		Replies	Views
Cannot able to query data into PostgreSQL from PI General database	4	125	3 May 2024
Node-red crash-Unable to open General	8	770	13 October 2021
Can't communicate with postgres database General	1	264	16 February 2021
SOLVED : NODE-RED crash while restarting Postgres service in WINDOWS! General	3	180	16 January 2023
Postgres Database Connection - "Error: Connection terminated due to connection timeout" General database	7	325	23 April 2023

Cannot connect to Postgres database

Related topics