nodejs pg-池似乎没有池。

Question

我一直试图让nodejs在我的应用程序中池postgresql连接，但没有成功。这是我做过的一项独立测试：

const config = require('../config/project_config.json');
const Pool = require('pg-pool');

var pool = new Pool({
    user: config.DB_USER,
    host: config.DB_HOST,
    database: config.DB_DB,
    password: config.DB_PW,
    port: 5432,
    max: 500,
    min: 200,
    idleTimeoutMillis: 0,
    connectionTimeoutMillis: 10000
});

for (var i=0; i<100; i++){
    pool.query(
        "SELECT id, email FROM players WHERE email ~ 'ltUser'",
        [],
        (err, res) => {
            if (err !== null && err != undefined){
                console.log(`err: ${err}`);
            }
            else{
                console.log(`num rows: ${res.rows.length}`);
            }
        });
}

我得到的结果是：

num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
err: Error: Connection terminated due to connection timeout
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400
num rows: 400

正如您所看到的，它正在抛出连接超时，这意味着当我创建池时，它没有创建连接。在创建池时，我尝试过各种参数组合，包括有一个keepalive: true，但似乎没有一个可以使pg池真正建立池连接。我也尝试过pg而不是pg池。但是得到了完全相同的结果，尽管后来我发现它基本上是相同的代码。

如果我使用运行时间较长的查询运行它，则可以连接psql中的数据库并运行。

SELECT datname,usename, ssl, client_addr, count(*
FROM pg_stat_ssl
  JOIN pg_stat_activity
    ON pg_stat_ssl.pid = pg_stat_activity.pid
where usename != 'azure_superuser'
group by datname,usename, ssl, client_addr;

然后看着连接计数我的IP地址上升，然后再倒车。

我是做错什么了还是pg-池坏了？

我在一个ubuntu异种服务器上使用NodeJSv10.22.1。

Answer 1

事实证明，pg池正在发挥作用，只是根据我在Java和Erlang等其他编程语言方面的经验，它并不是我所期望的那样。Nodejs不会提前创建连接，但是当连接被签出池时。

基于此，Nodejs中池的主要优点是程序员不必处理打开和关闭连接，并且可以重用连接。

Answer 2

如果您想打开一定数量的连接到后端，例如200 (这是一个太大的数字，很可能您想要大约64 )

然后，您可以创建池，然后立即发出200个查询()，而不释放客户机。

然后释放所有200个客户。

从那时起，如果是idleTimeoutMillis === 0，池将永远保存到DB的200个打开连接，您可以将其用作初始化池。

var pool = new Pool({
    user: config.DB_USER,
    host: config.DB_HOST,
    database: config.DB_DB,
    password: config.DB_PW,
    port: 5432,
    max: 200,   // set max to 200 connections to the db
    //min: 200, // min is not a configuration option
    idleTimeoutMillis: 0,
    connectionTimeoutMillis: 10000
});

// create a list to hold all the "done" functions 
//   that release clients
let releaseClientDoneList = [];

// open 200 clients, without releasing them
for (var i=0; i<200; i++){
  // callback - checkout a client
  pool.connect((err, client, done) => {
      // asyncronously store the "done" function once the client 
      //   has connected to the db
      connectedNewClient(done);
    }
  )
}

let connectedNewClient = function (doneFunction) {
  // store the "done" function in the list
  releaseClientDoneList.push(doneFunction)

  // if the list has 200 "done" functions inside, 
  //   then we know we have initialised all the clients we want
  if (releaseClientDoneList.length >= 200) {
    
    // call all the "done" functions to release all the clients
    for (let doneFunctionReleasesClient of releaseClientDoneList) {
      doneFunctionReleasesClient();
    }

    // now we know there are 200 available, 
    //   initialised db connections in the pool
    databasePoolInitialised();
  }
}

let databasePoolInitialised = function () {
  // ... rest of your application code goes here
}