PostgreSQL dblink with named connections

dblink

doesn't 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 seems to be connected fine, but my connection is not available when I try to use it. Any ideas on how to get this to work with named connections?

Unnamed with connstr Works Fine:

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 doesn't 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 doesn't work:

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

      

+3


source to share


1 answer


I have a working setup with unnamed connections.

What you call " No Name " in your question actually has a name parameter in it. You are mixing the two. Try this without 'myconn'

:

SELECT *
FROM   dblink('SELECT * FROM testtable'
        ) AS testtable (testtable_id integer, testtable_name text);

      

And remember that establishing a connection and using it must be done in the same session.




But I honestly can't find what happened to your connection name . I've run some tests and everything looks correct. I have tested PostgreSQL 9.1.

The error message means that dblink is expecting a connstr

. This only happens if the first parameter does not match the existing one connname

. Long story short: no connection 'myconn'

found, which makes me suspect that you are not calling dblink()

in the same session as dblink_connect()

.

+2


source







All Articles