Re: Large Object to Bytea Conversion

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,