1

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?

Cœur
  • 37,241
  • 25
  • 195
  • 267
zefciu
  • 1,967
  • 2
  • 17
  • 39
  • Does this answer your question? [Get size of large object in PostgreSQL query?](https://stackoverflow.com/questions/10169309/get-size-of-large-object-in-postgresql-query) – OrangeDog Mar 18 '21 at 10:23
  • While this dupe target is newer, it has more and better answers, and better SEO – OrangeDog Mar 18 '21 at 10:23

1 Answers1

8

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

Daniel Vérité
  • 58,074
  • 15
  • 129
  • 156
  • 2
    I would also testify that using length(data) method is significantly slower than seeking and telling. At least on my 8.4, on 370 records, using sum() - 30 secs, using open/seek - 1 sec. Thank you, Daniel. – Pawel Veselov Sep 04 '13 at 08:17