opengauss/mogdb大对象largeobject存取测试 -m6米乐安卓版下载

opengauss/mogdb数据库里bytea二进制类型受segment size编译参数限制，默认不能超过1gb，如果字段存储数据超过1gb可以使用lo（large object）扩展类型。

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

测试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

测试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

查看数据表大对象字段大小

分两步进行，首先查大对象字段的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应用层的使用

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();
		
	}

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();
		
	}