您正在查看: 标签 客户端 下的文章

PostgreSQL内部概览连接是如何建立起来的

44.2. 连接是如何建立起来的

PostgreSQL 是用一个简单的"每用户一进程"的"process per user"模型实现的。 在这种模式里一个客户端进程(client process) 只与恰好一个服务器进程(server process)连接。 因为不知道具体要建立多少个连接,所以不得不利用一个主进程(master process) 在每次连接请求时派生出一个新的服务器进程来 这个主进程叫做postgres, 它监听着一个特定的 TCP/IP 端口等待进来的连接。 每当检测到一个连接请求时,postgres进程派生出一个新的服务器进程。 服务器进程之间使用信号灯(semaphores) 和共享内存(shared memory)进行通讯, 以确保在并发的数据访问过程中的数据完整性。

在这种模式里一个客户端进程只与恰好一个服务器进程连接。因为不知道具体要建立多少个连接,所以不得不利用一个主进程在每次连接请求时派生出一个新的服务器进程来。这个主进程叫做 postgres ,它监听着一个特定的 TCP/IP 端口等待进来的连接。每当检测到一个连接请求时,postgres 进程派生出一个新的服务器进程。服务器进程之间使用信号灯和共享内存进行通讯,以确保在并发的数据访问过程中的数据完整性。</P><P>    客户端进程可以是任何理解 PostgreSQL 协议(在Chapter 46描述)的程序。
许多客户端都是基于 C 语言库<SPAN>libpq</SPAN>的程序,
但是也存在几个对协议之独立的实现,比如 Java<SPAN>JDBC</SPAN>驱动。</P><P>    一旦建立起来连接,客户端进程就可以向后端(backend)(服务器)进程发送查询了。
查询是通过纯文本传输的,也就是说在前端(frontend)(客户端)不做任何分析处理。
服务器分析查询,创建执行规划(execution plan),
执行该规划并且通过已经建立起来的连接把检索出来的数据行返回给客户端。</P></DIV>

Server Programming InterfaceSELECT INTO

SELECT INTO

Name

SELECT INTO -- 从一条查询的结果中定义一个新表

Synopsis

[ WITH [ RECURSIVE ] with_query [, ...] ]
SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]* | expression [ [ AS ] output_name ] [, ...]INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table[ FROM from_item [, ...] ][ WHERE condition ][ GROUP BY expression [, ...] ][ HAVING condition [, ...] ][ WINDOW window_name AS ( window_definition ) [, ...] ][ { UNION | INTERSECT | EXCEPT } [ ALL ] select ][ ORDER BY expression [ ASC | DESC | USING operator ] [ NULLS { FIRST | LAST } ] [, ...] ][ LIMIT { count | ALL } ][ OFFSET start [ ROW | ROWS ] ][ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ][ FOR { UPDATE | SHARE } [ OF table_name [, ...] ] [ NOWAIT ] [...] ]

描述

SELECT INTO从一个查询的计算结果中创建一个新表。 数据并不返回给客户端,这一点和普通的SELECT不同。 新表的字段具有和SELECT的输出字段相同的名字和数据类型。

参数

TEMPORARY or TEMP

如果声明了这个关键字,那么该表是作为一个临时表创建的。请参考CREATE TABLE获取细节。

new_table

要创建的表的名字(可以有模式修饰)

所有其它输入的域都在 SELECT中有详细描述。

注意

CREATE TABLE AS的作用和SELECT INTO类似。 建议使用CREATE TABLE AS语法, 因为SELECT INTO不是标准语法。实际上,它是不能在ECPGPL/pgSQL中使用的, 因为它们对INTO子句的解释是不同的。而且,CREATE TABLE AS提供了SELECT INTO所提供功能的超集。

PostgreSQL以前,SELECT INTO创建的表总是缺省包含OID。 到了PostgreSQL8.1,这不再是缺省了, 要想在新表中包含OID,可以打开default_with_oids配置参数或使用WITH OIDS子句。

例子

创建一个新表films_recent,它的值包含来自films的最近的条目:

SELECT * INTO films_recent FROM films WHERE date_prod >= '2002-01-01';

兼容性

SQL标准用SELECT INTO表示选取数值到一个宿主程序的标量变量中, 而不是创建一个新表。这种用法实际上就是在ECPG(参阅Chapter 33)和 PL/pgSQL(参阅Chapter 39)里的用途。 PostgreSQL用SELECT INTO创建表是历史原因。在新代码里最好使用CREATE TABLE AS实现这个目的。

又见

CREATE TABLE AS

Server Programming InterfacePREPARE

PREPARE

Name

PREPARE -- 创建一个预备语句

Synopsis

PREPARE name [ ( data_type [, ...] ) ] AS statement

描述

PREPARE创建一个预备语句。一个预备语句是服务器端的对象,可以用于优化性能。 在执行PREPARE语句的时候,指定的查询被分析、重写、规划。 当随后发出EXECUTE语句的时候,预备语句就只需要执行了。 因此,分析、重写、规划阶段都只执行一次,而不是每次都要执行一次。

预备语句可以接受参数:在它执行的时候替换到查询中的数值。 可以在一个预备语句里按照位置来引用参数,比如$1,$2等。 可以指定一个相应的参数数据类型列表。 如果一个参数的数据类型没有被指定或声明为unknown, 那么其类型将根据该参数所使用的实际上下文环境进行推测(如果有可能的话)。 当执行该语句的时候,将在EXECUTE语句中为这些参数指定实际值。 参见EXECUTE获取更多信息。

预备语句只是在当前数据库会话的过程中存在。如果客户端退出, 那么预备语句就会被遗忘,因此必须在被重新使用之前重新创建。 这也意味着一个预备语句不能被多个数据库客户端同时使用;但是, 每个客户端可以创建它们自己的预备语句来使用。预备语句可以用 DEALLOCATE命令手工清除。

如果一个会话准备用于执行大量类似的查询,那么预备语句可以获得最大限度的性能优势。 如果查询非常复杂,需要复杂的规划或者重写,那么性能差距将更加明显。 比如,如果查询设计许多表的连接,或者有多种规则要求应用。如果查询的规划和重写相对简单, 而执行起来开销相当大,那么预备语句的性能优势就不那么明显。

参数

name

给予这个特定的预备语句任意名字。它必须在一个会话中是唯一的,并且用于执行或者删除一个预备语句。

data_type

预备语句的某个参数的数据类型。如果某个参数的数据类型未指定或指定为unknown, 那么将根据该参数使用的上下文环境进行推断。可以使用 $1,$2等等在预备语句内部引用这个参数。

statement

SELECT,INSERT,UPDATE, DELETE或VALUES语句之一。

注意

在一些情况下,PostgreSQL为一个预备语句生成的查询 规划可能还不如按照普通方法提交并执行的查询生成的规划好。 这是因为该查询在被规划的时候(也是优化器判断最优查询规划的时候), 在查询中声明的任何参数的实际数值都还不可见。 PostgreSQL在表中收集数据分布的统计,而且可以利用查 询中的常量来猜测执行查询的可能结果。 因为这些数据在规划的时候还是未知,所以,得到的规划可能很差劲。 使用EXPLAIN查看PostgreSQL 为预备语句选取的查询计划。

有关查询规划和PostgreSQL为查询优化的目的收集统计的更多信息, 参阅ANALYZE文档。

可以通过查询pg_prepared_statements 系统试图获得某个会话中所有可用的预备语句

例子

为一个INSERT语句创建一个预备语句然后执行它:

PREPARE fooplan (int, text, bool, numeric) ASINSERT INTO foo VALUES($1, $2, $3, $4);
EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);

为一个SELECT语句创建一个预备语句然后执行它:

PREPARE usrrptplan (int) ASSELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usridAND l.date = $2;
EXECUTE usrrptplan(1, current_date);

注意,第二个参数的数据类型并未指定。所以将从上下文环境推测$2的类型。

兼容性

SQL标准包含一个PREPARE语句,但是它只用于嵌入式SQL。 PostgreSQL实现的PREPARE语句的语法也略有不同。

又见

DEALLOCATE, EXECUTE

Server Programming InterfaceNOTIFY

NOTIFY

Name

NOTIFY -- 生成一个通知

Synopsis

NOTIFY channel [ , payload ]

描述

NOTIFY命令发送一个通知事件连同一个可选择的"payload" 字符串到每个客户端应用程序,这些应用程序已经预先为当前数据库的指定通道名称执行 LISTEN channel。

NOTIFY提供了一个访问相同PostgreSQL 数据库的进程集的单一进程间通信机制。一个有效负载字符串可以连同通知一起发送, 高级机制通过结构化数据可以通过使用数据库中的表来建立,来讲通知器的额外数据传递给监听器。

为通知事件传递给客户端的信息包括通知通道名称,通知会话的服务器进程PID, 和负载字符串,若它未被指定则是空字符串。

定义用于给定数据库的通道名称以及每个名称的含义由数据库的设计者决定。通常, 通道名与数据库中的一些表相同,以及通知时间的本质含义,"我更改该表, 看看什么是新的"。但是NOTIFY和LISTEN 不强制此类关联规则。例如,一个数据库设计者可以使用几个不同的通道名称来标记 对一个单一变的不同种类的更改。或者,有效负载字符串可以用来区分不同的情况。

当NOTIFY用于通知某一特定表修改的动作的发生,一个实用的编程 技巧是将NOTIFY放在一个由表更新触发的规则里。用这种方法,通 知将在表更新的时候自动触发,而且应用程序员不会碰巧忘记处理它。

NOTIFY和SQL事务用某种重要的方法进行交换。首先,如果 NOTIFY在事务内部执行,通知事件直到事务提交才会送出。 这么做是有道理的,因为如果事务退出了,那么在它里面的所有命令都没有效果 (包括NOTIFY)。但如果有人希望通知事件立即发送,这就不太好了。 其次,当一个正在监听的会话在一次事务内收到一个通知信号,直到本次事务完成(提交或退出) 之前,该通知事件将不被送到与之相连的客户端。同样,如果一个通知在事务内部发送出去了, 而该事务稍后又退出了,就希望通知可以在某种程度上被撤消,因为通知一旦发送出去,服务器 便不能从客户端"收回"通知,所以通知时间只是在事务之间传递。这一点就要求 使用NOTIFY作为实时信号的应用应该确保他们的事务尽可能短。

若相同的通道名称多次从具有相同负载字符串的相同事务中多次提示,数据库服务器仅可以决定 发送一个单一通知。另一方面,有不同的有效负载的字符串的通知总是被交付作为不同的通知。 类似地,来自不同事务的通知从不能合到一个通知中。除了不删除税后的重复通知实例, NOTIFY保证来自相同事务的通知会按照他们发送时的顺序交付。这也保证了 来自不同事务的信息会按照事务提交的顺序交付。

对于一个客户来说执行监听相同通知通道本身的NOTIFY事很正常的。  在这种情况下将返回一个通知事件,就像所有其他的监听会话。根据应用程序逻辑,这回导致 无效的工作,例如:读取一个数据库表来找出会话刚写出的相同更新。可以通过注意该通知会话 的服务器进程PID(通知时间信息中提供的)来避免此类服务器进程,该进程 PID与其会话的相同(libpq中可获得的)。当他们相同时,通知 事件是其本身反弹回来的工作。

参数

channel

要表示的通知通道的名称 (任意标示符)。

payload

与通知交互的"payload"字符串。这必须被指定为一个简单的字符串。 在默认配置中它必须小于8000字节。(若二进制数据或者更大数量的信息需要被联系起来。 最好是将它们放到一个数据库中并发送记录的关键。)

注意

有一个队列,它持有被发送但是却未被所有监听会话处理的通知。若果该队列变为空,事务调用 NOTIFY将会提交失败。队列很大(在标准安装中是8GB)并且应该对几乎所 有用例有充足的空间。然而,若一个会话执行LISTEN并随后很长时间输入事务 那么不会发生清理。此时您应该确定该会话结束其当前事务,这样清理就可以进行。

执行过NOTIFY的事务无法准备两阶段提交。

pg_notify

为了发送一个提示你也可以使用函数 pg_notify(text,text)。该函数将通道明名称作为首个参数,并且将有效负载作为第二个。 该函数比NOTIFY命令更好用,如果您需要运转不恒定通道名称和有效载荷。

示例

psql里配置和执行一个监听/通知对:

LISTEN virtual;
NOTIFY virtual;
Asynchronous notification "virtual" received from server process with PID 8448.
NOTIFY virtual, 'This is the payload';
Asynchronous notification "virtual" with payload "This is the payload" received from server process with PID 8448.

LISTEN foo;
SELECT pg_notify('fo' || 'o', 'pay' || 'load');
Asynchronous notification "foo" with payload "payload" received from server process with PID 14728.

兼容性

SQL标准里没有NOTIFY语句。

又见

LISTEN, UNLISTEN

Server Programming InterfaceDECLARE

DECLARE

Name

DECLARE -- 定义一个游标

Synopsis

DECLARE name [ BINARY ] [ INSENSITIVE ] [ [ NO ] SCROLL ]CURSOR [ { WITH | WITHOUT } HOLD ] FOR query

描述

DECLARE允许一个用户来创建,这可以在较大查询的一个时间里 用来检索少量行。游标创建以后,通过使用FETCH可以将行抓取 出来。

Note: 这个页面在SQL命令水平描述了游标的使用方法。如果你视图使用PL/pgSQL 函数中的游标,规则就会不一样—请参阅Section 39.7。

参数

name

将要创建的游标名

BINARY

令游标以二进制而不是文本格式返回数据

INSENSITIVE

指明从游标中恢复的数据应该是不受影响的,在有表创建以后通过更新游标下的表。 在PostgreSQL中,这是默认的相应;所以这个关键字 没有影响并且仅接受兼容SQL标准。

SCROLL
NO SCROLL

SCROLL声明该游标可以用于以倒序的方式检索数据行 (也就是反向检索)。根据查询的执行计划的不同,声明SCROLL 可能会对查询的执行时间有不良影响。NO SCROLL声明该游标不 能用于以倒序的方式检索数据行。缺省仅允许在某些情况下倒序检索,这不同于指定 SCROLL。参见 注意获取细节。

WITH HOLD
WITHOUT HOLD

WITH HOLD(缺省)声明该游标可以在创建它的事务成功提交后继续使用。 WITHOUT HOLD声明该游标不能在创建它的事务提交后使用。 r若WITHOUT HOLD和WITH HOLD都未声明, 则默认为WITHOUT HOLD。

query

一个SELECT或者VALUES命令 会提供游标返回的行。

BINARY,INSENSITIVE,SCROLL关键字 可以以任何顺序出现。

注意

正常的游标以文本格式返回数据,与SELECT产生的一样。BINARY选项声明 BINARY选项声明游标会返回二进制格式的数据。这减少了服务器端和客户端的转换工作, 但代价却是产生更多的处理基于平台的二进制数据格式的程序工作。 例如:如果一个查询返回一个来自整型列的一个数值,你会得到一个带有默认游标的1的 字符串,而通过一个二进制游标您会得到一个包含值的内部表达的4字节字段(按大端字节顺序)。

二进制游标应当谨慎使用。包括psql在内的许多应用,不准备处理二进制游标 并且希望数据以文本形式返回。

Note: 当客户端应用使用"extended query"协议来发布FETCH命令, Bind协议信息声明是以文本形式还是以二进制形式恢复数据。该选项重写游标定义 的方式。,当使用任何光标可以看作是文本或二进制扩展查询协议 —同样二 进制游标的概念因此过时。

除非声明WITH HOLD,否则由该命令创建的游标只能在当前事务内使用。 因此,不带有WITH HOLD的DECLARE在事物块之外是无效的:游标只能持续到语句完成。所以,若这样一个命令在一个事务块之外使用时,PostgreSQL 或报告一个错误。使用BEGIN和COMMIT(或者ROLLBACK)来定义一个事务块。

