Node-postgres: Pool query never returns

[danbulant]

When I do Pool.query (Pool is instance of pg.Pool and successfully connected to DB), it never returns.
I tried both promise syntax (async/await and then/catch) and callback syntax, none of them worked. The script just never ends.

Using connection with the same details with psql works.

Node v12.14.0
npm 6.13.4
pg 7.17.1
PostgreSQL 12.1

EDIT: forgot to specify this happens when there's no row returned.
EDIT2: Select DATE() works while from table doesn't

[sehrope]

Can you connect to the same database via something else like psql? If it hangs forever it could be a network issues dropping packets in between your computer and the remote DB. That's not specific to this driver.

If you can connect via some other means, then show a minimal example of the code that's not working.

[danbulant]

Hello @sehrope .
As I already stated, using psql with the same details works.

It is not a connection issue, as it's connected to localhost.

[sehrope]

Show your actual code or a minimal example of JavaScript code that reproduces the issue.

[danbulant]

Just this query:
var res = await conn.query("SELECT * FROM public.posts p, public.users u WHERE p.user = u.id");

Never returns, never throws anything. No error in console, the app just hangs...

[sehrope]

Where is conn coming from? Show the entire block that's acquiring the connection, and how it's being created.

[danbulant]

const { Pool, Client } = require('pg')
const fs = require("fs");
conn = new Pool(JSON.parse(fs.readFileSync(__dirname + "/../sqlDetails.json")))
var res = await conn.query("SELECT * FROM public.posts p, public.users u WHERE p.user = u.id");
console.log(res)

with this code, the node just hangs and never outputs anything

[danbulant]

However, when I simplify the query to just "SELECT DATE()", it works

[danbulant]

the query SELECT * FROM public.posts p, public.users u WHERE p.user = u.id also works in psql

[sehrope]

That query would fetch every single row from post which could and return it as a single array. If it's a lot of rows (like millions...) that could take a really long time and possibly never finish due to memory allocation / GC issues.

If that's not the issue then try splitting things up to see where it's hanging. Also maybe break out the top level await to further isolate things:

const { Pool, Client } = require('pg')
const fs = require("fs");

const pool = new Pool(JSON.parse(fs.readFileSync(__dirname + "/../sqlDetails.json")));

async function testConnect() {
    console.log('Before connect');
    const client = await pool.connect();
    console.log('Connected!');
    client.release();
}

async function testSmallQuery() {
    console.log('Before small query');
    const sql = "SELECT 1 AS x";
    const result = await pool.query(sql);
    console.log('Small query returned %s rows', result.rows.length);
}

async function testBigQuery() {
    console.log('Before big query');
    const sql = "SELECT * FROM public.posts p, public.users u WHERE p.user = u.id";
    const result = await pool.query(sql);
    console.log('Big query returned %s rows', result.rows.length);
}

async function main() {
    await testConnect();
    await testSmallQuery();
    await testBigQuery();
}

main()
    .then (() => {
        console.error('Done');
        process.exit(0);
    })
    .catch((err) => {
        console.error('Error: %s', err);
        console.error('Error: %s', err.stack);
        process.exit(1);
    });

main();

[danbulant]

Ok I'll test it tomorrow, thanks.

As per the big data - that isn't the case, the DB is brand new and has just one post in it (and one user), so it should return just one row right now.

[danbulant]

So, as I expected, the code hangs in "Before big query", even when I added "LIMIT 1" at the end.

[danbulant]

The query code itself works. Could it possibly be that there are duplicate fields and/or number of fields?

Also SELECT * FROM information_schema.tables works as well. Why just this query doesn't work?

Could it be types? When I do SELECT u.name, u.username, p.content, p.uuid, p.type, p.date FROM public.posts p, public.users u WHERE p.user = u.id LIMIT 1 (which works in psql), it hangs as well.
The types are (not in order):

• character variable
• text
• uuid
• datetime without timezone
• integer

[charmander]

I can’t reproduce this.

$ container() {
    docker run --rm -it \
        --runtime=runsc \
        --security-opt=no-new-privileges \
        --user="$UID:$(id -g)" \
        --init \
        "$@"
}

