本期内容如下：
用户认证问题
大版本升级问题
约束延迟生效
服务端控制自动提交
package自治事务
1.PG用户认证问题
postgresql.conf文件里password_encryption参数设置为scram-sha-256，但pg_hba.conf文件里认证方法设置为md5，为什么认证能通过呢？
官方文档对密码认证有详细解释：
https://www.postgresql.org/docs/16/auth-password.html
当pg_hba.conf文件配置的认证方法为md5，但服务端用户密码采用SCRAM加密时，服务端会自动采用SCRAM认证。
演示过程如下：
$ psql -c "SELECT rule_number,type,address,database,user_name,auth_method FROM pg_hba_file_rules;"
rule_number | type | address | database | user_name | auth_method
-------------+-------+---------+----------+-----------+-------------
1 | local | | {all} | {all} | trust
2 | host | 0.0.0.0 | {all} | {all} | md5
(2 rows)
$ psql -h 192.168.20.200 -c "SELECT system_user,current_setting('password_encryption')"
Password for user postgres:
system_user | current_setting
--------------+-----------------
md5:postgres | scram-sha-256
(1 row)
注：SQL查询里可以使用system_user来获取pg_hba.conf里配置的客户端认证方法。
2.PG大版本升级问题
使用pg_upgrade工具从12升级到pg16，进行升级检查时遇到如下错误提示信息：
...Your installation contains user-definedobjects that refer to internal polymorphicfunctions with arguments of type "anyarray"or "anyelement". These user-defined objectsmust be dropped before upgrading and restoredafterwards, changing them to refer to the newcorresponding functions with arguments of type"anycompatiblearray" and "anycompatible'.
经过分析定位，低版本有用户自定义聚合函数，结构如下：
CREATE AGGREGATE public.group_concat(anyelement) (
SFUNC = array_append,
STYPE = anyarray,
INITCOND = '{}'
);
用户函数在12里引用了系统内部函数array_append，该系统函数具有多态性，array_append(anyarray, anyelement)形态从14版本开始升级为：
array_append(anycompatiblearray, anycompatible)::anycompatiblearray
因此从14版本开始，引用array_append(anyarray, anyelement)形态时会提示错误：
ERROR: function array_append(anyarray, anyelement) does not exist
需要重构为：
CREATE AGGREGATE public.group_concat(anycompatible) (
SFUNC = array_append,
STYPE = anycompatiblearray,
INITCOND = '{}'
);
升级的多态数据类型主要是为了兼容unknown未知类型，典型问题如下：
CREATE FUNCTION foo(anyelement, anyelement) RETURNS bool
LANGUAGE SQL AS 'select $1 = $2';
SELECT foo('a', 'b');
ERROR: could not determine polymorphic type because input has type unknown
下面语句可找到其他受影响的系统函数
WITH tm AS(
SELECT proname,prorettype::regtype,unnest(proargtypes)::regtype as argtype
FROM pg_proc
WHERE pronamespace=('pg_catalog'::regnamespace)
) SELECT distinct proname
FROM tm
WHERE argtype in ('anycompatible','anycompatiblearray')
AND proname not like 'anycompatible%'
;
受影响的相关函数如下：
array_append
array_prepend
array_cat
array_position
array_positions
array_remove
array_replace
width_bucket
lead
lag
3.PG约束延迟生效
PostgreSQL的约束延迟生效功能，是指在执行数据更新操作时，可以先暂时允许违反约束条件，直到事务提交或回滚时，才对约束条件进行检查和生效。约束延迟生效功能可以帮助用户在数据更新时，避免由于约束条件限制而导致的数据更新失败。
主要包含以下三种：
非延迟(NOT DEFERRABLE)
延迟生效之 DEFERRABLE INITIALLY IMMEDIATE
延迟生效之 DEFERRABLE INITIALLY DEFERRED
这个功能仅影响 UNIQUE，PRIMARY KEY，REFERENCES （外键）和 EXCLUDE 约束，他们是可延迟的，而NOT NULL和CHECK是不可以做延迟生效的。这点和ORACLE不同，ORACLE中check也可以延迟。并且IMMEDIATE会在每一个语句执行后进行约束检查，DEFERRED则只会在事务结束时才检查约束。（DEFERRED 只是推迟检查而不是不检查）
当约束设置可延迟生效时可以使用set constraints命令来推迟检查直到事务结束
postgres=# \help SET CONSTRAINTS
Command: SET CONSTRAINTS
Description: set constraint check timing for the current transaction
Syntax:
SET CONSTRAINTS { ALL | name [, ...] } { DEFERRED | IMMEDIATE }
创建表时指定
CREATE TABLE t_nd(id int primary key not deferrable);
除了创建表的时候就加上约束的延迟选项外，也可以使用ALTER TABLE语句去增加约束延迟
ALTER TABLE table_name ALTER CONSTRAINT constraint_name DEFERRABLE INITIALLY DEFERRED;
4.openGauss可控制事务是否自动提交
openGauss在MySQL模式下可以设置autocommit为off，例如在user级设置该参数：
postgres=# alter user admin set autocommit to off;
ALTER ROLE
5.MogDB的package自治事务
openGauss已经支持自治事务，示例如下：
create table test_auto_data (a int);
create or replace procedure test_auto_pp()
as
PRAGMA AUTONOMOUS_TRANSACTION;
begin
insert into test_auto_data select generate_series(1,100000);
commit;
end;
/
begin -- 主事务开始
test_auto_pp(); -- 执行自治事务
insert into test_auto_data select generate_series(1,2000000);
select 1/0;
end
/
自治事务可通过pg_running_xacts视图进行观测。
MogDB增强了在package里使用自治事务，下面为示例：
CREATE OR REPLACE PACKAGE Lis_Debug_Pkg IS
PROCEDURE Debug_Log(p_1 IN NUMBER);
END Lis_Debug_Pkg;
/
CREATE OR REPLACE PACKAGE BODY Lis_Debug_Pkg IS
PROCEDURE Debug_Log(p_1 IN NUMBER ) IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
commit;
END Debug_Log;
END Lis_Debug_Pkg;
/
本文结束～
往期回顾
与我联系
微信公众号：象楚之行
墨天轮：https://www.modb.pro/u/15675
微信：skypkmoon
勤耕细作，用心积微；静待花开，量变质成。
原文作者：象楚之行；如有侵权，请联系管理员删除。
