none
Azure Postgres and node-pg-pool connection problems

    Question

  • I'm developing node.js Function App that connects to an Azure Postgres Server v9.6 and under certain conditions the App expierences either connection timeouts or hanging queries.

    Part of the App is a scheduled function that is called every 60s to execute certain SQL queries. The sql connections are done via node-pg-pool (https://github.com/brianc/node-pg-pool).

    The problems occur when I set the max idle time in the pool to a value of less than 60s (so the connection in the pool are all timing out and the pool is empty on the next run).
    Under these conditions every 5 to 20 minutes either one of two Errors occurs:

    A) Connection timeout error
    This does not happen as often and is not as problematic, but still far from optimal.
    The node stack trace is quite void of details:

    Error: Connection terminiated due to connection timeout
        at Connection.con.once (D:\home\site\wwwroot\node_modules\pg\lib\client.js:179:21)
        at Connection.g (events.js:292:16)
        at emitNone (events.js:86:13)
        at Connection.emit (events.js:185:7)
        at Socket.<anonymous> (D:\home\site\wwwroot\node_modules\pg\lib\connection.js:76:10)
        at emitOne (events.js:96:13)
        at Socket.emit (events.js:188:7)
        at TCP._handle.close [as _onclose] (net.js:497:12)


    B) SQL queries do not return
    This is the error that is a bigger problem, since this causes the funtion to timeout and the function host to be restarted.
    The query is send to the node-pg connection, but the asyncrous call does not return to node.

    Everytime this happens the postgres logs contain the following lines:

    2018-03-02 11:07:00 UTC-5a993054.69420-LOG:  connection received: host=[REDACTED] port=26072 pid=431136
    2018-03-02 11:07:00 UTC-5a993054.69424-LOG:  connection received: host=[REDACTED] port=29952 pid=431140
    2018-03-02 11:07:01 UTC-5a993055.69428-LOG:  connection received: host=[REDACTED] port=26776 pid=431144
    2018-03-02 11:07:01 UTC-5a993054.69424-LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.
    2018-03-02 11:07:01 UTC-5a993054.69420-LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.
    2018-03-02 11:07:01 UTC-5a993055.69428-LOG:  could not receive data from client: An existing connection was forcibly closed by the remote host.


    The three connects / "could not receive data" correspond three queries that are launched asyncrously and waited for by node.

    If I set a longer timeout (ie. 120s, so the connections are kept alive) these problems disappear.
    Also these errors do not happen in local development with pg running in docker.

    I'd like to understand the underlying issue of why this happens since there are other functions in the Function App that are trigged by http requests and I fear that we might encounter these problems down the line when the connection pool grows & shrinks according to demand.

    Thanks in advance

    Friday, March 2, 2018 11:47 AM

All replies

  • Did you figure out your problem? I have exactly the same problem on Azure.
    Thursday, November 22, 2018 6:56 AM
  • We are experiencing seemingly the exact same issue with our production app's Azure PostgreSQL instance. Decreasing the timeout made the error occur more often.
    Thursday, January 10, 2019 2:59 PM