PostgreSQL dblink with named connections

Question

dblink does 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?

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 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:

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

Solution

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':

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.

The error message implies that dblink expects a connstr. That only happens if the first parameter does not match any connname in existence In short: the connection 'myconn' is not found - which makes me suspect that you are not calling dblink() in the same session as dblink_connect().

Comments

[bendiy]

I removed 'myconn' from the Unnamed section. I did not mean to include that.

[bendiy]

Thanks. The session appears to be the issue. Running the queries in different windows in pgAdmin appears to treat each window as a separate session. Running the queries in the same window one after the other is working.

[Erwin Brandstetter]

@bendiy: Yes, in pgAdmin every SQL editor window runs a separate session. Temporary tables or dblink connections are only visible in the same window (session).