Re: SQL/MED compatible connection manager - Mailing list pgsql-hackers

Peter Eisentraut wrote:
> If I read this right, SQL/MED requires option names to be unique for a
> server.  To this needs to be rethought.
>

Attached is another revision of the connection manager, notable changes:
* Generic options are now standard compliant -- no duplicate option names,
  possibility to alter individual options.
* information_schema views, added to the end of the file, using chapter
  numbers from part 5 of the standard.  Qualified names are used in fdw
  and server names unless directly visible.
* Added documentation for the connection lookup functions in "System
  Administration Functions". Also documented the new system catalogs.
* Example dblink implementation. Added functions dblink_connect_s,
  dblink_exec_s, dblink_s that operate on predefined foreign servers.
  No documentation or regression tests at the moment. Some examples at:
  http://wiki.postgresql.org/wiki/SqlMedConnectionManager#dblink

I'll also change the commitfest status to "Pending review" -- the features
are complete and I'm not actively working on the code.

regards,
Martin

*** a/contrib/dblink/dblink.c
--- b/contrib/dblink/dblink.c
***************
*** 46,51 ****
--- 46,52 ----
  #include "catalog/pg_type.h"
  #include "executor/executor.h"
  #include "executor/spi.h"
+ #include "foreign/foreign.h"
  #include "lib/stringinfo.h"
  #include "miscadmin.h"
  #include "nodes/execnodes.h"
***************
*** 77,83 **** typedef struct remoteConn
  /*
   * Internal declarations
   */
! static Datum dblink_record_internal(FunctionCallInfo fcinfo, bool is_async, bool do_get);
  static remoteConn *getConnectionByName(const char *name);
  static HTAB *createConnHash(void);
  static void createNewConnection(const char *name, remoteConn * rconn);
--- 78,85 ----
  /*
   * Internal declarations
   */
! static Datum dblink_record_internal(FunctionCallInfo fcinfo, bool is_async, bool do_get, bool use_server);
! static Datum dblink_exec_internal(FunctionCallInfo fcinfo, bool use_server);
  static remoteConn *getConnectionByName(const char *name);
  static HTAB *createConnHash(void);
  static void createNewConnection(const char *name, remoteConn * rconn);
***************
*** 96,101 **** static char *generate_relation_name(Oid relid);
--- 98,104 ----
  static void dblink_connstr_check(const char *connstr);
  static void dblink_security_check(PGconn *conn, remoteConn *rconn);
  static void dblink_res_error(const char *conname, PGresult *res, const char *dblink_context_msg, bool fail);
+ static char *get_connect_string(const char *servername);

  /* Global */
  static remoteConn *pconn = NULL;
***************
*** 165,171 **** typedef struct remoteConnHashEnt
              } \
              else \
              { \
!                 connstr = conname_or_str; \
                  dblink_connstr_check(connstr); \
                  conn = PQconnectdb(connstr); \
                  if (PQstatus(conn) == CONNECTION_BAD) \
--- 168,177 ----
              } \
              else \
              { \
!                 if (use_server) \
!                     connstr = get_connect_string(conname_or_str); \
!                 else \
!                     connstr = conname_or_str; \
                  dblink_connstr_check(connstr); \
                  conn = PQconnectdb(connstr); \
                  if (PQstatus(conn) == CONNECTION_BAD) \
***************
*** 204,209 **** typedef struct remoteConnHashEnt
--- 210,246 ----
      } while (0)

  /*
+  * Create a persistent connection to another database - predefined
+  * foreign server
+  */
+ PG_FUNCTION_INFO_V1(dblink_connect_server);
+ Datum
+ dblink_connect_server(PG_FUNCTION_ARGS)
+ {
+     char       *servername = NULL;
+     char       *connname = NULL;
+     char       *connstr = NULL;
+
+     if (PG_NARGS() == 2)
+     {
+         servername = text_to_cstring(PG_GETARG_TEXT_PP(1));
+         connname = text_to_cstring(PG_GETARG_TEXT_PP(0));
+     }
+     else if (PG_NARGS() == 1)
+         servername = text_to_cstring(PG_GETARG_TEXT_PP(0));
+
+     connstr = get_connect_string(servername);
+
+     if (!connname)
+         return DirectFunctionCall1(dblink_connect,
+                                    CStringGetTextDatum(connstr));
+
+     return DirectFunctionCall2(dblink_connect,
+                                CStringGetTextDatum(connname),
+                                CStringGetTextDatum(connstr));
+ }
+
+ /*
   * Create a persistent connection to another database
   */
  PG_FUNCTION_INFO_V1(dblink_connect);
