Re: proposal: lob conversion functionality

2013/9/19 Rushabh Lathia <rushabh(dot)lathia(at)gmail(dot)com>

> Hi Pavel,
>
> I have reviewed you patch.
>
> -- Patch got applied cleanly (using patch -p1)
> -- Make & Make install works fine
> -- make check looks good
>
> I done code-walk and it looks good. Also did some manual testing and
> haven't
> found any issue with the implementation.
>
> Patch introduced two new API load_lo() and make_lo() for loading and saving
> from/to large objects Functions. When it comes to drop an lo object created
> using make_lo() this still depend on older API lo_unlink(). I think we
> should
> add that into documentation for the clerification.
>
> As a user to lo object function when I started testing this new API, first
> question came to mind is why delete_lo() or destroy_lo() API is missing.
> Later I realize that need to use lo_unlink() older API for that
> functionality.
> So I feel its good to document that. Do let you know what you think ?
>

good idea

I'll send a updated patch evening

>
>
> Otherwise patch looks nice and clean.
>
>
Thank you :)

Regards

Pavel

> Regards,
> Rushabh Lathia
> www.EnterpriseDB.com
>
>
>
> On Sun, Aug 25, 2013 at 8:31 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>wrote:
>
>> Hello
>>
>> here is a patch
>>
>> it introduce a load_lo and make_lo functions
>>
>> postgres=# select make_lo(decode('ffffff00','hex'));
>> make_lo
>> ─────────
>> 24629
>> (1 row)
>>
>> Time: 40.724 ms
>> postgres=# select load_lo(24628);
>> load_lo
>> ────────────
>> \xffffff00
>> (1 row)
>>
>> postgres=# \lo_import ~/avatar.png
>> lo_import 24630
>>
>> postgres=# select md5(load_lo(24630));
>> md5
>> ──────────────────────────────────
>> 513f60836f3b625713acaf1c19b6ea78
>> (1 row)
>>
>> postgres=# \q
>> bash-4.1$ md5sum ~/avatar.png
>> 513f60836f3b625713acaf1c19b6ea78 /home/pavel/avatar.png
>>
>> Regards
>>
>> Pavel Stehule
>>
>>
>>
>> 2013/8/22 Jov <amutu(at)amutu(dot)com>
>>
>>> +1
>>> badly need the large object and bytea convert function.
>>>
>>> Once I have to use the ugly pg_read_file() to put some text to pg,I
>>> tried to use large object but find it is useless without function to
>>> convert large object to bytea.
>>>
>>> Jov
>>> blog: http:amutu.com/blog <http://amutu.com/blog>
>>>
>>>
>>> 2013/8/10 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
>>>
>>>> Hello
>>>>
>>>> I had to enhance my older project, where XML documents are parsed and
>>>> created on server side - in PLpgSQL and PLPerl procedures. We would to
>>>> use a LO API for client server communication, but we have to
>>>> parse/serialize LO on server side.
>>>>
>>>> I found so there are no simple API for working with LO from PL without
>>>> access to file system. I had to use a ugly hacks:
>>>>
>>>> CREATE OR REPLACE FUNCTION parser.save_as_lob(bytea)
>>>> RETURNS oid AS $$
>>>> DECLARE
>>>> _loid oid;
>>>> _substr bytea;
>>>> BEGIN
>>>> _loid := lo_creat(-1);
>>>> FOR i IN 0..length($1)/2048
>>>> LOOP
>>>> _substr := substring($1 FROM i * 2048 + 1 FOR 2048);
>>>> IF _substr <> '' THEN
>>>> INSERT INTO pg_largeobject(loid, pageno, data)
>>>> VALUES(_loid, i, _substr);
>>>> END IF;
>>>> END LOOP;
>>>>
>>>> EXECUTE format('GRANT SELECT ON LARGE OBJECT %s TO ohs', _loid);
>>>> RETURN _loid;
>>>> END;
>>>> $$ LANGUAGE plpgsql SECURITY DEFINER STRICT SET search_path =
>>>> 'pg_catalog';
>>>>
>>>> and
>>>>
>>>> CREATE OR REPLACE FUNCTION fbuilder.attachment_to_xml(attachment oid)
>>>> RETURNS xml AS $$
>>>> DECLARE
>>>> b_cum bytea = '';
>>>> b bytea;
>>>> BEGIN
>>>> FOR b IN SELECT l.data
>>>> FROM pg_largeobject l
>>>> WHERE l.loid = attachment_to_xml.attachment
>>>> ORDER BY l.pageno
>>>> LOOP
>>>> b_cum := b_cum || b;
>>>> END LOOP;
>>>> IF NOT FOUND THEN
>>>> RETURN NULL;
>>>> ELSE
>>>> RETURN xmlelement(NAME "attachment",
>>>> encode(b_cum, 'base64'));
>>>> END IF;
>>>> END;
>>>> $$ LANGUAGE plpgsql STRICT SECURITY DEFINER SET search_path =
>>>> 'pg_catalog';
>>>>
>>>> These functions can be simplified if we supports some functions like
>>>> encode, decode for LO
>>>>
>>>> So my proposal is creating functions:
>>>>
>>>> * lo_encode(loid oid) .. returns bytea
>>>> * lo_encode(loid oid, encoding text) .. returns text
>>>> * lo_make(loid oid, data bytea)
>>>> * lo_make(loid oid, data text, encoding text)
>>>>
>>>> This can simplify all transformation between LO and VARLENA. Known
>>>> limit is 1G for varlena, but it is still relative enough high.
>>>>
>>>> Notes. comments?
>>>>
>>>> Regards
>>>>
>>>> Pavel
>>>>
>>>>
>>>> --
>>>> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
>>>> To make changes to your subscription:
>>>> http://www.postgresql.org/mailpref/pgsql-hackers
>>>>
>>>>
>>>
>>
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>>
>>
>
>
> --
> Rushabh Lathia
>