关于数据库:MogDB大对象LargeObject存取测试

44次阅读

共计 4044 个字符，预计需要花费 11 分钟才能阅读完成。

openGauss/MogDB 数据库里 bytea 二进制类型受 segment size 编译参数限度，默认不能超过 1GB，如果字段存储数据超过 1GB 能够应用 lo（Large Object）扩大类型。
01
lo 类型须要先创立 lo extension

$ gsql -p5432 -Uomm postgres -r
gsql ((MogDB 2.0.1 build f892ccb7) compiled at 2021-07-09 16:15:21 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.

postgres=# create extension lo;
CREATE EXTENSION

创立完 lo 扩大，咱们新建 test_lo 表，info 字段应用 lo 类型。

postgres=# create table test_lo(id int,info lo);
CREATE TABLE

创立 test_lo 表治理触发器，对 update 和 delete 操作应用 lo_manage 函数治理，不然会产生孤立大对象。

postgres=# create trigger test_lo before UPDATE OR DELETE ON test_lo FOR EACH ROW EXECUTE procedure lo_manage(info);
WARNING:  Trigger function with non-plpgsql type is not recommended.
DETAIL:  Non-plpgsql trigger function are not shippable by default.
HINT:  Unshippable trigger may lead to bad performance.
CREATE TRIGGER

应用 dd 生成 2GB 文件：

postgres=#  \! dd if=/dev/zero of=test_lo bs=1M count=2048 && sync
记录了 2048+0 的读入
记录了 2048+0 的写出
2147483648 字节（2.1 GB，2.0 GiB）已复制，0.805435 s，2.7 GB/s

02
测试 lo_import 函数导入数据到数据表

postgres=# insert into test_lo values(1,lo_import('/home/omm/test_lo'));
INSERT 0 1

能够看到数据能够失常导入，如果不应用 lo 类型，应用 bytea 类型会提醒上面的报错。

ERROR: requested length too large

03
测试 lo_export 函数导出数据表数据到文件

postgres=# select lo_export(test_lo.info,'/home/omm/test_ext_lo') from test_lo where id=1;
 lo_export 
-----------
         1
(1 row)

能够看到数据失常导出。

查看导入导出的数据文件，也能够应用 diff 命令进行比对。

postgres=# \! ls -lh test_*
-rw-r--r-- 1 omm dbgrp 2.0G 12 月 17 13:00 test_ext_lo
-rw------- 1 omm dbgrp 2.0G 12 月 17 12:58 test_lo

04
查看数据表大对象字段大小
分两步进行，首先查大对象字段的 oid（lo 类型字段在用户表外面只存储一个 oid 援用指针，并不理论存数据）

postgres=# select * from test_lo;
 id | info  
----+-------
  1 | 16392
(1 row)

理论数据应用多条 bytea 记录存储在 pg_largeobject 表，能够依据 oid 查问统计字段的大小。

postgres=# select loid,pg_size_pretty(sum(octet_length(data)))
from pg_largeobject 
where loid =16392  
group by loid;
 loid  | pg_size_pretty 
-------+----------------
 16392 | 2048 MB
(1 row)

也能够应用如下函数来查问：

create or replace function get_lo_size(oid)
returns bigint
volatile strict
as $function$
declare
    fd integer;
    sz bigint;
begin
    fd := lo_open($1, x'40000'::int);
    perform lo_lseek64(fd, 0, 2);
    sz := lo_tell64(fd);
    perform lo_close(fd);
    return sz;
end;
$function$ language plpgsql;

查问后果如下：

postgres=# select pg_size_pretty(get_lo_size(16392));
 pg_size_pretty 
----------------
 2048 MB
(1 row)

再来测试 JDBC 应用层的应用：

05
JDBC-Java 文件入库

    public static void main(String[] args) throws Exception{Class.forName("org.postgresql.Driver");

        Connection conn = DriverManager.getConnection("jdbc:postgresql://ip:port/dbname","username","password");
        
        conn.setAutoCommit(false);
        
        LargeObjectManager lobj = conn.unwrap(org.postgresql.PGConnection.class).getLargeObjectAPI();

        long oid = lobj.createLO(LargeObjectManager.READ | LargeObjectManager.WRITE);

        LargeObject obj = lobj.open(oid, LargeObjectManager.WRITE);

        File file = new File("c:/work/test_lo");
        FileInputStream fis = new FileInputStream(file);

        byte buf[] = new byte[10*1024*1024];
        int s, tl = 0;
        while ((s = fis.read(buf, 0, 2048)) > 0)
        {obj.write(buf, 0, s);
            tl += s;
        }

        obj.close();

        PreparedStatement ps = conn.prepareStatement("INSERT INTO test_lo VALUES (?, ?)");
        ps.setInt(1, 100);
        ps.setLong(2, oid);
        ps.executeUpdate();
        ps.close();
        fis.close();

        conn.commit();
        conn.close();}

06
JDBC-Java 读数据输入到文件

    public static void main(String[] args) throws Exception{Class.forName("org.postgresql.Driver");

        Connection conn = DriverManager.getConnection("jdbc:postgresql://ip:port/dbname","username","password");

        conn.setAutoCommit(false);

        LargeObjectManager lobj = conn.unwrap(org.postgresql.PGConnection.class).getLargeObjectAPI();

        PreparedStatement ps = conn.prepareStatement("SELECT info FROM test_lo WHERE id = ?");
        ps.setInt(1, 100);
        ResultSet rs = ps.executeQuery();
        
        File file = new File("c:/work/test_out_lo");
        FileOutputStream fos = new FileOutputStream(file);
        
        while (rs.next())
        {long oid = rs.getLong(1);
            LargeObject obj = lobj.open(oid, LargeObjectManager.READ);

            byte buf[] = new byte[10*1024*1024];
            int s, tl = 0;
            while ((s = obj.read(buf, 0, 2048)) > 0)
            {fos.write(buf, 0, s);
                tl += s;
            }

            obj.close();}
        rs.close();
        ps.close();
        fos.close();

        conn.commit();
        conn.close();}

Jdbc-Java Large Object 示例参考：（复制链接至浏览器中浏览）

https://jdbc.postgresql.org/d…

墨天轮原文链接：https://www.modb.pro/db/21475…（复制链接至浏览器或点击文末浏览原文查看)

对于作者
彭冲，云和恩墨 PG 技术顾问，网名“多米爸比”，PG 社区认证专家，中国首期 PostgreSQL ACE Partner，多年从事基于 PostgreSQL 数据库的软件研发，擅长于 PL/PGSQL 业务迁徙及优化，Oracle 到 PostgreSQL 的迁徙降级，异构数据库整合；作为墨天轮 PostgreSQL 实际专栏作者，热衷于 PostgreSQL 实际技术分享，在本人的岗位踊跃推广 PostgreSQL，致力为 PG 社区多做贡献。

正文完