***************
*** 689,713 **** PG_FUNCTION_INFO_V1(dblink_record);
  Datum
  dblink_record(PG_FUNCTION_ARGS)
  {
!     return dblink_record_internal(fcinfo, false, false);
  }

  PG_FUNCTION_INFO_V1(dblink_send_query);
  Datum
  dblink_send_query(PG_FUNCTION_ARGS)
  {
!     return dblink_record_internal(fcinfo, true, false);
  }

  PG_FUNCTION_INFO_V1(dblink_get_result);
  Datum
  dblink_get_result(PG_FUNCTION_ARGS)
  {
!     return dblink_record_internal(fcinfo, true, true);
  }

  static Datum
! dblink_record_internal(FunctionCallInfo fcinfo, bool is_async, bool do_get)
  {
      FuncCallContext *funcctx;
      TupleDesc    tupdesc = NULL;
--- 726,757 ----
  Datum
  dblink_record(PG_FUNCTION_ARGS)
  {
!     return dblink_record_internal(fcinfo, false, false, false);
! }
!
! PG_FUNCTION_INFO_V1(dblink_record_server);
! Datum
! dblink_record_server(PG_FUNCTION_ARGS)
! {
!     return dblink_record_internal(fcinfo, false, false, true);
  }

  PG_FUNCTION_INFO_V1(dblink_send_query);
  Datum
  dblink_send_query(PG_FUNCTION_ARGS)
  {
!     return dblink_record_internal(fcinfo, true, false, false);
  }

  PG_FUNCTION_INFO_V1(dblink_get_result);
  Datum
  dblink_get_result(PG_FUNCTION_ARGS)
  {
!     return dblink_record_internal(fcinfo, true, true, false);
  }

  static Datum
! dblink_record_internal(FunctionCallInfo fcinfo, bool is_async, bool do_get, bool use_server)
  {
      FuncCallContext *funcctx;
      TupleDesc    tupdesc = NULL;
***************
*** 1102,1111 **** dblink_error_message(PG_FUNCTION_ARGS)
--- 1146,1168 ----
  /*
   * Execute an SQL non-SELECT command
   */
+ PG_FUNCTION_INFO_V1(dblink_exec_server);
+ Datum
+ dblink_exec_server(PG_FUNCTION_ARGS)
+ {
+     return dblink_exec_internal(fcinfo, true);
+ }
+
  PG_FUNCTION_INFO_V1(dblink_exec);
  Datum
  dblink_exec(PG_FUNCTION_ARGS)
  {
+     return dblink_exec_internal(fcinfo, false);
+ }
+
+ static Datum
+ dblink_exec_internal(FunctionCallInfo fcinfo, bool use_server)
+ {
      char       *msg;
      PGresult   *res = NULL;
      text       *sql_cmd_status = NULL;
***************
*** 2358,2360 **** dblink_res_error(const char *conname, PGresult *res, const char *dblink_context_
--- 2415,2442 ----
           errcontext("Error occurred on dblink connection named \"%s\": %s.",
                      dblink_context_conname, dblink_context_msg)));
  }
+
+ /*
+  * Obtain the connection string for the foreign server.
+  */
+ static char *
+ get_connect_string(const char *servername)
+ {
+     Oid            serverid;
+     ListCell   *cell;
+
+     /* look up the connection string */
+     serverid = ForeignServerNameGetServerid(stringToQualifiedNameList(servername), false);
+     foreach (cell, GetRemoteConnectionInfo(serverid, GetUserId()))
+     {
+         DefElem *def = lfirst(cell);
+
+         if (strcmp(def->defname, "datasource") == 0)
+             return strVal(def->arg);
+     }
+
+     ereport(ERROR,
+             (errcode(ERRCODE_UNDEFINED_PARAMETER),
+              errmsg("could not find \"datasource\" in connection definition")));
+     return NULL;
+ }
*** a/contrib/dblink/dblink.h
--- b/contrib/dblink/dblink.h
***************
*** 40,50 ****
--- 40,52 ----
   * External declarations
   */
  extern Datum dblink_connect(PG_FUNCTION_ARGS);
+ extern Datum dblink_connect_server(PG_FUNCTION_ARGS);
  extern Datum dblink_disconnect(PG_FUNCTION_ARGS);
  extern Datum dblink_open(PG_FUNCTION_ARGS);
  extern Datum dblink_close(PG_FUNCTION_ARGS);
  extern Datum dblink_fetch(PG_FUNCTION_ARGS);
  extern Datum dblink_record(PG_FUNCTION_ARGS);
+ extern Datum dblink_record_server(PG_FUNCTION_ARGS);
  extern Datum dblink_send_query(PG_FUNCTION_ARGS);
  extern Datum dblink_get_result(PG_FUNCTION_ARGS);
  extern Datum dblink_get_connections(PG_FUNCTION_ARGS);
***************
*** 52,57 **** extern Datum dblink_is_busy(PG_FUNCTION_ARGS);
--- 54,60 ----
  extern Datum dblink_cancel_query(PG_FUNCTION_ARGS);
  extern Datum dblink_error_message(PG_FUNCTION_ARGS);
  extern Datum dblink_exec(PG_FUNCTION_ARGS);
+ extern Datum dblink_exec_server(PG_FUNCTION_ARGS);
  extern Datum dblink_get_pkey(PG_FUNCTION_ARGS);
  extern Datum dblink_build_sql_insert(PG_FUNCTION_ARGS);
  extern Datum dblink_build_sql_delete(PG_FUNCTION_ARGS);
*** a/contrib/dblink/dblink.sql.in
--- b/contrib/dblink/dblink.sql.in
***************
*** 15,20 **** RETURNS text
--- 15,31 ----
  AS 'MODULE_PATHNAME','dblink_connect'
  LANGUAGE C STRICT;

+ -- dblink_connect_s restricts users to predefined servers
+ CREATE OR REPLACE FUNCTION dblink_connect_s (text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_connect_server'
+ LANGUAGE C STRICT;
+
+ CREATE OR REPLACE FUNCTION dblink_connect_s (text, text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_connect_server'
+ LANGUAGE C STRICT;
+
  -- dblink_connect_u allows non-superusers to use
  -- non-password authenticated connections, but initially
  -- privileges are revoked from public
***************
*** 121,126 **** RETURNS setof record
--- 132,147 ----
  AS 'MODULE_PATHNAME','dblink_record'
  LANGUAGE C STRICT;

+ CREATE OR REPLACE FUNCTION dblink_s (text, text)
+ RETURNS setof record
+ AS 'MODULE_PATHNAME','dblink_record_server'
+ LANGUAGE C STRICT;
+
+ CREATE OR REPLACE FUNCTION dblink_s (text, text, boolean)
+ RETURNS setof record
+ AS 'MODULE_PATHNAME','dblink_record_server'
+ LANGUAGE C STRICT;
+
  CREATE OR REPLACE FUNCTION dblink_exec (text, text)
  RETURNS text
  AS 'MODULE_PATHNAME','dblink_exec'
***************
*** 141,146 **** RETURNS text
--- 162,177 ----
  AS 'MODULE_PATHNAME','dblink_exec'
  LANGUAGE C STRICT;

+ CREATE OR REPLACE FUNCTION dblink_exec_s (text, text)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_exec_server'
+ LANGUAGE C STRICT;
+
+ CREATE OR REPLACE FUNCTION dblink_exec_s (text, text, boolean)
+ RETURNS text
+ AS 'MODULE_PATHNAME','dblink_exec_server'
+ LANGUAGE C STRICT;
+
  CREATE TYPE dblink_pkey_results AS (position int, colname text);

  CREATE OR REPLACE FUNCTION dblink_get_pkey (text)