具有命名连接的 PostgreSQL dblink
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9876573/
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
PostgreSQL dblink with named connections
提问by bendiy
dblinkdoes not seem to work when I use a named connection to a remote server or an unnamed connection and disconnect. It works fine if I use an unnamed connection with a connection string in dblink(). It appears to connect fine, but my connection is not available when I try to use it. Any ideas on how to get this working with named connections?
dblink当我使用到远程服务器的命名连接或未命名连接并断开连接时，似乎不起作用。如果我在 dblink() 中使用带有连接字符串的未命名连接，它工作正常。它似乎连接正常，但是当我尝试使用它时我的连接不可用。关于如何使用命名连接进行工作的任何想法？
Unnamed with connstr Works Fine:
未命名的 connstr 工作正常：
SELECT testtable.*
FROM dblink('dbname=testdb port=5432 host=192.168.1.1 user=usr password=pw'
,'SELECT * FROM testtable')
AS testtable(testtable_id integer, testtable_name text);
Returns: Two columns as expected.
返回：如预期的两列。
Named Does not Work:
命名不起作用：
Connect:
连接：
SELECT dblink_connect('myconn'
,'dbname=testdb port=5432 host=192.168.1.1 user=usr password=pw');
Returns: "OK"
返回：“好的”
Query:
询问：
SELECT testtable.* FROM dblink('myconn', 'SELECT * FROM testtable')
AS testtable(testtable_id integer, testtable_name text);
Returns:
返回：
ERROR: could not establish connection
DETAIL: missing "=" after "myconn" in connection info string
********** Error **********
ERROR: could not establish connection
SQL state: 08001
Detail: missing "=" after "myconn" in connection info string
Disconnect:
断开：
SELECT dblink_disconnect('myconn');
Returns:
返回：
ERROR: connection "myconn" not available
********** Error **********
ERROR: connection "myconn" not available
SQL state: 08003
Unnamed with _connect and _disconnect Does not Work:
未命名的 _connect 和 _disconnect 不起作用：
Connect:
连接：
SELECT dblink_connect('dbname=testdb port=5432 host=192.168.1.1
user=usr password=pw');
Returns: "OK"
返回：“好的”
Query:
询问：
SELECT testtable.* FROM dblink('SELECT * FROM testtable')
AS testtable(testtable_id integer, testtable_name text);
Returns:
返回：
ERROR: connection not available
********** Error **********
ERROR: connection not available
SQL state: 08003
Disconnect:
断开：
SELECT dblink_disconnect();
Returns:
返回：
ERROR: connection not available
********** Error **********
ERROR: connection not available
SQL state: 08003
采纳答案by Erwin Brandstetter
I have a working setup with unnamed connections.
我有一个带有未命名连接的工作设置。
What you call "Unnamed" in your question, actually has a name parameter in it. You are confusing the two variants there. Try that without 'myconn':
您在问题中所说的“未命名”实际上有一个名称参数。你混淆了那里的两个变体。尝试没有'myconn'：
SELECT *
FROM dblink('SELECT * FROM testtable'
) AS testtable (testtable_id integer, testtable_name text);
And remember that establishing the connection and using it has to happen in the same session.
请记住，建立连接和使用它必须在同一个会话中进行。
But I honestly cannot find what's wrong with your named connection. I have run a few tests and everything looks correct. I tested with PostgreSQL 9.1.
但老实说，我找不到您命名的 connection有什么问题。我已经运行了一些测试，一切看起来都正确。我用 PostgreSQL 9.1 测试过。
The error message implies that dblink expects a connstr. That only happens if the first parameter does not match any connnamein existence In short: the connection 'myconn'is not found - which makes me suspect that you are not calling dblink()in the same sessionas dblink_connect().
该错误消息暗示 dblink 需要一个connstr. 如果第一个参数不匹配任何只发生connname在存在简而言之：连接'myconn'没有发现-这让我怀疑你是不是要求dblink()在同一会话的dblink_connect()。
