Re: [HACKERS] proposal: lob conversion functionality

[Pavel Stehule]

2013/10/26 Noah Misch 

> On Fri, Oct 25, 2013 at 03:35:05PM +0200, Pavel Stehule wrote:
> > 2013/10/24 Heikki Linnakangas 
> > > On 22.10.2013 13:55, Pavel Stehule wrote:
> > >> 2013/10/21 Noah Misch
> > >>> If you're prepared to change the function names and add the
> > >>> subset-oriented
> > >>> functions, I would appreciate that.
> > >>>
> > >>>  here is patch
> > >>
> > >
> > > lobj.sgml still refer to the old names.
>
> > fixed documentation
>
> Thanks.  I made these noteworthy changes:
>
> 1. Fix lo_get(oid) on a LO larger than INT_MAX bytes: raise an error rather
> than performing a modulo operation on the size.
>
> 2. Remove the undocumented ability to pass a negative length to request all
> bytes up to the end of the LO.  substr() also rejects negative lengths.
>  Note
> that one can get the same effect by passing any length >MaxAllocSize.
>
> 3. Documentation reshuffling.  I placed all the documentation for these
> functions in the large objects chapter, and I emphasized the new functions
> over the prospect of calling the older functions (whose primary role is to
> support client interfaces) from SQL.
>
> If this still looks reasonable, I will commit it.
>

it is ok

Regards

Pavel


>
> --
> Noah Misch
> EnterpriseDB http://www.enterprisedb.com
>

Re: [HACKERS] proposal: lob conversion functionality

[Noah Misch]

On Fri, Oct 25, 2013 at 03:35:05PM +0200, Pavel Stehule wrote:
> 2013/10/24 Heikki Linnakangas 
> > On 22.10.2013 13:55, Pavel Stehule wrote:
> >> 2013/10/21 Noah Misch
> >>> If you're prepared to change the function names and add the
> >>> subset-oriented
> >>> functions, I would appreciate that.
> >>>
> >>>  here is patch
> >>
> >
> > lobj.sgml still refer to the old names.

> fixed documentation

Thanks.  I made these noteworthy changes:

1. Fix lo_get(oid) on a LO larger than INT_MAX bytes: raise an error rather
than performing a modulo operation on the size.

2. Remove the undocumented ability to pass a negative length to request all
bytes up to the end of the LO.  substr() also rejects negative lengths.  Note
that one can get the same effect by passing any length >MaxAllocSize.

3. Documentation reshuffling.  I placed all the documentation for these
functions in the large objects chapter, and I emphasized the new functions
over the prospect of calling the older functions (whose primary role is to
support client interfaces) from SQL.

If this still looks reasonable, I will commit it.

-- 
Noah Misch
EnterpriseDB http://www.enterprisedb.com
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 2b91e6e..a1d3aee 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -3420,7 +3420,8 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 
'three');
 
   
See also the aggregate function string_agg in
-   .
+and the large object functions
+   in .
   
  
 
diff --git a/doc/src/sgml/lobj.sgml b/doc/src/sgml/lobj.sgml
index bb3e08f..05a9310 100644
--- a/doc/src/sgml/lobj.sgml
+++ b/doc/src/sgml/lobj.sgml
@@ -526,11 +526,79 @@ int lo_unlink(PGconn *conn, Oid lobjId);
 Server-side Functions
 
   
-   There are server-side functions callable from SQL that correspond to
-   each of the client-side functions described above; indeed, for the
-   most part the client-side functions are simply interfaces to the
-   equivalent server-side functions.  The ones that are actually useful
-   to call via SQL commands are
+   Server-side functions tailored for manipulating large objects from SQL are
+   listed in .
+  
+
+  
+   SQL-oriented Large Object Functions
+   
+
+ 
+  Function
+  Return Type
+  Description
+  Example
+  Result
+ 
+
+
+
+ 
+  
+   
+lo_create
+   
+   lo_create(loid 
oid, string 
bytea)
+  
+  oid
+  
+   Create a large object and store data there, returning its OID.
+   Pass 0 to have the system choose an OID.
+  
+  lo_create(0, E'\\xff00')
+  24528
+ 
+
+ 
+  
+   
+lo_put
+   
+   lo_put(loid oid, 
offset bigint, str 
bytea)
+  
+  void
+  
+   Write data at the given offset.
+  
+  lo_put(24528, 1, E'\\xaa')
+  
+ 
+
+ 
+  
+   
+lo_get
+   
+   lo_get(loid oid 
, from bigint, 
for int)
+  
+  bytea
+  
+   Extract contents or a substring thereof.
+  
+  lo_get(24528, 0, 3)
+  \xffaaff
+ 
+
+
+   
+  
+
+  
+   There are additional server-side functions corresponding to each of the
+   client-side functions described earlier; indeed, for the most part the
+   client-side functions are simply interfaces to the equivalent server-side
+   functions.  The ones just as convenient to call via SQL commands are
lo_creatlo_creat,
lo_createlo_create,
lo_unlinklo_unlink,
diff --git a/src/backend/libpq/be-fsstubs.c b/src/backend/libpq/be-fsstubs.c
index fa00383..4d6716e 100644
--- a/src/backend/libpq/be-fsstubs.c
+++ b/src/backend/libpq/be-fsstubs.c
@@ -754,3 +754,152 @@ deleteLOfd(int fd)
 {
cookies[fd] = NULL;
 }
