Thread: Large Object to Bytea Conversion
Hi, I want to convert some large objects to bytea fields on the server. Searching through the documentation didn't reveal any hints. Am I missing something or is there really no such thing as a lo_convert_to_bytea function? Regards Markus
Hi,
Le mardi 14 novembre 2006 14:36, Markus Schiltknecht a écrit :
> I want to convert some large objects to bytea fields on the server.
> Searching through the documentation didn't reveal any hints. Am I
> missing something or is there really no such thing as a
> lo_convert_to_bytea function?
You may want to try this code given on IRC by i-can't-remember-who:
CREATE FUNCTION _phpads_agg_concat (bytea, bytea) RETURNS bytea AS $_$SELECT
$1 || $2$_$ LANGUAGE sql IMMUTABLE STRICT;
CREATE AGGREGATE phpads_agg_concat (bytea) (SFUNC = _phpads_agg_concat, STYPE
= bytea, INITCOND = '');
EXPLAIN ANALYZE SELECT phpads_agg_concat(data) FROM (SELECT data FROM
pg_largeobject WHERE loid = 24513361 ORDEY BY pageno) t;
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=10.62..10.63 rows=1 width=32) (actual time=4.682..4.685
rows=1 loops=1)
-> Index Scan using pg_largeobject_loid_pn_index on pg_largeobject
(cost=0.00..10.57 rows=4 width=1863) (actual time=0.075..0.162 rows=4
loops=1)
Index Cond: (loid = 24513361::oid)
Total runtime: 5.146 ms
CREATE FUNCTION phppgads_lo_readall(oid) RETURNS bytea AS $_$SELECT
phpads_agg_concat(data) FROM (SELECT data FROM pg_largeobject WHERE loid = $1
ORDEY BY pageno) t$_$ LANGUAGE sql STRICT;
Hope this will help,
--
Dimitri Fontaine
http://www.dalibo.com/
Attachment
Hello Dimitri, Cool, thank you. Even though.. it looks a little... ehm... well optimized? Or shall I say hard to read? However, it should work for what I want. Shouldn't such a thing be at least in contrib? How about the reverse, filling a large object with a bytea value? Admittedly, this is less attractive ;-) Regards Markus Dimitri Fontaine wrote: > Hi, > > Le mardi 14 novembre 2006 14:36, Markus Schiltknecht a écrit : >> I want to convert some large objects to bytea fields on the server. >> Searching through the documentation didn't reveal any hints. Am I >> missing something or is there really no such thing as a >> lo_convert_to_bytea function? > > You may want to try this code given on IRC by i-can't-remember-who: > > CREATE FUNCTION _phpads_agg_concat (bytea, bytea) RETURNS bytea AS $_$SELECT > $1 || $2$_$ LANGUAGE sql IMMUTABLE STRICT; > CREATE AGGREGATE phpads_agg_concat (bytea) (SFUNC = _phpads_agg_concat, STYPE > = bytea, INITCOND = ''); > > EXPLAIN ANALYZE SELECT phpads_agg_concat(data) FROM (SELECT data FROM > pg_largeobject WHERE loid = 24513361 ORDEY BY pageno) t; > QUERY > PLAN > ------------------------------------------------------------------------------------------------------------------------------------------------------- > Aggregate (cost=10.62..10.63 rows=1 width=32) (actual time=4.682..4.685 > rows=1 loops=1) > -> Index Scan using pg_largeobject_loid_pn_index on pg_largeobject > (cost=0.00..10.57 rows=4 width=1863) (actual time=0.075..0.162 rows=4 > loops=1) > Index Cond: (loid = 24513361::oid) > Total runtime: 5.146 ms > > > CREATE FUNCTION phppgads_lo_readall(oid) RETURNS bytea AS $_$SELECT > phpads_agg_concat(data) FROM (SELECT data FROM pg_largeobject WHERE loid = $1 > ORDEY BY pageno) t$_$ LANGUAGE sql STRICT; > > > Hope this will help,
Hi, I'm using the function below to convert a large object to a bytea value. Unfortunately, it performs very poorly for *large* objects, i.e. I'm currently fiddling with a file of about 100 MB. I've increased work_mem to 256 MB, but that didn't help much. I suspect the aggregate function is not as efficient as it looks. Is it probably not releasing memory? The postgres process consumes all the work_mem I've allowed. Theoretically, this could be a streaming operation and would not need much memory at all. As bytea is normally a varlena field, I suspect postgres needs to be able to hold the file at least once in memory. Any idea on how to speed this up? Regards Markus Dimitri Fontaine wrote: > Hi, > > Le mardi 14 novembre 2006 14:36, Markus Schiltknecht a écrit : >> I want to convert some large objects to bytea fields on the server. >> Searching through the documentation didn't reveal any hints. Am I >> missing something or is there really no such thing as a >> lo_convert_to_bytea function? > > You may want to try this code given on IRC by i-can't-remember-who: > > CREATE FUNCTION _phpads_agg_concat (bytea, bytea) RETURNS bytea AS $_$SELECT > $1 || $2$_$ LANGUAGE sql IMMUTABLE STRICT; > CREATE AGGREGATE phpads_agg_concat (bytea) (SFUNC = _phpads_agg_concat, STYPE > = bytea, INITCOND = ''); > > EXPLAIN ANALYZE SELECT phpads_agg_concat(data) FROM (SELECT data FROM > pg_largeobject WHERE loid = 24513361 ORDEY BY pageno) t; > QUERY > PLAN > ------------------------------------------------------------------------------------------------------------------------------------------------------- > Aggregate (cost=10.62..10.63 rows=1 width=32) (actual time=4.682..4.685 > rows=1 loops=1) > -> Index Scan using pg_largeobject_loid_pn_index on pg_largeobject > (cost=0.00..10.57 rows=4 width=1863) (actual time=0.075..0.162 rows=4 > loops=1) > Index Cond: (loid = 24513361::oid) > Total runtime: 5.146 ms > > > CREATE FUNCTION phppgads_lo_readall(oid) RETURNS bytea AS $_$SELECT > phpads_agg_concat(data) FROM (SELECT data FROM pg_largeobject WHERE loid = $1 > ORDEY BY pageno) t$_$ LANGUAGE sql STRICT; > > > Hope this will help,
Hi,
I've sort of solved the problem for me. I'm now doing one single
lo_read() to fetch the bytea field. Those functions do not operate on
the large object OID, but one needs to open them first with lo_open().
I'm doing another hack to get the size of the large object.
All combined in a sql function (plpgsql is not installed...) looks like
that:
-- a helper function to convert large objects to bytea more efficiently
CREATE FUNCTION lo_readall(oid) RETURNS bytea
AS $_$
SELECT loread(q3.fd, q3.filesize + q3.must_exec) FROM
(SELECT q2.fd, q2.filesize, lo_lseek(q2.fd, 0, 0) AS must_exec FROM
(SELECT q1.fd, lo_lseek(q1.fd, 0, 2) AS filesize FROM
(SELECT lo_open($1, 262144) AS fd)
AS q1)
AS q2)
AS q3
$_$ LANGUAGE sql STRICT;
Does anybody know a better way? Why isn't a simple function like that
included? And is it just me or is the documentation lacking to describe
all the server side lo_* function? I've been studying the source to find
those.
Regards
Markus