openGauss數(shù)據(jù)庫里bytea二進(jìn)制類型受segment size編譯參數(shù)限制，默認(rèn)不能超過1GB，如果字段存儲數(shù)據(jù)超過1GB可以使用lo（Large Object）擴(kuò)展類型。

lo類型需要先創(chuàng)建lo extension

$ gsql -p5432 -Uomm postgres -r

postgres=# create extension lo;
CREATE EXTENSION

創(chuàng)建完lo擴(kuò)展，我們新建test_lo表，info字段使用lo類型。

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

創(chuàng)建test_lo表管理觸發(fā)器，對update和delete操作使用lo_manage函數(shù)管理，不然會產(chǎn)生孤立大對象。

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字節(jié)（2.1 GB，2.0 GiB）已復(fù)制，0.805435 s，2.7 GB/s

測試lo_import函數(shù)導(dǎo)入數(shù)據(jù)到數(shù)據(jù)表

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

可以看到數(shù)據(jù)可以正常導(dǎo)入，如果不使用lo類型，使用bytea類型會提示下面的報(bào)錯(cuò)。

ERROR:  requested length too large

測試lo_export函數(shù)導(dǎo)出數(shù)據(jù)表數(shù)據(jù)到文件

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

可以看到數(shù)據(jù)正常導(dǎo)出。

查看導(dǎo)入導(dǎo)出的數(shù)據(jù)文件，也可以使用diff命令進(jìn)行比對。

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

查看數(shù)據(jù)表大對象字段大小

分兩步進(jìn)行，首先查大對象字段的oid（lo類型字段在用戶表里面只存儲一個(gè)oid引用指針，并不實(shí)際存數(shù)據(jù)）

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

實(shí)際數(shù)據(jù)使用多條bytea記錄存儲在pg_largeobject表，可以根據(jù)oid查詢統(tǒng)計(jì)字段的大小

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)

也可以使用如下函數(shù)來查詢

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;

查詢結(jié)果如下

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

再來測試JDBC應(yīng)用層的使用

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讀數(shù)據(jù)輸出到文件

	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示例參考