+
+/*
+ * Wrappers oriented toward SQL callers
+ */
+
+/*
+ * Read [offset, offset+nbytes) within LO; when nbytes is -1, read to end.
+ */
+static bytea *
+lo_get_fragment_internal(Oid loOid, int64 offset, int32 nbytes)
+{
+   LargeObjectDesc *loDesc;
+   int64   loSize;
+   int64   result_length;
+   int total_read;
+   bytea  *result = NULL;
+
+   /*
+* We don't actually need to store into fscxt, but create it anyway to
+* ensure that AtEOXact_LargeObject knows there is state to clean up
+*/
+   CreateFSContext();
+
+   loDesc = inv_open(loOid, INV_READ, fscxt);
+
+   /* Permission check */
+   if (!lo_compat_privileges &&
+   pg_largeobject_aclcheck_snapshot(loDesc->id,
+   
 GetUserId(),
+   
 ACL_SELECT,
+ 

Re: [HACKERS] proposal: lob conversion functionality

[Pavel Stehule]

fixed documentation

Regards

Pavel


2013/10/24 Heikki Linnakangas 

> On 22.10.2013 13:55, Pavel Stehule wrote:
>
>> 2013/10/21 Noah Misch
>>
>>> If you're prepared to change the function names and add the
>>> subset-oriented
>>> functions, I would appreciate that.
>>>
>>>  here is patch
>>
>
> lobj.sgml still refer to the old names.
>
> - Heikki
>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index e397386..8509d09 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -3399,6 +3399,78 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
See also the aggregate function string_agg in
.
   
+
+   
+Loading and saving from/to Large Objects Functions
+
+ 
+  
+   Function
+   Return Type
+   Description
+   Example
+   Result
+  
+ 
+
+ 
+ 
+  
+
+ lo_create
+
+   lo_create(loid oid, string bytea )
+  
+  oid
+  
+   Create a large object and store a binary string there. Returns a oid of
+   created large object.
+  
+  select lo_create(0, decode('ff00','hex'))
+  24528
+ 
+
+  
+   
+
+ lo_get
+
+lo_get(loid oid , offset bigint, str bytea)
+   
+   bytea
+   
+Returns a binary string based on content a entered large object. Attention: binary
+string has lower size limit (1GB) than large objects (4GB). Processing very large
+large object can be very expensive for memory resources. Bytea data are completly
+holded in memomory.
+  
+  lo_get(24628)
+  \xff00
+ 
+
+  
+   
+
+ lo_put
+
+lo_put(loidoid, offset bigint, str bytea)
+   
+   void
+   
+Write data at offset.
+  
+  lo_put(24628, 0, decode('', 'hex'))
+  
+ 
+
+
+   
+  
+
+  
+   See also a description of other Large Objects Function
+   in .
+  
  
 
 
diff --git a/doc/src/sgml/lobj.sgml b/doc/src/sgml/lobj.sgml
index bb3e08f..32fda11 100644
--- a/doc/src/sgml/lobj.sgml
+++ b/doc/src/sgml/lobj.sgml
@@ -580,6 +580,42 @@ SELECT lo_export(image.raster, '/tmp/motd') FROM image
 these functions as loread and lowrite.
   
 
+  
+   There are other two functions , that doesn't correspond with client api
+   (see in ).
+   lo_create transforms a binary string to lo object,
+   lo_get transforms a lo object to binary string, and
+   lo_put write a binary string at offset to lo object.
+  
+
+  
+   Some examples:
+
+SELECT lo_create(0, decode('ff00','hex'));
+ lo_create 
+---
+ 16392
+(1 row)
+
+SELECT lo_get(16392);
+   lo_get   
+
+ \xff00
+(1 row)
+
+SELECT lo_put(16392, 1, decode('aa','hex'));
+ lo_put 
+
+ 
+(1 row)
+
+SELECT lo_get(16392);
+   lo_get   
+
+ \xffaaff00
+(1 row)
+
+  
 
 
 
diff --git a/src/backend/libpq/be-fsstubs.c b/src/backend/libpq/be-fsstubs.c
index fa00383..aa12349 100644
--- a/src/backend/libpq/be-fsstubs.c
+++ b/src/backend/libpq/be-fsstubs.c
@@ -754,3 +754,137 @@ deleteLOfd(int fd)
 {
 	cookies[fd] = NULL;
 }
+
+/*
+ *	auxiliary LO functions for management LO from SQL and PL
+ */
+
+/*
+ * Load LO fragment and returns bytea
+ *
+ * When nbytes is a -1, then it reads from start (specified by offset) to end.
+ */
+static bytea *
+lo_get_fragment_internal(Oid loOid, int64 offset, int nbytes)
+{
+	LargeObjectDesc	*loDesc;
+	int64		loSize;
+	int		result_length;
+	int total_read;
+	bytea *result = NULL;
+
+	/*
+	 * We don't actually need to store into fscxt, but create it anyway to
+	 * ensure that AtEOXact_LargeObject knows there is state to clean up
+	 */
+	CreateFSContext();
+
+	loDesc = inv_open(loOid, INV_READ, fscxt);
+
+	/* Permission check */
+	if (!lo_compat_privileges &&
+		pg_largeobject_aclcheck_snapshot(loDesc->id,
+	 GetUserId(),
+	 ACL_SELECT,
+	 loDesc->snapshot) != ACLCHECK_OK)
+		ereport(ERROR,
+(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied for large object %u",
+		loDesc->id)));
+
+	loSize = inv_seek(loDesc, 0, SEEK_END);
+
+	if (loSize > offset)
+	{
+		if (nbytes >= 0 && offset + nbytes <= loSize)
+			result_length = nbytes;
+		else
+			result_length = loSize - offset;
+	}
+	else
+		result_length = 0;
+
+	result = (bytea *) palloc(VARHDRSZ + result_length);
+
+	inv_seek(loDesc, offset, SEEK_SET);
+	total_read = inv_read(loDesc, VARDATA(result), result_length);
+
+	Assert(total_read == result_length);
+
+	inv_close(loDesc);
+
+	SET_VARSIZE(result, result_length + VARHDRSZ);
+
+	return result;
+}
+
+/*
+ * Get LO as bytea
+ */
+Datum
+lo_get(PG_FUNCTION_ARGS)
+{
+	Oid	loOid = PG_GETARG_OID(0);
+	bytea *result;
+
+	result = lo_get_fragment_internal(loOid, 0, -1);
+
+	PG_RETURN_BYTEA_P(result);
+}
+

Re: [HACKERS] proposal: lob conversion functionality

[Heikki Linnakangas]

On 22.10.2013 13:55, Pavel Stehule wrote:

2013/10/21 Noah Misch

If you're prepared to change the function names and add the subset-oriented
functions, I would appreciate that.


here is patch


lobj.sgml still refer to the old names.

- Heikki


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] proposal: lob conversion functionality

[Pavel Stehule]

Hello

here is patch

Regards

Pavel


2013/10/21 Noah Misch 

> On Mon, Oct 21, 2013 at 08:10:24PM +0200, Pavel Stehule wrote:
> > > On 10/20/2013 07:52 PM, Noah Misch wrote:
> > >> Anything we do here effectively provides wrappers around the existing
> > >> functions tailored toward the needs of libpq.
>
> To clarify the above statement: the existing lo* SQL functions are
> designed to
> fit the needs of the libpq APIs that call those SQL functions internally.
>  The
> additions we're discussing are SQL functions designed to fit the needs of
> user-written SQL statements.
>
> > I am for including to core - we have no buildin SQL functions that allows
> > access simple and fast access on binary level. Next - these functions
> > completes lo functionality.
> >
> > Other questions - should be these functions propagated to libpq?
>
> No; I agree that the existing libpq large object API is adequate.
>
> > and who will write patch? You or me?
>
> If you're prepared to change the function names and add the subset-oriented
> functions, I would appreciate that.
>
> Thanks,
> nm
>
> --
> Noah Misch
> EnterpriseDB http://www.enterprisedb.com
>


load_lo_v3.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] proposal: lob conversion functionality

