炫“库”行动-人大金仓有奖征文—KingbaseES 中的动态DDL

【本文正在参加与炫“库”行动-人大金仓有奖征文】CSDN

概述

        在DBA的日常工作中，经常遇到一些需要基于数据库当前状态的DDL语句的实例。比如一个逻辑复制的目标表，主键 ID列与生成数据的序列不同步，这将导致插入新行时，会有主键冲突。要防止这个问题，需要设置序列可以生成的值，超过目标表的当前主键最大值。

        解决此类问题可以使用动态DDL，虽然SQL 中的数据定义语言(DDL) 本身是非动态的，因为DDL需要有严格的解析规则，而且必须预定义的其中的数据类型、表结构，关联列也必须是已知列。

        下面的案例，可以让 SQL随心所欲地执行动态 DDL，而不必每次都手动编写DDL语句。

改变序列重启值的解决方案

        希望通过查询显式地为序列提供 RESTART 值，重置一个序列，使其从它所关联的表的当前最大值之后开始。预想中，使用简单直接的DDL语句：

ALTER SEQUENCE tablename_id_seq RESTART (SELECT max(id)+1 FROM tablename);

ERROR:  syntax error at or near "(", at character 38

STATEMENT:  ALTER SEQUENCE tablename_id_seq RESTART (SELECT max(id) + 1 FROM tablename);

        正如所看到的，KingbaseE语法不支持这种方法，因为这里需要的是实际值，而不是子查询。

使用ksql的变量替换

        使用ksql变量，首先选择一个变量的值，然后将这个值替换到表达式中。

-- 使用\gset设置一个带有查询结果的变量

SELECT max(id) + 1 as id_max from tablename \gset

-- 在新的SQL语句中替换变量

ALTER SEQUENCE tablename _id_seq RESTART :id_max ;

        在实例中，使用\gset命令来得到max(id)+1的结果，并将其存储在会话中，供以后使用。然后使用:id_max语法将该变量插入到SQL语句中，将其直接传递给服务器。

使用ksql的 \gexec 命令

        使用ksql进行动态SQL的另一种方法，是设计一个查询SELECT语句，返回希望运行的语句，然后使用\gexec命令执行。

SELECT 'ALTER SEQUENCE tablename_id_seq RESTART ' || max(id) + 1 as query FROM tablename;

SELECT 'ALTER SEQUENCE tablename_id_seq RESTART ' || max(id) + 1 as query FROM tablename \gexec

query

ALTER SEQUENCE tablename_id_seq RESTART 100001

ALTER SEQUENCE

        与变量替换相比，这种方法的一个优点是，它可以处理更复杂的语句和多个返回值，因此可以基于任意条件设计查询语句，并生成多个SQL语句;第一个方法实现仅限于每次返回单个行的查询。在使用\gexec对服务器执行SQL语句之前，这还可以预览将要运行的SQL语句，因此提供一定程度的安全性。

非ksql的动态SQL

        ksql具有处理动态SQL的功能，但如何在第三方应用中，实现动态SQL的功能。现在使用基本类似的方法，编写sql语句块来生成查询，然后执行返回的SQL语句。这些方法与上面的ksql部分中的方法大致相关，因此适用于单个或多个动态语句。

匿名块

        要使用服务器端动态SQL，我们需要使用format函数构造SQL语句，并执行脚本，就像我们自己发出SQL语句一样。

BEGIN

    EXECUTE format('ALTER SEQUENCE tablename_id_seq RESTART %s', (SELECT max(id) + 1 FROM tablename));

END;

创建函数 exec()

与匿名块的功能几乎相同，我们也可以创建一个简单的用户函数，简单地调用它的输入参数EXECUTE。

CREATE OR REPLACE FUNCTION exec(raw_query text) RETURNS text AS

BEGIN

  EXECUTE sql_statement;

  RETURN sql_statement;

END;

SELECT exec(format('ALTER SEQUENCE tablename_id_seq RESTART %s', (SELECT max(id) + 1 FROM tablename)));

        函数方法基本上只是将SQL语句移动到传入的参数中，但它带来更灵活的针对构造的语句列表，在SQL语句中调用此函数，可以选择依次运行每个 SQL。

限制

        这些方法对动态SQL运行的限制，在显式事务块之外，运行的SQL命令受到限制。如果想在所有已知索引上运行REINDEX CONCURRENTLY，那么使用exec()函数为public模式中的所有索引执行REINDEX CONCURRENTLY语句:

SELECT exec(format('REINDEX INDEX CONCURRENTLY %I', relname))

FROM sys_class r

JOIN sys_namespace s ON r.relnamespace = s.oid

WHERE relkind = 'i' AND nspname = 'public' ;

ERROR:  REINDEX CONCURRENTLY cannot be executed from a function

CONTEXT:  SQL statement "REINDEX INDEX CONCURRENTLY tablename_pkey"

PL/SQL function exec(text) line 3 at EXECUTE

        从这里看到，由于REINDEX CONCURRENTLY需要管理自己的事务状态，因此它不能作为函数运行；在 KingbaseES 中，函数的内在事务运行时，是以函数的SQL语句完全成功或完全失败。

试一试命令 \gexec

SELECT format('REINDEX INDEX CONCURRENTLY %I', relname)

FROM sys_class r

JOIN sys_namespace s ON r.relnamespace = s.oid

WHERE  relkind = 'i' AND nspname = 'public'

\gexec

由于\gexec处理是由ksql完成的，因此生成的语句独立运行。

相关内容可参考官方文档：

《[应用开发及迁移][参考手册]SQL语句参考手册.pdf》、《[系统管理][参考手册]KingbaseES客户端应用参考手册.pdf》