Postgresql中的large object
1、初识postgresql large object
一位同事在对使用pg_dump备份出来的文件(使用plain格式)进行恢复时,觉得速度非常慢,让我分析一下是什么原因。
我拿到他的.bak文件,文件有1个多G。为了方便分析,我用split工具把文件给拆成了多个小文件。在.bak文件中,我发现有大量这样的SQL:
通过查询文档,我知道lo表示large object。还别说,这是我第一次接触到postgresql 中的large object 。因为受oracle中LOB概念的影响,我想当然地以为large object 也是postgresql中的一种数据类型,就像ORACLE中的BLOB或CLOB。可是翻看了参考书籍与官方文档后,发现竟然没有对应的字段类型。网上有人说,postgresql中的large object,其对应的字段类型为oid。这样的说法让我真的好费解,只到最后,待我搞明白large object是怎么回事后,我意识到这样说的人大概知道一点,但应该没有理解透。
在查询官方文档的过程中,接触到两张与large object相关的系统表:
简单点说,pg_largeobject_metadata表是large object的元数据表,记录每个large object的OID(对象标识符)、属主、访问权限。Pg_largeobject表是具体存储large object的表,其存储方式为:将large ojbect以page(2K)为单位分成多个单元,第个单元在Pg_largeobject中表示为1条记录。
可是,我没有发现有任何说明,可以解释如何以large object作为字段值。
2、如何理解postgresql large object
在oracle中,假设我们定义了表的一个字段类型为BLOB,例如
Create table t (desc blob)
然后我们可以向该字段中插入记录: insert into t values ('0Xadfe4358942c')
最后,在我们查询时,直接select desc from t 就会返回:
0Xadfe4358942c
我错就错在默认了postgresql也是这么玩儿的。然而呢?
实际上,在postgresql中(至少截止到9.6版本)没有large object字段。Large object在postgresql中是作为一个个的对象存在的,可以使用特定方法以oid来引用它们。我们来举个现实中的例子来说明一下:假如把数据库表的每条记录比喻成1个人,LOB比喻成1只狗,那么oracle的处理方式为1个人有了1只狗,数据库中才有了这只狗。而关于这只狗怎么来的,是凭空变出来的、基因克隆的、还是自己用泥巴做的,oracle不管它。postgresql large object的管理方式则不同,数据库中首先得有1只狗或多只狗,然后这个人就可以办理领养手续,但领养手续只完成领养登记,表示这只狗属于你了,但狗你不能真正带回家。因为狗并没有真正带回来,所以多个人可以登记领养同一只狗,只需要将领养编号赋予同一只狗的ID即可。
3、Postgresql large object 的使用
上面只是一个比喻,那postgresql large object如何使用的呢?使用过程中的每一步操作又对应上文的什么内容呢?
我们就以.bak中的过程来做示例。
-
创建large object (数据库中有了一只狗,但现在这只狗还只是个概念,还不是实体)
执行该过程后,会在pg_largeobject_metadata中生成1条记录,oid字段值为1000001。而pg_largeobject表不会生成相应记录,因为现在的large object还是空的。
-
打开large object (找到这只狗,准备为它赋予生命)--执行这一步前需要开启事务: begin;
-
为large object 注入实际数据 (给狗赋予生命,让它变成一个实体)--执行这一步后需要提交事务:commit;
执行该过程后,会在pg_largeobject生成记录,记录的条数与large object的大小有关(每2K大小一条记录)
-
至此,狗已经有了,但它只是孤零零地在那时。现在需要有人对它办理领养登记
我们新建一张表:
然后往该表中插入一条记录,lo字段的值为1000001
好了,我们现在领养手续办理完了。可能有人要说,不对呀,这就完了?是的,这就完了。从这里可以看出,人知道它领养了哪只狗,但狗不知道谁领养了它。在计算机术语来说,就是引用者知道被引用者,但被引用者不知道引用者。
现在领也领养了,但有什么意义呢?就这么标识一下引用,是不是就可以通过test表的lo字段来查看large object的内容呢?我们来看一下:
很遗憾,看到的只是引用值,看不到large object的具体的值。实际上,在postgresql中,对large object 的使用需要使用专门的方法,不能直接使用引用字段的方式来查看、使用。
-
办理领养登记之后的使用
办理领养登记之后,可以的方法包括:
-
lo_export(oid loid, text filename) --将大对象loid的数据导出到一个服务器文件filename中,返回导出长度(整型)。
-
lo_unlink(oid loid) -- 删除一个地址为loid的大对象,返回整型 1-成功 -1 -失败。
-
lo_open(oid loid, integer open_mode) -- 打开一个地址为loid的大对象,为读写做准备,open_mode为打开类型: inv_write(写,值为131072)、inv_read(读,值为262144)或者inv_write|inv_read (读写,值为393216)。返回文件句柄fd(整型),若fd为负数,失败。
-
loread(integer fd, integer len) -- 读句柄fd当前位置开始的len大小的数据,返回数据内容(bytea类型)。
-
lowrite(integer fd, bytea buf) -- 在句柄fd当前位置开始将二进制数据buf写入大对象中,返回所写的长度(整型)。
-
lo_lseek(integer fd, integer offset, integer whence) -- 改变句柄fd当前的读写位置。whence是寻址方式,seek_set(值为0)从对象头开始,seek_cur(值为1)从当前读写位置开始,seek_end(值为2)从对象尾开始,offset是偏移尺寸。返回新的读写位置(整型),-1表示错误。
注:loread、lowrite会自动改变当前读写位置,所以若顺序读写,lo_lseek这个命令就没什么用。
-
lo_tell(integer fd) -- 返回句柄fd的当前位置(整型)
-
lo_truncate(integer fd, integer len) -- 截取句柄fd所打开的大对象长度为len大小。若len大于原来大对象的长度,会在大对象后缀一个'\0'字符。成功返回0,失败为负数。
-
lo_close(integer fd) -- 关闭句柄fd, 成功返回0,失败为负数。
以上函数涉及到df句柄的,必须在一个transaction内完成,也就是说句柄fd只在一个事务内有效,事务结束它自动关闭。
-
-
使用案例
例如,我们可以lo_export方法将test表lo字段所对应的large object导出到本地。
查看test.f文件
通过查询1f 8b 08文件头所对应的文件格式,可知test.f文件为gz压缩文件。通过gunzip解压test.f文件
好,现在我们看清它的明文是什么了。这实际是一种矢量瓦片的明码格式。