$ docker network create --internal pg-2069-net
e66e2556d6a4eb04aca0d33b13bff31ec686b0ffbd69fe7e91cf2bb21bafb7b0

$ container --network=pg-2069-net --name=pg-2069-postgres --user= --detach postgres:12
3664b467bee0056c7550569d387889a09915d2be21b9a149fe0fb65e9d97d1a6

$ container --name=pg-2069-node --rm=false --workdir=/home/node node:12.14.0-alpine sh
~ $ cat > package.json
{ "private": true }
~ $ npm install pg
npm notice created a lockfile as package-lock.json. You should commit this file.
+ [email protected]
added 17 packages from 9 contributors and audited 17 packages in 2.283s
found 0 vulnerabilities

~ $ npm ls
/home/node
`-- [email protected]
  +-- [email protected]
  +-- [email protected]
  +-- [email protected]
  +-- [email protected]
  +-- [email protected]
  +-- [email protected]
  | +-- [email protected]
  | +-- [email protected]
  | +-- [email protected]
  | +-- [email protected]
  | `-- [email protected]
  |   `-- [email protected]
  +-- [email protected]
  | `-- [email protected]
  |   `-- [email protected]
  `-- [email protected]

~ $ npm --version
6.13.4
~ $ %

$ docker container inspect pg-2069-postgres --format '{{(index .NetworkSettings.Networks "pg-2069-net").IPAddress}}'
172.21.0.2

$ docker commit pg-2069-node
sha256:04ac3514864cb83b2380822242d1fd4c5714391d3f8f18cd722d036f313564c1

$ container --network=pg-2069-net 04ac3514864c node --experimental-repl-await
Welcome to Node.js v12.14.0.
Type ".help" for more information.
> const { Pool } = require('pg');
undefined
> const pool = new Pool({ host: '172.21.0.2', user: 'postgres' });
undefined
> void await pool.query(`CREATE TABLE users (id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, name text NOT NULL, username text NOT NULL); INSERT INTO users (name, username) VALUES ('John Doe', 'john doe')`);
undefined
> void await pool.query(`CREATE TABLE posts (id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY, "user" integer NOT NULL REFERENCES users (id), content text NOT NULL, uuid uuid NOT NULL, type integer NOT NULL, date timestamp without time zone NOT NULL); INSERT INTO posts ("user", content, uuid, type, date) VALUES (1, 'Testing post', '87be7d22-7a6e-463f-afb3-b8e5349c9d3a', 0, '2020-01-12 18:28:48'), (1, 'Testing post', 'dc7e636c-5aeb-4c81-aef2-e1477a35f341', 0, '2020-01-12 18:28:48')`);
undefined
> (await pool.query(`SELECT u.name, u.username, p.content, p.uuid, p.type, p.date FROM public.posts p, public.users u WHERE p.user = u.id LIMIT 1`)).rows
[
  {
    name: 'John Doe',
    username: 'john doe',
    content: 'Testing post',
    uuid: '87be7d22-7a6e-463f-afb3-b8e5349c9d3a',
    type: 0,
    date: 2020-01-12T18:28:48.000Z
  }
]
> (await pool.query(`SHOW server_version`)).rows[0]
{ server_version: '12.1 (Debian 12.1-1.pgdg100+1)' }

cleanup

$ docker attach pg-2069-postgres
^C2020-01-15 23:18:54.712 UTC [2] LOG:  received fast shutdown request
2020-01-15 23:18:54.720 UTC [2] LOG:  aborting any active transactions
2020-01-15 23:18:54.778 UTC [2] LOG:  background worker "logical replication launcher" (PID 58) exited with exit code 1
2020-01-15 23:18:54.784 UTC [53] LOG:  shutting down
2020-01-15 23:18:54.911 UTC [2] LOG:  database system is shut down

$ docker rm pg-2069-node
pg-2069-node

$ docker rmi 04ac3514864c
Deleted: sha256:04ac3514864cb83b2380822242d1fd4c5714391d3f8f18cd722d036f313564c1
Deleted: sha256:9cee742410306e8e6e1deda09daa494e8939fc757119a06008800b18003b4fe7

$ docker network rm pg-2069-net
pg-2069-net

