526互联

KingbaseES V8R6 Deallocate 语句使用说明

发布时间 2023-05-09 19:41:51作者: KINGBASE研究院

用途

DEALLOCATE被用来释放一个之前PREPARE好的SQL语句。如果不显式地释放一个PREPARE语句，那么会话结束时会释放它。
prepare语句类似oracle的绑定变量

绑定过程：
1)PREPARE，准备绑定变量SQL
2)EXECUTE，绑定并执行
3)DEALLOCATE，释放绑定变量

测试

1.只有本地会话可以看的prepare语句

创建测试表：
test=# create table test_c (id int4, name character varying(12));
CREATE TABLE


test=# insert into test_c values (1,'a'),(2,'b'),(3,'c');
INSERT 0 3


TEST=#  select * from test_c ;
 id | name
----+------
  1 | a
  2 | b
  3 | c
(3 rows)

备注：创建完表后，接着开启新会话，顺序按以下操作进行：
test=# PREPARE select_1 (character varying) AS
select * From test_c where name=$1; 
PREPARE


TEST=# EXECUTE select_1('a');
 id | name
----+------
  1 | a
(1 row)


在本地会话中可以查询到prepare语句：
TEST=# select * from sys_prepared_statements;
   name   |                statement                |         prepare_time          | parameter_types | from_sql
----------+-----------------------------------------+-------------------------------+-----------------+----------
 select_1 | PREPARE select_1 (character varying) AS+| 2023-04-20 10:22:16.063035+08 | {varchar}       | t
          | select * From test_c where name=$1;     |                               |                 |
(1 row)


这时，切换到之前创建表的会话查看此视图没有任何信息：
TEST=#  select * from sys_prepared_statements;
 name | statement | prepare_time | parameter_types | from_sql
------+-----------+--------------+-----------------+----------
(0 rows)


同样，执行的时候也会报错：
TEST=# EXECUTE select_1('a');
ERROR:  prepared statement "select_1" does not exist
TEST=#

说明prepare语句只对当前会话可见并生效，采用了会话隔离机制。

2.DDL更改表结构后，prepare语句无法继续使用

依然在创建prepare语句的会话中执行：
TEST=#  alter table test_c alter column name type character varying;
ALTER TABLE

由于发生了DDL语句后，原来创建的prepare语句失效：
TEST=# EXECUTE select_1('a');
ERROR:  cached plan must not change result type
TEST=#

但是仍然可以查看prepare语句：
TEST=#  select * from sys_prepared_statements;
   name   |                statement                |         prepare_time          | parameter_types | from_sql
----------+-----------------------------------------+-------------------------------+-----------------+----------
 select_1 | PREPARE select_1 (character varying) AS+| 2023-04-20 10:22:16.063035+08 | {varchar}       | t
          | select * From test_c where name=$1;     |                               |                 |
(1 row)

解决方法

1.释放prepare语句
deallocate select_1;


这时，视图中已经查看不到，如需再次使用prepare语句需要重新创建：
TEST=# select * from sys_prepared_statements;
 name | statement | prepare_time | parameter_types | from_sql
------+-----------+--------------+-----------------+----------
(0 rows)

执行语句已经提示不存在：
TEST=# EXECUTE select_1('a');
ERROR:  prepared statement "select_1" does not exist

2.断开连接会话，重建连接后，已经查不到prepare语句，因为它是会话级别的，如需使用需要重建prepare语句：
TEST=# PREPARE select_1 (character varying) AS
TEST-# select * From test_c where name=$1;
PREPARE

TEST=# EXECUTE select_1('a');
 id | name
----+------
  1 | a
(1 row)

TEST=#
TEST=# select * from sys_prepared_statements;
   name   |                statement                |         prepare_time          | parameter_types | from_sql
----------+-----------------------------------------+-------------------------------+-----------------+----------
 select_1 | PREPARE select_1 (character varying) AS+| 2023-04-20 11:30:56.040584+08 | {varchar}       | t
          | select * From test_c where name=$1;     |                               |                 |
(1 row)


TEST=# alter table test_c alter column name type varchar2(5);
ALTER TABLE
TEST=# EXECUTE select_1('a');
ERROR:  cached plan must not change result type
TEST=#
TEST=# \q

[kingbase7@localhost ~]$ ksql -USYSTEM TEST
ksql (V8.0)
Type "help" for help.
断开重建连接后，之前prepare语句已无法从视图中查到：
TEST=# select * from sys_prepared_statements;
 name | statement | prepare_time | parameter_types | from_sql
------+-----------+--------------+-----------------+----------
(0 rows)

总结

1.PREPARE语句在会话结束后，在视图sys_prepared_statements中会自动消失。
2.同一个prepared语句不能在多个并发会话中共有。
3.对表结构ddl修改后，创建好的对应prepare语句无法再次使用，需要使用deallocate命令取消 prepare语句，然后重新生成prepare语句；或者重建连接会话后，重新创建prepare语句。
备注：DEALLOCATE语句只对本地单个会话生效，如果是应用程序批量报错，这个方法显然不合适。