Get size of large object in PostgreSQL query?
Solution 1
Not that I've used large objects, but looking at the docs: http://www.postgresql.org/docs/current/interactive/lo-interfaces.html#LO-TELL
I think you have to use the same technique as some file system APIs require: seek to the end, then tell the position. PostgreSQL has SQL functions that appear to wrap the internal C functions. I couldn't find much documentation, but this worked:
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;
$$;
Testing it:
-- 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);
It seems the LO functionality is designed to be used mostly through the client or through low-level server programming, but at least they've provided some SQL visible functions for it, which makes the above possible. I did a query for SELECT relname FROM pg_proc where relname LIKE 'lo%'
to get myself started. Vague memories of C programming and a bit of research for the mode x'40000'::int
and SEEK_END = 2
value were needed for the rest!
Solution 2
You could change your application to store the size when you create the large object. Otherwise you can use a query such as:
select sum(length(lo.data)) from pg_largeobject lo
where lo.loid=XXXXXX
You can use also the large object API functions, as suggested in a previous post, they work ok, but are an order of magnitude slower than the select method suggested above.
Solution 3
select pg_column_size(lo_get(lo_oid)) from table;
Gives you the size in bytes.
If you want pretty printing:
select pg_size_pretty(pg_column_size(lo_get(lo_oid))::numeric) from table;
Solution 4
Try length()
or octet_length()
Solution 5
This is my solution:
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;
Bob
Updated on February 24, 2021Comments
-
Bob over 3 years
I would like to obtain the byte size of a blob.
I am using Postgresql and would like to obtain the size using an SQL query. Something like this:
SELECT sizeof(field) FROM table;
Is this possible in Postgresql?
Update: I have read the postgresql manual and could not find an appropriate function to calculate the file size. Also, the blob is stored as a large object.
-
MarekM about 8 yearsThis is the cleanest solution. Thanks
-
ochedru almost 7 yearsUnfortunately, the
pg_largeobject
catalog is no longer publicly accessible since PostgreSQL 9.0: postgresql.org/docs/current/static/catalog-pg-largeobject.html. -
wutzebaer about 4 yearssomehow it is always 4 bytes more than it actually is, why?
-
ochedru about 4 yearsTo avoid "result out of range" errors and make it work with large objects larger than 2GB, use
lo_seek64
andlo_tell64
. -
OrangeDog over 3 years@wutzebaer the first four bytes of a
bytea
column are the size of the rest of it -
OrangeDog over 3 yearsHow does the performance compare to the other answers?
-
OrangeDog over 3 yearsi.e. you should use
octet_length
(as per other answers) notpg_column_size