Re: [GENERAL] Selecting Large Object and TOAST
vishal saberwal <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Selecting Large Object and TOAST
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, vishOn 12/5/05, vishal saberwal <[EMAIL PROTECTED] > 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 <[EMAIL PROTECTED] > 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 functionswould be very handy: lo_get(oid) returns bytea lo_set(oid, bytea) returns void lo_ins(bytea) returns oid lo_del(oid) returns voidThose (and maybe some more) would allow access of traditional large objects through client interfaces that don't support the regular largeobject calls.Jan--#==## It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. ##== [EMAIL PROTECTED] #
Re: [GENERAL] Selecting Large Object and TOAST
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 <[EMAIL PROTECTED]> 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 functionswould be very handy: lo_get(oid) returns bytea lo_set(oid, bytea) returns void lo_ins(bytea) returns oid lo_del(oid) returns voidThose (and maybe some more) would allow access of traditional large objects through client interfaces that don't support the regular largeobject calls.Jan--#==## It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. ##== [EMAIL PROTECTED] #
Re: [GENERAL] Selecting Large Object and TOAST
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. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Selecting Large Object and TOAST
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Selecting Large Object and TOAST
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. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Selecting Large Object and TOAST
(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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Selecting Large Object and TOAST
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. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] 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
