Hi. dblink generates orphaned connections when we failed on performing dblink() function. # But it occurs only when dblink('conn_str', 'sql', true). See following examples.
============================================================================== $ pslq local local=# SELECT datname, procpid, current_query FROM pg_stat_activity WHERE pg_backend_pid() <> procpid; datname | procpid | current_query ---------+---------+--------------- (0 rows) local=# SELECT * FROM dblink('dbname=remote', 'SELECT * FROM no_exist') AS t1 (aid bigint); ERROR: relation "no_exist" does not exist CONTEXT: Error occurred on dblink connection named "unnamed": could not execute query. local=# SELECT datname, procpid, current_query FROM pg_stat_activity WHERE pg_backend_pid() <> procpid; datname | procpid | current_query ---------+---------+--------------- remote | 29620 | <IDLE> (1 row) local=# SELECT * FROM dblink('dbname=remote', 'SELECT * FROM no_exist') AS t1 (aid bigint); ERROR: relation "no_exist" does not exist CONTEXT: Error occurred on dblink connection named "unnamed": could not execute query. local=# SELECT datname, procpid, current_query FROM pg_stat_activity WHERE pg_backend_pid() <> procpid; datname | procpid | current_query ---------+---------+--------------- remote | 29620 | <IDLE> remote | 29630 | <IDLE> (2 rows) ============================================================================== I think that the dblink should not ereport() before PQfinish(conn) in dblink_record_internal() when we use temporary connection. Attached the brief patch to avoid above problem. Best regards, -- Tatsuhito Kasahara kasahara.tatsuh...@oss.ntt.co.jp
*** contrib/dblink/dblink.c.org 2009-09-09 13:54:35.000000000 +0900 --- contrib/dblink/dblink.c 2009-09-09 13:59:52.000000000 +0900 *************** *** 855,863 **** (PQresultStatus(res) != PGRES_COMMAND_OK && PQresultStatus(res) != PGRES_TUPLES_OK)) { - dblink_res_error(conname, res, "could not execute query", fail); if (freeconn) PQfinish(conn); MemoryContextSwitchTo(oldcontext); SRF_RETURN_DONE(funcctx); } --- 855,863 ---- (PQresultStatus(res) != PGRES_COMMAND_OK && PQresultStatus(res) != PGRES_TUPLES_OK)) { if (freeconn) PQfinish(conn); + dblink_res_error(conname, res, "could not execute query", fail); MemoryContextSwitchTo(oldcontext); SRF_RETURN_DONE(funcctx); }
-- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs