PG 预处理语句和游标

[TOC]

1. 预处理语句

PREPARE 指令用于创建一个预备（Prepare）语句，本质是一段待执行的、且进行了预编译的 SQL 语句，所以也称之为 “预编译语句”。在 RDBMS 中，预备语句常被用于优化性能。

当我们需要在一个 Session 中执行大量近似的 SQL 语句时，使用预备语句可以获得最大的性能提升。如果该语句很复杂（难于规划或重写），例如，如果查询涉及很多表的连接（Join）或者要求应用多个不同的规则，那么性能差异将会特别明显。反之，如果 SQL 语句相对比较容易规划和重写，但是执行起来开销相对较大，那么预备语句的性能优势就不那么显著了。

预备语句进行性能优化的原理就是 “预编译”，即：在执行实际的 SQL 语句之前就规划好了后续的数据写入流程，尤其当 SQL 语句非常近似时，预备语句就可以避免重复性的解析工作（类似于缓存预热），继而提升 SQL 语句执行的效率。

在 PostgreSQL 中执行 PREPARE 指令时，其内含定义的的 SQL 预备语句会被预编译。当后续再发出一个 EXECUTE 指令时，预备语句就可以快速的被执行。

⚠️ 注意：PostgreSQL 的预备语句仅在一个 Session 中有效。即：当 Session 终结时，预备语句的定义也会被自动清除，因此在重新使用之前必须重新建立它。这也意味着一个预备语句不能被多个数据库客户端（不同的 Session）同时使用。当然，在同一个 Session 中的预备语句也可以使用 DEALLOCATE 指令手工清除。

1.1 定义

创建一个预备语句：

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

比如：

PREPARE fooplan (int, text, bool, numeric) AS 
INSERT INTO foo VALUES($1, $2, $3, $4);

执行一个预处理语句:

EXECUTE name(parameter_values);

比如：

EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00);

1.2 参数

name：指定预备语句的名称。它在同一个会话中必须保持唯一，也作为后续 EXECUTE（执行）、DEALLOCATE（清除）预备语句的句柄。
data_type：预备语句可以接受参数列表，data_type 则用于指定参数的数据类型。如果一个特定参数的数据类型没有被指定或者被指定为 unknown 类型，那么该参数的类型将从当前的运行环境中推理得出。
statement：即 SQL 语句的定义，可以是任何 SELECT、INSERT、UPDATE、DELETE 或者 VALUES 语句。

1.3 使用

定义一个预处理语句：

test_check=#prepare get_data(integer, integer) as 
	select 
		id, 
		pg_catalog.hashtextextended(t::text, 0) as hash, 
		ctid as ctid,
		xmin as xmin 
	from public.test_public t 
	where true and (id) > ($1) and (id) <= ($2)   
	order by id limit 4096;
PREPARE

使用：

test_check=# execute get_data(1, 10);
 id |         hash         | ctid  | xmin 
----+----------------------+-------+------
  2 |  5030098376654668729 | (0,2) | 1527
  3 |  5814810164831543628 | (0,3) | 1527
  4 |   368488698624252940 | (0,4) | 1527
  5 |  5215424886562786071 | (0,5) | 1527
  6 | -8849220602237191839 | (0,6) | 1527
  7 |  8700983981519922528 | (0,7) | 1527
  8 |  3584052945399255328 | (1,1) | 1527
  9 | -3677749872033444363 | (1,2) | 1527
 10 |  -804159612782741165 | (1,3) | 1527
(9 rows)

test_check=# execute get_data(10, 20);
 id |         hash         | ctid  | xmin 
----+----------------------+-------+------
 11 |  -763796658949607443 | (1,4) | 1527
 12 |  8640223124057086644 | (1,5) | 1527
 13 |  6407311739145033076 | (1,6) | 1527
 14 | -8389821342166946345 | (1,7) | 1527
 15 | -3248777546410796405 | (2,1) | 1527
 16 | -2494659550102110026 | (2,2) | 1527
 17 |  1222998119883039498 | (2,3) | 1527
 18 | -2000453746152768403 | (2,4) | 1527
 19 |  7198823951037788211 | (2,5) | 1527
 20 | -3371057416683607524 | (2,6) | 1527
(10 rows)

2. 游标

2.1 定义

所有在PL/pgSQL里对游标的访问都是通过游标变量实现的，它总是特殊的数据类型refcursor。创建游标变量的一个方法是把它声明为一个类型为refcursor的变量。另外一个方法是使用游标声明语法，像下面这样：

name [ [ NO ] SCROLL ] CURSOR [ ( arguments ) ] FOR query;

