Postgresql 中文操作指南

36.2. Managing Database Connections #

本节介绍如何打开、关闭和切换数据库连接。

This section describes how to open, close, and switch database connections.

36.2.1. Connecting to the Database Server #

可以使用以下语句连接到数据库：

One connects to a database using the following statement:

EXEC SQL CONNECT TO target [AS connection-name] [USER user-name];

target 可以通过以下方式指定：

The target can be specified in the following ways:

连接目标 DEFAULT 在默认用户名下启动到默认数据库的连接。在这种情况下，不能指定单独的用户名或连接名称。

The connection target DEFAULT initiates a connection to the default database under the default user name. No separate user name or connection name can be specified in that case.

如果直接指定连接目标（即，不是作为字符串文字或变量引用），则目标组件将通过常规 SQL 解析；这意味着，例如，hostname 必须看起来像一个或多个由点分隔的 SQL 标识符，并且除非使用双引号，否则这些标识符将折叠大小写。任何 options 的值必须是 SQL 标识符、整数或变量引用。当然，通过双引号，你可以将几乎任何内容放入 SQL 标识符中。实际上，可能更不容易出错，可以使用（单引号）字符串文字或变量引用，而不是直接写入连接目标。

If you specify the connection target directly (that is, not as a string literal or variable reference), then the components of the target are passed through normal SQL parsing; this means that, for example, the hostname must look like one or more SQL identifiers separated by dots, and those identifiers will be case-folded unless double-quoted. Values of any options must be SQL identifiers, integers, or variable references. Of course, you can put nearly anything into an SQL identifier by double-quoting it. In practice, it is probably less error-prone to use a (single-quoted) string literal or a variable reference than to write the connection target directly.

还可以使用不同的方式指定用户名：

There are also different ways to specify the user name:

如上所述，参数 username 和 password 可以是 SQL 标识符、SQL 字符串文本或对字符变量的引用。

As above, the parameters username and password can be an SQL identifier, an SQL string literal, or a reference to a character variable.

如果连接目标包括任何 options ，则那些目标由用与号分隔的 keyword=value 规范组成（ & ）。可允许的关键字与 libpq 识别的关键词相同（参见 Section 34.1.2 ）。在任何 keyword 或 value 之前会忽略空格，但在一个 keyword 或 value 中或之后则不会。注意，没有办法在 value 中编写 & 。

If the connection target includes any options, those consist of keyword_=value specifications separated by ampersands (&_). The allowed key words are the same ones recognized by libpq (see Section 34.1.2). Spaces are ignored before any keyword or value, though not within or after one. Note that there is no way to write & within a value.

请注意，当指定套接字连接（带 unix: 前缀）时，主机名必须完全是 localhost。若要选择非默认套接字目录，请将目录的路径名写为目标 options 部分中 host 选项的值。

Notice that when specifying a socket connection (with the unix: prefix), the host name must be exactly localhost. To select a non-default socket directory, write the directory’s pathname as the value of a host option in the options part of the target.

connection-name 用于在一个程序中处理多个连接。如果一个程序仅使用一个连接，则可以省略它。最近打开的连接变成当前连接，并且在需要执行 SQL 语句时使用它（参见本章后面的内容）。

The connection-name is used to handle multiple connections in one program. It can be omitted if a program uses only one connection. The most recently opened connection becomes the current connection, which is used by default when an SQL statement is to be executed (see later in this chapter).

以下是 CONNECT 语句的一些示例：

Here are some examples of CONNECT statements:

EXEC SQL CONNECT TO mydb@sql.mydomain.com;

EXEC SQL CONNECT TO tcp:postgresql://sql.mydomain.com/mydb AS myconnection USER john;

EXEC SQL BEGIN DECLARE SECTION;
const char *target = "mydb@sql.mydomain.com";
const char *user = "john";
const char *passwd = "secret";
EXEC SQL END DECLARE SECTION;
 ...
EXEC SQL CONNECT TO :target USER :user USING :passwd;
/* or EXEC SQL CONNECT TO :target USER :user/:passwd; */

最后一个示例使用了上面提到的字符变量引用的特性。在后面章节中，您将看到当用冒号为 C 变量添加前缀时，如何在 SQL 语句中使用它们。

The last example makes use of the feature referred to above as character variable references. You will see in later sections how C variables can be used in SQL statements when you prefix them with a colon.

需要注意的是，SQL 标准未指定连接目标的格式。因此，如果您想开发可移植应用程序，则可能需要使用基于上述最后一个示例的内容，将连接目标字符串封装到某个地方。

Be advised that the format of the connection target is not specified in the SQL standard. So if you want to develop portable applications, you might want to use something based on the last example above to encapsulate the connection target string somewhere.