Can you show the contents of sqlDetails.json and the versions of all packages involved (npm ls)? Also, to narrow down whether it does have something to do with types, try removing fields one by one from your last query.

[danbulant]

sqlDetails.json:

{
    "user": "Pushr",
    "password": "Pg554;",
    "host": "localhost",
    "database": "pushr"
}

NPM LS
+-- [email protected]
+-- [email protected]
+-- [email protected]
| +-- [email protected]
| | +-- [email protected]
| | | `-- [email protected]
| | `-- [email protected]
| +-- [email protected]
| +-- [email protected]
| | +-- [email protected]
| | +-- [email protected] deduped
| | +-- [email protected] deduped
| | +-- [email protected] deduped
| | +-- [email protected]
| | | +-- [email protected] deduped
| | | +-- [email protected]
| | | +-- [email protected] deduped
| | | +-- [email protected] deduped
| | | `-- [email protected]
| | +-- [email protected]
| | | `-- [email protected]
| | +-- [email protected] deduped
| | +-- [email protected] deduped
| | +-- [email protected]
| | | +-- [email protected] deduped
| | | +-- [email protected] deduped
| | | +-- [email protected] deduped
| | | `-- [email protected] deduped
| | `-- [email protected] deduped
| +-- [email protected]
| | `-- [email protected] deduped
| +-- [email protected]
| +-- [email protected]
| +-- [email protected]
| +-- [email protected]
| | `-- [email protected]
| +-- [email protected]
| +-- [email protected]
| +-- [email protected]
| +-- [email protected]
| +-- [email protected]
| | +-- [email protected] deduped
| | +-- [email protected] deduped
| | +-- [email protected] deduped
| | +-- [email protected] deduped
| | +-- [email protected] deduped
| | +-- [email protected] deduped
| | `-- [email protected]
| +-- [email protected]
| +-- [email protected]
| +-- [email protected]
| +-- [email protected]
| | `-- [email protected]
| +-- [email protected]
| +-- [email protected]
| +-- [email protected]
| | +-- [email protected]
| | `-- [email protected]
| +-- [email protected]
| +-- [email protected]
| +-- [email protected]
| +-- [email protected]
| | +-- [email protected] deduped
| | +-- [email protected] deduped
| | +-- [email protected]
| | +-- [email protected] deduped
| | +-- [email protected] deduped
| | +-- [email protected] deduped
| | +-- [email protected] deduped
| | +-- [email protected] deduped
| | +-- [email protected]
| | +-- [email protected]
| | +-- [email protected] deduped
| | +-- [email protected] deduped
| | `-- [email protected] deduped
| +-- [email protected]
| | +-- [email protected] deduped
| | +-- [email protected] deduped
| | +-- [email protected] deduped
| | `-- [email protected] deduped
| +-- [email protected]
| +-- [email protected]
| +-- [email protected]
| | +-- [email protected]
| | `-- [email protected] deduped
| +-- [email protected]
| `-- [email protected]
+-- [email protected]
| `-- [email protected]
|   `-- [email protected]
`-- [email protected]
  +-- [email protected]
  +-- [email protected]
  +-- [email protected]
  +-- [email protected]
  +-- [email protected]
  +-- [email protected]
  | +-- [email protected]
  | +-- [email protected]
  | +-- [email protected]
  | +-- [email protected]
  | `-- [email protected]
  |   `-- [email protected]
  +-- [email protected]
  | `-- [email protected]
  |   `-- [email protected]
  `-- [email protected]

Even doing simple SELECT name FROM public.users LIMIT 1 hangs. Name is varchar(32).

[nieverbe]

Any new on this issue? Seeing the same problem

[brianc]

Any new on this issue? Seeing the same problem

This is _almost_ certainly an issue w/ your setup....but I'm willing to investigate it if you give me a self-contained way to reproduce the problem. By that I mean a single gist or code sample I can copy/paste into a blank file, run node the-new-file.js and have the problem triggered. Send me that & I'll take a look!

[danbulant]

The conection itself works - I see in pgAdmin there's a new connection, but the query just never finished.

I moved to MySQL because of this, so I won't update much.

Even the simple code @sehrope send hang up when using non-system select (means selecting actual data from table). Same query worked in pgAdmin.

[charmander]

@danbulant @nieverbe Some information that might help find the problem:

• Which operating systems are you using?
• How is PostgreSQL installed on them?

• If you add:

  client.connection.on('message', console.log)
  

  what gets logged?

• Is Node hanging, or is it waiting? (Check CPU usage or see if a setInterval(() => console.log(1), 1000) stops.)
• If you’re familiar with a packet capture tool (e.g. Wireshark), what does it show?
• Does it work on a different device? Against a remote database?
• Does it work with TLS enabled?

@nieverbe Which options are you passing to the pg.Pool or pg.Client constructor?

[NomadXD]

I am trying to use postgresql inside a docker container and same thing happens. No errors, exceptions or anything. It just stops when i call database from nodejs.

[brianc]

@NomadXD gonna need some info on what version of node, postgres, node-postgres, operating system, code example, etc.

Like I mentioned above it's most likely an issue w/ your configuration or the way your code is written...this lib is heavily tested and heavily deployed across basically every node/pg/os version there is w/o this being a common issue...generally this ends up being "user error" though I'd still like to understand what it is as often "user error" is due to poor or missing documentation or bad defaults which I can totally help fix!

[NomadXD]

@brianc Thanks a lot for your quick response.

So I am running an application with 6 containers using docker-compose. When I run the application on my local machine it works without any error. But today I tried to deploy it in aws ec2 ubuntu 18.04. Project builds and when I start the server everything works fine.

When I run an end point without database call, it works fine. But when there is a database call, it hangs without giving any error or result. Tried adding console logs and found that the code does not execute after pool.query(). Tried to get some exceptions with try catch but doesnt work either.

docker-compose.yml

version: '3'
services:
  postgres:
    container_name: postgres
    image: mdillon/postgis
    environment:
      POSTGRES_MULTIPLE_DATABASES: user_data,gov_auth
      POSTGRES_USER: commhawk
      POSTGRES_PASSWORD: password
    volumes:
        - postgres:/data/postgres
        - ./database:/docker-entrypoint-initdb.d
    ports:
      - "5433:5432"
    restart: unless-stopped

  rethinkdb:
    container_name: rethinkdb
    image: rethinkdb:latest
    ports:
      - "3004:8080"
      - "29015:29015"
      - "28015:28015"
    volumes: 
      - rethinkdb:/data

  user_data_service:
    build: './user_data_service'
    container_name: uds
    ports: 
      - "3002:3000"
    depends_on: 
      - postgres
    environment:
      DATABASE_URL: postgres://commhawk:password@postgres:5432/user_data
    # volumes: 
    #   - ./user_data_service:/src
      # - container_node_modules:/src/node_modules

  gov_authority_service:
    build: './gov_authority_service'
    container_name: gov_auth
    ports:
      - "3001:3000"
    depends_on: 
      - postgres
    environment:
      DATABASE_URL: postgres://commhawk:password@postgres:5432/gov_auth
    # volumes:
    #   - ./gov_authority_service:/src 



  socket_service:
    build: './socket_service'
    container_name: socket
    ports: 
      - "3003:3000"
    depends_on: 
      - rethinkdb
    # volumes: 
    #   - ./socket_service:/src


  api_gateway:
    image: express-gateway:latest
    ports:
      - "8080:8080"
    depends_on:
      - user_data_service
      - socket_service
      - gov_authority_service
    volumes: 
      - ./api_gateway/gateway.config.yml:/var/lib/eg/gateway.config.yml 


volumes:
  postgres:
  rethinkdb:
  # container_node_modules:

In my Dockerfile I am using node:latest . Sample Dockerfile here.

FROM node:latest

COPY . /src

WORKDIR /src

RUN npm install --save-dev nodemon

RUN npm install --production

EXPOSE 3000

CMD ["npm","run","start"]

And package.json

{
  "name": "gov_authority_service",
  "version": "1.0.0",
  "description": "",
  "main": "server.js",
  "scripts": {
    "test": "echo \"Error: no test specified\" && exit 1",
    "start": "node server.js",
    "dev": "nodemon -L server.js"
  },
  "author": "",
  "license": "ISC",
  "devDependencies": {
    "nodemon": "^2.0.3"
  },
  "dependencies": {
    "bcrypt": "^4.0.1",
    "body-parser": "^1.19.0",
    "express": "^4.17.1",
    "jsonwebtoken": "^8.5.1",
    "node-fetch": "^2.6.0",
    "pg": "^8.0.2",
    "uuid": "^7.0.3"
  }
}

Can provide github project also if u need.

I don't have to install postgresql in my ubuntu server right ?

[NomadXD]

@brianc Here is my github link https://github.com/NomadXD/commhawk.git.
There is a brach called deploy/aws. You can directly build it using docker-compose build and run using docker-compose up.

Then try the {yourhost}/api/gov/signup end point with the following json as a POST request.

{
  "type":4,
  "addressLine1":"Main street Lane",
  "addressLine2":"Horana",
  "city":"Horana",
  "district":"Kalutara",
  "province": "WP",
  "location":{"lng":80.062324,"lat":6.714721},
  "email":"[email protected]",
  "telephoneNumber":"0342266888",
  "fax":"0342266888",
  "password":"password",
  "firestation":{
  "fireTrucks":2,
  "fireFighters":10
  }


}

[NomadXD]

@brianc

How I create pool

const pool = new Pool({
  user: "commhawk",
  host: "postgres",
  database: "gov_auth",
  password: "password",
  port: "5432"
});

[charmander]

@NomadXD Try upgrading to pg 8.0.3. 8.0.2 isn’t compatible with Node 14.

[NomadXD]

@charmander @brianc Thanks a lot. I was trying to fix this yesterday for like 10 hours but couldnt get it working. Now it works like a charm !!! The reason for my local version to run normally is that the local version is running node 13+ pg 8.0.2 (built it last month or so). But when yesterday , I built it on server, node:latest has pulled node 14 from docker hub which is incompatible with pg 8.0.2 as u mentioned. So I downgraded node 14 to node 13 on the server. Now works fine. Thanks a lot for helping me out.

And also I think it is more helpful and convenient if you can add checking versions for the library. I mean if the versions are incompatible return something like "pg 8.0.2 is incompatible with your current node version ". Or maybe include a chart with matching versions in the docs. Because otherwise it's like nothing happens when users face this situation. Cheers !!!!!

[charmander]

pg 8.0.2 didn’t know it wouldn’t be compatible with Node 14, and now it can’t be changed.

[lanre-mark]

Thanks @NomadXD @charmander @brianc I have been troubleshooting same issue since the last 2 days and this conversation log came to my rescue.

However, @NomadXD's suggestion will be helpful going forward.

And also I think it is more helpful and convenient if you can add checking versions for the library. I mean if the versions are incompatible return something like "pg 8.0.2 is incompatible with your current node version "

[brianc]

yeah i do keep the engines field up to date in package.json here. Problem is I didn't know before node 14 came out that it would change something in a non-backwards-compatible way and break this module. If I could go back and edit the older versions & mark in their engines field they aren't compatible w/ node 14 that'd save everyone some trouble but node module versions are immutable once published...which is ultimately for the best but does cause occasional things like this.

[charmander]

@brianc It was someone else’s bug :D

[brianc]

It was someone else’s bug

ah woops yup missed that my bad!

[NomadXD]

@brianc Totally understand and appreciate your efforts in trying to keep this library updated to the latest. Since we can't handle this version mismatch issue at code level, how about creating a table with pg version in one column and node version in the other column and including that in the documentation. At least people will give a tiny consideration to check the versions, when an issue like this happens. I literally never though about versions until @charmander mentioned about it haha

[mymro]

I just tried debugging the exact same issue. Downgrading to node 13 solved it. It would be great to have some form of documentation or feedback when running the code. Just having a promise that never resolves or rejects is really hard to debug.

[charmander]

@mymro You should upgrade to the latest pg (8.3.0) instead of downgrading to an EOL Node.

[milad1367]

@NomadXD Try upgrading to pg 8.0.3. 8.0.2 isn’t compatible with Node 14.

I was trying for 5 hours
finally fixed
I want to left :)