[Pavel Stehule]

2013/10/21 Noah Misch 

> On Mon, Oct 21, 2013 at 08:10:24PM +0200, Pavel Stehule wrote:
> > > On 10/20/2013 07:52 PM, Noah Misch wrote:
> > >> Anything we do here effectively provides wrappers around the existing
> > >> functions tailored toward the needs of libpq.
>
> To clarify the above statement: the existing lo* SQL functions are
> designed to
> fit the needs of the libpq APIs that call those SQL functions internally.
>  The
> additions we're discussing are SQL functions designed to fit the needs of
> user-written SQL statements.
>
> > I am for including to core - we have no buildin SQL functions that allows
> > access simple and fast access on binary level. Next - these functions
> > completes lo functionality.
> >
> > Other questions - should be these functions propagated to libpq?
>
> No; I agree that the existing libpq large object API is adequate.
>

ok


>
> > and who will write patch? You or me?
>
> If you're prepared to change the function names and add the subset-oriented
> functions, I would appreciate that.
>

I'll try to prepare patch in next two days

Regards

Pavel


>
> Thanks,
> nm
>
> --
> Noah Misch
> EnterpriseDB http://www.enterprisedb.com
>

Re: [HACKERS] proposal: lob conversion functionality

[Noah Misch]

On Mon, Oct 21, 2013 at 08:10:24PM +0200, Pavel Stehule wrote:
> > On 10/20/2013 07:52 PM, Noah Misch wrote:
> >> Anything we do here effectively provides wrappers around the existing
> >> functions tailored toward the needs of libpq.

To clarify the above statement: the existing lo* SQL functions are designed to
fit the needs of the libpq APIs that call those SQL functions internally.  The
additions we're discussing are SQL functions designed to fit the needs of
user-written SQL statements.

> I am for including to core - we have no buildin SQL functions that allows
> access simple and fast access on binary level. Next - these functions
> completes lo functionality.
> 
> Other questions - should be these functions propagated to libpq?

No; I agree that the existing libpq large object API is adequate.

> and who will write patch? You or me?

If you're prepared to change the function names and add the subset-oriented
functions, I would appreciate that.

Thanks,
nm

-- 
Noah Misch
EnterpriseDB http://www.enterprisedb.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] proposal: lob conversion functionality

[Pavel Stehule]

Hello


2013/10/21 Andrew Dunstan 

>
> On 10/20/2013 07:52 PM, Noah Misch wrote:
>
>> Consider this list of new functions in their place:
>>
>> lo_create(oid, bytea) RETURNS oid  -- new LO with content (similar to
>> make_lo)
>> lo_get(oid) RETURNS bytea-- read entire LO (same
>> as load_lo)
>> lo_get(oid, bigint, int) RETURNS bytea   -- read from offset for length
>> lo_put(oid, bigint, bytea) RETURNS void  -- write data at offset
>>
>
should be - it is more consistent with current API than my proposal.


