PostgreSQL数据库网络层——libpq PQconnectdbParams和PQconnectdb

以下函数处理与 PostgreSQL 后端服务器的连接。 一个应用程序可以同时打开多个后端连接。 （这样做的一个原因是访问多个数据库。）每个连接都由一个 PGconn 对象表示，该对象是从函数 PQconnectdb、PQconnectdbParams 或 PQsetdbLogin 获得的。 请注意，这些函数将始终返回一个非空对象指针，除非内存太少甚至无法分配 PGconn 对象。 在通过连接对象发送查询之前，应调用 PQstatus 函数来检查连接成功的返回值。
The following functions deal with making a connection to a PostgreSQL backend server. An application program can have several backend connections open at one time. (One reason to do that is to access more than one database.) Each connection is represented by a PGconn object, which is obtained from the function PQconnectdb, PQconnectdbParams, or PQsetdbLogin. Note that these functions will always return a non-null object pointer, unless perhaps there is too little memory even to allocate the PGconn object. The PQstatus function should be called to check the return value for a successful connection before queries are sent via the connection object.

如果不受信任的用户可以访问未采用安全模式使用模式的数据库，则通过从 search_path 中删除可公开写入的模式来开始每个会话。 可以将参数关键字选项设置为值 ​​-csearch_path=​​​。 或者，可以在连接后发出 PQexec(conn, “SELECT pg_catalog.set_config(‘search_path’, ‘’, false)”) 。 这种考虑并非特定于 libpq。 它适用于执行任意 SQL 命令的每个接口。
If untrusted users have access to a database that has not adopted a secure schema usage pattern, begin each session by removing publicly-writable schemas from search_path. One can set parameter key word options to value ​​​-csearch_path=​​​. Alternately, one can issue ​​PQexec(conn, "SELECT pg_catalog.set_config('search_path', '', false)")​​ after connecting. This consideration is not specific to libpq; it applies to every interface for executing arbitrary SQL commands.

在 Unix 上，使用打开的 libpq 连接分叉一个进程可能会导致不可预知的结果，因为父进程和子进程共享相同的套接字和操作系统资源。 出于这个原因，虽然从子进程执行 exec 以加载新的可执行文件是安全的，但不建议使用这种用法。
On Unix, forking a process with open libpq connections can lead to unpredictable results because the parent and child processes share the same sockets and operating system resources. For this reason, such usage is not recommended, though doing an exec from the child process to load a new executable is safe.

PQconnectdbParams

与数据库服务器建立新连接。

PGconn *PQconnectdbParams(const char * const *keywords,
                          const char * const *values,
                          int expand_dbname);

此函数使用取自两个以 NULL 结尾的数组的参数打开一个新的数据库连接。第一个，keywords，被定义为一个字符串数组，每个字符串都是一个关键字。第二个，values，给出每个关键词的值。与下面的 PQsetdbLogin 不同，可以在不更改函数签名的情况下扩展参数集，因此对于新的应用程序编程，首选使用此函数（或其非阻塞类似物 PQconnectStartParams 和 PQconnectPoll）。
This function opens a new database connection using the parameters taken from two NULL-terminated arrays. The first, keywords, is defined as an array of strings, each one being a key word. The second, values, gives the value for each key word. Unlike PQsetdbLogin below, the parameter set can be extended without changing the function signature, so use of this function (or its nonblocking analogs PQconnectStartParams and PQconnectPoll) is preferred for new application programming.

当前识别的参数关键字在第 34.1.2 节中列出。The currently recognized parameter key words are listed in Section 34.1.2.

传递的数组可以为空以使用所有默认参数，也可以包含一个或多个参数设置。它们的长度必须匹配。处理将在关键字数组中的第一个 NULL 条目处停止。此外，如果与非 NULL 关键字条目关联的值条目是 NULL 或空字符串，则忽略该条目并继续处理下一对数组条目。
The passed arrays can be empty to use all default parameters, or can contain one or more parameter settings. They must be matched in length. Processing will stop at the first NULL entry in the keywords array. Also, if the values entry associated with a non-NULL keywords entry is NULL or an empty string, that entry is ignored and processing continues with the next pair of array entries.