如果不受信用户可以访问尚未采用 secure schema usage pattern 的数据库，从 search_path 中移除可公开写的模式来开始每个会话。例如，将 options=-c search_path= 添加到 options 中，或在连接后发出 EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); 。此注意事项并非只针对 ECPG；它适用于执行任意 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. For example, add options=-c search_path= to options, or issue EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); after connecting. This consideration is not specific to ECPG; it applies to every interface for executing arbitrary SQL commands.

36.2.2. Choosing a Connection #

嵌入式 SQL 程序中的 SQL 语句默认在当前连接（即最近打开的连接）上执行。如果某个应用程序需要管理多个连接，则有三种方法可以处理此问题。

SQL statements in embedded SQL programs are by default executed on the current connection, that is, the most recently opened one. If an application needs to manage multiple connections, then there are three ways to handle this.

第一个选项是为每个 SQL 语句显式选择一个连接，例如：

The first option is to explicitly choose a connection for each SQL statement, for example:

EXEC SQL AT connection-name SELECT ...;

如果应用程序需要按照混合顺序使用多个连接，则此选项特别合适。

This option is particularly suitable if the application needs to use several connections in mixed order.

如果您的应用程序使用多个执行线程，则它们不能同时共享一个连接。您必须显式控制对连接的访问（使用互斥体）或为每个线程使用一个连接。

If your application uses multiple threads of execution, they cannot share a connection concurrently. You must either explicitly control access to the connection (using mutexes) or use a connection for each thread.

第二个选项是执行一条语句来切换当前连接。该语句是：

The second option is to execute a statement to switch the current connection. That statement is:

EXEC SQL SET CONNECTION connection-name;

如果要对同一个连接执行许多语句，则此选项特别方便。

This option is particularly convenient if many statements are to be executed on the same connection.

这是一个管理多个数据库连接的示例程序：

Here is an example program managing multiple database connections:

#include <stdio.h>

EXEC SQL BEGIN DECLARE SECTION;
    char dbname[1024];
EXEC SQL END DECLARE SECTION;

int
main()
{
    EXEC SQL CONNECT TO testdb1 AS con1 USER testuser;
    EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;
    EXEC SQL CONNECT TO testdb2 AS con2 USER testuser;
    EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;
    EXEC SQL CONNECT TO testdb3 AS con3 USER testuser;
    EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;

    /* This query would be executed in the last opened database "testdb3". */
    EXEC SQL SELECT current_database() INTO :dbname;
    printf("current=%s (should be testdb3)\n", dbname);

    /* Using "AT" to run a query in "testdb2" */
    EXEC SQL AT con2 SELECT current_database() INTO :dbname;
    printf("current=%s (should be testdb2)\n", dbname);

    /* Switch the current connection to "testdb1". */
    EXEC SQL SET CONNECTION con1;

    EXEC SQL SELECT current_database() INTO :dbname;
    printf("current=%s (should be testdb1)\n", dbname);

    EXEC SQL DISCONNECT ALL;
    return 0;
}

此示例将生成以下输出：

This example would produce this output:

current=testdb3 (should be testdb3)
current=testdb2 (should be testdb2)
current=testdb1 (should be testdb1)

第三个选项是声明一个链接到连接的 SQL 标识符，例如：

The third option is to declare an SQL identifier linked to the connection, for example:

EXEC SQL AT connection-name DECLARE statement-name STATEMENT;
EXEC SQL PREPARE statement-name FROM :dyn-string;

一旦将一个 SQL 标识符链接到一个连接，就可以在不使用 AT 子句的情况下执行动态 SQL。请注意，此选项的行为类似于预处理器指令，因此仅在文件中的链接才可用。

Once you link an SQL identifier to a connection, you execute dynamic SQL without an AT clause. Note that this option behaves like preprocessor directives, therefore the link is enabled only in the file.

这是一个示例程序，它使用了这个选项：

Here is an example program using this option:

#include <stdio.h>

EXEC SQL BEGIN DECLARE SECTION;
char dbname[128];
char *dyn_sql = "SELECT current_database()";
EXEC SQL END DECLARE SECTION;

int main(){
  EXEC SQL CONNECT TO postgres AS con1;
  EXEC SQL CONNECT TO testdb AS con2;
  EXEC SQL AT con1 DECLARE stmt STATEMENT;
  EXEC SQL PREPARE stmt FROM :dyn_sql;
  EXEC SQL EXECUTE stmt INTO :dbname;
  printf("%s\n", dbname);

  EXEC SQL DISCONNECT ALL;
  return 0;
}

即使默认连接是 testdb，此示例也会生成此输出：

This example would produce this output, even if the default connection is testdb:

postgres

36.2.3. Closing a Connection #

要关闭连接，请使用以下语句：

To close a connection, use the following statement:

EXEC SQL DISCONNECT [connection];

connection 可通过以下方式指定：

The connection can be specified in the following ways:

如果没有指定连接名称，则当前连接将关闭。

If no connection name is specified, the current connection is closed.

一个好的开发实践是应用程序始终显式断开与它已打开的每个连接。

It is good style that an application always explicitly disconnect from every connection it opened.