云服务器内容精选

  • WITH中的数据修改语句 在WITH子句中使用数据修改命令INSERT、UPDATE、DELETE。这允许用户在同一个查询中执行多个不同操作。示例如下所示: 1 2 3 4 5 6 WITH moved_tree AS ( DELETE FROM tree WHERE parentid = 4 RETURNING * ) INSERT INTO tree_log SELECT * FROM moved_tree; 上述查询示例实际上从tree把行移动到tree_log。WITH中的DELETE删除来自tree的指定行,以它的RETURNING子句返回它们的内容,并且接着主查询读该输出并将它插入到tree_log。 WIYH子句中的数据修改语句必须有RETURNING子句,用来返回RETURNING子句的输出,而不是数据修改语句的目标表,RETURNING子句形成了可以被查询的其余部分引用的临时表。如果一个WITH中的数据修改语句缺少一个RETURNING子句,则它形不成临时表并且不能在剩余的查询中被引用。 如果声明了RECURSIVE关键字,则不允许在数据修改语句中进行递归自引用。在某些情况中可以通过引用递归WITH的输出来绕过这个限制,例如: 1 2 3 4 5 6 7 8 9 WITH RECURSIVE included_parts(sub_part, part) AS ( SELECT sub_part, part FROM parts WHERE part = 'our_product' UNION ALL SELECT p.sub_part, p.part FROM included_parts pr, parts p WHERE p.part = pr.sub_part ) DELETE FROM parts WHERE part IN (SELECT part FROM included_parts); 这个查询将会移除一个产品的所有直接或间接子部件。 WITH子句中的子语句与主查询同时执行。因此,在使用WITH中的数据修改语句时,指定更新的顺序实际是以不可预测的方式发生的。所有的语句都使用同一个快照中执行,语句的效果在目标表上不可见。这减轻了行更新的实际顺序的不可预见性的影响,并且意味着RETURNING数据是在不同WITH子语句和主查询之间传达改变的唯一方法。 本示例中外层SELECT可以返回更新之前的数据: 1 2 3 4 WITH t AS ( UPDATE tree SET id = id + 1 RETURNING * ) SELECT * FROM tree; 本示例中外部SELECT将返回更新过的数据: 1 2 3 4 WITH t AS ( UPDATE tree SET id = id + 1 RETURNING * ) SELECT * FROM t; 不支持在单个语句中更新同一行两次。这种语句的效果是不可预测的。如果只有一个修改发生了,但却不容易(有时也不可能)预测哪一个发生了修改。
  • WITH递归查询 通过声明RECURSIVE关键字,一个WITH查询可以引用它自己的输出。 递归WITH查询的通常形式如下: 1 non_recursive_term UNION [ALL] recursive_term 其中:UNION在合并集合时会执行去重操作,而UNION ALLL则直接将结果集合并、不执行去重;只有递归项能够包含对于查询自身输出的引用。 使用递归WITH时,必须确保查询的递归项最终不会返回元组,否则查询将无限循环。 使用表tree来存储下图中的所有节点信息: 表定义语句如下: 1 CREATE TABLE tree(id INT, parentid INT); 表中数据如下: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 INSERT INTO tree VALUES(1,0),(2,1),(3,1),(4,2),(5,2),(6,3),(7,3),(8,4),(9,4),(10,6),(11,6),(12,10); SELECT * FROM tree; id | parentid ----+---------- 1 | 0 2 | 1 3 | 1 4 | 2 5 | 2 6 | 3 7 | 3 8 | 4 9 | 4 10 | 6 11 | 6 12 | 10 (12 rows) 通过以下WITH RECURSIVE语句,我们可以返回从顶层1号节点开始,整个树的节点,以及层次信息: 1 2 3 4 5 6 7 8 9 10 11 WITH RECURSIVE nodeset AS ( -- recursive initializing query SELECT id, parentid, 1 AS level FROM tree WHERE id = 1 UNION ALL -- recursive join query SELECT tree.id, tree.parentid, level + 1 FROM tree, nodeset WHERE tree.parentid = nodeset.id ) SELECT * FROM nodeset ORDER BY id; 上述查询中,我们可以看出,一个典型的WITH RECURSIVE表达式包含至少一个递归查询的CTE,该CTE中的定义为一个UNION ALL集合操作,第一个分支为递归起始查询,第二个分支为递归关联查询,需要自引用第一部分进行不断递归关联。该语句执行时,递归起始查询执行一次,关联查询执行若干次并将结果叠加到起始查询结果集中,直到某一些关联查询结果为空,则返回。 上述查询的执行结果如下: id | parentid | level ----+----------+------- 1 | 0 | 1 2 | 1 | 2 3 | 1 | 2 4 | 2 | 3 5 | 2 | 3 6 | 3 | 3 7 | 3 | 3 8 | 4 | 4 9 | 4 | 4 10 | 6 | 4 11 | 6 | 4 12 | 10 | 5 (12 rows) 从返回结果可以看出,起始查询结果包含level=1的结果集,关联查询执行了五次,前四次分别输出level=2,3,4,5的结果集,在第五次执行时,由于没有parentid和输出结果集id相等的记录,也就是再没有多余的孩子节点,因此查询结束。 对于WITH RECURSIVE表达式,GaussDB(DWS)支持其分布式执行。由于WITH RECURSIVE涉及到循环运算,GaussDB(DWS)引入了参数max_recursive_times,用于控制WITH RECURSIVE的最大循环次数,默认值为200,超过该次数则报错。
  • 日志 PL/Java使用标准的Java Logger。 因此,用户可以通过如下方式记录日志: Logger.getAnonymousLogger().config( "Time is " + new Date(System.currentTimeMillis())); 初始化的Java Logger类会默认设置为CONFIG级别,对应为GaussDB(DWS)的LOG级别。Java Logger类输出的日志消息都会重定向到GaussDB(DWS)后端,并写入到服务器日志或显示在用户界面上。MPPDB服务器日志将记录LOG、WARNING、ERROR级别的信息,而SQL用户界面将显示WARNING和ERROR级别的日志消息。Java Logger级别与GaussDB(DWS)的日志级别对应关系见下表。 表2 PL/Java日志级别 java.util.logging.Level GaussDB(DWS) 日志级别 SERVER ERROR WARNING WARNING CONFIG LOG INFO INFO FINE DEBUG1 FINER DEBUG2 FINEST DEBUG3 用户可以通过以下方式更改Java Logger的记录级别。例如通过下面的Java代码修改Java Logger级别为SEVERE,此时再记录WARNING级别的日志时,日志消息(msg)就不会再写入到GaussDB(DWS)日志中。 Logger log = Logger.getAnonymousLogger(); Log.setLevel(Level.SEVERE); log.log(Level.WARNING, msg);
  • 基本数据类型映射关系 表1 PL/Java默认数据类型映射关系 GaussDB(DWS) Java BOOLEAN boolean "char" byte bytea byte[] SMALLINT short INTEGER int BIGINT long FLOAT4 float FLOAT8 double CHAR java.lang.String VARCHAR java.lang.String TEXT java.lang.String name java.lang.String DATE java.sql.Timestamp TIME java.sql.Time (stored value treated as local time) TIMETZ java.sql.Time TIMESTAMP java.sql.Timestamp TIMESTAMPTZ java.sql.Timestamp
  • 相关GUC参数 udf_memory_limit 系统级别的GUC参数,用于限制每个CN、DN执行UDF可以使用的物理内存量,默认为0.05 * max_process_memory。可通过修改postgresql.conf文件进行配置,配置后需要重启数据库服务后才可生效。 udf_memory_limit是max_process_memory的一部分。每个CN、DN启动时,会预留(udf_memory_limit - 200MB)内存供UDF Worker进程使用。CN、DN和UDF Worker是不同的进程,但CN、DN自动少用一部分内存,把这部分内存节省下来供UDF Worker进程使用。 例如:在某DN上把max_process_memory设置为10GB,udf_memory_limit设置为4GB,则此DN最多使用10GB - (4GB - 200MB)=6.2GB内存。即使用户没有执行任何UDF,则此DN也最多只能使用6.2GB内存。默认情况下,udf_memory_limit为200MB。查询pv_total_memory_detail视图时可以发现,process_used_memory永远不会超过max_process_memory - (udf_memory_limit - 200MB)。 一个CN执行最简单的Java UDF函数,使用的物理内存量大约为50MB,用户可以根据自己Java函数的内存使用量和并发度设置此参数。新增此参数后,不再建议用户设置UDFWorkerMemHardLimit和FencedUDFMemoryLimit。 当UDF进程并发度过大,内存超出udf_memory_limit设置值时会导致进程退出等非预期情况,该场景下执行结果可能不可靠,强烈建议根据实际情况进行参数设置,保留足够内存余量。如果系统记录有/var/log/messages,可查看该日志文件是否存在因超过cgroup内存限制而造成内存不足。内存严重不足时,甚至可能导致UDF master进程退出,可以查看UDF日志进行分析,默认的UDF日志路径在$GAUSSLOG/cm/cm_agent/pg_log下。例如,出现以下日志时就说明内存资源严重不足,导致了UDF master进程退出,需要检查udf_memory_limit参数设置。 0 [BACKEND] FATAL: poll() failed: Bad address, please check the parameter:udf_memory_limit to make sure there is enough memory. FencedUDFMemoryLimit 会话级别的GUC参数,用户限制会话发起的单个Fenced UDF Worker进程的最大虚拟内存使用量,设置方法如下: SET FencedUDFMemoryLimit='512MB'; 该参数的取值范围为 (150MB, 1G],当设置大于1G时会立即报错,当设置小于等于150MB时,则会在调用函数时报错。 FencedUDFMemoryLimit设置为0,表示不控制Fenced UDF Worker的虚拟内存使用量。 建议通过设置udf_memory_limit控制Fenced UDF Worker使用的物理内存量。不建议用户使用FencedUDFMemoryLimit,尤其在使用Java UDF时不建议用户设置此参数。但是如果用户非常清楚设置该参数带来的影响,可以参考下列信息进行设置: C UDF worker启动之后,占用的虚拟内存约为200MB,占用的物理内存约为16MB。 Java UDF worker启动之后,占用的虚拟内存约为2.5GB,占用的物理内存约为50MB。
  • 数组类型处理 GaussDB(DWS)支持基础数组类型的转换,只需要在创建函数时在数据类型后追加 [] 即可,例如: CREATE FUNCTION java_arrayLength(INTEGER[]) RETURNS INTEGER AS 'Example.getArrayLength' LANGUAGE JAVA; Java代码类似于: public class Example { public static int getArrayLength(Integer[] intArray) { return intArray.length; } } 那么下面的调用的语句后: SELECT java_arrayLength(ARRAY[1, 2, 3]); 得到预期结果应该如下所示: java_arrayLength --------------------- 3 (1 row)
  • 重载函数 PL/Java支持重载函数,因此可以创建同名函数,或者调用Java代码中的重载方法。步骤如下: 创建重载函数 例如,在Java中可以实现两个方法名相同,输入参数类型不同的方法dummy(int) 和dummy(String) public class Example { public static int dummy(int value) { return value*2; } public static String dummy(String value) { return value; } } 并在GaussDB(DWS)中创建两个同名函数分别指定为上述两个方法: CREATE FUNCTION java_dummy(INTEGER) RETURNS INTEGER AS 'Example.dummy' LANGUAGE JAVA; CREATE FUNCTION java_dummy(VARCHAR) RETURNS VARCHAR AS 'Example.dummy' LANGUAGE JAVA; 调用重载函数 在调用重载函数时,GaussDB(DWS)会根据输入的参数类型去调用匹配该类型的Java方法。因此上述两个函数的调用结果如下所示: SELECT java_dummy(5); java_dummy ----------------- 10 (1 row) SELECT java_dummy('5'); java_dummy --------------- 5 (1 row) 需要注意的是,由于GaussDB(DWS)对数据类型存在隐式转换的情况,因此建议在调用重载函数时,指定输入参数的类型,例如: SELECT java_dummy(5::varchar); java_dummy ---------------- 5 (1 row) 此时会优先匹配所指定的参数类型,如果不存在指定参数类型的Java方法,则会对参数进行隐式转换匹配转换后的参数类型对应的Java方法。 SELECT java_dummy(5::INTEGER); java_dummy ----------------- 10 (1 row) DROP FUNCTION java_dummy(INTEGER); SELECT java_dummy(5::INTEGER); java_dummy ---------------- 5 (1 row) 隐式转换的数据类型包括: 可以默认转换为INTEGER类型的包括:SMALLINT 可以默认转换为BIGINT类型的包括:SMALLINT, INTEGER 可以默认转换为BOOL类型的包括:TINYINT, SMALLINT, INTEGER, BIGINT 可以默认转换为TEXT类型的包括:CHAR, NAME, BIGINT, INTEGER, SMALLINT, TINYINT, RAW, FLOAT4, FLOAT8, BPCHAR, VARCHAR, NVARCHAR2, DATE, TIMESTAMP, TIMESTAMPTZ, NUMERIC, SMALLDATETIME 可以默认转换为VARCHAR类型的包括:TEXT, CHAR, BIGINT, INTEGER, SMALLINT, TINYINT, RAW, FLOAT4, FLOAT8, BPCHAR, DATE, NVARCHAR2, TIMESTAMP, NUMERIC, SMALLDATETIME 删除重载函数 对于重载函数,删除时需要指定函数的参数类型,否则无法删除。 DROP FUNCTION java_dummy(INTEGER);
  • 示例 使用PL/Java函数时,需要首先将Java方法的实现打包为jar包并且部署到数据库中,然后使用数据库管理员账号创建函数,考虑兼容性问题,请使用1.8.0_322版本的JRE进行编译。 编译jar包。 Java方法的实现和出包可以借助IDE来实现,以下是一个通过命令行来进行编译和出包的简单的示例,通过这个简单示例可以创建出一个包含单个方法的jar包文件。 首先,编写一个Example.java文件,在此文件中实现子字符串大写转换的方法,本例中类名为Example,方法名为upperString,内容如下: 1 2 3 4 5 6 7 public class Example { public static String upperString (String text, int beginIndex, int endIndex) { return text.substring(beginIndex, endIndex).toUpperCase(); } } 然后,创建manifest.txt清单文件,文件内容如下: 1 2 3 4 5 6 Manifest-Version: 1.0 Main-Class: Example Specification-Title: "Example" Specification-Version: "1.0" Created-By: 1.6.0_35-b10-428-11M3811 Build-Date: 08/14/2018 10:09 AM 其中,Manifest-Version定义了manifest文件的版本,Main-Class定义了jar文件的入口类,Specification-Title和Specification-Version属于包的扩展属性,Specification-Title定义了扩展规范的标题,Specification-Version定义了扩展规范的版本,Created-By声明了该文件的生成者,Build-Date声明了该文件构建日期。 最后,编译java文件并打包得到javaudf-example.jar 1 2 javac Example.java jar cfm javaudf-example.jar manifest.txt Example.class jar包的命名规则应符合JDK命名要求,如果含有非法字符,在部署或者使用函数时将出错。 部署jar包。 Jar包首先需要放置到OBS服务器中,放置方法具体请参见《对象存储服务控制台指南》的上传文件章节。接着创建访问密钥AK/SK,获取访问密钥的具体步骤,请参见创建访问密钥(AK和SK)章节。登录数据库运行gs_extend_library函数,将文件导入到GaussDB(DWS)中: 1 SELECT gs_extend_library('addjar', 'obs://bucket/path/javaudf-example.jar accesskey=access_key_value_to_be_replaced secretkey=secret_access_key_value_to_be_replaced region=region_name libraryname=example'); gs_extend_library函数如何使用请参见 管理jar包和文件。函数中的AK/SK值,请用户根据实际获取值替换。region_name请用户根据实际所在的区域名称替换。 使用PL/Java函数。 首先,使用拥有sysadmin权限的数据库用户(例如:dbadmin)登录数据库并创建java_upperstring函数如下: 1 2 3 4 CREATE FUNCTION java_upperstring(VARCHAR, INTEGER, INTEGER) RETURNS VARCHAR AS 'Example.upperString' LANGUAGE JAVA; 函数java_upperstring中定义的数据类型为GaussDB(DWS)的数据类型。该数据类型需要和步骤1中java定义的方法upperString中数据类型一一对应。GaussDB(DWS)与Java数据类型的对应关系,请参见表1。 AS子句用于指定该函数所调用的Java方法的类名和static方法名,格式为“类名.方法名”。该字段需要和步骤1中java定义的类名和方法名一致。 使用PL/Java函数时,LANGUAGE字段应指定为JAVA。 CREATE FUNCTION更多说明,请参见创建函数。 然后,执行java_upperstring函数: 1 SELECT java_upperstring('test', 0, 1); 得到预期结果为: 1 2 3 4 java_upperstring --------------------- T (1 row) 授权普通用户使用PL/Java函数。 创建普通用户,名称为udf_user。 1 CREATE USER udf_user PASSWORD 'password'; 授权普通用户udf_user对java_upperstring函数的使用权限。注意,此处需要把函数所在模式和函数的使用权限同时赋予给用户,用户才可以使用此函数。 1 2 GRANT ALL PRIVILEGES ON SCHEMA public TO udf_user; GRANT ALL PRIVILEGES ON FUNCTION java_upperstring(VARCHAR, INTEGER, INTEGER) TO udf_user; 以普通用户udf_user登录数据库。 1 SET SESSION SESSION AUTHORIZATION udf_user PASSWORD 'password'; 执行java_upperstring函数: 1 SELECT public.java_upperstring('test', 0, 1); 得到预期结果为: 1 2 3 4 java_upperstring --------------------- T (1 row) 删除函数。 如果不再使用该函数可以进行删除: 1 DROP FUNCTION java_upperstring; 卸载jar包。 使用gs_extend_library函数卸载jar包: 1 SELECT gs_extend_library('rmjar', 'libraryname=example');
  • wlm_sql_allow_list 参数说明:用于指定资源管理SQL白名单语句,SQL白名单语句不受资源管理监控。 参数类型:SIGHUP 取值范围:字符串,支持最大字符为1024 默认值:空 wlm_sql_allow_list中可指定一条或多条SQL白名单语句,指定多条时,通过“;”进行分隔。 系统通过前置匹配判断SQL语句是否受监控,不区分大小写,例如:wlm_sql_allow_list='SELECT',则所有select语句均不受资源管理监控。 识别参数值白名单字符串头部的空格,例如:'SELECT'与' SELECT'的含义是不一致的,' SELECT'只过滤头部带空格的SELECT语句。 系统默认部分SQL语句为白名单语句,默认白名单语句不可修改;可以通过系统视图gs_wlm_sql_allow查询默认和已经通过GUC设置成功的SQL白名单语句。 通过wlm_sql_allow_list指定的SQL语句不可追加,只能通过覆盖的方式设置;若需追加SQL语句,需要先查出原先指定的GUC值,在原值后面加补上新增的语句,以“;”分隔后重新设置。
  • resource_track_duration 参数说明:设置资源监控实时视图(参见表1)中记录的语句执行结束后进行历史信息转存的最小执行时间。当执行完成的作业,其执行时间不小于此参数值时,作业信息会从实时视图(以statistics为后缀的视图)转存到相应的历史视图(以history为后缀的视图)中。该参数只有当enable_resource_track为on时才有效。 参数类型:USERSET 取值范围:整型,0~INT_MAX,单位为秒。 值为0时,资源监控实时视图(表1)中记录的所有语句都进行历史信息归档。 值大于0时,资源监控实时视图(表1)中记录的语句的执行时间超过这个值就会进行历史信息归档。 默认值:60s
  • resource_track_subsql_duration 参数说明:设置过滤存储过程中子语句的最小执行时间。该参数仅8.2.1及以上集群版本支持。 当存储过程中子语句执行完成时,其执行时间大于此参数设置值时,作业信息会转储到TopSQL归档表中。该参数只有当enable_track_record_subsql为on时生效。 参数类型:USERSET 取值范围:整型,0~INT_MAX,单位为秒。 值为0时,存储过程中所有子语句都进行历史信息归档。 值大于0时,存储过程中子语句记录的执行时间超过设置值就会进行历史信息归档。 默认值:180s
  • enable_dynamic_workload 参数说明:是否开启动态负载管理功能。 参数类型:POSTMASTER 取值范围:布尔型 on表示打开动态负载管理功能。 off表示关闭动态负载管理功能。 默认值:on 开启内存自适应后,收集统计信息后不再需要使用work_mem进行算子内存使用调优,由系统根据当前负载情况,为每个语句生成计划,并估算每个算子的内存使用量和整个语句的内存使用量。系统根据负载情况和整个语句内存使用量进行队列调度,所以多并发场景会出现语句排队的情况。 由于优化器估算内存存在估算不准的情况,会出现语句内存使用量低估或高估的情况。低估时,执行时内存会自动扩展。高估时,会导致系统内存利用不足,排队语句增多,可能导致性能非最优。此时需要识别语句估算内存远大于实际DN峰值内存的语句,通过设置query_max_mem进行调优,详见SQL调优关键参数调整。 由于优化器估算内存存在估算不准的情况,8.2.1之前的集群版本中为了避免出现CCN全局排队的情况,经常需要关闭enable_dynamic_workload参数,这样操作会导致动态负载管理功能不可用。因此在8.2.1集群版本中引入了enable_global_memctl,在出现CCN异常排队情况时,可直接关闭参数enable_global_memctl,作业即可下发至资源池运行。
  • transaction_pending_time 参数说明:当enable_transaction_parctl为on时,事务块语句和存储过程语句排队的最大时间。 参数类型:USERSET 取值范围:整型,-1~INT_MAX,单位为秒。 值为-1或0:事务块语句和存储过程语句无超时判断,排队至资源满足可执行条件。 值大于0:事务块语句和存储过程语句排队超过所设数值的时间后,无视当前资源情况强制执行。 默认值:0 此参数仅对存储过程及事务块的内部语句有效,即PG_SESSION_WLMSTAT中enqueue字段显示为Transaction或StoredProc的语句才会生效。
  • enable_wlm_internal_memory_limit 参数说明:是否开启负载管理对语句估算内存的内置限制功能。(该参数仅8.2.0及以上集群版本支持) 负载管理中的内存管理模块中,会对语句的估算内存做一些内置限制,如: 语句的估算内存无法超过关联资源池内存上限的80%。 当资源池的并发控制参数active_statements不为1时,语句的估算内存无法超过关联资源池内存上限的40%。 内核中语句的估算内存开始时是一个范围值,可以理解为[最小估算内存,最大估算内存],其中,最大估算内存为语句运行性能最佳所需要的内存资源,最小估算内存为通过结果集下盘而保证语句能运行所需要的内存资源;最终的语句估算内存会在这个范围内取一个合理值,这个范围值中的最大估算内存也无法超过关联资源池内存上限的90%; 这些内置限制的目的是为了防止语句内存过高估算场景,高估场景下,语句预占内存资源过大,会引发后续作业排队,导致系统资源利用率下降,因此,内核限制了单个语句的估算内存上限值。内置限制可能会导致语句执行计划不是最优,可能在一定程度上影响单个语句性能;从8.2.0集群版本开始增加了内存负反馈机制,从一定程度上缓解了语句严重高估导致系统资源利用率下降的问题。为此,对应的从8.2.0版本开始,增加了enable_wlm_internal_memory_limit参数,由用户自由控制是否开启内置限制功能。 参数类型:SIGHUP 取值范围:布尔型 on表示打开负载管理对语句估算内存的内置限制。 off表示关闭负载管理对语句估算内存的内置限制。 默认值:on
  • allow_zero_estimate_memory 参数说明:是否允许语句估算内存为0。(该参数仅8.2.0及以上集群版本支持) 当语句查询的表没有统计信息时,该语句在CN节点上的估算内存可能设置为0,当估算内存为0时,语句的内存将会变化为算子内存受到work_mem限制(work_mem来限制算子内存的方式已经不推荐使用),如果work_mem设置较大,而语句的算子数量较多时,语句的实际内存值也能会较大,该参数设置为off时,将不允许未做analyze的查询出现估算内存为0,避免不可预期的问题。 参数类型:SIGHUP 取值范围:布尔型 on表示允许语句估算内存为0。 off表示不允许语句估算内存为0。 默认值:on