当 expand_dbname 不为零时，检查第一个 dbname 关键字的值以查看它是否是连接字符串。如果是这样，则将其“扩展”为从字符串中提取的各个连接参数。如果该值包含等号 (=) 或以 URI 方案指示符开头，则该值被视为连接字符串，而不仅仅是数据库名称。 （有关连接字符串格式的更多详细信息见第 34.1.1 节。）只有第一次出现的 dbname 会以这种方式处理；任何后续的 dbname 参数都将作为纯数据库名称处理。
When expand_dbname is non-zero, the value for the first dbname key word is checked to see if it is a connection string. If so, it is “expanded” into the individual connection parameters extracted from the string. The value is considered to be a connection string, rather than just a database name, if it contains an equal sign (=) or it begins with a URI scheme designator. (More details on connection string formats appear in Section 34.1.1.) Only the first occurrence of dbname is treated in this way; any subsequent dbname parameter is processed as a plain database name.

一般来说，参数数组是从头到尾处理的。如果任何关键字重复，则使用最后一个值（即非 NULL 或空）。当在连接字符串中找到的关键字与出现在关键字数组中的关键字冲突时，此规则尤其适用。因此，程序员可以确定数组条目是否可以被取自连接字符串的值覆盖或覆盖。出现在扩展的 dbname 条目之前的数组条目可以被连接字符串的字段覆盖，而这些字段又被出现在 dbname 之后的数组条目覆盖（但同样，只有当这些条目提供非空值时）。
In general the parameter arrays are processed from start to end. If any key word is repeated, the last value (that is not NULL or empty) is used. This rule applies in particular when a key word found in a connection string conflicts with one appearing in the keywords array. Thus, the programmer may determine whether array entries can override or be overridden by values taken from a connection string. Array entries appearing before an expanded dbname entry can be overridden by fields of the connection string, and in turn those fields are overridden by array entries appearing after dbname (but, again, only if those entries supply non-empty values).

在处理完所有数组条目和任何扩展的连接字符串后，任何未设置的连接参数都将填充默认值。如果设置了未设置参数的相应环境变量（参见第 34.15 节），则使用其值。如果环境变量也未设置，则使用参数的内置默认值。
After processing all the array entries and any expanded connection string, any connection parameters that remain unset are filled with default values. If an unset parameter’s corresponding environment variable (see Section 34.15) is set, its value is used. If the environment variable is not set either, then the parameter’s built-in default value is used.

PQconnectdbParams使用两个数组中的连接信息通过 postmaster 建立到 postgres 后端的连接。返回所有后续 libpq 调用所需的 PGconn*，如果内存分配失败，则返回 NULL。 如果返回的连接的状态字段是 CONNECTION_BAD，则某些字段可能会被清空，而不是具有有效值。无论此调用是否成功，您都应该调用 PQfinish（如果 conn 不为 NULL）。src/interfaces/libpq/fe-connect.c

* The keywords array is defined as
 *     const char *params[] = {"option1", "option2", NULL}
 * The values array is defined as
 *     const char *values[] = {"value1", "value2", NULL}
 PGconn *PQconnectdbParams(const char *const *keywords, const char *const *values, int expand_dbname) {
  PGconn     *conn = PQconnectStartParams(keywords, values, expand_dbname);
  if (conn && conn->status != CONNECTION_BAD)
    (void) connectDBComplete(conn);
  return conn;
}

PQconnectStartParams

PQconnectStartParams以非阻塞方式连接到数据库服务器。该函数用于打开与数据库服务器的连接，这样您的应用程序的执行线程就不会在远程 I/O 上被阻塞。 这种方法的要点是等待 I/O 完成可以发生在应用程序的主循环中，而不是在 PQconnectdbParams 或 PQconnectdb 内部，因此应用程序可以与其他活动并行管理此操作。

