postgresql 何时断开连接以及何时结束 pg 客户端或池
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/50497583/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
when to disconnect and when to end a pg client or pool
提问by slevin
My stack is node, express and the pg module. I really try to understand by the documentation and some outdated tutorials. I dont know when and how to disconnect and to end a client.
我的堆栈是 node、express 和 pg 模块。我真的试图通过文档和一些过时的教程来理解。我不知道何时以及如何断开连接并结束客户端。
For some routes I decided to use a pool. This is my code
对于某些路线，我决定使用游泳池。这是我的代码
const pool = new pg.Pool({
user: 'pooluser',host: 'localhost',database: 'mydb',password: 'pooluser',port: 5432});
pool.on('error', (err, client) => {
console.log('error ', err); process.exit(-1);
});
app.get('/', (req, res)=>{
pool.connect()
.then(client => {
return client.query('select ....')
.then(resolved => {
client.release();
console.log(resolved.rows);
})
.catch(e => {
client.release();
console.log('error', e);
})
pool.end();
})
});
In the routes of the CMS, I use client instead of pool that has different db privileges than the pool.
在 CMS 的路由中，我使用客户端而不是与池具有不同数据库权限的池。
const client = new pg.Client({
user: 'clientuser',host: 'localhost',database: 'mydb',password: 'clientuser',port: 5432});
client.connect();
const signup = (user) => {
return new Promise((resolved, rejeted)=>{
getUser(user.email)
.then(getUserRes => {
if (!getUserRes) {
return resolved(false);
}
client.query('insert into user(username, password) values (,)',[user.username,user.password])
.then(queryRes => {
client.end();
resolved(true);
})
.catch(queryError => {
client.end();
rejeted('username already used');
});
})
.catch(getUserError => {
return rejeted('error');
});
})
};
const getUser = (username) => {
return new Promise((resolved, rejeted)=>{
client.query('select username from user WHERE username= ',[username])
.then(res => {
client.end();
if (res.rows.length == 0) {
return resolved(true);
}
resolved(false);
})
.catch(e => {
client.end();
console.error('error ', e);
});
})
}
In this case if I get a username already usedand try to re-post with another username, the query of the getUsernever starts and the page hangs. If I remove the client.end();from both functions, it will work.
在这种情况下，如果我得到 ausername already used并尝试使用另一个用户名重新发布，则查询getUser永远不会开始并且页面挂起。如果我client.end();从两个函数中删除，它将起作用。
I am confused, so please advice on how and when to disconnect and to completely end a pool or a client. Any hint or explanation or tutorial will be appreciated.
我很困惑，所以请就如何以及何时断开连接并完全结束池或客户端提供建议。任何提示或解释或教程将不胜感激。
Thank you
谢谢
采纳答案by lucchi
First, from the pg documentation*:
首先，来自pg 文档*：
const { Pool } = require('pg')
const pool = new Pool()
// the pool with emit an error on behalf of any idle clients
// it contains if a backend error or network partition happens
pool.on('error', (err, client) => {
console.error('Unexpected error on idle client', err) // your callback here
process.exit(-1)
})
// promise - checkout a client
pool.connect()
.then(client => {
return client.query('SELECT * FROM users WHERE id = ', [1]) // your query string here
.then(res => {
client.release()
console.log(res.rows[0]) // your callback here
})
.catch(e => {
client.release()
console.log(err.stack) // your callback here
})
})
This code/construct is suficient/made to get your pool working, providing the your thing herethings. If you shut down your application, the connection will hang normaly, since the pool is created well, exactly not to hang, even if it does provides a manual way of hanging, see last section of the article. Also look at the previous red section which says "You must always return the client..." to accept
此代码/结构是suficient/制作，让您的池的工作，提供了这里你的事的东西。如果关闭应用程序，连接将正常挂起，因为池创建良好，完全不会挂起，即使它确实提供了手动挂起方式，请参阅文章的最后一部分。另请查看前面的红色部分，上面写着“您必须始终返回客户端...”以接受
- the mandatory
client.release()instruction - before accesing argument.
- you scope/closure client within your callbacks.
- 强制性
client.release()指令 - 在访问参数之前。
- 您在回调中的范围/关闭客户端。
Then, from the pg.client documentation*:
然后，从pg.client 文档*：
Plain text query with a promise
带有承诺的纯文本查询
const { Client } = require('pg').Client
const client = new Client()
client.connect()
client.query('SELECT NOW()') // your query string here
.then(result => console.log(result)) // your callback here
.catch(e => console.error(e.stack)) // your callback here
.then(() => client.end())
seems to me the clearest syntax:
在我看来最清晰的语法：
- you end the client whatever the results.
- you access the result before endingthe client.
- you don′t scope/closure the client within your callbacks
- 无论结果如何，您都可以结束客户端。
- 您在结束客户端之前访问结果。
- 您不会在回调中限定/关闭客户端
It is this sort of oposition between the two syntaxes that may be confusing at first sight, but there is no magic in there, it is implementation construction syntax. Focus on yourcallbacks and queries, not on those constructs, just pick up the most elegant for your eyes and feed it with yourcode.
正是这两种语法之间的这种对立，乍一看可能令人困惑，但其中没有什么神奇之处，它是实现构造语法。专注于您的回调和查询，而不是那些构造，只需选择最适合您的眼睛并用您的代码提供它。
*I added the comments // your xxx herefor clarity
*为了清楚起见，我在此处添加了评论// 您的 xxx
回答by Simone Sanfratello
You shouldn't disconnect the pool on every query, connection pool is supposed to be used to have "hot" connections.
您不应该在每次查询时断开连接池，连接池应该用于“热”连接。
I usually have a global connection on startup and the pool connection close on (if) application stop; you just have to release the connection from pool every time the query ends, as you already do, and use the same pool also in the signupfunction.
我通常在启动时有一个全局连接，池连接在（如果）应用程序停止时关闭；你只需要在每次查询结束时从池中释放连接，就像你已经做的那样，并在signup函数中使用相同的池。
Sometimes I need to preserve connections, I use a wrapper to the query function that checks if the connection is active or not before perform the query, but it's just an optimization.
有时我需要保留连接，我对查询函数使用包装器，在执行查询之前检查连接是否处于活动状态，但这只是一种优化。
In case you don't want to manage open/close connections/pool or release, you could try https://github.com/vitaly-t/pg-promise, it manage all that stuff silently and it works well.
如果您不想管理打开/关闭连接/池或释放，您可以尝试https://github.com/vitaly-t/pg-promise，它以静默方式管理所有这些东西并且运行良好。
回答by EMX
Its quite simple, a client-connection (single connection) opens up, query with it, once you are done you end it.
它非常简单，打开一个客户端连接（单连接），用它查询，一旦完成就结束它。
The pool concept is different, in the case of mysql: you have to .release() the connection back to the pool once you are done with it, but it seems that with pgis a different story:
池的概念是不同的，在以下情况下mysql：您必须在完成后将连接 .release() 返回到池，但似乎 withpg是一个不同的故事：
From an issue on the github repo : Cannot use a pool after calling end on the pool#1635
来自 github repo 上的一个问题：在池上调用 end 后无法使用池#1635
"Cannot use a pool after calling end on the pool"
You can't reuse a pool after it has been closed (i.e. after calling the .end() function). You would need to recreate the pool and discard the old one.
The simplest way to deal with pooling in a Lambda is to not do it at all. Have your database interactions create their own connections and close them when they're done.You can't maintain a pool across freeze/thaw cycles anyway as the underlying TCP sockets would be closed.
If opening/closing the connections becomes a performance issue then look into setting up an external pool like pgbouncer.
“在池上调用结束后无法使用池”
池关闭后（即调用 .end() 函数后）不能重用。您需要重新创建池并丢弃旧池。
在 Lambda 中处理池化的最简单方法是根本不这样做。让您的数据库交互创建自己的连接并在完成后关闭它们。无论如何，您无法在冻结/解冻周期内维护池，因为底层 TCP 套接字将被关闭。
如果打开/关闭连接成为一个性能问题，那么考虑设置一个像 pgbouncer 这样的外部池。
So I would say that your best option is to not end the pool, unless you are shutting down the server
所以我会说你最好的选择是不要结束池，除非你关闭服务器