>
>> Anything we do here effectively provides wrappers around the existing
>> functions tailored toward the needs of libpq.  A key outstanding question
>> is
>> whether doing so provides a compelling increment in usability.  On the
>> plus
>> side, adding such functions resolves the weirdness of having a variety of
>> database object that is easy to access from libpq but awkward to access
>> from
>> plain SQL.  On the minus side, this could easily live as an extension
>> module.
>> I have not used the large object facility to any significant degree, but I
>> generally feel this is helpful enough to justify core inclusion.  Any
>> other
>> opinions on the general suitability or on the specifics of the API
>> offered?
>>
>>
>
I am for including to core - we have no buildin SQL functions that allows
access simple and fast access on binary level. Next - these functions
completes lo functionality.

Other questions - should be these functions propagated to libpq? and who
will write patch? You or me?

Regards

Pavel



>
> I am currently working with a client on a largeish LO migration. I would
> certainly have appreciated having lo_get(oid) available - I wrote something
> in plpgsql that did almost exactly what Pavel's code does. Your additional
> lo_get(oid, offset, length) and lo_put(oid, offset, bytea) seem sane
> enough. So +1 from me for adding all these.
>
> If we're going to be doing work in this area, let me note that I'm not
> sure the decision in commit c0d5be5d6a736d2ee8141e920bc3de**8e001bf6d9 to
> have pg_dump write a separate archive entry for each LO was the wisest
> decision we ever made. My client is currently migrating to use of bytea
> instead of LOs partly because we didn't want to think about the issue of
> having hundreds of millions of archive entries in a dump. When that process
> is complete we'll upgrade. :-)
>
> cheers
>
> andrew
>

Re: [HACKERS] proposal: lob conversion functionality

[Andrew Dunstan]

On 10/20/2013 07:52 PM, Noah Misch wrote:

Consider this list of new functions in their place:

lo_create(oid, bytea) RETURNS oid  -- new LO with content (similar to make_lo)
lo_get(oid) RETURNS bytea-- read entire LO (same as 
load_lo)
lo_get(oid, bigint, int) RETURNS bytea   -- read from offset for length
lo_put(oid, bigint, bytea) RETURNS void  -- write data at offset

Anything we do here effectively provides wrappers around the existing
functions tailored toward the needs of libpq.  A key outstanding question is
whether doing so provides a compelling increment in usability.  On the plus
side, adding such functions resolves the weirdness of having a variety of
database object that is easy to access from libpq but awkward to access from
plain SQL.  On the minus side, this could easily live as an extension module.
I have not used the large object facility to any significant degree, but I
generally feel this is helpful enough to justify core inclusion.  Any other
opinions on the general suitability or on the specifics of the API offered?





I am currently working with a client on a largeish LO migration. I would 
certainly have appreciated having lo_get(oid) available - I wrote 
something in plpgsql that did almost exactly what Pavel's code does. 
Your additional lo_get(oid, offset, length) and lo_put(oid, offset, 
bytea) seem sane enough. So +1 from me for adding all these.


If we're going to be doing work in this area, let me note that I'm not 
sure the decision in commit c0d5be5d6a736d2ee8141e920bc3de8e001bf6d9 to 
have pg_dump write a separate archive entry for each LO was the wisest 
decision we ever made. My client is currently migrating to use of bytea 
instead of LOs partly because we didn't want to think about the issue of 
having hundreds of millions of archive entries in a dump. When that 
process is complete we'll upgrade. :-)


cheers

andrew


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] proposal: lob conversion functionality

[Pavel Stehule]

2013/10/21 Noah Misch 

> On Mon, Sep 30, 2013 at 01:06:15PM +0300, Heikki Linnakangas wrote:
> > On 12.08.2013 21:08, Pavel Stehule wrote:
> >> 2013/8/10 Tom Lane:
> >>> Pavel Stehule  writes:
>  I found so there are no simple API for working with LO from PL without
>  access to file system.
> >>>
> >>> What?  See lo_open(), loread(), lowrite(), etc.
> >>
> >> yes, so there are three problems with these functions:
> >>
> >> a) probably (I didn't find) undocumented
> >
> > It's there, although it's a bit difficult to find by searching. See:
> > http://www.postgresql.org/docs/devel/static/lo-funcs.html.
> >
> > I don't actually agree with this phrase on that page:
> >
> >> The ones that are actually useful to call via SQL commands are
> >> lo_creat, lo_create, lo_unlink, lo_import, and lo_export
> >
> > Calling lo_open, loread and lowrite seems equally useful to me.
> >
> >> b) design with lo handler is little bit PL/pgSQL unfriendly.
> >
> > It's a bit awkward, I agree.
>
> All the other large object functions are named like lo*, so I think new
> ones
> should also be lo* rather than *_lo.  One of the key benefits of large
> objects, compared to a bytea column in some table, is their efficiency when
> reading or writing only a subset of the object.  However, the proposed
> functions only deal with the large object as a whole.  We could easily fix
> that.  Consider this list of new functions in their place:
>
> lo_create(oid, bytea) RETURNS oid  -- new LO with content (similar to
> make_lo)
> lo_get(oid) RETURNS bytea-- read entire LO (same
> as load_lo)
> lo_get(oid, bigint, int) RETURNS bytea   -- read from offset for length
> lo_put(oid, bigint, bytea) RETURNS void  -- write data at offset
>
> Anything we do here effectively provides wrappers around the existing
> functions tailored toward the needs of libpq.  A key outstanding question
> is
> whether doing so provides a compelling increment in usability.  On the plus
> side, adding such functions resolves the weirdness of having a variety of
> database object that is easy to access from libpq but awkward to access
> from
> plain SQL.  On the minus side, this could easily live as an extension
> module.
> I have not used the large object facility to any significant degree, but I
> generally feel this is helpful enough to justify core inclusion.  Any other
> opinions on the general suitability or on the specifics of the API offered?
>