PGconn *PQconnectStartParams(const char *const *keywords, const char *const *values, int expand_dbname)
{
  PGconn     *conn;
  PQconninfoOption *connOptions;  
  conn = makeEmptyPGconn(); /* Allocate memory for the conn structure */
  if (conn == NULL) return NULL;  
  connOptions = conninfo_array_parse(keywords, values, &conn->errorMessage, true, expand_dbname); /* Parse the conninfo arrays */
  if (connOptions == NULL) {
    conn->status = CONNECTION_BAD; /* errorMessage is already set */    
    return conn;
  }  
  if (!fillPGconn(conn, connOptions)) { /* Move option values into conn structure */
    PQconninfoFree(connOptions);
    return conn;
  }  
  PQconninfoFree(connOptions); /* Free the option info - all is in conn now */  
  if (!connectOptions2(conn)) return conn; /* Compute derived options */  
  if (!connectDBStart(conn)) { /* Connect to the database */
    /* Just in case we failed to set it in connectDBStart */
    conn->status = CONNECTION_BAD;
  }
  return conn;
}

These three functions are used to open a connection to a database server such that your application’s thread of execution is not blocked on remote I/O whilst doing so. The point of this approach is that the waits for I/O to complete can occur in the application’s main loop, rather than down inside PQconnectdbParams or PQconnectdb, and so the application can manage this operation in parallel with other activities.

使用 PQconnectStartParams，数据库连接是使用从关键字和值数组中获取的参数进行的，并由 expand_dbname 控制，如上文对 PQconnectdbParams 所述。
With PQconnectStartParams, the database connection is made using the parameters taken from the keywords and values arrays, and controlled by expand_dbname, as described above for PQconnectdbParams.

只要满足一些限制条件，PQconnectStartParams 和 PQconnectStart 和 PQconnectPoll 都不会阻塞：

• 必须适当使用 hostaddr 参数以防止进行 DNS 查询。 有关详细信息，请参阅第 34.1.2 节中有关此参数的文档。The hostaddr parameter must be used appropriately to prevent DNS queries from being made. See the documentation of this parameter in Section 34.1.2 for details.
• 如果您调用 PQtrace，请确保您跟踪的流对象不会阻塞。If you call PQtrace, ensure that the stream object into which you trace will not block.
• 在调用 PQconnectPoll 之前，您必须确保套接字处于适当的状态，如下所述。You must ensure that the socket is in the appropriate state before calling PQconnectPoll, as described below.

要开始一个非阻塞连接请求，请调用 PQconnectStart 或 PQconnectStartParams。如果结果为 null，则 libpq 一直无法分配新的 PGconn 结构。否则，返回一个有效的 PGconn 指针（虽然还没有表示到数据库的有效连接）。接下来调用 PQstatus(conn)。如果结果是 CONNECTION_BAD，则连接尝试已经失败，通常是因为连接参数无效。
To begin a nonblocking connection request, call PQconnectStart or PQconnectStartParams. If the result is null, then libpq has been unable to allocate a new PGconn structure. Otherwise, a valid PGconn pointer is returned (though not yet representing a valid connection to the database). Next call PQstatus(conn). If the result is CONNECTION_BAD, the connection attempt has already failed, typically because of invalid connection parameters.

