将函数索引从Oracle迁移到PostgreSQL

使用AWSSchemaConversionTool（SCT）将函数索引从Oracle迁移到PostgreSQL

作为AWS云服务器的用户，我们需要将数据库的元数据进行转换。但是，在使用AWS转换后的脚本在PostgreSQL中执行时，我们发现函数索引无法成功执行。更加深入的查询发现，我们的函数索引使用了在Oracle中常用的substr和to_char等函数。

将函数索引从Oracle迁移到PostgreSQL

我们在PostgreSQL中手动执行上述函数，并不会报错。根据PostgreSQL文档，函数可以是3种类型：VOLATILE、STABLE或者IMMUTABLE。如果CREATE FUNCTION命令没有指定一个分类，则默认是VOLATILE。其中，VOLATILE函数可以做任何事情，包括修改数据库，而在使用相同的参数连续调用时，它能返回不同的结果。STABLE函数则不能修改数据库，并且被确保对一个语句中的所有行用给定的相同参数返回相同的结果。特别是，在一个索引扫描条件中使用包含这样一个函数的表达式是安全的（因为一次索引扫描只会计算一次比较值,而不是为每一行都计算一次，在一个索引扫描条件中不能使用VOLATILE函数）。最后，IMMUTABLE函数不能修改数据库，并且被确保用相同的参数永远返回相同的结果。

如何确认函数类型，并将函数设置为IMMUTABLE函数

通过查询pg_proc，可以确认函数类型，例如sysdate函数。

SELECTproname,provolatile,proargtypes,proargtypes[0]::regtypeASargtype,prosrc FROM pg_proc WHEREpronamelike‘sysdate%’;

其中，Provolatile等于s，则说明是STABLE函数。另外，要创建函数索引，就必须把函数设置成IMMUTABLE。而实现办法可以自己建一个IMMUTABLE函数，该函数接受输入参数作为numeric类型。然后在创建函数索引的地方使用自己创建的。由于我这里的函数索引使用了2种函数，一个是substr，一个是to_char，substr已经是IMMUTABLE的，所以只需要将to_char函数建成IMMUTABLE就行了。

CREATE OR REPLACE FUNCTION immutable_to_char(numeric) RETURNS character varying AS select aws_oracle_ext.to_char($1) LANGUAGE SQL IMMUTABLE;

如何确定函数稳定性

我们可以用current_timestamp来进行测试。可以看到current_timestamp是的Provolatile状态s，是STABLE函数。STABLE函数不能修改数据库，并且被确保对一个语句中的所有行用给定的相同参数返回相同的结果。

除此以外，还可以找类似的几个时间函数测试一下。除了clock_timestamp是VOLATILE，now和sysdate都是STABLE。在创建函数索引时，我们必须把函数设置成IMMUTABLE。可以自己建一个IMMUTABLE函数，该函数接受输入参数作为numeric类型。然后在创建函数索引的地方使用自己创建的。

参考文档：

函数稳定性讲解-retalkPostgreSQLfunctions[volatile|stable|immutable] https://github.com/digoal/blog/blob/master/201212/20121226_01.md
MigratingFunctionbasedindexesfromOracletoPostgreSQL https://askdba.org/weblog/2018/01/migrating-function-based-indexes-from-oracle-to-postgresql/

原创文章，作者：小编小本本，如若转载，请注明出处：https://www.benjiyun.com/yunzhujiyunwei/vps-yunwei/5910.html