fast reply - I reply again later in my office.

I don't think so new functions (for bytea type) has any sense in libpq.
>From C is usually better to use a native C interface than ensure conversion
to bytea. Probably the interface libpq should be modernized, but it
complete and enough now. I don't have a motivation to enhance a API now.
And a fact, so proposed bytea functions are not in libpq is a reason why I
used different naming convention. A main motivation was a access from PL to
LO without obscure patterns - mainly for PL/pgSQL. For other languages it
is available - but maybe better direction is direct mapping to related
primitives based on host environment possibilities.

Today evening I'll look on your proposal with some more time.

Regards

Pavel


>
> Thanks,
> nm
>
> --
> Noah Misch
> EnterpriseDB http://www.enterprisedb.com
>

Re: [HACKERS] proposal: lob conversion functionality

[Noah Misch]

On Mon, Sep 30, 2013 at 01:06:15PM +0300, Heikki Linnakangas wrote:
> On 12.08.2013 21:08, Pavel Stehule wrote:
>> 2013/8/10 Tom Lane:
>>> Pavel Stehule  writes:
 I found so there are no simple API for working with LO from PL without
 access to file system.
>>>
>>> What?  See lo_open(), loread(), lowrite(), etc.
>>
>> yes, so there are three problems with these functions:
>>
>> a) probably (I didn't find) undocumented
>
> It's there, although it's a bit difficult to find by searching. See:  
> http://www.postgresql.org/docs/devel/static/lo-funcs.html.
>
> I don't actually agree with this phrase on that page:
>
>> The ones that are actually useful to call via SQL commands are
>> lo_creat, lo_create, lo_unlink, lo_import, and lo_export
>
> Calling lo_open, loread and lowrite seems equally useful to me.
>
>> b) design with lo handler is little bit PL/pgSQL unfriendly.
>
> It's a bit awkward, I agree.

All the other large object functions are named like lo*, so I think new ones
should also be lo* rather than *_lo.  One of the key benefits of large
objects, compared to a bytea column in some table, is their efficiency when
reading or writing only a subset of the object.  However, the proposed
functions only deal with the large object as a whole.  We could easily fix
that.  Consider this list of new functions in their place:

lo_create(oid, bytea) RETURNS oid  -- new LO with content (similar to make_lo)
lo_get(oid) RETURNS bytea-- read entire LO (same as 
load_lo)
lo_get(oid, bigint, int) RETURNS bytea   -- read from offset for length
lo_put(oid, bigint, bytea) RETURNS void  -- write data at offset

Anything we do here effectively provides wrappers around the existing
functions tailored toward the needs of libpq.  A key outstanding question is
whether doing so provides a compelling increment in usability.  On the plus
side, adding such functions resolves the weirdness of having a variety of
database object that is easy to access from libpq but awkward to access from
plain SQL.  On the minus side, this could easily live as an extension module.
I have not used the large object facility to any significant degree, but I
generally feel this is helpful enough to justify core inclusion.  Any other
opinions on the general suitability or on the specifics of the API offered?

Thanks,
nm

-- 
Noah Misch
EnterpriseDB http://www.enterprisedb.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] proposal: lob conversion functionality

[Pavel Stehule]

2013/9/30 Heikki Linnakangas 

> On 12.08.2013 21:08, Pavel Stehule wrote:
>
>> 2013/8/10 Tom Lane:
>>
>>> Pavel Stehule>
>>>  writes:
>>>
 I found so there are no simple API for working with LO from PL without
 access to file system.

>>>
>>> What?  See lo_open(), loread(), lowrite(), etc.
>>>
>>
>> yes, so there are three problems with these functions:
>>
>> a) probably (I didn't find) undocumented
>>
>
> It's there, although it's a bit difficult to find by searching. See:
> http://www.postgresql.org/**docs/devel/static/lo-funcs.**html
> .
>
> I don't actually agree with this phrase on that page:
>
>  The ones that are actually useful to call via SQL commands are
>> lo_creat, lo_create, lo_unlink, lo_import, and lo_export
>>
>
> Calling lo_open, loread and lowrite seems equally useful to me.
>
>
>  b) design with lo handler is little bit PL/pgSQL unfriendly.
>>
>
> It's a bit awkward, I agree.
>
>
>  c) probably there is a bug - it doesn't expect handling errors
>>
>> postgres=# select fbuilder.attachment_to_xml(0);
>> WARNING:  Snapshot reference leak: Snapshot 0x978f6f0 still referenced
>>   attachment_to_xml
>> ───
>>   [null]
>> (1 row)
>>
>
> Yeah, that's a server-side bug. inv_open() registers the snapshot before
> checking if the large object exists. If it doesn't, the already-registered
> snapshot is not unregistered, hence the warning.
>
> I've committed the attached fix for that bug.
>

nice, I afraid so it is mine bug

thank you

Pavel


>
> - Heikki
>

Re: [HACKERS] proposal: lob conversion functionality

[Heikki Linnakangas]

On 12.08.2013 21:08, Pavel Stehule wrote:

2013/8/10 Tom Lane:

Pavel Stehule  writes:

I found so there are no simple API for working with LO from PL without
access to file system.


What?  See lo_open(), loread(), lowrite(), etc.


yes, so there are three problems with these functions:

a) probably (I didn't find) undocumented


It's there, although it's a bit difficult to find by searching. See: 
http://www.postgresql.org/docs/devel/static/lo-funcs.html.


I don't actually agree with this phrase on that page:


The ones that are actually useful to call via SQL commands are
lo_creat, lo_create, lo_unlink, lo_import, and lo_export


Calling lo_open, loread and lowrite seems equally useful to me.


b) design with lo handler is little bit PL/pgSQL unfriendly.


It's a bit awkward, I agree.


c) probably there is a bug - it doesn't expect handling errors

postgres=# select fbuilder.attachment_to_xml(0);
WARNING:  Snapshot reference leak: Snapshot 0x978f6f0 still referenced
  attachment_to_xml
───
  [null]
(1 row)


Yeah, that's a server-side bug. inv_open() registers the snapshot before 
checking if the large object exists. If it doesn't, the 
already-registered snapshot is not unregistered, hence the warning.


I've committed the attached fix for that bug.

- Heikki
>From 357f7521384df34c697b3544115622520a6a0e9f Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas 
Date: Mon, 30 Sep 2013 11:29:09 +0300
Subject: [PATCH 1/1] Fix snapshot leak if lo_open called on non-existent
 object.

lo_open registers the currently active snapshot, and checks if the
large object exists after that. Normally, snapshots registered by lo_open
are unregistered at end of transaction when the lo descriptor is closed, but
if we error out before the lo descriptor is added to the list of open
descriptors, it is leaked. Fix by moving the snapshot registration to after
checking if the large object exists.

Reported by Pavel Stehule. Backpatch to 8.4. The snapshot registration
system was introduced in 8.4, so prior versions are not affected (and not
supported, anyway).
---
 src/backend/storage/large_object/inv_api.c | 44 ++
 1 file changed, 26 insertions(+), 18 deletions(-)

diff --git a/src/backend/storage/large_object/inv_api.c b/src/backend/storage/large_object/inv_api.c
index fb91571..d248743 100644
--- a/src/backend/storage/large_object/inv_api.c
+++ b/src/backend/storage/large_object/inv_api.c
@@ -240,29 +240,18 @@ LargeObjectDesc *
 inv_open(Oid lobjId, int flags, MemoryContext mcxt)
 {
 	LargeObjectDesc *retval;
-
-	retval = (LargeObjectDesc *) MemoryContextAlloc(mcxt,
-	sizeof(LargeObjectDesc));
-
-	retval->id = lobjId;
-	retval->subid = GetCurrentSubTransactionId();
-	retval->offset = 0;
+	Snapshot	snapshot = NULL;
+	int			descflags = 0;
 
 	if (flags & INV_WRITE)
 	{
-		retval->snapshot = NULL;		/* instantaneous MVCC snapshot */
-		retval->flags = IFS_WRLOCK | IFS_RDLOCK;
+		snapshot = NULL;		/* instantaneous MVCC snapshot */
+		descflags = IFS_WRLOCK | IFS_RDLOCK;
 	}
 	else if (flags & INV_READ)
 	{
-		/*
-		 * We must register the snapshot in TopTransaction's resowner, because
-		 * it must stay alive until the LO is closed rather than until the
-		 * current portal shuts down.
-		 */
-		retval->snapshot = RegisterSnapshotOnOwner(GetActiveSnapshot(),
-TopTransactionResourceOwner);
-		retval->flags = IFS_RDLOCK;
+		snapshot = GetActiveSnapshot();
+		descflags = IFS_RDLOCK;
 	}
 	else
 		ereport(ERROR,
@@ -271,11 +260,30 @@ inv_open(Oid lobjId, int flags, MemoryContext mcxt)
 		flags)));
 
 	/* Can't use LargeObjectExists here because we need to specify snapshot */
-	if (!myLargeObjectExists(lobjId, retval->snapshot))
+	if (!myLargeObjectExists(lobjId, snapshot))
 		ereport(ERROR,
 (errcode(ERRCODE_UNDEFINED_OBJECT),
  errmsg("large object %u does not exist", lobjId)));
 
+	/*
+	 * We must register the snapshot in TopTransaction's resowner, because
+	 * it must stay alive until the LO is closed rather than until the
+	 * current portal shuts down. Do this after checking that the LO exists,
+	 * to avoid leaking the snapshot if an error is thrown.
+	 */
+	if (snapshot)
+		snapshot = RegisterSnapshotOnOwner(snapshot,
+		   TopTransactionResourceOwner);
+
+	/* All set, create a descriptor */
+	retval = (LargeObjectDesc *) MemoryContextAlloc(mcxt,
+	sizeof(LargeObjectDesc));
+	retval->id = lobjId;
+	retval->subid = GetCurrentSubTransactionId();
+	retval->offset = 0;
+	retval->snapshot = snapshot;
+	retval->flags = descflags;
+
 	return retval;
 }
 
-- 
1.8.4.rc3


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] proposal: lob conversion functionality

[Rushabh Lathia]

On Thu, Sep 19, 2013 at 10:19 PM, Pavel Stehule wrote:

> Hello
>
> here is patch
>

Looks good.

Marking it as Ready for Committer.



>
> Regards
>
> Pavel
>
>
>
> 2013/9/19 Pavel Stehule 
>
>>
>>
>>
>> 2013/9/19 Rushabh Lathia 
>>
>>> 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 
>>> wrote:
>>>
 Hello

 here is a patch

 it introduce a load_lo and make_lo functions

 postgres=# select make_lo(decode('ff00','hex'));
  make_lo
 ─