如果 PQconnectStart 或 PQconnectStartParams 成功，下一阶段是轮询 libpq 以便它可以继续连接序列。使用 PQsocket(conn) 获取数据库连接底层套接字的描述符。 （注意：不要假设套接字在 PQconnectPoll 调用中保持不变。）因此循环：如果 PQconnectPoll(conn) 最后返回 PGRES_POLLING_READING，请等待套接字准备好读取（如 select()、poll() 或类似的系统功能）。然后再次调用 PQconnectPoll(conn)。相反，如果 PQconnectPoll(conn) 最后返回 PGRES_POLLING_WRITING，则等到套接字准备好写入，然后再次调用 PQconnectPoll(conn)。在第一次迭代中，即如果您还没有调用 PQconnectPoll，表现得好像它最后一次返回了 PGRES_POLLING_WRITING。继续这个循环，直到 PQconnectPoll(conn) 返回 PGRES_POLLING_FAILED，表示连接过程失败，或 PGRES_POLLING_OK，表示连接成功。
If PQconnectStart or PQconnectStartParams succeeds, the next stage is to poll libpq so that it can proceed with the connection sequence. Use PQsocket(conn) to obtain the descriptor of the socket underlying the database connection. (Caution: do not assume that the socket remains the same across PQconnectPoll calls.) Loop thus: If PQconnectPoll(conn) last returned PGRES_POLLING_READING, wait until the socket is ready to read (as indicated by select(), poll(), or similar system function). Then call PQconnectPoll(conn) again. Conversely, if PQconnectPoll(conn) last returned PGRES_POLLING_WRITING, wait until the socket is ready to write, then call PQconnectPoll(conn) again. On the first iteration, i.e., if you have yet to call PQconnectPoll, behave as if it last returned PGRES_POLLING_WRITING. Continue this loop until PQconnectPoll(conn) returns PGRES_POLLING_FAILED, indicating the connection procedure has failed, or PGRES_POLLING_OK, indicating the connection has been successfully made.

在连接过程中的任何时候，都可以通过调用 PQstatus 来检查连接的状态。如果此调用返回 CONNECTION_BAD，则连接过程失败；如果调用返回 CONNECTION_OK，则连接准备就绪。这两种状态都可以从 PQconnectPoll 的返回值中检测到，如上所述。在异步连接过程期间（并且仅在期间）也可能出现其他状态。这些指示连接过程的当前阶段，并且可能有助于向用户提供反馈。这些状态是：
At any time during connection, the status of the connection can be checked by calling PQstatus. If this call returns CONNECTION_BAD, then the connection procedure has failed; if the call returns CONNECTION_OK, then the connection is ready. Both of these states are equally detectable from the return value of PQconnectPoll, described above. Other states might also occur during (and only during) an asynchronous connection procedure. These indicate the current stage of the connection procedure and might be useful to provide feedback to the user for example. These statuses are:

状态	用途
CONNECTION_STARTED	等待建立连接	Waiting for connection to be made.
CONNECTION_MADE	连接正常； 等待发送	Connection OK; waiting to send.
CONNECTION_AWAITING_RESPONSE	等待来自服务器的响应	Waiting for a response from the server.
CONNECTION_AUTH_OK	收到认证； 等待后端启动完成	Received authentication; waiting for backend start-up to finish.
CONNECTION_SSL_STARTUP	协商 SSL 加密	Negotiating SSL encryption.
CONNECTION_SETENV	协商环境驱动的参数设置	Negotiating environment-driven parameter settings.
CONNECTION_CHECK_WRITABLE	检查连接是否能够处理写事务	Checking if connection is able to handle write transactions.
CONNECTION_CONSUME	在连接上使用任何剩余的响应消息	Consuming any remaining response messages on connection.

请注意，尽管这些常量将保留（为了保持兼容性），但应用程序绝不应依赖这些以特定顺序发生的，或根本不依赖这些常量，或始终是这些记录值之一的状态。 应用程序可能会执行以下操作：Note that, although these constants will remain (in order to maintain compatibility), an application should never rely upon these occurring in a particular order, or at all, or on the status always being one of these documented values. An application might do something like this:

switch(PQstatus(conn)){
        case CONNECTION_STARTED:
            feedback = "Connecting...";
            break;
        case CONNECTION_MADE:
            feedback = "Connected to server...";
            break;
        ...
        default:
            feedback = "Connecting...";
}

