数据库微观案例第51期

原创彭冲 2024-10-18

199

本期内容如下：

事务号耗尽告警
数据类型转型
字段引用关键字
字段带默认值修改类型
触发器案例

1.事务号耗尽告警问题

某客户PG 12.4数据库无法写入数据，查询某个db下namespace系统表年龄很大，达到事务号冻结的边界值，即便进入单用户维护模式进行freeze操作，也提示如下信息：

found xmin 3708522673 from before relfrozenxid 4244967804;
复制

后了解业务有大量使用临时表对象，系统模式表有两百多个临时模式，于是建议客户删除这些临时模式后再执行vacuum。

客户最终采取快速重建该db后，整个cluster恢复正常。

2.数据类型转型问题

基于PG的国产库遇到数据类型强制转换的问题比较常见，下面这个场景是业务程序使用Boolean类型映射数据库字段类型为bit类型，但SQL语句插入值使用int整型，此时int到bit类型数据库不会进行自动转换，表结构参考示例如下：

postgres=# create table tab_bit(a bit(1));
CREATE TABLE
postgres=# insert into tab_bit values (1);
ERROR:  column "a" is of type bit but expression is of type integer
LINE 1: insert into tab_bit values (1);
                                    ^
HINT:  You will need to rewrite or cast the expression.
复制

业务SQL语句插入值时需要明确使用cast转型：

insert into tab_bit values (cast(1 as bit));
复制

3.字段使用关键字引用问题

如果业务字段名称用到了数据库关键字，例如order关键字，此时带双引号可以创建成功

postgres=# create table tab("order" varchar);
CREATE TABLE
复制

但后面的同学使用时会遇到麻烦：

postgres=# \d tab
                     Table "public.tab"
 Column |       Type        | Collation | Nullable | Default 
--------+-------------------+-----------+----------+---------
 order  | character varying |           |          | 

postgres=# select order from tab;
ERROR:  syntax error at or near "from"
LINE 1: select order from tab;
                     ^
复制

尤其困惑的是上面表结构的字段看起来也正常(并未展示建表结构里引用的双引号)，为什么查询使用就报错呢?

其实查看最初的原始建表语句就能get到，元命令\d查看结构有这个限制

例如兼容MySQL模式的国产库，可以使用show create table查看：

postgres=# show create table tab1;
 Table |                      Create Table                      
-------+--------------------------------------------------------
 tab1  | SET search_path = public;                             +
       | CREATE TABLE tab1 (                                   +
       |     "order" varchar                                   +
       | )                                                     +
       | WITH (orientation=row, compression=no, fillfactor=80);
(1 row)
复制

PG或Oracle兼容模式可以使用pg_get_tabledef函数

postgres=# select pg_get_tabledef('tab1');
                    pg_get_tabledef                     
--------------------------------------------------------
 SET search_path = public;                             +
 CREATE TABLE tab1 (                                   +
     "order" varchar                                   +
 )                                                     +
 WITH (orientation=row, compression=no, fillfactor=80);
(1 row)
复制

这样使用查询语句就一目了然，当然原始的建表语句字段名称不触发数据库关键字是最规范的，但大多数迁移或遗留项目或多或少都存在这种问题，不可避免。

4.字段定义带默认值时修改类型报错

CREATE TABLE tab(a varchar default 0::varchar);
复制

此时修改a字段数据类型时，可能会失败，因为字段的默认值定义也包含数据类型。

postgres=# alter table tab alter column a type int using a::int;
ERROR:  default for column "a" cannot be cast automatically to type integer
复制

目前PG并不能根据ALTER修改字段的新类型自动修改默认值里的数据类型，因此带默认值的字段修改类型需要先删除默认值，改完类型后再重新适配默认值。

5.触发器案例

用户反馈数据库遇到一个奇怪的现象，删除一笔记录后再次查询，该记录仍然存在。

postgres=# delete from tab where id=100;
DELETE 0
复制

按主键去删除一条记录，在psql里回显为DELETE 0，再次查看该条记录，仍然可见。

查看该表存在触发器，触发器函数的主要定义如下：

CREATE OR REPLACE FUNCTION trigger_f1()
     RETURNS trigger
     LANGUAGE 'plpgsql'
     COST 100
     VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
   ...
BEGIN
    IF TG_OP = 'INSERT' THEN   
        INSERT INTO tab_log... ;
    ELSIF TG_OP = 'UPDATE' THEN   
        INSERT INTO tab_log... ;
    ELSIF TG_OP = 'DELETE' THEN  
        INSERT INTO tab_log... ;
    END IF; 
  
 exception
   when others then
     INSERT INTO error_log... ; 
     RETURN null;

RETURN new;
END;
$BODY$;
复制

对触发器编程比较熟悉的朋友可能一眼就发现了问题。

本地模拟测试插入和修改正常，删除确实未成功，也排除了函数里delete语句执行出错走异常的分支影响。

将上面函数里最后一处RETURN改写到Insert/Update/Delete分区里，如下所示：

CREATE OR REPLACE FUNCTION trigger_f1()
     RETURNS trigger
     LANGUAGE 'plpgsql'
     COST 100
     VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
   ...
BEGIN
    IF TG_OP = 'INSERT' THEN  
        INSERT INTO tab_log... ; 
        RETURN new;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO tab_log... ; 
        RETURN new;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO tab_log... ; 
        RETURN new;
    END IF;   
 exception
   when others then
     INSERT INTO error_log... ; 
     RETURN null;
END;
$BODY$;
复制

再进行复测也能得到相同的测试结果。查看官网触发器使用文档，可以发现new关键字代表DML操作的新数据行，对Insert和Update有效，而对Delete操作该变量不会赋值。

所以对Delete操作，应该return返回的是旧数据行，修改版本如下：

CREATE OR REPLACE FUNCTION trigger_f1()
     RETURNS trigger
     LANGUAGE 'plpgsql'
     COST 100
     VOLATILE NOT LEAKPROOF
AS $BODY$
DECLARE
   ...
BEGIN
    IF TG_OP = 'INSERT' THEN  
        INSERT INTO tab_log... ; 
        RETURN new;
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO tab_log... ; 
        RETURN new;
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO tab_log... ; 
        RETURN old;
    END IF;   
 exception
   when others then
     INSERT INTO error_log... ; 
     RETURN null;
END;
$BODY$;
复制

最后再测试DELETE操作删除正常。

针对操作前后的行变量，需要注意行级和语句级触发下Old和New行变量的使用场景。

触发器里另一个需要注意的问题：避免陷入无限递归

可以使用PG_TRIGGER_DEPTH()函数进行预防

CREATE OR REPLACE FUNCTION trigger_f2() RETURNS trigger
   LANGUAGE plpgsql 
AS $BODY$
BEGIN
   IF (...) AND pg_trigger_depth() < 2 THEN
      -- do something ;
   END IF;
 
   RETURN NEW;
END;
$BODY$;
复制

本文结束～

往期回顾

与我联系

微信公众号：象楚之行
墨天轮：https://www.modb.pro/u/15675
微信：skypkmoon

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

墨力计划

最后修改时间：2024-10-24 14:16:25

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

关注作者