24629
 (1 row)

 Time: 40.724 ms
 postgres=# select load_lo(24628);
   load_lo
 
  \xff00
 (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 

> +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 
>
>
> 2013/8/10 Pavel Stehule 
>
>>  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 stil

Re: [HACKERS] proposal: lob conversion functionality

[Pavel Stehule]

Hello

here is patch

Regards

Pavel



2013/9/19 Pavel Stehule 

>
>
>
> 2013/9/19 Rushabh Lathia 
>
>> 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 
>> wrote:
>>
>>> Hello
>>>
>>> here is a patch
>>>
>>> it introduce a load_lo and make_lo functions
>>>
>>> postgres=# select make_lo(decode('ff00','hex'));
>>>  make_lo
>>> ─
>>>24629
>>> (1 row)
>>>
>>> Time: 40.724 ms
>>> postgres=# select load_lo(24628);
>>>   load_lo
>>> 
>>>  \xff00
>>> (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 
>>>
 +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 


 2013/8/10 Pavel Stehule 

>  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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>

>>>
>>>
>

Re: [HACKERS] proposal: lob conversion functionality

[Pavel Stehule]

2013/9/19 Rushabh Lathia 

> 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 wrote:
>
>> Hello
>>
>> here is a patch
>>
>> it introduce a load_lo and make_lo functions
>>
>> postgres=# select make_lo(decode('ff00','hex'));
>>  make_lo
>> ─
>>24629
>> (1 row)
>>
>> Time: 40.724 ms
>> postgres=# select load_lo(24628);
>>   load_lo
>> 
>>  \xff00
>> (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 
>>
>>> +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 
>>>
>>>
>>> 2013/8/10 Pavel Stehule 
>>>
  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@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers


>>>
>>
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>>
>>
>
>
> --
> Rushabh Lathia
>

Re: [HACKERS] proposal: lob conversion functionality

[Rushabh Lathia]

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 wrote:

> Hello
>
> here is a patch
>
> it introduce a load_lo and make_lo functions
>
> postgres=# select make_lo(decode('ff00','hex'));
>  make_lo
> ─
>24629
> (1 row)
>
> Time: 40.724 ms
> postgres=# select load_lo(24628);
>   load_lo
> 
>  \xff00
> (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 
>
>> +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 
>>
>>
>> 2013/8/10 Pavel Stehule 
>>
>>>  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@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-hackers
>>>
>>>
>>
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>


-- 
Rushabh Lathia

Re: [HACKERS] proposal: lob conversion functionality

[Pavel Stehule]

2013/8/25 Pavel Stehule 

> Hello
>
> here is a patch
>
> it introduce a load_lo and make_lo functions
>
> postgres=# select make_lo(decode('ff00','hex'));
>  make_lo
> ─
>24629
> (1 row)
>
> Time: 40.724 ms
> postgres=# select load_lo(24628);
>   load_lo
> 
>  \xff00
> (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
>
>
my motivation and reason for implementation of this patch was little bit
difficult implementation of conversion blob to base64 string.

I have a communication system based on XML documents. These documents are
created in PG with XML functions. There was customer request to support
binary (images) attachments. LO API is really very good for transport
binary data from client side to server side, but next processing was less
nice - LO -> Bytea transformation is not well documented and there is
necessary to use a magic integer constants. With these two functions this
transformations are very simple.

just

select xmlforest(encode(load_lo(24630), 'base64') AS attachment);

Regards

Pavel


> Regards
>
> Pavel Stehule
>
>
>
> 2013/8/22 Jov 
>
>> +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 
>>
>>
>> 2013/8/10 Pavel Stehule 
>>
>>>  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@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-hackers
>>>
>>>
>>
>

Re: [HACKERS] proposal: lob conversion functionality

[Pavel Stehule]

Hello

here is a patch

it introduce a load_lo and make_lo functions

postgres=# select make_lo(decode('ff00','hex'));
 make_lo
─
   24629
(1 row)

Time: 40.724 ms
postgres=# select load_lo(24628);
  load_lo

 \xff00
(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 

> +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 
>
>
> 2013/8/10 Pavel Stehule 
>
>>  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@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>>
>>
>


load_lo.patch
Description: Binary data

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] proposal: lob conversion functionality

[Jov]

+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 


2013/8/10 Pavel Stehule 

> 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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>
>

Re: [HACKERS] proposal: lob conversion functionality

[Pavel Stehule]

2013/8/21 Jim Nasby 

> On 8/12/13 1:08 PM, Pavel Stehule wrote:
>
>>IF (bytes != LENGTH($1)) THEN
>>  RAISE EXCEPTION 'Not all data copied to blob';
>>END IF;
>>PERFORM lo_close(fd);
>>
>
> FWIW, it's probably better to close before raising the exception...
>

it should to be done automatically - lo handler is released after
transaction end

Pavel


> --
> Jim C. Nasby, Data Architect   j...@nasby.net
> 512.569.9461 (cell) http://jim.nasby.net
>

Re: [HACKERS] proposal: lob conversion functionality

[Jim Nasby]

On 8/12/13 1:08 PM, Pavel Stehule wrote:

   IF (bytes != LENGTH($1)) THEN
 RAISE EXCEPTION 'Not all data copied to blob';
   END IF;
   PERFORM lo_close(fd);


FWIW, it's probably better to close before raising the exception...
--
Jim C. Nasby, Data Architect   j...@nasby.net
512.569.9461 (cell) http://jim.nasby.net


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] proposal: lob conversion functionality

[Pavel Stehule]

Hello

2013/8/12 Pavel Stehule :
> 2013/8/10 Tom Lane :
>> Pavel Stehule  writes:
>>> I found so there are no simple API for working with LO from PL without
>>> access to file system.
>>
>> What?  See lo_open(), loread(), lowrite(), etc.
>>

so simplified functionality should have a little bit different names
than original LO API:

/*
 * saving bytea to lo (with possibility enter a target loid)
 */
FUNCTION make_lo(src bytea, loid oid DEFAULT -1) RETURNS oid

/*
 * loading from lo to bytea
*/
FUNCTION load_lo(loid) RETURNS bytea

This API is simple and friendly to PL languages, and for more complex
and specific work, there is still older LO server side API

Regards

Pavel

>
> yes, so there are three problems with these functions:
>
> a) probably (I didn't find) undocumented
>
> b) design with lo handler is little bit PL/pgSQL unfriendly.
>
>
> CREATE OR REPLACE FUNCTION parser.save_as_lob(bytea)
> RETURNS oid AS $$
> DECLARE
>   loid oid;
>   fd integer;
>   bytes integer;
> BEGIN
>   loid := lo_creat(-1);
>   fd := lo_open(loid, 131072);
>   bytes := lowrite(fd, $1);
>   IF (bytes != LENGTH($1)) THEN
> RAISE EXCEPTION 'Not all data copied to blob';
>   END IF;
>   PERFORM lo_close(fd);
>   RETURN loid;
> END;
> $$ LANGUAGE plpgsql SECURITY DEFINER STRICT SET search_path = 'pg_catalog';
>
>
> CREATE OR REPLACE FUNCTION fbuilder.attachment_to_bytea(attachment oid)
> RETURNS bytea AS $$
> DECLARE
>  fdinteger;
>  size  integer;
> BEGIN
>  fd   := lo_open(attachment, 262144);
>  size := lo_lseek(fd, 0, 2);
>  PERFORM lo_lseek(fd, 0, 0);
>  RETURN loread(fd, size);
> EXCEPTION WHEN undefined_object THEN
>   PERFORM lo_close(fd);
>   RETURN NULL;
> END;
> $$ LANGUAGE plpgsql STRICT SECURITY DEFINER SET search_path = 'pg_catalog';
>
> I had to use lot of magic constants, and getting size is not size too.
> I believe so almost all reading will be a complete read, and then it
> should be supported (maybe loread(fd, -1)).
>
> c) probably there is a bug - it doesn't expect handling errors
>
> postgres=# select fbuilder.attachment_to_xml(0);
> WARNING:  Snapshot reference leak: Snapshot 0x978f6f0 still referenced
>  attachment_to_xml
> ───
>  [null]
> (1 row)
>
> Time: 0.809 ms
>
>>> These functions can be simplified if we supports some functions like
>>> encode, decode for LO
>
>>
>> I do not see any good reason to tie encode/decode to LOs.
>
> It can save a one transformations - but it is not too important and
> can be easy done with current bytea API.
>
>>
>> regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] proposal: lob conversion functionality

[Pavel Stehule]

2013/8/10 Tom Lane :
> Pavel Stehule  writes:
>> I found so there are no simple API for working with LO from PL without
>> access to file system.
>
> What?  See lo_open(), loread(), lowrite(), etc.
>

yes, so there are three problems with these functions:

a) probably (I didn't find) undocumented

b) design with lo handler is little bit PL/pgSQL unfriendly.


