数据库微观案例第50期

原创彭冲 2024-10-06

482

本期内容如下：

用户认证问题
大版本升级问题
约束延迟生效
服务端可控制自动提交
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-defined objects that refer to internal polymorphic functions with arguments of type "anyarray" or "anyelement". These user-defined objects must be dropped before upgrading and restored afterwards, changing them to refer to the new corresponding 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视图进行观测，自治事务的会话连接，sessionid对应的is_autonomous_session为t，parent_sessionid不为0。

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

勤耕细作，用心积微；静待花开，量变质成。

墨力计划 postgresql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者