thupi
commented
10 months ago I have had a chance to run node-postgres and this package side by side.
I am using connection pools with the same configurations in both implementations:
import postgres from "postgres";
import { Pool } from "pg";
import {
POSTGRE_DATABASE,
POSTGRE_HOST,
POSTGRE_PASSWORD,
POSTGRE_PORT,
POSTGRE_USERNAME,
} from "../constants/env";
export const sql = postgres({
host: POSTGRE_HOST,
port: POSTGRE_PORT,
database: POSTGRE_DATABASE,
username: POSTGRE_USERNAME,
password: POSTGRE_PASSWORD,
ssl: "require",
idle_timeout: 30,
});
export const pool = new Pool({
host: POSTGRE_HOST,
port: POSTGRE_PORT,
database: POSTGRE_DATABASE,
user: POSTGRE_USERNAME,
password: POSTGRE_PASSWORD,
ssl: true,
application_name: "node-postgres",
idleTimeoutMillis: 30000,
});
I found that node-postgres continues to work while postgres.js stops functioning within an hour. My current thought is that, the above issues are caused by the connection pool being empty, which cloud be a logical reason for the queries to stay pending.
lithdew
porsager
Environment
I have a local test server in Denmark. I also have a production server and a Postgres server in Netherland. The production server and Postgres server runs within the same network and therefore has a fast connection. Due to the distance between the test server in Denmark and the Postgres server in Netherland, the connection between those could be rather slow. We have an uptime monitor that checks the health of the application by running a query every minute.
Production server Location: Amsterdam OS: Ubuntu Runtime: Bun 1.0.4 Process Manager: PM2 Framework: Hono Postgres.js version: 3.3.5
Test server Location: Denmark OS: MacOS Runtime: Bun 1.0.4 Process Manager: PM2 Framework: Hono Postgres.js version: 3.3.5
PostgreSQL Server Location: Amsterdam Version: 15
Bug
I experience that at some point, all the "sql
..." function's returned promises stops resolving and stays pending through out the entire application. This causes all the endpoint that queries the db to timeout. All the other endpoint that doesn't query the endpoint continues to function. I haven't noticed this bug in our production server that runs on the same network as the PostgreSQL server. From that I conclude that slow connection has something to do with the bug.How to reproduce
I haven't spent time creating an isolated environment to reproduce the bug as this requires setting up a new Database and new vm with an isolated node app. I am willing to setup an isolated environment that reproduces the bug in case an isolated environment would be helpful.
In our current test environment i can accelerate the time it takes to get into the broken state by randomly terminating the connection from the PostgreSQL server a few times(2-5 times). I also found, setting 'idle_timeout' to 30 accelerates the time it takes. So I guess the combination of slow connection and frequent closing of the connection has something to do with the issue. Restarting the application will solve the problem for a while until it goes into the broken state again.
Expected behaviour
I think the above indicates 2 seperate issues:
..." under any circumstances get into a state where they stay pending? I would expect that the "sql..." functions should always resolve or reject the return promise.Next steps