在PostgreSQL查询中获取大对象的大小？

Question

我想获取blob的字节大小。

我正在使用PostgreSQL，并希望使用SQL查询获得其大小。类似这样：

SELECT sizeof(field) FROM table;

在Postgresql中是否有可能实现这个功能？

更新：我已经阅读了Postgresql手册，但未能找到适当的函数来计算文件大小。此外，该blob被存储为一个大对象。

- Bob

---

在发表此类问题之前，请阅读手册：http://www.postgresql.org/docs/current/static/functions.html - user330315

@DanielVérité：看起来确实是重复的问题，但公平地说，在我发布答案之前搜索时我找不到那个问题。谁会称呼它们为“lobjects”，真的吗？;-) 尽管我的函数与那个问题中的你的函数非常相似，但在我的辩护中，如果我复制了它，我也会复制错误处理！ - Edmund

Answer 1

虽然我没有使用过Large Objects，但是根据文档：http://www.postgresql.org/docs/current/interactive/lo-interfaces.html#LO-TELL

我认为你需要使用与某些文件系统API相同的技术：先定位到结尾，然后读取位置。PostgreSQL有SQL函数来包装内部的C函数。我没有找到太多文档，但是这个方法是有效的：

CREATE OR REPLACE FUNCTION get_lo_size(oid) RETURNS bigint
VOLATILE STRICT
LANGUAGE 'plpgsql'
AS $$
DECLARE
    fd integer;
    sz bigint;
BEGIN
    -- Open the LO; N.B. it needs to be in a transaction otherwise it will close immediately.
    -- Luckily a function invocation makes its own transaction if necessary.
    -- The mode x'40000'::int corresponds to the PostgreSQL LO mode INV_READ = 0x40000.
    fd := lo_open($1, x'40000'::int);
    -- Seek to the end.  2 = SEEK_END.
    PERFORM lo_lseek(fd, 0, 2);
    -- Fetch the current file position; since we're at the end, this is the size.
    sz := lo_tell(fd);
    -- Remember to close it, since the function may be called as part of a larger transaction.
    PERFORM lo_close(fd);
    -- Return the size.
    RETURN sz;
END;
$$; 

测试它：

-- Make a new LO, returns an OID e.g. 1234567
SELECT lo_create(0);

-- Populate it with data somehow
...

-- Get the length.
SELECT get_lo_size(1234567);

看起来LO功能主要是设计用于客户端或低级服务器编程，但至少他们提供了一些可见的SQL函数，这使得上述操作成为可能。我执行了一个查询 SELECT relname FROM pg_proc where relname LIKE 'lo%' 来开始我的工作。对于模式x'40000'::int和SEEK_END = 2值，模糊的C编程记忆和一些研究是必需的来完成其余部分！

- Edmund

---

为避免“结果超出范围”错误并使其能够处理大于2GB的大型对象，请使用lo_seek64和lo_tell64。 - ochedru

性能与其他答案相比如何？ - OrangeDog

由于lo_seek64已经返回了当前位置，是否有必要执行lo_tell呢？ - Simon Sobisch

Answer 2

你可以在创建大对象时更改应用程序以存储大小。否则，您可以使用以下查询：

select sum(length(lo.data)) from pg_largeobject lo
where lo.loid=XXXXXX

如之前的帖子所建议，您也可以使用大对象API函数。它们可以正常工作，但速度比上面建议的选择方法慢一个数量级。

- vstan

---

这是最干净的解决方案。谢谢。 - MarekM

很遗憾，自从PostgreSQL 9.0版本以后，pg_largeobject目录不再公开访问：https://www.postgresql.org/docs/current/static/catalog-pg-largeobject.html。 - ochedru

Answer 3

select pg_column_size(lo_get(lo_oid)) from table;

给出以字节为单位的大小。

如果您想进行漂亮的打印：

select pg_size_pretty(pg_column_size(lo_get(lo_oid))::numeric) from table;

- Chris Travers

---

为什么它总是比实际多4个字节呢？ - wutzebaer

@wutzebaer bytea列的前四个字节是其余部分的大小。 - OrangeDog

请使用 octet_length（根据其他答案）而不是 pg_column_size。 - OrangeDog

这个方案可以运行，但看起来有点过度设计——为什么数据库需要读取完整的BLOB（可能是多达几百兆字节）并在计算长度后将其丢弃呢？特别是当lo_seek（end）仅从内部存储的长度中直接返回位置时。 - Simon Sobisch

Answer 4

尝试使用length()或octet_length()函数。

- i-g

Answer 5

这是我的解决方案：

select
lo.loid,
pg_size_pretty(sum(octet_length(lo.data)))
from pg_largeobject lo
where lo.loid in (select pg_largeobject.loid from pg_largeobject)
group by lo.loid;

- rafahead

Answer 6

如果blob列的类型是oid

SELECT length(lo_get(blob_column)) FROM table;

lo_get ( loid oid [, offset bigint, length integer ] ) → bytea

提取大型对象的内容，或其子字符串。

– https://www.postgresql.org/docs/current/lo-funcs.html

length ( bytea ) → integer

返回二进制字符串中的字节数。

– https://www.postgresql.org/docs/current/functions-binarystring.html

- user2418306