Re: proposal: lob conversion functionality

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 ?

Otherwise patch looks nice and clean.

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