获取PostgreSQL中lobject的大小

本文介绍了获取PostgreSQL中lobject的大小的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

限时送ChatGPT账号..

我想知道为什么PostgreSQL中没有直接获取大对象大小的函数.我相信可以 seek() 对象的末尾，然后 tell() 位置，但它是不是太贵了?我在谷歌上找不到有关它的任何信息?那么获得 lobject 大小的正确方法是什么，例如如果要填充 Content-Size http 标头?

解决方案

这个功能对我来说已经足够高效了，你可能想用你的数据试试:

CREATE OR REPLACE FUNCTION lo_size(oid) 返回整数作为 $$宣布fd 整数；sz 整数；开始fd = lo_open($1, 262144);如果 (fd<0) 那么引发异常无法打开大对象 %"，$1；万一;sz=lo_lseek(fd,0,2);如果 (lo_close(fd)!=0) 那么引发异常无法关闭大对象 %"，$1；万一;返回 sz;结尾;$$ LANGUAGE 'plpgsql';

另一个选项是 select sum(length(data)) from pg_largeobject where loid=the_oid 但它需要对 pg_largeobject 的读取访问权限，我认为这在 pg 9.0+ 中已被禁止用于非超级用户>

I wonder why is there no function to directly obtain the size of large object in PostgreSQL. I believe one can seek() the end of object and then tell() the position, but isn't it too expensive? I can't find any info about it in google? So what is the proper way to obtain the size of lobject e.g. if you want to fill the Content-Size http header?

解决方案

This function has been efficient enough for me, you may want to try it with you data:

CREATE OR REPLACE FUNCTION lo_size(oid) RETURNS integer 
AS $$ 
declare 
 fd integer; 
 sz integer; 
begin 
 fd = lo_open($1, 262144);
 if (fd<0) then
   raise exception 'Failed to open large object %', $1;
 end if;
 sz=lo_lseek(fd,0,2);
 if (lo_close(fd)!=0) then
   raise exception 'Failed to close large object %', $1;
 end if;
 return sz;
end; 
$$ LANGUAGE 'plpgsql'; 

Another option is select sum(length(data)) from pg_largeobject where loid=the_oid but it requires read access to pg_largeobject which I think has been suppressed in pg 9.0+ for non-superusers

这篇关于获取PostgreSQL中lobject的大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！