(Oracle兼容中FOR可以用IS代替)。如果定义了SCROLL，则游标可以向后回滚；如果定义了NO SCROLL，则向后抓取的动作被拒绝；如果二者都没有定义，那么是否进行向后取的动作会根据查询来判断。如果有arguments，那么它是一个逗号分隔name datatype 列表，这个列表定义由已给查询中的参数值来替代的name。实际用于代换这些名字的数值将在游标打开之后声明。

使用游标：

FETCH [ direction { FROM | IN } ] cursor INTO target;

FETCH从游标中检索下一行到目标中，目标可以是一个行变量、记录变量、逗号分隔的普通变量列表，就像SELECT INTO一样，如果下一行中没有，目标会设为NULL。如同SELECT INTO，可以使用特殊变量FOUND来检查是否检索出一个行。

direction子句可以是任何一个SQL FETCH命令允许的变量，除了那些可以抓取不止一行的；形如：NEXT, PRIOR，FIRST，LAST， ABSOLUTE count， RELATIVE count， FORWARD或者BACKWARD。忽略direction作为声明的NEXT是相同的。 direction值需要往后移动可能会失败，除非声明的或者打开的游标带有SCROLL`选项。

cursor必须是一个指向一个打开的游标的refcursor变量的名字。

⚠️ 注意：PostgreSQL 的游标语句仅在一个 Session 中有效。即：当 Session 终结时，游标语句的定义也会被自动清除，因此在重新使用之前必须重新建立它。

2.2 参数

name: 要创建的游标的名称。
BINARY: 让游标返回二进制数据而不是返回文本格式数据。
ASENSITIVE INSENSITIVE: 游标敏感性决定了是否更改为游标下面的数据，在同一事务中完成，当游标被声明后，在游标中可见。 INSENSITIVE 表示它们不可见，ASENSITIVE表示行为是实现相关的。第三个行为，SENSITIVE，意味着此类更改在游标中可见，但在PostgreSQL中不可用。在PostgreSQL中，所有游标都不敏感;因此这些关键字没有任何影响，并且只在兼容SQL标准时才被接受。把 INSENSITIVE 与 FOR UPDATE 或 FOR SHARE 一起指定，是一个错误。
SCROLL 和 NO SCROLL
SCROLL指定游标可以用非顺序（例如，反向）的方式从中检索行。根据查询的执行计划的复杂度，指定 SCROLL可能导致查询执行时间上的性能损失。 NO SCROLL指定游标不能以非顺序的方式从中检索行。默认是允许在某些情况下滚动，但这和指定 SCROLL不完全相同。
WITH HOLD 和 WITHOUT HOLD:
WITH HOLD指定该游标在创建它的事务提交之后还能被继续使用。WITHOUT HOLD指定该游标不能在创建它的事务之外使用。如果两者都没有指定，则默认为 WITHOUT HOLD。
query: 用于提供该游标返回的行的SELECT或者VALUES命令。

关键词ASENSITIVE、BINARY、INSENSITIVE和SCROLL可以以任意顺序出现。

2.3 使用

定义一个游标，使用 with hold ，在这个情况下事务被提交后任可以继续使用

test_check=# BEGIN;
BEGIN
test_check=# declare dc_cursor_30771_2 cursor with hold for select id,    pg_catalog.hashtextextended(t::text, 0) as hash,
    ctid as ctid,
    xmin as xmin from public.test_public t 
  where true  
  order by id;
DECLARE CURSOR

获取游标中的数据（10行）：

test_check=# Fetch 10 dc_cursor_30771_2;
 id |         hash         | ctid  | xmin 
----+----------------------+-------+------
  1 |  8156028811834156745 | (0,1) | 1527
  2 |  5030098376654668729 | (0,2) | 1527
  3 |  5814810164831543628 | (0,3) | 1527
  4 |   368488698624252940 | (0,4) | 1527
  5 |  5215424886562786071 | (0,5) | 1527
  6 | -8849220602237191839 | (0,6) | 1527
  7 |  8700983981519922528 | (0,7) | 1527
  8 |  3584052945399255328 | (1,1) | 1527
  9 | -3677749872033444363 | (1,2) | 1527
 10 |  -804159612782741165 | (1,3) | 1527
(10 rows)

test_check=# Fetch 10 dc_cursor_30771_2;
 id |         hash         | ctid  | xmin 
----+----------------------+-------+------
 11 |  -763796658949607443 | (1,4) | 1527
 12 |  8640223124057086644 | (1,5) | 1527
 13 |  6407311739145033076 | (1,6) | 1527
 14 | -8389821342166946345 | (1,7) | 1527
 15 | -3248777546410796405 | (2,1) | 1527
 16 | -2494659550102110026 | (2,2) | 1527
 17 |  1222998119883039498 | (2,3) | 1527
 18 | -2000453746152768403 | (2,4) | 1527
 19 |  7198823951037788211 | (2,5) | 1527
 20 | -337105

Bug描述:按时间戳排序 PostgreSQL prepare