数据库微观案例第53期

原创彭冲 2024-12-12

476

本期主要介绍openGauss系数据库的几个案例：

SQLLDR像素级模仿
全表查询结果集溢出
timestamp类型B模式带时区
update并发更新死锁推演图

SQLLDR像素级模仿

openGauss提供了gs_loader命令行工具，用于将外部数据文件快速加载到数据库表中‌。商业发行版MogDB对gs_loader进一步增强，几乎达到SQLLDR像素级模仿的用法。

参考链接：DarkAthena https://www.modb.pro/db/1847308456702861312

gs_loader使用示例如下：

1.使用管理员用户创建新的用户

CREATE USER load_user WITH PASSWORD '************';

2.将public schema权限赋给新的用户

GRANT ALL ON SCHEMA public TO load_user;

3.创建新用户并授权

新用户授权gs_copy_summary执行结果汇总表和pgxc_copy_error_log错误表

gs_copy_summary表记录COPY执行结果汇总，包括成功行数，出错行数，忽略行数，空行数。

SELECT copy_summary_create() WHERE NOT EXISTS(SELECT * FROM pg_tables WHERE schemaname='public' AND tablename='gs_copy_summary');

GRANT INSERT,SELECT ON  public.gs_copy_summary To load_user;

SELECT copy_error_log_create() WHERE NOT EXISTS(SELECT * FROM pg_tables WHERE schemaname='public' AND tablename='pgxc_copy_error_log');

GRANT INSERT,SELECT,DELETE ON  public.pgxc_copy_error_log To load_user;

4.切换到普通用户load_user，并创建业务表

\c - load_user

创建业务表loader_tbl

CREATE TABLE  loader_tbl
(
    ID   NUMBER,
    NAME VARCHAR2(20),
    CON  VARCHAR2(20),
    DT   DATE
);

5.创建gs_loader所需的控制文件loader.ctl

在gs_loader客户端机器上创建控制文件loader.ctl

LOAD DATA
truncate into table loader_tbl
fields terminated by ','
trailing nullcols
(
    id integer external,
    name char(32),
    con ":id || '-' || :name",
    dt date
)

6.模拟数据文件data.csv

在gs_loader客户端机器上创建数据文件data.csv

1,OK,,2007-07-8
2,OK,,2008-07-8
3,OK,,2009-07-8
4,OK,,2007-07-8
43,DISCARD,,2007-07-8
,,,
32,DISCARD,,2007-07-8
a,ERROR int,,2007-07-8
8,ERROR date,,2007-37-8
,,,,
 ,
8,ERROR fields,,2007-37-8
,,,
5,OK,,2021-07-30

7.执行gs_loader导入

gs_loader control=loader.ctl data=data.csv log=loader.log bad=loader.bad discard=loader.discard create=false clean=true errors=5 host=127.0.0.1  port=5090 db=postgres passwd=**** user=admin

执行结果：

gs_loader: version 0.1

 6 Rows successfully loaded. 

log file is: 
 loader.log

查看错误记录

 cat loader.bad 

 a,ERROR int,,2007-07-8
 8,ERROR date,,2007-37-8
  ,
 8,ERROR fields,,2007-37-8

全表查询结果集溢出

业务查询这个大表时，字段有50多个，记录有三千多万

select * from tab;

执行时提示如下错误：

Error: Integer overflow when select execution.

其原因是表的行列结果集超过了4字节Int的最大值，从openGauss 5.0开始，内核加了这个硬限制。

src/bin/psql/print.cpp

    if (res >= (int64)PG_INT32_MAX) {
        fprintf(stderr, _("Error: Integer overflow when select execution.\n"));
        exit(EXIT_FAILURE);
    }

B模式timestamp类型带时区信息

在PG系里，timestamp类型不带时区信息，timestamptz带时区信息

postgres=# create table t(a timestamp,b timestamptz);
CREATE TABLE
postgres=# \d t
                 Table "public.t"
 Column |            Type             | Modifiers 
--------+-----------------------------+-----------
 a      | timestamp without time zone | 
 b      | timestamp with time zone    |

但openGauss系B模式下timestamp类型解析为带时区信息的timestamp(0) with time zone

db_mysql=# create table t1(a timestamp);
CREATE TABLE
db_mysql=# \d t1
                Table "public.t1"
 Column |            Type             | Modifiers 
--------+-----------------------------+-----------
 a      | timestamp(0) with time zone |

从MySQL迁移过来的业务如果不希望带时区信息时，应该明确使用timestamp without time zone类型，而不要使用timestamp别名类型。

update并发更新死锁推演图

客户端并发更新同一张表的多行数据时，数据库会自动检测到死锁，出错信息参考如下：

ERROR:  deadlock detected
DETAIL:  Process 3001767 waits for ShareLock on transaction 103443111; blocked by process 2983755.
Process 2983755 waits for ShareLock on transaction 103443178; blocked by process 3001767.
HINT:  See server log for query details.
CONTEXT:  while rechecking updated tuple (10,163) in relation "test"

表结构和测试语句如下：

create table test(id int,create_timestamp timestamp);
insert into test select n,now() from generate_series(1,10) n;

打开两个客户端会话执行下面的匿名块即可复现出死锁问题。

do
$do$
BEGIN
   FOR i IN 1..100000 LOOP
      UPDATE test
      SET create_timestamp = clock_timestamp()
      WHERE id>=2;
      COMMIT;
   END LOOP;
END;
$do$ language plpgsql;

内核同事传成大佬(walminer作者:https://gitee.com/movead/XLogMiner)整理出如下的推演图，分享如下：