如果声明了WITH HOLD并且创建该游标的事务成功提交,那么游标还可以在同一会话 随后的事务里访问。但如果创建它的事务回滚,那么游标被删除。带WITH HOLD创建 的游标是用一个明确的CLOSE命令或者是会话终止来关闭的。在目前的实现里,由一 个游标代表的行是被拷贝到一个临时文件或者内存区里的,这样他们就仍然可以在随后的事务中被访问。

当查询包括FOR UPDATE或者FOR SHARE,WITH HOLD不会被声明。

在定义一个要用来反向抓取的游标的时候,应该声明SCROLL选项,这是 SQL 标准要求的。 不过,为了和早期的版本兼容,只要游标的查询计划简单得不需要额外的开销, PostgreSQL在没有声明SCROLL的时候也允许反向抓取。不过, 建议应用开发人员不要依赖于使用没有使用SCROLL定义的游标的反向查找功能。如果 声明了NO SCROLL,那么不管怎样都会禁止反向抓取的功能。

当查询包括FOR UPDATE或者FOR SHARE时,向读取也是不允许的; 所以在这种情况下可能不会声明SCROLL。

Caution

滚动效果和WITH HOLD游标可能会给出意想不到的结果,如果他们 调用任何易失函数(参阅Section 35.6)。当一个已抓取的行 再次被抓取,函数可能会被重新执行,可能会导致不同于第一次的结果。针对该情况的 一个工作区是为了声明游标WITH HOLD并且在阅读其任意行之前提 交事务。这回强制游标的整个输出在临时表中实现,因此易失函数完全是为每一行执行一次。

若游标的查询包括FOR UPDATE或者FOR SHARE,那么返回的行会在他们首次 被抓取时锁定,与带有这些选项的定期SELECT命令以相同的方式。 另外,返回的行将是最新的版本;因此,这些选项提供SQL标准中称作"sensitive cursor" 的等价物。(声明带有FOR UPDATE或者FOR SHARE的INSENSITIVE 是错误。)

Caution

若游标旨在与UPDATE ... WHERE CURRENT OF或者DELETE ... WHERE CURRENT OF 一起使用,那么通常建议使用FOR UPDATE。使用FOR UPDATE阻止其他会话 在其被抓去和更新之间的时间里改变行。没有FOR UPDATE,一个随后的 WHERE CURRENT OF命令将会没有效果,如果行在游标创建之后被改变。

使用FOR UPDATE的另一个原因是:没有它,一个随后的WHERE CURRENT OF 可能会失败,若游标查询不满足SQL标准对"simply updatable" 的规则(有钱,游标必须 仅参考一个表并且不使用分组或者ORDER BY)。不是简单可更新的游标可能会工作或者 不会工作,这由计划选择的详细情况决定;所以在最坏的情况下,一个应用程序可能测试工作并然后 在生产中失败。

不使用带有WHERE CURRENT OF的FOR UPDATE的主要原因是:如果您 需要可卷动的游标,或者对后续更新不敏感(即继续显示旧数据)。若这是一个要求, 密切关注以上显示的警告。

SQL 标准中的游标只能在嵌入SQL(ESQL)的应用中使用。 PostgreSQL服务器没有一个明确的OPEN语句; 一个游标被认为在定义时就已经打开了。不过,PostgreSQL嵌入的SQL 预编译器(ECPG)支持 SQL 标准的习惯,包括那些和 DECLARE和OPEN相关的语句。

可以通过查询pg_cursors 系统视图看到所有可用游标。

示例

定义一个游标:

DECLARE liahona CURSOR FOR SELECT * FROM films;

参阅FETCH获取有关游标使用的更多例子。 See FETCH for more examples of cursor usage.

兼容性

SQL标准认为游标是否对默认的底层数据的并发更新敏感是依赖实现决定的。 在PostgreSQL中,游标是默认不敏感的,并且 可以通过声明FOR UPDATE来使其敏感。其他产品可能以不同的方式工作。

SQL 标准只允许在嵌入的SQL中和模块中使用游标。 PostgreSQL允许交互地使用游标。

二进制游标是PostgreSQL扩展。

又见

CLOSE, FETCH, MOVE