proposal: lob conversion functionality
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
Pavel Stehule <pavel.stehule@gmail.com> 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
2013/8/10 Tom Lane <tgl@sss.pgh.pa.us>:
Pavel Stehule <pavel.stehule@gmail.com> 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
fd integer;
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
Hello
2013/8/12 Pavel Stehule <pavel.stehule@gmail.com>:
2013/8/10 Tom Lane <tgl@sss.pgh.pa.us>:
Pavel Stehule <pavel.stehule@gmail.com> 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
fd integer;
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 LOI 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
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 jim@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
2013/8/21 Jim Nasby <jim@nasby.net>
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
Show quoted text
--
Jim C. Nasby, Data Architect jim@nasby.net
512.569.9461 (cell) http://jim.nasby.net
+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.stehule@gmail.com>
Show quoted text
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 LOSo 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
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@amutu.com>
Show quoted text
+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.stehule@gmail.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 LOSo 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
Attachments:
load_lo.patchapplication/octet-stream; name=load_lo.patchDownload
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 3399,3404 **** SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
--- 3399,3457 ----
See also the aggregate function <function>string_agg</function> in
<xref linkend="functions-aggregate">.
</para>
+
+ <table id="functions-binarystring-largeobjects-transformations">
+ <title>Loading and saving from/to large objects Functions</title>
+ <tgroup cols="5">
+ <thead>
+ <row>
+ <entry>Function</entry>
+ <entry>Return Type</entry>
+ <entry>Description</entry>
+ <entry>Example</entry>
+ <entry>Result</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry>
+ <indexterm>
+ <primary>load_lo</primary>
+ </indexterm>
+ <literal><function>load_lo(<parameter>loid</parameter>
+ <type>oid</type>)</function></literal>
+ </entry>
+ <entry><type>bytea</type></entry>
+ <entry>
+ 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.
+ </entry>
+ <entry><literal>load_lo(24628)</literal></entry>
+ <entry><literal>\xffffff00</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>make_lo</primary>
+ </indexterm>
+ <literal><function>make_lo(<parameter>string</parameter> <type>bytea</type>,
+ <optional><parameter>loid</parameter> <type>oid</type></optional> )</function></literal>
+ </entry>
+ <entry><type>oid</type></entry>
+ <entry>
+ Create a large object and store a binary string there. Returns a oid of
+ created large object.
+ </entry>
+ <entry><literal>select make_lo(decode('ffffff00','hex'))</literal></entry>
+ <entry><literal>24528</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
</sect1>
*** a/src/backend/libpq/be-fsstubs.c
--- b/src/backend/libpq/be-fsstubs.c
***************
*** 754,756 **** deleteLOfd(int fd)
--- 754,848 ----
{
cookies[fd] = NULL;
}
+
+ /*****************************************************************************
+ * LO simplified SQL API for manipulation with LO
+ *****************************************************************************/
+
+ /*
+ * load LO and return it as bytea
+ */
+ Datum
+ load_lo(PG_FUNCTION_ARGS)
+ {
+ Oid loOid = PG_GETARG_OID(0);
+ LargeObjectDesc *loDesc;
+ int64 len;
+ 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)));
+
+ len = inv_seek(loDesc, 0, SEEK_END);
+ inv_seek(loDesc, 0, SEEK_SET);
+
+ result = (bytea *) palloc(VARHDRSZ + len);
+ total_read = inv_read(loDesc, VARDATA(result), len);
+
+ Assert(total_read == len);
+
+ inv_close(loDesc);
+
+ SET_VARSIZE(result, total_read + VARHDRSZ);
+
+ PG_RETURN_BYTEA_P(result);
+ }
+
+ /*
+ * internal - shared code for make_lo and make_lo_with_oid
+ */
+ static Oid
+ make_lo_internal(bytea *str, Oid loOid)
+ {
+ LargeObjectDesc *loDesc;
+
+ CreateFSContext();
+
+ loOid = inv_create(loOid);
+ loDesc = inv_open(loOid, INV_WRITE, fscxt);
+ inv_write(loDesc, VARDATA_ANY(str),
+ VARSIZE_ANY_EXHDR(str));
+ inv_close(loDesc);
+
+ return loOid;
+ }
+
+ /*
+ * Save bytea to LO
+ */
+ Datum
+ make_lo(PG_FUNCTION_ARGS)
+ {
+ bytea *str = PG_GETARG_BYTEA_PP(0);
+
+ PG_RETURN_OID(make_lo_internal(str, InvalidOid));
+ }
+
+ /*
+ * Save bytea to LO with specified loOid
+ */
+ Datum
+ make_lo_with_oid(PG_FUNCTION_ARGS)
+ {
+ bytea *str = PG_GETARG_BYTEA_PP(0);
+ Oid loOid = PG_GETARG_OID(1);
+
+ PG_RETURN_OID(make_lo_internal(str, loOid));
+ }
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 1055,1060 **** DESCR("truncate large object");
--- 1055,1067 ----
DATA(insert OID = 3172 ( lo_truncate64 PGNSP PGUID 12 1 0 0 0 f f f f t f v 2 0 23 "23 20" _null_ _null_ _null_ _null_ lo_truncate64 _null_ _null_ _null_ ));
DESCR("truncate large object (64 bit)");
+ DATA(insert OID = 3178 ( load_lo PGNSP PGUID 12 1 0 0 0 f f f f t f v 1 0 17 "26" _null_ _null_ _null_ _null_ load_lo _null_ _null_ _null_ ));
+ DESCR("load large object to bytea");
+ DATA(insert OID = 3179 ( make_lo PGNSP PGUID 12 1 0 0 0 f f f f t f v 1 0 26 "17" _null_ _null_ _null_ _null_ make_lo _null_ _null_ _null_ ));
+ DESCR("save bytea to large object");
+ DATA(insert OID = 3180 ( make_lo PGNSP PGUID 12 1 0 0 0 f f f f t f v 2 0 26 "17 26" _null_ _null_ _null_ _null_ make_lo_with_oid _null_ _null_ _null_ ));
+ DESCR("save bytea to large object");
+
DATA(insert OID = 959 ( on_pl PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 16 "600 628" _null_ _null_ _null_ _null_ on_pl _null_ _null_ _null_ ));
DATA(insert OID = 960 ( on_sl PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 16 "601 628" _null_ _null_ _null_ _null_ on_sl _null_ _null_ _null_ ));
DATA(insert OID = 961 ( close_pl PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 600 "600 628" _null_ _null_ _null_ _null_ close_pl _null_ _null_ _null_ ));
*** a/src/include/libpq/be-fsstubs.h
--- b/src/include/libpq/be-fsstubs.h
***************
*** 46,51 **** extern Datum lo_truncate64(PG_FUNCTION_ARGS);
--- 46,58 ----
extern bool lo_compat_privileges;
/*
+ * Simplified LO API
+ */
+ extern Datum load_lo(PG_FUNCTION_ARGS);
+ extern Datum make_lo(PG_FUNCTION_ARGS);
+ extern Datum make_lo_with_oid(PG_FUNCTION_ARGS);
+
+ /*
* These are not fmgr-callable, but are available to C code.
* Probably these should have had the underscore-free names,
* but too late now...
*** a/src/test/regress/input/largeobject.source
--- b/src/test/regress/input/largeobject.source
***************
*** 203,207 **** SELECT pageno, data FROM pg_largeobject WHERE loid = :newloid;
--- 203,219 ----
SELECT lo_unlink(loid) FROM lotest_stash_values;
\lo_unlink :newloid
+ \lo_import 'results/lotest.txt'
+
+ \set newloid_1 :LASTOID
+
+ SELECT make_lo(load_lo(:newloid_1)) AS newloid_2
+ \gset
+
+ SELECT md5(load_lo(:newloid_1)) = md5(load_lo(:newloid_2));
+
+ \lo_unlink :newloid_1
+ \lo_unlink :newloid_2
+
TRUNCATE lotest_stash_values;
DROP ROLE regresslo;
*** a/src/test/regress/output/largeobject.source
--- b/src/test/regress/output/largeobject.source
***************
*** 391,395 **** SELECT lo_unlink(loid) FROM lotest_stash_values;
--- 391,407 ----
(1 row)
\lo_unlink :newloid
+ \lo_import 'results/lotest.txt'
+ \set newloid_1 :LASTOID
+ SELECT make_lo(load_lo(:newloid_1)) AS newloid_2
+ \gset
+ SELECT md5(load_lo(:newloid_1)) = md5(load_lo(:newloid_2));
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ \lo_unlink :newloid_1
+ \lo_unlink :newloid_2
TRUNCATE lotest_stash_values;
DROP ROLE regresslo;
*** a/src/test/regress/output/largeobject_1.source
--- b/src/test/regress/output/largeobject_1.source
***************
*** 391,395 **** SELECT lo_unlink(loid) FROM lotest_stash_values;
--- 391,407 ----
(1 row)
\lo_unlink :newloid
+ \lo_import 'results/lotest.txt'
+ \set newloid_1 :LASTOID
+ SELECT make_lo(load_lo(:newloid_1)) AS newloid_2
+ \gset
+ SELECT md5(load_lo(:newloid_1)) = md5(load_lo(:newloid_2));
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ \lo_unlink :newloid_1
+ \lo_unlink :newloid_2
TRUNCATE lotest_stash_values;
DROP ROLE regresslo;
2013/8/25 Pavel Stehule <pavel.stehule@gmail.com>
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 24630postgres=# 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
Show quoted text
Regards
Pavel Stehule
2013/8/22 Jov <amutu@amutu.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.stehule@gmail.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 LOSo 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
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.stehule@gmail.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 24630postgres=# select md5(load_lo(24630));
md5
──────────────────────────────────
513f60836f3b625713acaf1c19b6ea78
(1 row)postgres=# \q
bash-4.1$ md5sum ~/avatar.png
513f60836f3b625713acaf1c19b6ea78 /home/pavel/avatar.pngRegards
Pavel Stehule
2013/8/22 Jov <amutu@amutu.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.stehule@gmail.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 LOSo 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
2013/9/19 Rushabh Lathia <rushabh.lathia@gmail.com>
Hi Pavel,
I have reviewed you patch.
-- Patch got applied cleanly (using patch -p1)
-- Make & Make install works fine
-- make check looks goodI 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
Show quoted text
Regards,
Rushabh Lathia
www.EnterpriseDB.comOn Sun, Aug 25, 2013 at 8:31 PM, Pavel Stehule <pavel.stehule@gmail.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 24630postgres=# select md5(load_lo(24630));
md5
──────────────────────────────────
513f60836f3b625713acaf1c19b6ea78
(1 row)postgres=# \q
bash-4.1$ md5sum ~/avatar.png
513f60836f3b625713acaf1c19b6ea78 /home/pavel/avatar.pngRegards
Pavel Stehule
2013/8/22 Jov <amutu@amutu.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.stehule@gmail.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 LOSo 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
Hello
here is patch
Regards
Pavel
2013/9/19 Pavel Stehule <pavel.stehule@gmail.com>
Show quoted text
2013/9/19 Rushabh Lathia <rushabh.lathia@gmail.com>
Hi Pavel,
I have reviewed you patch.
-- Patch got applied cleanly (using patch -p1)
-- Make & Make install works fine
-- make check looks goodI 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.comOn Sun, Aug 25, 2013 at 8:31 PM, Pavel Stehule <pavel.stehule@gmail.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 24630postgres=# select md5(load_lo(24630));
md5
──────────────────────────────────
513f60836f3b625713acaf1c19b6ea78
(1 row)postgres=# \q
bash-4.1$ md5sum ~/avatar.png
513f60836f3b625713acaf1c19b6ea78 /home/pavel/avatar.pngRegards
Pavel Stehule
2013/8/22 Jov <amutu@amutu.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.stehule@gmail.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 LOSo 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
Attachments:
load_lo_v2.patchapplication/octet-stream; name=load_lo_v2.patchDownload
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***************
*** 3399,3404 **** SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
--- 3399,3462 ----
See also the aggregate function <function>string_agg</function> in
<xref linkend="functions-aggregate">.
</para>
+
+ <table id="functions-binarystring-largeobjects-transformations">
+ <title>Loading and saving from/to Large Objects Functions</title>
+ <tgroup cols="5">
+ <thead>
+ <row>
+ <entry>Function</entry>
+ <entry>Return Type</entry>
+ <entry>Description</entry>
+ <entry>Example</entry>
+ <entry>Result</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry>
+ <indexterm>
+ <primary>load_lo</primary>
+ </indexterm>
+ <literal><function>load_lo(<parameter>loid</parameter>
+ <type>oid</type>)</function></literal>
+ </entry>
+ <entry><type>bytea</type></entry>
+ <entry>
+ 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.
+ </entry>
+ <entry><literal>load_lo(24628)</literal></entry>
+ <entry><literal>\xffffff00</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>make_lo</primary>
+ </indexterm>
+ <literal><function>make_lo(<parameter>string</parameter> <type>bytea</type>,
+ <optional><parameter>loid</parameter> <type>oid</type></optional> )</function></literal>
+ </entry>
+ <entry><type>oid</type></entry>
+ <entry>
+ Create a large object and store a binary string there. Returns a oid of
+ created large object.
+ </entry>
+ <entry><literal>select make_lo(decode('ffffff00','hex'))</literal></entry>
+ <entry><literal>24528</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ See also a description of other Large Objects Function
+ in <xref linkend="lo-funcs">.
+ </para>
</sect1>
*** a/doc/src/sgml/lobj.sgml
--- b/doc/src/sgml/lobj.sgml
***************
*** 580,585 **** SELECT lo_export(image.raster, '/tmp/motd') FROM image
--- 580,608 ----
these functions as <function>loread</> and <function>lowrite</>.
</para>
+ <para>
+ There are other two functions , that doesn't correspond with client api
+ (see in <xref linkend="functions-binarystring-largeobjects-transformations">).
+ <function>make_lo</function> transforms a binary string to lo object, and
+ <function>load_lo</function> transforms a lo object to binary string.
+ </para>
+
+ <para>
+ Some examples:
+ <programlisting>
+ SELECT make_lo(decode('ffffff00','hex'));
+ make_lo
+ ---------
+ 24629
+ (1 row)
+
+ SELECT load_lo(24628);
+ load_lo
+ -----------
+ \xffffff00
+ (1 row)
+ </programlisting>
+ </para>
</sect1>
<sect1 id="lo-examplesect">
*** a/src/backend/libpq/be-fsstubs.c
--- b/src/backend/libpq/be-fsstubs.c
***************
*** 754,756 **** deleteLOfd(int fd)
--- 754,848 ----
{
cookies[fd] = NULL;
}
+
+ /*****************************************************************************
+ * LO simplified SQL API for manipulation with LO
+ *****************************************************************************/
+
+ /*
+ * load LO and return it as bytea
+ */
+ Datum
+ load_lo(PG_FUNCTION_ARGS)
+ {
+ Oid loOid = PG_GETARG_OID(0);
+ LargeObjectDesc *loDesc;
+ int64 len;
+ 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)));
+
+ len = inv_seek(loDesc, 0, SEEK_END);
+ inv_seek(loDesc, 0, SEEK_SET);
+
+ result = (bytea *) palloc(VARHDRSZ + len);
+ total_read = inv_read(loDesc, VARDATA(result), len);
+
+ Assert(total_read == len);
+
+ inv_close(loDesc);
+
+ SET_VARSIZE(result, total_read + VARHDRSZ);
+
+ PG_RETURN_BYTEA_P(result);
+ }
+
+ /*
+ * internal - shared code for make_lo and make_lo_with_oid
+ */
+ static Oid
+ make_lo_internal(bytea *str, Oid loOid)
+ {
+ LargeObjectDesc *loDesc;
+
+ CreateFSContext();
+
+ loOid = inv_create(loOid);
+ loDesc = inv_open(loOid, INV_WRITE, fscxt);
+ inv_write(loDesc, VARDATA_ANY(str),
+ VARSIZE_ANY_EXHDR(str));
+ inv_close(loDesc);
+
+ return loOid;
+ }
+
+ /*
+ * Save bytea to LO
+ */
+ Datum
+ make_lo(PG_FUNCTION_ARGS)
+ {
+ bytea *str = PG_GETARG_BYTEA_PP(0);
+
+ PG_RETURN_OID(make_lo_internal(str, InvalidOid));
+ }
+
+ /*
+ * Save bytea to LO with specified loOid
+ */
+ Datum
+ make_lo_with_oid(PG_FUNCTION_ARGS)
+ {
+ bytea *str = PG_GETARG_BYTEA_PP(0);
+ Oid loOid = PG_GETARG_OID(1);
+
+ PG_RETURN_OID(make_lo_internal(str, loOid));
+ }
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***************
*** 1055,1060 **** DESCR("truncate large object");
--- 1055,1067 ----
DATA(insert OID = 3172 ( lo_truncate64 PGNSP PGUID 12 1 0 0 0 f f f f t f v 2 0 23 "23 20" _null_ _null_ _null_ _null_ lo_truncate64 _null_ _null_ _null_ ));
DESCR("truncate large object (64 bit)");
+ DATA(insert OID = 3178 ( load_lo PGNSP PGUID 12 1 0 0 0 f f f f t f v 1 0 17 "26" _null_ _null_ _null_ _null_ load_lo _null_ _null_ _null_ ));
+ DESCR("load large object to bytea");
+ DATA(insert OID = 3179 ( make_lo PGNSP PGUID 12 1 0 0 0 f f f f t f v 1 0 26 "17" _null_ _null_ _null_ _null_ make_lo _null_ _null_ _null_ ));
+ DESCR("save bytea to large object");
+ DATA(insert OID = 3180 ( make_lo PGNSP PGUID 12 1 0 0 0 f f f f t f v 2 0 26 "17 26" _null_ _null_ _null_ _null_ make_lo_with_oid _null_ _null_ _null_ ));
+ DESCR("save bytea to large object");
+
DATA(insert OID = 959 ( on_pl PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 16 "600 628" _null_ _null_ _null_ _null_ on_pl _null_ _null_ _null_ ));
DATA(insert OID = 960 ( on_sl PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 16 "601 628" _null_ _null_ _null_ _null_ on_sl _null_ _null_ _null_ ));
DATA(insert OID = 961 ( close_pl PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 600 "600 628" _null_ _null_ _null_ _null_ close_pl _null_ _null_ _null_ ));
*** a/src/include/libpq/be-fsstubs.h
--- b/src/include/libpq/be-fsstubs.h
***************
*** 46,51 **** extern Datum lo_truncate64(PG_FUNCTION_ARGS);
--- 46,58 ----
extern bool lo_compat_privileges;
/*
+ * Simplified LO API
+ */
+ extern Datum load_lo(PG_FUNCTION_ARGS);
+ extern Datum make_lo(PG_FUNCTION_ARGS);
+ extern Datum make_lo_with_oid(PG_FUNCTION_ARGS);
+
+ /*
* These are not fmgr-callable, but are available to C code.
* Probably these should have had the underscore-free names,
* but too late now...
*** a/src/test/regress/input/largeobject.source
--- b/src/test/regress/input/largeobject.source
***************
*** 203,207 **** SELECT pageno, data FROM pg_largeobject WHERE loid = :newloid;
--- 203,219 ----
SELECT lo_unlink(loid) FROM lotest_stash_values;
\lo_unlink :newloid
+ \lo_import 'results/lotest.txt'
+
+ \set newloid_1 :LASTOID
+
+ SELECT make_lo(load_lo(:newloid_1)) AS newloid_2
+ \gset
+
+ SELECT md5(load_lo(:newloid_1)) = md5(load_lo(:newloid_2));
+
+ \lo_unlink :newloid_1
+ \lo_unlink :newloid_2
+
TRUNCATE lotest_stash_values;
DROP ROLE regresslo;
*** a/src/test/regress/output/largeobject.source
--- b/src/test/regress/output/largeobject.source
***************
*** 391,395 **** SELECT lo_unlink(loid) FROM lotest_stash_values;
--- 391,407 ----
(1 row)
\lo_unlink :newloid
+ \lo_import 'results/lotest.txt'
+ \set newloid_1 :LASTOID
+ SELECT make_lo(load_lo(:newloid_1)) AS newloid_2
+ \gset
+ SELECT md5(load_lo(:newloid_1)) = md5(load_lo(:newloid_2));
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ \lo_unlink :newloid_1
+ \lo_unlink :newloid_2
TRUNCATE lotest_stash_values;
DROP ROLE regresslo;
*** a/src/test/regress/output/largeobject_1.source
--- b/src/test/regress/output/largeobject_1.source
***************
*** 391,395 **** SELECT lo_unlink(loid) FROM lotest_stash_values;
--- 391,407 ----
(1 row)
\lo_unlink :newloid
+ \lo_import 'results/lotest.txt'
+ \set newloid_1 :LASTOID
+ SELECT make_lo(load_lo(:newloid_1)) AS newloid_2
+ \gset
+ SELECT md5(load_lo(:newloid_1)) = md5(load_lo(:newloid_2));
+ ?column?
+ ----------
+ t
+ (1 row)
+
+ \lo_unlink :newloid_1
+ \lo_unlink :newloid_2
TRUNCATE lotest_stash_values;
DROP ROLE regresslo;
On Thu, Sep 19, 2013 at 10:19 PM, Pavel Stehule <pavel.stehule@gmail.com>wrote:
Hello
here is patch
Looks good.
Marking it as Ready for Committer.
Regards
Pavel
2013/9/19 Pavel Stehule <pavel.stehule@gmail.com>
2013/9/19 Rushabh Lathia <rushabh.lathia@gmail.com>
Hi Pavel,
I have reviewed you patch.
-- Patch got applied cleanly (using patch -p1)
-- Make & Make install works fine
-- make check looks goodI 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.comOn Sun, Aug 25, 2013 at 8:31 PM, Pavel Stehule <pavel.stehule@gmail.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 24630postgres=# select md5(load_lo(24630));
md5
──────────────────────────────────
513f60836f3b625713acaf1c19b6ea78
(1 row)postgres=# \q
bash-4.1$ md5sum ~/avatar.png
513f60836f3b625713acaf1c19b6ea78 /home/pavel/avatar.pngRegards
Pavel Stehule
2013/8/22 Jov <amutu@amutu.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.stehule@gmail.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 LOSo 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
--
Rushabh Lathia
On 12.08.2013 21:08, Pavel Stehule wrote:
2013/8/10 Tom Lane<tgl@sss.pgh.pa.us>:
Pavel Stehule<pavel.stehule@gmail.com> 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
Attachments:
0001-Fix-snapshot-leak-if-lo_open-called-on-non-existent-.patchtext/x-diff; name=0001-Fix-snapshot-leak-if-lo_open-called-on-non-existent-.patchDownload
>From 357f7521384df34c697b3544115622520a6a0e9f Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas <heikki.linnakangas@iki.fi>
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
2013/9/30 Heikki Linnakangas <hlinnakangas@vmware.com>
On 12.08.2013 21:08, Pavel Stehule wrote:
2013/8/10 Tom Lane<tgl@sss.pgh.pa.us>:
Pavel Stehule<pavel.stehule@gmail.**com <pavel.stehule@gmail.com>>
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<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
Show quoted text
- Heikki
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<tgl@sss.pgh.pa.us>:
Pavel Stehule<pavel.stehule@gmail.com> 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_exportCalling 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
2013/10/21 Noah Misch <noah@leadboat.com>
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<tgl@sss.pgh.pa.us>:
Pavel Stehule<pavel.stehule@gmail.com> 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_exportCalling 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 offsetAnything 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
Show quoted text
Thanks,
nm--
Noah Misch
EnterpriseDB http://www.enterprisedb.com
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 offsetAnything 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
Hello
2013/10/21 Andrew Dunstan <andrew@dunslane.net>
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
Show quoted text
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
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
2013/10/21 Noah Misch <noah@leadboat.com>
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
Show quoted text
Thanks,
nm--
Noah Misch
EnterpriseDB http://www.enterprisedb.com
Hello
here is patch
Regards
Pavel
2013/10/21 Noah Misch <noah@leadboat.com>
Show quoted text
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
Attachments:
load_lo_v3.patchapplication/octet-stream; name=load_lo_v3.patchDownload
From e83324a7ead7b3411d51583f4368836b9ed54048 Mon Sep 17 00:00:00 2001
From: Pavel Stehule <pavel.stehule@gooddata.com>
Date: Tue, 22 Oct 2013 12:50:51 +0200
Subject: [PATCH] enhance LO API be more friendly for usage from SQL level
---
doc/src/sgml/func.sgml | 72 ++++++++++++++
doc/src/sgml/lobj.sgml | 23 +++++
src/backend/libpq/be-fsstubs.c | 134 +++++++++++++++++++++++++++
src/include/catalog/pg_proc.h | 9 ++
src/include/libpq/be-fsstubs.h | 5 +
src/test/regress/input/largeobject.source | 17 ++++
src/test/regress/output/largeobject.source | 36 +++++++
src/test/regress/output/largeobject_1.source | 36 +++++++
8 files changed, 332 insertions(+)
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 <function>string_agg</function> in
<xref linkend="functions-aggregate">.
</para>
+
+ <table id="functions-binarystring-largeobjects-transformations">
+ <title>Loading and saving from/to Large Objects Functions</title>
+ <tgroup cols="5">
+ <thead>
+ <row>
+ <entry>Function</entry>
+ <entry>Return Type</entry>
+ <entry>Description</entry>
+ <entry>Example</entry>
+ <entry>Result</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry>
+ <indexterm>
+ <primary>lo_create</primary>
+ </indexterm>
+ <literal><function>lo_create(<parameter>loid</parameter> <type>oid</type>, <parameter>string</parameter> <type>bytea</type> )</function></literal>
+ </entry>
+ <entry><type>oid</type></entry>
+ <entry>
+ Create a large object and store a binary string there. Returns a oid of
+ created large object.
+ </entry>
+ <entry><literal>select lo_create(0, decode('ffffff00','hex'))</literal></entry>
+ <entry><literal>24528</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>lo_get</primary>
+ </indexterm>
+ <literal><function>lo_get(<parameter>loid</parameter> <type>oid</type> <optional>, <parameter>offset</parameter> <type>bigint</type>, <parameter>str</parameter> <type>bytea</type></optional>)</function></literal>
+ </entry>
+ <entry><type>bytea</type></entry>
+ <entry>
+ 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.
+ </entry>
+ <entry><literal>lo_get(24628)</literal></entry>
+ <entry><literal>\xffffff00</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>lo_put</primary>
+ </indexterm>
+ <literal><function>lo_put(<parameter>loid</parameter><type>oid</type>, <parameter>offset</parameter> <type>bigint</type>, <parameter>str</parameter> <type>bytea</type>)</function></literal>
+ </entry>
+ <entry><type>void</type></entry>
+ <entry>
+ Write data at offset.
+ </entry>
+ <entry><literal>lo_put(24628, 0, decode('aaaa', 'hex'))</literal></entry>
+ <entry></entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ See also a description of other Large Objects Function
+ in <xref linkend="lo-funcs">.
+ </para>
</sect1>
diff --git a/doc/src/sgml/lobj.sgml b/doc/src/sgml/lobj.sgml
index bb3e08f..82b1786 100644
--- a/doc/src/sgml/lobj.sgml
+++ b/doc/src/sgml/lobj.sgml
@@ -580,6 +580,29 @@ SELECT lo_export(image.raster, '/tmp/motd') FROM image
these functions as <function>loread</> and <function>lowrite</>.
</para>
+ <para>
+ There are other two functions , that doesn't correspond with client api
+ (see in <xref linkend="functions-binarystring-largeobjects-transformations">).
+ <function>make_lo</function> transforms a binary string to lo object, and
+ <function>load_lo</function> transforms a lo object to binary string.
+ </para>
+
+ <para>
+ Some examples:
+<programlisting>
+SELECT make_lo(decode('ffffff00','hex'));
+ make_lo
+ ---------
+ 24629
+(1 row)
+
+SELECT load_lo(24628);
+ load_lo
+ -----------
+ \xffffff00
+(1 row)
+</programlisting>
+ </para>
</sect1>
<sect1 id="lo-examplesect">
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);
+}
+
+Datum
+lo_get_fragment(PG_FUNCTION_ARGS)
+{
+ Oid loOid = PG_GETARG_OID(0);
+ int64 offset = PG_GETARG_INT64(1);
+ int32 nbytes = PG_GETARG_INT32(2);
+
+ bytea *result;
+
+ result = lo_get_fragment_internal(loOid, offset, nbytes);
+
+ PG_RETURN_BYTEA_P(result);
+}
+
+/*
+ * Create LO from bytea
+ */
+Datum
+lo_create_bytea(PG_FUNCTION_ARGS)
+{
+ Oid loOid = PG_GETARG_OID(0);
+ bytea *str = PG_GETARG_BYTEA_PP(1);
+ LargeObjectDesc *loDesc;
+
+ CreateFSContext();
+
+ loOid = inv_create(loOid);
+ loDesc = inv_open(loOid, INV_WRITE, fscxt);
+ inv_write(loDesc, VARDATA_ANY(str),
+ VARSIZE_ANY_EXHDR(str));
+ inv_close(loDesc);
+
+ PG_RETURN_OID(loOid);
+}
+
+/*
+ * Modify LO object
+ */
+Datum
+lo_put(PG_FUNCTION_ARGS)
+{
+ Oid loOid = PG_GETARG_OID(0);
+ int64 offset = PG_GETARG_INT64(1);
+ bytea *str = PG_GETARG_BYTEA_PP(2);
+ LargeObjectDesc *loDesc;
+
+ CreateFSContext();
+
+ loDesc = inv_open(loOid, INV_WRITE, fscxt);
+ inv_seek(loDesc, offset, SEEK_SET);
+ inv_write(loDesc, VARDATA_ANY(str),
+ VARSIZE_ANY_EXHDR(str));
+ inv_close(loDesc);
+
+ PG_RETURN_VOID();
+}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 08586ae..ca4fc62 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1055,6 +1055,15 @@ DESCR("truncate large object");
DATA(insert OID = 3172 ( lo_truncate64 PGNSP PGUID 12 1 0 0 0 f f f f t f v 2 0 23 "23 20" _null_ _null_ _null_ _null_ lo_truncate64 _null_ _null_ _null_ ));
DESCR("truncate large object (64 bit)");
+DATA(insert OID = 3457 ( lo_create PGNSP PGUID 12 1 0 0 0 f f f f t f v 2 0 26 "26 17" _null_ _null_ _null_ _null_ lo_create_bytea _null_ _null_ _null_ ));
+DESCR("create new large object with content");
+DATA(insert OID = 3458 ( lo_get PGNSP PGUID 12 1 0 0 0 f f f f t f v 1 0 17 "26" _null_ _null_ _null_ _null_ lo_get _null_ _null_ _null_ ));
+DESCR("read entire large object");
+DATA(insert OID = 3459 ( lo_get PGNSP PGUID 12 1 0 0 0 f f f f t f v 3 0 17 "26 20 23" _null_ _null_ _null_ _null_ lo_get_fragment _null_ _null_ _null_ ));
+DESCR("read large object from offset for length");
+DATA(insert OID = 3460 ( lo_put PGNSP PGUID 12 1 0 0 0 f f f f t f v 3 0 2278 "26 20 17" _null_ _null_ _null_ _null_ lo_put _null_ _null_ _null_ ));
+DESCR("write data at offset");
+
DATA(insert OID = 959 ( on_pl PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 16 "600 628" _null_ _null_ _null_ _null_ on_pl _null_ _null_ _null_ ));
DATA(insert OID = 960 ( on_sl PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 16 "601 628" _null_ _null_ _null_ _null_ on_sl _null_ _null_ _null_ ));
DATA(insert OID = 961 ( close_pl PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 600 "600 628" _null_ _null_ _null_ _null_ close_pl _null_ _null_ _null_ ));
diff --git a/src/include/libpq/be-fsstubs.h b/src/include/libpq/be-fsstubs.h
index a2b803a..50b9190 100644
--- a/src/include/libpq/be-fsstubs.h
+++ b/src/include/libpq/be-fsstubs.h
@@ -25,6 +25,7 @@ extern Datum lo_export(PG_FUNCTION_ARGS);
extern Datum lo_creat(PG_FUNCTION_ARGS);
extern Datum lo_create(PG_FUNCTION_ARGS);
+extern Datum lo_create_bytea(PG_FUNCTION_ARGS);
extern Datum lo_open(PG_FUNCTION_ARGS);
extern Datum lo_close(PG_FUNCTION_ARGS);
@@ -32,6 +33,10 @@ extern Datum lo_close(PG_FUNCTION_ARGS);
extern Datum loread(PG_FUNCTION_ARGS);
extern Datum lowrite(PG_FUNCTION_ARGS);
+extern Datum lo_get(PG_FUNCTION_ARGS);
+extern Datum lo_get_fragment(PG_FUNCTION_ARGS);
+extern Datum lo_put(PG_FUNCTION_ARGS);
+
extern Datum lo_lseek(PG_FUNCTION_ARGS);
extern Datum lo_tell(PG_FUNCTION_ARGS);
extern Datum lo_lseek64(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/input/largeobject.source b/src/test/regress/input/largeobject.source
index f0ea7a2..944b623 100644
--- a/src/test/regress/input/largeobject.source
+++ b/src/test/regress/input/largeobject.source
@@ -203,5 +203,22 @@ SELECT pageno, data FROM pg_largeobject WHERE loid = :newloid;
SELECT lo_unlink(loid) FROM lotest_stash_values;
\lo_unlink :newloid
+\lo_import 'results/lotest.txt'
+
+\set newloid_1 :LASTOID
+
+SELECT lo_create(0, lo_get(:newloid_1)) AS newloid_2
+\gset
+
+SELECT md5(lo_get(:newloid_1)) = md5(lo_get(:newloid_2));
+
+SELECT lo_get(:newloid_1, 0, 20);
+SELECT lo_get(:newloid_1, 10, 20);
+SELECT lo_put(:newloid_1, 5, decode('afafafaf', 'hex'));
+SELECT lo_get(:newloid_1, 0, 20);
+
+\lo_unlink :newloid_1
+\lo_unlink :newloid_2
+
TRUNCATE lotest_stash_values;
DROP ROLE regresslo;
diff --git a/src/test/regress/output/largeobject.source b/src/test/regress/output/largeobject.source
index a25ac2a..9bdf75c 100644
--- a/src/test/regress/output/largeobject.source
+++ b/src/test/regress/output/largeobject.source
@@ -391,5 +391,41 @@ SELECT lo_unlink(loid) FROM lotest_stash_values;
(1 row)
\lo_unlink :newloid
+\lo_import 'results/lotest.txt'
+\set newloid_1 :LASTOID
+SELECT lo_create(0, lo_get(:newloid_1)) AS newloid_2
+\gset
+SELECT md5(lo_get(:newloid_1)) = md5(lo_get(:newloid_2));
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT lo_get(:newloid_1, 0, 20);
+ lo_get
+-------------------------------------------
+ 8800\0110\0110\0110\0110\0110\0110\011800
+(1 row)
+
+SELECT lo_get(:newloid_1, 10, 20);
+ lo_get
+-------------------------------------------
+ \0110\0110\0110\011800\011800\0113800\011
+(1 row)
+
+SELECT lo_put(:newloid_1, 5, decode('afafafaf', 'hex'));
+ lo_put
+--------
+
+(1 row)
+
+SELECT lo_get(:newloid_1, 0, 20);
+ lo_get
+-------------------------------------------------
+ 8800\011\257\257\257\2570\0110\0110\0110\011800
+(1 row)
+
+\lo_unlink :newloid_1
+\lo_unlink :newloid_2
TRUNCATE lotest_stash_values;
DROP ROLE regresslo;
diff --git a/src/test/regress/output/largeobject_1.source b/src/test/regress/output/largeobject_1.source
index bae74f6..b02489f 100644
--- a/src/test/regress/output/largeobject_1.source
+++ b/src/test/regress/output/largeobject_1.source
@@ -391,5 +391,41 @@ SELECT lo_unlink(loid) FROM lotest_stash_values;
(1 row)
\lo_unlink :newloid
+\lo_import 'results/lotest.txt'
+\set newloid_1 :LASTOID
+SELECT lo_create(0, lo_get(:newloid_1)) AS newloid_2
+\gset
+SELECT md5(lo_get(:newloid_1)) = md5(lo_get(:newloid_2));
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT lo_get(:newloid_1, 0, 20);
+ lo_get
+-------------------------------------------
+ 8800\0110\0110\0110\0110\0110\0110\011800
+(1 row)
+
+SELECT lo_get(:newloid_1, 10, 20);
+ lo_get
+-------------------------------------------
+ \0110\0110\0110\011800\011800\0113800\011
+(1 row)
+
+SELECT lo_put(:newloid_1, 5, decode('afafafaf', 'hex'));
+ lo_put
+--------
+
+(1 row)
+
+SELECT lo_get(:newloid_1, 0, 20);
+ lo_get
+-------------------------------------------------
+ 8800\011\257\257\257\2570\0110\0110\0110\011800
+(1 row)
+
+\lo_unlink :newloid_1
+\lo_unlink :newloid_2
TRUNCATE lotest_stash_values;
DROP ROLE regresslo;
--
1.8.3.1
On 22.10.2013 13:55, Pavel Stehule wrote:
2013/10/21 Noah Misch<noah@leadboat.com>
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
fixed documentation
Regards
Pavel
2013/10/24 Heikki Linnakangas <hlinnakangas@vmware.com>
Show quoted text
On 22.10.2013 13:55, Pavel Stehule wrote:
2013/10/21 Noah Misch<noah@leadboat.com>
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
Attachments:
load_lo_v4.patchtext/x-patch; charset=US-ASCII; name=load_lo_v4.patchDownload
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 <function>string_agg</function> in
<xref linkend="functions-aggregate">.
</para>
+
+ <table id="functions-binarystring-largeobjects-transformations">
+ <title>Loading and saving from/to Large Objects Functions</title>
+ <tgroup cols="5">
+ <thead>
+ <row>
+ <entry>Function</entry>
+ <entry>Return Type</entry>
+ <entry>Description</entry>
+ <entry>Example</entry>
+ <entry>Result</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry>
+ <indexterm>
+ <primary>lo_create</primary>
+ </indexterm>
+ <literal><function>lo_create(<parameter>loid</parameter> <type>oid</type>, <parameter>string</parameter> <type>bytea</type> )</function></literal>
+ </entry>
+ <entry><type>oid</type></entry>
+ <entry>
+ Create a large object and store a binary string there. Returns a oid of
+ created large object.
+ </entry>
+ <entry><literal>select lo_create(0, decode('ffffff00','hex'))</literal></entry>
+ <entry><literal>24528</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>lo_get</primary>
+ </indexterm>
+ <literal><function>lo_get(<parameter>loid</parameter> <type>oid</type> <optional>, <parameter>offset</parameter> <type>bigint</type>, <parameter>str</parameter> <type>bytea</type></optional>)</function></literal>
+ </entry>
+ <entry><type>bytea</type></entry>
+ <entry>
+ 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.
+ </entry>
+ <entry><literal>lo_get(24628)</literal></entry>
+ <entry><literal>\xffffff00</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>lo_put</primary>
+ </indexterm>
+ <literal><function>lo_put(<parameter>loid</parameter><type>oid</type>, <parameter>offset</parameter> <type>bigint</type>, <parameter>str</parameter> <type>bytea</type>)</function></literal>
+ </entry>
+ <entry><type>void</type></entry>
+ <entry>
+ Write data at offset.
+ </entry>
+ <entry><literal>lo_put(24628, 0, decode('aaaa', 'hex'))</literal></entry>
+ <entry></entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ See also a description of other Large Objects Function
+ in <xref linkend="lo-funcs">.
+ </para>
</sect1>
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 <function>loread</> and <function>lowrite</>.
</para>
+ <para>
+ There are other two functions , that doesn't correspond with client api
+ (see in <xref linkend="functions-binarystring-largeobjects-transformations">).
+ <function>lo_create</function> transforms a binary string to lo object,
+ <function>lo_get</function> transforms a lo object to binary string, and
+ <function>lo_put</function> write a binary string at offset to lo object.
+ </para>
+
+ <para>
+ Some examples:
+<programlisting>
+SELECT lo_create(0, decode('ffffff00','hex'));
+ lo_create
+-----------
+ 16392
+(1 row)
+
+SELECT lo_get(16392);
+ lo_get
+------------
+ \xffffff00
+(1 row)
+
+SELECT lo_put(16392, 1, decode('aa','hex'));
+ lo_put
+--------
+
+(1 row)
+
+SELECT lo_get(16392);
+ lo_get
+------------
+ \xffaaff00
+(1 row)
+</programlisting>
+ </para>
</sect1>
<sect1 id="lo-examplesect">
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);
+}
+
+Datum
+lo_get_fragment(PG_FUNCTION_ARGS)
+{
+ Oid loOid = PG_GETARG_OID(0);
+ int64 offset = PG_GETARG_INT64(1);
+ int32 nbytes = PG_GETARG_INT32(2);
+
+ bytea *result;
+
+ result = lo_get_fragment_internal(loOid, offset, nbytes);
+
+ PG_RETURN_BYTEA_P(result);
+}
+
+/*
+ * Create LO from bytea
+ */
+Datum
+lo_create_bytea(PG_FUNCTION_ARGS)
+{
+ Oid loOid = PG_GETARG_OID(0);
+ bytea *str = PG_GETARG_BYTEA_PP(1);
+ LargeObjectDesc *loDesc;
+
+ CreateFSContext();
+
+ loOid = inv_create(loOid);
+ loDesc = inv_open(loOid, INV_WRITE, fscxt);
+ inv_write(loDesc, VARDATA_ANY(str),
+ VARSIZE_ANY_EXHDR(str));
+ inv_close(loDesc);
+
+ PG_RETURN_OID(loOid);
+}
+
+/*
+ * Modify LO object
+ */
+Datum
+lo_put(PG_FUNCTION_ARGS)
+{
+ Oid loOid = PG_GETARG_OID(0);
+ int64 offset = PG_GETARG_INT64(1);
+ bytea *str = PG_GETARG_BYTEA_PP(2);
+ LargeObjectDesc *loDesc;
+
+ CreateFSContext();
+
+ loDesc = inv_open(loOid, INV_WRITE, fscxt);
+ inv_seek(loDesc, offset, SEEK_SET);
+ inv_write(loDesc, VARDATA_ANY(str),
+ VARSIZE_ANY_EXHDR(str));
+ inv_close(loDesc);
+
+ PG_RETURN_VOID();
+}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 08586ae..ca4fc62 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1055,6 +1055,15 @@ DESCR("truncate large object");
DATA(insert OID = 3172 ( lo_truncate64 PGNSP PGUID 12 1 0 0 0 f f f f t f v 2 0 23 "23 20" _null_ _null_ _null_ _null_ lo_truncate64 _null_ _null_ _null_ ));
DESCR("truncate large object (64 bit)");
+DATA(insert OID = 3457 ( lo_create PGNSP PGUID 12 1 0 0 0 f f f f t f v 2 0 26 "26 17" _null_ _null_ _null_ _null_ lo_create_bytea _null_ _null_ _null_ ));
+DESCR("create new large object with content");
+DATA(insert OID = 3458 ( lo_get PGNSP PGUID 12 1 0 0 0 f f f f t f v 1 0 17 "26" _null_ _null_ _null_ _null_ lo_get _null_ _null_ _null_ ));
+DESCR("read entire large object");
+DATA(insert OID = 3459 ( lo_get PGNSP PGUID 12 1 0 0 0 f f f f t f v 3 0 17 "26 20 23" _null_ _null_ _null_ _null_ lo_get_fragment _null_ _null_ _null_ ));
+DESCR("read large object from offset for length");
+DATA(insert OID = 3460 ( lo_put PGNSP PGUID 12 1 0 0 0 f f f f t f v 3 0 2278 "26 20 17" _null_ _null_ _null_ _null_ lo_put _null_ _null_ _null_ ));
+DESCR("write data at offset");
+
DATA(insert OID = 959 ( on_pl PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 16 "600 628" _null_ _null_ _null_ _null_ on_pl _null_ _null_ _null_ ));
DATA(insert OID = 960 ( on_sl PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 16 "601 628" _null_ _null_ _null_ _null_ on_sl _null_ _null_ _null_ ));
DATA(insert OID = 961 ( close_pl PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 600 "600 628" _null_ _null_ _null_ _null_ close_pl _null_ _null_ _null_ ));
diff --git a/src/include/libpq/be-fsstubs.h b/src/include/libpq/be-fsstubs.h
index a2b803a..50b9190 100644
--- a/src/include/libpq/be-fsstubs.h
+++ b/src/include/libpq/be-fsstubs.h
@@ -25,6 +25,7 @@ extern Datum lo_export(PG_FUNCTION_ARGS);
extern Datum lo_creat(PG_FUNCTION_ARGS);
extern Datum lo_create(PG_FUNCTION_ARGS);
+extern Datum lo_create_bytea(PG_FUNCTION_ARGS);
extern Datum lo_open(PG_FUNCTION_ARGS);
extern Datum lo_close(PG_FUNCTION_ARGS);
@@ -32,6 +33,10 @@ extern Datum lo_close(PG_FUNCTION_ARGS);
extern Datum loread(PG_FUNCTION_ARGS);
extern Datum lowrite(PG_FUNCTION_ARGS);
+extern Datum lo_get(PG_FUNCTION_ARGS);
+extern Datum lo_get_fragment(PG_FUNCTION_ARGS);
+extern Datum lo_put(PG_FUNCTION_ARGS);
+
extern Datum lo_lseek(PG_FUNCTION_ARGS);
extern Datum lo_tell(PG_FUNCTION_ARGS);
extern Datum lo_lseek64(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/input/largeobject.source b/src/test/regress/input/largeobject.source
index f0ea7a2..944b623 100644
--- a/src/test/regress/input/largeobject.source
+++ b/src/test/regress/input/largeobject.source
@@ -203,5 +203,22 @@ SELECT pageno, data FROM pg_largeobject WHERE loid = :newloid;
SELECT lo_unlink(loid) FROM lotest_stash_values;
\lo_unlink :newloid
+\lo_import 'results/lotest.txt'
+
+\set newloid_1 :LASTOID
+
+SELECT lo_create(0, lo_get(:newloid_1)) AS newloid_2
+\gset
+
+SELECT md5(lo_get(:newloid_1)) = md5(lo_get(:newloid_2));
+
+SELECT lo_get(:newloid_1, 0, 20);
+SELECT lo_get(:newloid_1, 10, 20);
+SELECT lo_put(:newloid_1, 5, decode('afafafaf', 'hex'));
+SELECT lo_get(:newloid_1, 0, 20);
+
+\lo_unlink :newloid_1
+\lo_unlink :newloid_2
+
TRUNCATE lotest_stash_values;
DROP ROLE regresslo;
diff --git a/src/test/regress/output/largeobject.source b/src/test/regress/output/largeobject.source
index a25ac2a..9bdf75c 100644
--- a/src/test/regress/output/largeobject.source
+++ b/src/test/regress/output/largeobject.source
@@ -391,5 +391,41 @@ SELECT lo_unlink(loid) FROM lotest_stash_values;
(1 row)
\lo_unlink :newloid
+\lo_import 'results/lotest.txt'
+\set newloid_1 :LASTOID
+SELECT lo_create(0, lo_get(:newloid_1)) AS newloid_2
+\gset
+SELECT md5(lo_get(:newloid_1)) = md5(lo_get(:newloid_2));
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT lo_get(:newloid_1, 0, 20);
+ lo_get
+-------------------------------------------
+ 8800\0110\0110\0110\0110\0110\0110\011800
+(1 row)
+
+SELECT lo_get(:newloid_1, 10, 20);
+ lo_get
+-------------------------------------------
+ \0110\0110\0110\011800\011800\0113800\011
+(1 row)
+
+SELECT lo_put(:newloid_1, 5, decode('afafafaf', 'hex'));
+ lo_put
+--------
+
+(1 row)
+
+SELECT lo_get(:newloid_1, 0, 20);
+ lo_get
+-------------------------------------------------
+ 8800\011\257\257\257\2570\0110\0110\0110\011800
+(1 row)
+
+\lo_unlink :newloid_1
+\lo_unlink :newloid_2
TRUNCATE lotest_stash_values;
DROP ROLE regresslo;
diff --git a/src/test/regress/output/largeobject_1.source b/src/test/regress/output/largeobject_1.source
index bae74f6..b02489f 100644
--- a/src/test/regress/output/largeobject_1.source
+++ b/src/test/regress/output/largeobject_1.source
@@ -391,5 +391,41 @@ SELECT lo_unlink(loid) FROM lotest_stash_values;
(1 row)
\lo_unlink :newloid
+\lo_import 'results/lotest.txt'
+\set newloid_1 :LASTOID
+SELECT lo_create(0, lo_get(:newloid_1)) AS newloid_2
+\gset
+SELECT md5(lo_get(:newloid_1)) = md5(lo_get(:newloid_2));
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT lo_get(:newloid_1, 0, 20);
+ lo_get
+-------------------------------------------
+ 8800\0110\0110\0110\0110\0110\0110\011800
+(1 row)
+
+SELECT lo_get(:newloid_1, 10, 20);
+ lo_get
+-------------------------------------------
+ \0110\0110\0110\011800\011800\0113800\011
+(1 row)
+
+SELECT lo_put(:newloid_1, 5, decode('afafafaf', 'hex'));
+ lo_put
+--------
+
+(1 row)
+
+SELECT lo_get(:newloid_1, 0, 20);
+ lo_get
+-------------------------------------------------
+ 8800\011\257\257\257\2570\0110\0110\0110\011800
+(1 row)
+
+\lo_unlink :newloid_1
+\lo_unlink :newloid_2
TRUNCATE lotest_stash_values;
DROP ROLE regresslo;
On Fri, Oct 25, 2013 at 03:35:05PM +0200, Pavel Stehule wrote:
2013/10/24 Heikki Linnakangas <hlinnakangas@vmware.com>
On 22.10.2013 13:55, Pavel Stehule wrote:
2013/10/21 Noah Misch<noah@leadboat.com>
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
Attachments:
load_lo_v5.patchtext/plain; charset=us-asciiDownload
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');
<para>
See also the aggregate function <function>string_agg</function> in
- <xref linkend="functions-aggregate">.
+ <xref linkend="functions-aggregate"> and the large object functions
+ in <xref linkend="lo-funcs">.
</para>
</sect1>
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);
<title>Server-side Functions</title>
<para>
- 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 <xref linkend="lo-funcs-table">.
+ </para>
+
+ <table id="lo-funcs-table">
+ <title>SQL-oriented Large Object Functions</title>
+ <tgroup cols="5">
+ <thead>
+ <row>
+ <entry>Function</entry>
+ <entry>Return Type</entry>
+ <entry>Description</entry>
+ <entry>Example</entry>
+ <entry>Result</entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry>
+ <indexterm>
+ <primary>lo_create</primary>
+ </indexterm>
+ <literal><function>lo_create(<parameter>loid</parameter> <type>oid</type>, <parameter>string</parameter> <type>bytea</type>)</function></literal>
+ </entry>
+ <entry><type>oid</type></entry>
+ <entry>
+ Create a large object and store data there, returning its OID.
+ Pass <literal>0</> to have the system choose an OID.
+ </entry>
+ <entry><literal>lo_create(0, E'\\xffffff00')</literal></entry>
+ <entry><literal>24528</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>lo_put</primary>
+ </indexterm>
+ <literal><function>lo_put(<parameter>loid</parameter> <type>oid</type>, <parameter>offset</parameter> <type>bigint</type>, <parameter>str</parameter> <type>bytea</type>)</function></literal>
+ </entry>
+ <entry><type>void</type></entry>
+ <entry>
+ Write data at the given offset.
+ </entry>
+ <entry><literal>lo_put(24528, 1, E'\\xaa')</literal></entry>
+ <entry></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>lo_get</primary>
+ </indexterm>
+ <literal><function>lo_get(<parameter>loid</parameter> <type>oid</type> <optional>, <parameter>from</parameter> <type>bigint</type>, <parameter>for</parameter> <type>int</type></optional>)</function></literal>
+ </entry>
+ <entry><type>bytea</type></entry>
+ <entry>
+ Extract contents or a substring thereof.
+ </entry>
+ <entry><literal>lo_get(24528, 0, 3)</literal></entry>
+ <entry><literal>\xffaaff</literal></entry>
+ </row>
+
+ </tbody>
+ </tgroup>
+ </table>
+
+ <para>
+ 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
<function>lo_creat</function><indexterm><primary>lo_creat</></>,
<function>lo_create</function><indexterm><primary>lo_create</></>,
<function>lo_unlink</function><indexterm><primary>lo_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,
+ loDesc->snapshot) != ACLCHECK_OK)
+ ereport(ERROR,
+ (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
+ errmsg("permission denied for large object %u",
+ loDesc->id)));
+
+ /*
+ * Compute number of bytes we'll actually read, accommodating nbytes == -1
+ * and reads beyond the end of the LO.
+ */
+ loSize = inv_seek(loDesc, 0, SEEK_END);
+ if (loSize > offset)
+ {
+ if (nbytes >= 0 && nbytes <= loSize - offset)
+ result_length = nbytes; /* request is wholly inside LO */
+ else
+ result_length = loSize - offset; /* adjust to end of LO */
+ }
+ else
+ result_length = 0; /* request is wholly outside LO */
+
+ /*
+ * A result_length calculated from loSize may not fit in a size_t. Check
+ * that the size will satisfy this and subsequently-enforced size limits.
+ */
+ if (result_length > MaxAllocSize - VARHDRSZ)
+ ereport(ERROR,
+ (errcode(ERRCODE_PROGRAM_LIMIT_EXCEEDED),
+ errmsg("large object read request is too large")));
+
+ 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);
+ SET_VARSIZE(result, result_length + VARHDRSZ);
+
+ inv_close(loDesc);
+
+ return result;
+}
+
+/*
+ * Read entire LO
+ */
+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);
+}
+
+/*
+ * Read range within LO
+ */
+Datum
+lo_get_fragment(PG_FUNCTION_ARGS)
+{
+ Oid loOid = PG_GETARG_OID(0);
+ int64 offset = PG_GETARG_INT64(1);
+ int32 nbytes = PG_GETARG_INT32(2);
+ bytea *result;
+
+ if (nbytes < 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg("requested length cannot be negative")));
+
+ result = lo_get_fragment_internal(loOid, offset, nbytes);
+
+ PG_RETURN_BYTEA_P(result);
+}
+
+/*
+ * Create LO with initial contents
+ */
+Datum
+lo_create_bytea(PG_FUNCTION_ARGS)
+{
+ Oid loOid = PG_GETARG_OID(0);
+ bytea *str = PG_GETARG_BYTEA_PP(1);
+ LargeObjectDesc *loDesc;
+
+ CreateFSContext();
+
+ loOid = inv_create(loOid);
+ loDesc = inv_open(loOid, INV_WRITE, fscxt);
+ inv_write(loDesc, VARDATA_ANY(str), VARSIZE_ANY_EXHDR(str));
+ inv_close(loDesc);
+
+ PG_RETURN_OID(loOid);
+}
+
+/*
+ * Update range within LO
+ */
+Datum
+lo_put(PG_FUNCTION_ARGS)
+{
+ Oid loOid = PG_GETARG_OID(0);
+ int64 offset = PG_GETARG_INT64(1);
+ bytea *str = PG_GETARG_BYTEA_PP(2);
+ LargeObjectDesc *loDesc;
+ int written;
+
+ CreateFSContext();
+
+ loDesc = inv_open(loOid, INV_WRITE, fscxt);
+ inv_seek(loDesc, offset, SEEK_SET);
+ written = inv_write(loDesc, VARDATA_ANY(str), VARSIZE_ANY_EXHDR(str));
+ Assert(written == VARSIZE_ANY_EXHDR(str));
+ inv_close(loDesc);
+
+ PG_RETURN_VOID();
+}
diff --git a/src/include/catalog/pg_proc.h b/src/include/catalog/pg_proc.h
index 08586ae..ca4fc62 100644
--- a/src/include/catalog/pg_proc.h
+++ b/src/include/catalog/pg_proc.h
@@ -1055,6 +1055,15 @@ DESCR("truncate large object");
DATA(insert OID = 3172 ( lo_truncate64 PGNSP PGUID 12 1 0 0 0 f f f f t f v 2 0 23 "23 20" _null_ _null_ _null_ _null_ lo_truncate64 _null_ _null_ _null_ ));
DESCR("truncate large object (64 bit)");
+DATA(insert OID = 3457 ( lo_create PGNSP PGUID 12 1 0 0 0 f f f f t f v 2 0 26 "26 17" _null_ _null_ _null_ _null_ lo_create_bytea _null_ _null_ _null_ ));
+DESCR("create new large object with content");
+DATA(insert OID = 3458 ( lo_get PGNSP PGUID 12 1 0 0 0 f f f f t f v 1 0 17 "26" _null_ _null_ _null_ _null_ lo_get _null_ _null_ _null_ ));
+DESCR("read entire large object");
+DATA(insert OID = 3459 ( lo_get PGNSP PGUID 12 1 0 0 0 f f f f t f v 3 0 17 "26 20 23" _null_ _null_ _null_ _null_ lo_get_fragment _null_ _null_ _null_ ));
+DESCR("read large object from offset for length");
+DATA(insert OID = 3460 ( lo_put PGNSP PGUID 12 1 0 0 0 f f f f t f v 3 0 2278 "26 20 17" _null_ _null_ _null_ _null_ lo_put _null_ _null_ _null_ ));
+DESCR("write data at offset");
+
DATA(insert OID = 959 ( on_pl PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 16 "600 628" _null_ _null_ _null_ _null_ on_pl _null_ _null_ _null_ ));
DATA(insert OID = 960 ( on_sl PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 16 "601 628" _null_ _null_ _null_ _null_ on_sl _null_ _null_ _null_ ));
DATA(insert OID = 961 ( close_pl PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 600 "600 628" _null_ _null_ _null_ _null_ close_pl _null_ _null_ _null_ ));
diff --git a/src/include/libpq/be-fsstubs.h b/src/include/libpq/be-fsstubs.h
index a2b803a..50b9190 100644
--- a/src/include/libpq/be-fsstubs.h
+++ b/src/include/libpq/be-fsstubs.h
@@ -25,6 +25,7 @@ extern Datum lo_export(PG_FUNCTION_ARGS);
extern Datum lo_creat(PG_FUNCTION_ARGS);
extern Datum lo_create(PG_FUNCTION_ARGS);
+extern Datum lo_create_bytea(PG_FUNCTION_ARGS);
extern Datum lo_open(PG_FUNCTION_ARGS);
extern Datum lo_close(PG_FUNCTION_ARGS);
@@ -32,6 +33,10 @@ extern Datum lo_close(PG_FUNCTION_ARGS);
extern Datum loread(PG_FUNCTION_ARGS);
extern Datum lowrite(PG_FUNCTION_ARGS);
+extern Datum lo_get(PG_FUNCTION_ARGS);
+extern Datum lo_get_fragment(PG_FUNCTION_ARGS);
+extern Datum lo_put(PG_FUNCTION_ARGS);
+
extern Datum lo_lseek(PG_FUNCTION_ARGS);
extern Datum lo_tell(PG_FUNCTION_ARGS);
extern Datum lo_lseek64(PG_FUNCTION_ARGS);
diff --git a/src/test/regress/input/largeobject.source b/src/test/regress/input/largeobject.source
index f0ea7a2..996304b 100644
--- a/src/test/regress/input/largeobject.source
+++ b/src/test/regress/input/largeobject.source
@@ -203,5 +203,26 @@ SELECT pageno, data FROM pg_largeobject WHERE loid = :newloid;
SELECT lo_unlink(loid) FROM lotest_stash_values;
\lo_unlink :newloid
+\lo_import 'results/lotest.txt'
+
+\set newloid_1 :LASTOID
+
+SELECT lo_create(0, lo_get(:newloid_1)) AS newloid_2
+\gset
+
+SELECT md5(lo_get(:newloid_1)) = md5(lo_get(:newloid_2));
+
+SELECT lo_get(:newloid_1, 0, 20);
+SELECT lo_get(:newloid_1, 10, 20);
+SELECT lo_put(:newloid_1, 5, decode('afafafaf', 'hex'));
+SELECT lo_get(:newloid_1, 0, 20);
+
+SELECT lo_put(:newloid_1, 4294967310, 'foo');
+SELECT lo_get(:newloid_1);
+SELECT lo_get(:newloid_1, 4294967294, 100);
+
+\lo_unlink :newloid_1
+\lo_unlink :newloid_2
+
TRUNCATE lotest_stash_values;
DROP ROLE regresslo;
diff --git a/src/test/regress/output/largeobject.source b/src/test/regress/output/largeobject.source
index a25ac2a..e6dd97e 100644
--- a/src/test/regress/output/largeobject.source
+++ b/src/test/regress/output/largeobject.source
@@ -391,5 +391,55 @@ SELECT lo_unlink(loid) FROM lotest_stash_values;
(1 row)
\lo_unlink :newloid
+\lo_import 'results/lotest.txt'
+\set newloid_1 :LASTOID
+SELECT lo_create(0, lo_get(:newloid_1)) AS newloid_2
+\gset
+SELECT md5(lo_get(:newloid_1)) = md5(lo_get(:newloid_2));
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT lo_get(:newloid_1, 0, 20);
+ lo_get
+-------------------------------------------
+ 8800\0110\0110\0110\0110\0110\0110\011800
+(1 row)
+
+SELECT lo_get(:newloid_1, 10, 20);
+ lo_get
+-------------------------------------------
+ \0110\0110\0110\011800\011800\0113800\011
+(1 row)
+
+SELECT lo_put(:newloid_1, 5, decode('afafafaf', 'hex'));
+ lo_put
+--------
+
+(1 row)
+
+SELECT lo_get(:newloid_1, 0, 20);
+ lo_get
+-------------------------------------------------
+ 8800\011\257\257\257\2570\0110\0110\0110\011800
+(1 row)
+
+SELECT lo_put(:newloid_1, 4294967310, 'foo');
+ lo_put
+--------
+
+(1 row)
+
+SELECT lo_get(:newloid_1);
+ERROR: large object read request is too large
+SELECT lo_get(:newloid_1, 4294967294, 100);
+ lo_get
+---------------------------------------------------------------------
+ \000\000\000\000\000\000\000\000\000\000\000\000\000\000\000\000foo
+(1 row)
+
+\lo_unlink :newloid_1
+\lo_unlink :newloid_2
TRUNCATE lotest_stash_values;
DROP ROLE regresslo;
diff --git a/src/test/regress/output/largeobject_1.source b/src/test/regress/output/largeobject_1.source
index bae74f6..8665822 100644
--- a/src/test/regress/output/largeobject_1.source
+++ b/src/test/regress/output/largeobject_1.source
@@ -391,5 +391,55 @@ SELECT lo_unlink(loid) FROM lotest_stash_values;
(1 row)
\lo_unlink :newloid
+\lo_import 'results/lotest.txt'
+\set newloid_1 :LASTOID
+SELECT lo_create(0, lo_get(:newloid_1)) AS newloid_2
+\gset
+SELECT md5(lo_get(:newloid_1)) = md5(lo_get(:newloid_2));
+ ?column?
+----------
+ t
+(1 row)
+
+SELECT lo_get(:newloid_1, 0, 20);
+ lo_get
+-------------------------------------------
+ 8800\0110\0110\0110\0110\0110\0110\011800
+(1 row)
+
+SELECT lo_get(:newloid_1, 10, 20);
+ lo_get
+-------------------------------------------
+ \0110\0110\0110\011800\011800\0113800\011
+(1 row)
+
+SELECT lo_put(:newloid_1, 5, decode('afafafaf', 'hex'));
+ lo_put
+--------
+
+(1 row)
+
+SELECT lo_get(:newloid_1, 0, 20);
+ lo_get
+-------------------------------------------------
+ 8800\011\257\257\257\2570\0110\0110\0110\011800
+(1 row)
+
+SELECT lo_put(:newloid_1, 4294967310, 'foo');
+ lo_put
+--------
+
+(1 row)
+
+SELECT lo_get(:newloid_1);
+ERROR: large object read request is too large
+SELECT lo_get(:newloid_1, 4294967294, 100);
+ lo_get
+---------------------------------------------------------------------
+ \000\000\000\000\000\000\000\000\000\000\000\000\000\000\000\000foo
+(1 row)
+
+\lo_unlink :newloid_1
+\lo_unlink :newloid_2
TRUNCATE lotest_stash_values;
DROP ROLE regresslo;
2013/10/26 Noah Misch <noah@leadboat.com>
On Fri, Oct 25, 2013 at 03:35:05PM +0200, Pavel Stehule wrote:
2013/10/24 Heikki Linnakangas <hlinnakangas@vmware.com>
On 22.10.2013 13:55, Pavel Stehule wrote:
2013/10/21 Noah Misch<noah@leadboat.com>
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
Show quoted text
--
Noah Misch
EnterpriseDB http://www.enterprisedb.com