Re: Selecting Large Object and TOAST

hi,

We are storing the Icons/IMages in the database as Large Objects using
lo_import functions.

(1) what would be the return type if i want to return a large object (
XYZ.gif) to the remote client (GUI) using stored procedure.
Can anyone give an example please?
Are there any size limitations i need to consider when returning Large
Object using procedures?

(2) A statement from documentation:
"PostgreSQL 7.1 introduced a mechanism (nicknamed "TOAST") that allows data
values to be much larger than single pages. This makes the large object
facility partially obsolete."
How do i TOAST my data stored as Large Object?

thanks,
vish

On 12/4/2005 7:55 PM, vishal saberwal wrote:

> hi,
>
> We are storing the Icons/IMages in the database as Large Objects using
> lo_import functions.
>
> (1) what would be the return type if i want to return a large object (
> XYZ.gif) to the remote client (GUI) using stored procedure.
> Can anyone give an example please?
> Are there any size limitations i need to consider when returning Large
> Object using procedures?
>
> (2) A statement from documentation:
> "PostgreSQL 7.1 introduced a mechanism (nicknamed "TOAST") that allows data
> values to be much larger than single pages. This makes the large object
> facility partially obsolete."
> How do i TOAST my data stored as Large Object?

You don't. You would change you schema and application to store the
images in bytea columns instead.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

>>
>> (1) what would be the return type if i want to return a large object (
>> XYZ.gif) to the remote client (GUI) using stored procedure.
>> Can anyone give an example please?
>> Are there any size limitations i need to consider when returning Large
>> Object using procedures?
You have to use a lookup table that correlates the meta information
(filename, content-type)
with a particular loid. That way you can store any binary you want.

>>
>> How do i TOAST my data stored as Large Object?
This isn't a concern as it is all internal and automatic.

> You don't. You would change you schema and application to store the
> images in bytea columns instead.
>
Well I have to disagree with this. It entirely depends on the size of
the data you are storing. Bytea is remarkably
innefficient.

Joshua D. Drake

>
> Jan
>

On 12/4/2005 9:24 PM, Joshua D. Drake wrote:

>>>
>>> (1) what would be the return type if i want to return a large object (
>>> XYZ.gif) to the remote client (GUI) using stored procedure.
>>> Can anyone give an example please?
>>> Are there any size limitations i need to consider when returning Large
>>> Object using procedures?
> You have to use a lookup table that correlates the meta information
> (filename, content-type)
> with a particular loid. That way you can store any binary you want.

This doesn't answer the question.

Fact is that most procedural languages (including PL/pgSQL) don't have
any access to classic large objects in the first place. So all the
stored procedure can do is to return the identifier of the large object
to the client and the client must then use lo_open(), lo_read() etc. to
actually get the data of the object. Not all client interfaces support
these fastpath based libpq functions.

>
>>>
>>> How do i TOAST my data stored as Large Object?
> This isn't a concern as it is all internal and automatic.
>
>> You don't. You would change you schema and application to store the
>> images in bytea columns instead.
>>
> Well I have to disagree with this. It entirely depends on the size of
> the data you are storing. Bytea is remarkably
> innefficient.

Which would be the data type of your choice for images?

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

>
> This doesn't answer the question.
>
> Fact is that most procedural languages (including PL/pgSQL) don't have
> any access to classic large objects in the first place. So all the
> stored procedure can do is to return the identifier of the large
> object to the client and the client must then use lo_open(), lo_read()
> etc. to actually get the data of the object. Not all client interfaces
> support these fastpath based libpq functions.
>

You are correct, I missed the part about wanting to return from a stored
procedure.

>> Well I have to disagree with this. It entirely depends on the size of
>> the data you are storing. Bytea is remarkably
>> innefficient.
>
> Which would be the data type of your choice for images?
Well as I said it depends on the size of the data. Are we talking 100
meg vector images? Then large objects. Are we talking thumbnails that
are 32k then bytea.

Joshua D. Drake

>
>
> Jan
>

On 12/4/2005 11:45 PM, Joshua D. Drake wrote:

> Well as I said it depends on the size of the data. Are we talking 100
> meg vector images? Then large objects. Are we talking thumbnails that
> are 32k then bytea.

I'd say that anything up to a megabyte or so can easily live in bytea.
Beyond that it depends on the access pattern.

That said, for certain situations I think some sql-callable functions
would be very handy:

lo_get(oid) returns bytea
lo_set(oid, bytea) returns void
lo_ins(bytea) returns oid
lo_del(oid) returns void

Those (and maybe some more) would allow access of traditional large
objects through client interfaces that don't support the regular large
object calls.

Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #

thanks for all your responses,
really appreciate it,

I am sorry but I am not as familiar with this as much as you are.

So are you suggesting, I need to send the Large object ID to the client?
Since the application is time critical, is there a way to skip one of the
two steps (querying once for LOID and then again for its data) to a one step
by sending the Object data in the first call?

Are there any examples or pseudocode someone who's implemented using
largeobjects to retrieve images to be shown as icons/images in web pages
(using Stored procedures).

http://www.postgresql.org/docs/8.1/interactive/lo-examplesect.html
Is the above site's ExportFile() something that can help me (but it creates
a file, different from what i want)?

As far as the size is concerned, i am testing with image resource, but we
plan to store video clips too. Though we are planning to store thumbnails in
Large Objects too.

>> lo_get(oid) returns bytea
>> lo_set(oid, bytea) returns void
>> lo_ins(bytea) returns oid
>> lo_del(oid) returns void
How do you suggest i create lo_get(oid) function returning bytea data.

I have understood that TOAST is internal implementation, thanks for the
info.

thanks,
vish

On 12/4/05, Jan Wieck <JanWieck(at)yahoo(dot)com> wrote:
>
> On 12/4/2005 11:45 PM, Joshua D. Drake wrote:
>
> > Well as I said it depends on the size of the data. Are we talking 100
> > meg vector images? Then large objects. Are we talking thumbnails that
> > are 32k then bytea.
>
> I'd say that anything up to a megabyte or so can easily live in bytea.
> Beyond that it depends on the access pattern.
>
> That said, for certain situations I think some sql-callable functions
> would be very handy:
>
> lo_get(oid) returns bytea
> lo_set(oid, bytea) returns void
> lo_ins(bytea) returns oid
> lo_del(oid) returns void
>
> Those (and maybe some more) would allow access of traditional large
> objects through client interfaces that don't support the regular large
> object calls.
>
>
> Jan
>
> --
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me. #
> #================================================== JanWieck(at)Yahoo(dot)com #
>

So are you suggesting, I need to send the Large object ID to the client?
Since the application is time critical, is there a way to skip one of the
two steps (querying once for LOID and then again for its data) to a one step
by sending the Object data in the first call?

Are there any examples or pseudocode someone who's implemented using
largeobjects to retrieve images to be shown as icons/images in web pages
(using Stored procedures).

http://www.postgresql.org/docs/8.1/interactive/lo-examplesect.html
Is the above site's ExportFile() something that can help me (but it creates
a file, different from what i want)?

As far as the size is concerned, i am testing with image resource, but we
plan to store video clips too. Though we are planning to store thumbnails in
Large Objects too.

>> lo_get(oid) returns bytea
>> lo_set(oid, bytea) returns void
>> lo_ins(bytea) returns oid
>> lo_del(oid) returns void
How do you suggest i create lo_get(oid) function returning bytea data.

thanks,
vish

On 12/5/05, vishal saberwal <vishalsaberwal(at)gmail(dot)com> wrote:
>
> thanks for all your responses,
> really appreciate it,
>
> I am sorry but I am not as familiar with this as much as you are.
>
> So are you suggesting, I need to send the Large object ID to the client?
> Since the application is time critical, is there a way to skip one of the
> two steps (querying once for LOID and then again for its data) to a one step
> by sending the Object data in the first call?
>
> Are there any examples or pseudocode someone who's implemented using
> largeobjects to retrieve images to be shown as icons/images in web pages
> (using Stored procedures).
>
> http://www.postgresql.org/docs/8.1/interactive/lo-examplesect.html
> Is the above site's ExportFile() something that can help me (but it
> creates a file, different from what i want)?
>
> As far as the size is concerned, i am testing with image resource, but we
> plan to store video clips too. Though we are planning to store thumbnails in
> Large Objects too.
>
> >> lo_get(oid) returns bytea
> >> lo_set(oid, bytea) returns void
> >> lo_ins(bytea) returns oid
> >> lo_del(oid) returns void
> How do you suggest i create lo_get(oid) function returning bytea data.
>
> I have understood that TOAST is internal implementation, thanks for the
> info.
>
> thanks,
> vish
>
> On 12/4/05, Jan Wieck <JanWieck(at)yahoo(dot)com> wrote:
> >
> > On 12/4/2005 11:45 PM, Joshua D. Drake wrote:
> >
> > > Well as I said it depends on the size of the data. Are we talking 100
> > > meg vector images? Then large objects. Are we talking thumbnails that
> > > are 32k then bytea.
> >
> > I'd say that anything up to a megabyte or so can easily live in bytea.
> > Beyond that it depends on the access pattern.
> >
> > That said, for certain situations I think some sql-callable functions
> > would be very handy:
> >
> > lo_get(oid) returns bytea
> > lo_set(oid, bytea) returns void
> > lo_ins(bytea) returns oid
> > lo_del(oid) returns void
> >
> > Those (and maybe some more) would allow access of traditional large
> > objects through client interfaces that don't support the regular large
> > object calls.
> >
> >
> > Jan
> >
> > --
> > #======================================================================#
> > # It's easier to get forgiveness for being wrong than for being right. #
> >
> > # Let's break this rule - forgive me. #
> > #================================================== JanWieck(at)Yahoo(dot)com #
> >
>
>

vishal saberwal <vishalsaberwal(at)gmail(dot)com> writes:

> So are you suggesting, I need to send the Large object ID to the client?
> Since the application is time critical, is there a way to skip one of the
> two steps (querying once for LOID and then again for its data) to a one step
> by sending the Object data in the first call?

The main reason to use large objects at all is if you have a need to read and
write *parts* of the data. For instance if your data is being served up by a
web server then you may want to be able to pipeline the data in chunks instead
of waiting until you can download the entire object from the database. So you
would use loread() to read out one chunk, stuff it into your network buffers,
then loop back and loread() the next chunk.

If all you want to do is store and read the entire object in a single query
then large objects don't really have any advantage for you. Postgres removes
many of the limitations that legacy databases imposed on regular data types
that made large objects necessary for such routine storage.

--
greg