使用 PQconnectPoll 时忽略 connect_timeout 连接参数； 应用程序有责任决定是否已经过过多的时间。 否则，PQconnectStart 后跟一个 PQconnectPoll 循环等效于 PQconnectdb。
The connect_timeout connection parameter is ignored when using PQconnectPoll; it is the application’s responsibility to decide whether an excessive amount of time has elapsed. Otherwise, PQconnectStart followed by a PQconnectPoll loop is equivalent to PQconnectdb.

请注意，当 PQconnectStart 或 PQconnectStartParams 返回一个非空指针时，您必须在完成它后调用 PQfinish，以便处理结构和任何相关的内存块。 即使连接尝试失败或被放弃，也必须这样做。
Note that when PQconnectStart or PQconnectStartParams returns a non-null pointer, you must call PQfinish when you are finished with it, in order to dispose of the structure and any associated memory blocks. This must be done even if the connection attempt fails or is abandoned.

PQconnectdb

PQconnectdb与数据库服务器建立新连接。此函数使用从字符串 conninfo 获取的参数打开一个新的数据库连接。传递的字符串可以为空以使用所有默认参数，也可以包含一个或多个以空格分隔的参数设置，也可以包含一个 URI。 有关详细信息，请参阅第 34.1.1 节。

PGconn *PQconnectdb(const char *conninfo){
  PGconn     *conn = PQconnectStart(conninfo);
  if (conn && conn->status != CONNECTION_BAD)
    (void) connectDBComplete(conn);
  return conn;
}

PQconnectStart

使用字符串中的连接信息开始通过 postmaster 建立与 postgres 后端的连接。有关字符串格式的定义，请参阅 PQconnectdb 的注释。返回一个 PGconn*。 如果返回 NULL，则表示发生了 malloc 错误，您不应尝试继续此连接。 如果返回的连接的状态字段为 CONNECTION_BAD，则发生错误。 在这种情况下，您应该对结果调用 PQfinish（可能首先检查错误消息）。 如果发生这种情况，结构的其他字段可能无效。 如果状态字段不是 CONNECTION_BAD，则此阶段已成功 - 调用 PQconnectPoll，使用 select(2) 查看何时需要。

PGconn *PQconnectStart(const char *conninfo) {
  PGconn     *conn;  
  conn = makeEmptyPGconn(); /* Allocate memory for the conn structure */
  if (conn == NULL) return NULL;  
  if (!connectOptions1(conn, conninfo)) return conn; /* Parse the conninfo string */  
  if (!connectOptions2(conn)) return conn; /* Compute derived options */  
  if (!connectDBStart(conn)) { /* Connect to the database */    
    conn->status = CONNECTION_BAD; /* Just in case we failed to set it in connectDBStart */
  }
  return conn;
}

connectDBStart

connectDBStart函数开始与后端建立连接的过程。

static int connectDBStart(PGconn *conn) {
  if (!conn) return 0;
  if (!conn->options_valid) goto connect_errReturn;
  /* Check for bad linking to backend-internal versions of src/common
   * functions (see comments in link-canary.c for the reason we need this).
   * Nobody but developers should see this message, so we don't bother
   * translating it. */
  if (!pg_link_canary_is_frontend()) {
    printfPQExpBuffer(&conn->errorMessage, "libpq is incorrectly linked to backend functions\n");
    goto connect_errReturn;
  }
  
  conn->inStart = conn->inCursor = conn->inEnd = 0; /* Ensure our buffers are empty */
  conn->outCount = 0;
  /* Ensure errorMessage is empty, too.  PQconnectPoll will append messages
   * to it in the process of scanning for a working server.  Thus, if we
   * fail to connect to multiple hosts, the final error message will include
   * details about each failure. */
  resetPQExpBuffer(&conn->errorMessage);
  /* Set up to try to connect to the first host.  (Setting whichhost = -1 is
   * a bit of a cheat, but PQconnectPoll will advance it to 0 before
   * anything else looks at it.) */
  conn->whichhost = -1;
  conn->try_next_addr = false;
  conn->try_next_host = true;
  conn->status = CONNECTION_NEEDED;
  /* The code for processing CONNECTION_NEEDED state is in PQconnectPoll(),
   * so that it can easily be re-executed if needed again during the
   * asynchronous startup process.  However, we must run it once here,
   * because callers expect a success return from this routine to mean that
   * we are in PGRES_POLLING_WRITING connection state. */
  if (PQconnectPoll(conn) == PGRES_POLLING_WRITING) return 1;
connect_errReturn:
  /* If we managed to open a socket, close it immediately rather than waiting till PQfinish.  (The application cannot have gotten the socket from PQsocket yet, so this doesn't risk breaking anything.) */
  pqDropConnection(conn, true);
  conn->status = CONNECTION_BAD;
  return 0;
}

PQconnectPoll

PQconnectPoll函数轮询异步连接

connectDBComplete

PQconnectStartParams和PQconnectdb函数都通过connectDBComplete函数阻止并完成连接。

static int connectDBComplete(PGconn *conn) {
  PostgresPollingStatusType flag = PGRES_POLLING_WRITING;
  time_t    finish_time = ((time_t) -1);
  int      timeout = 0;
  int      last_whichhost = -2;  /* certainly different from whichhost */
  struct addrinfo *last_addr_cur = NULL;
  if (conn == NULL || conn->status == CONNECTION_BAD) return 0;
  
  if (conn->connect_timeout != NULL) { /* Set up a time limit, if connect_timeout isn't zero. */  
    if (!parse_int_param(conn->connect_timeout, &timeout, conn, "connect_timeout")) {      
      conn->status = CONNECTION_BAD; /* mark the connection as bad to report the parsing failure */
      return 0;
    }
    if (timeout > 0) {
      /* Rounding could cause connection to fail unexpectedly quickly; to prevent possibly waiting hardly-at-all, insist on at least two seconds.
       */
      if (timeout < 2) timeout = 2;
    } else          /* negative means 0 */
      timeout = 0;
  }

  for (;;) {
    int      ret = 0;
    /* (Re)start the connect_timeout timer if it's active and we are considering a different host than we were last time through.  If we've already succeeded, though, needn't recalculate. */
    if (flag != PGRES_POLLING_OK && timeout > 0 && (conn->whichhost != last_whichhost || conn->addr_cur != last_addr_cur)) {
      finish_time = time(NULL) + timeout;
      last_whichhost = conn->whichhost;
      last_addr_cur = conn->addr_cur;
    }

    /* Wait, if necessary.  Note that the initial state (just after PQconnectStart) is to wait for the socket to select for writing. */
    switch (flag) {
      case PGRES_POLLING_OK: /* Reset stored error messages since we now have a working connection */      
        resetPQExpBuffer(&conn->errorMessage);
        return 1;    /* success! */
      case PGRES_POLLING_READING:
        ret = pqWaitTimed(1, 0, conn, finish_time);
        if (ret == -1) { /* hard failure, eg select() problem, aborts everything */        
          conn->status = CONNECTION_BAD;
          return 0;
        }
        break;
      case PGRES_POLLING_WRITING:
        ret = pqWaitTimed(0, 1, conn, finish_time);
        if (ret == -1) { /* hard failure, eg select() problem, aborts everything */      
          conn->status = CONNECTION_BAD;
          return 0;
        }
        break;
      default: /* Just in case we failed to set it in PQconnectPoll */      
        conn->status = CONNECTION_BAD;
        return 0;
    }

    if (ret == 1) {      /* connect_timeout elapsed connect_timeout 已过 */  
      /* Give up on current server/address, try the next one. 放弃当前服务器/地址，尝试下一个。*/
      conn->try_next_addr = true;
      conn->status = CONNECTION_NEEDED;
    }
    /* Now try to advance the state machine. 现在尝试推进状态机。 */
    flag = PQconnectPoll(conn);
  }
}

​​https://www.postgresql.org/docs/current/libpq-connect.html​​ https://www.postgresql.org/docs/current/libpq.html