CREATE OR REPLACE FUNCTION parser.save_as_lob(bytea)
RETURNS oid AS $$
DECLARE
  loid oid;
  fd integer;
  bytes integer;
BEGIN
  loid := lo_creat(-1);
  fd := lo_open(loid, 131072);
  bytes := lowrite(fd, $1);
  IF (bytes != LENGTH($1)) THEN
RAISE EXCEPTION 'Not all data copied to blob';
  END IF;
  PERFORM lo_close(fd);
  RETURN loid;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER STRICT SET search_path = 'pg_catalog';


CREATE OR REPLACE FUNCTION fbuilder.attachment_to_bytea(attachment oid)
RETURNS bytea AS $$
DECLARE
 fdinteger;
 size  integer;
BEGIN
 fd   := lo_open(attachment, 262144);
 size := lo_lseek(fd, 0, 2);
 PERFORM lo_lseek(fd, 0, 0);
 RETURN loread(fd, size);
EXCEPTION WHEN undefined_object THEN
  PERFORM lo_close(fd);
  RETURN NULL;
END;
$$ LANGUAGE plpgsql STRICT SECURITY DEFINER SET search_path = 'pg_catalog';

I had to use lot of magic constants, and getting size is not size too.
I believe so almost all reading will be a complete read, and then it
should be supported (maybe loread(fd, -1)).

c) probably there is a bug - it doesn't expect handling errors

postgres=# select fbuilder.attachment_to_xml(0);
WARNING:  Snapshot reference leak: Snapshot 0x978f6f0 still referenced
 attachment_to_xml
───
 [null]
(1 row)

Time: 0.809 ms

>> These functions can be simplified if we supports some functions like
>> encode, decode for LO

>
> I do not see any good reason to tie encode/decode to LOs.

It can save a one transformations - but it is not too important and
can be easy done with current bytea API.

>
> regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] proposal: lob conversion functionality

[Tom Lane]

Pavel Stehule  writes:
> I found so there are no simple API for working with LO from PL without
> access to file system.

What?  See lo_open(), loread(), lowrite(), etc.

> These functions can be simplified if we supports some functions like
> encode, decode for LO

I do not see any good reason to tie encode/decode to LOs.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers