dblink generates orphaned connections - Mailing list pgsql-bugs

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.tatsuhito@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);
          }