Thread: Handling Blobs with libpq
What is the best approach to handle blobs with libpq ? I've read in documentation that there are 2 ways: 1- Bytea 2- Larges objects (lo_import) My main need is storing pictures (like Jpegs etc) or small blobs. Doc says that (2) is more appropriate but I need to work with blobs in memory, not from files. Thanks for your help. ------------------------------------------------------------------------ --- Stephane ------------------------------------------------------------------------ ---
On Thu, Mar 20, 2003 at 10:56:44AM +0100, Stéphane Pinel wrote: > > 2- Larges objects (lo_import) > > My main need is storing pictures (like Jpegs etc) or small blobs. Doc > says that (2) is more > appropriate but I need to work with blobs in memory, not from files. You don't _have_ to import them from files--lo_import is just a convenience function for reading a file into memory and writing it to a large object. Just use the other large object functions: lo_open(), lo_creat(), lo_read(), lo_write(), lo_seek(). Jeroen
On Thu, 20 Mar 2003, Stéphane Pinel wrote: > What is the best approach to handle blobs with libpq ? > > I've read in documentation that there are 2 ways: > > 1- Bytea > 2- Larges objects (lo_import) > > My main need is storing pictures (like Jpegs etc) or small blobs. Doc > says that (2) is more > appropriate but I need to work with blobs in memory, not from files. > FWIW, from some tests I ran with PHP bytea is slower and can be several times slower. Depending on the data the escaping can increase the data size for transmission up to 4 times. (Can that be right? I'm sure I saw a factor of 4 but can't find the graph now.) That is obviously going to slow the query transmission/result reception and also cause the backend some work. Indeed, my tests showed simple retrieving of data files stored as bytea to be slower than doing the same thing with lo_s with the degree of slow down proportional to the increase in data size caused by the escaping. I'm not sure how much of this is down to the escaping/unescaping operations specifically but that is irrelevent because they're part of the operation to get what's wanted and what lo_s deliver naturally. Also, someone recently pointed out that even with bytea if the client encoding is different when selecting than it was when storing there is still a chance that your data will be come out different to how it should. -- Nigel J. Andrews
Nigel J. Andrews wrote: > Also, someone recently pointed out that even with bytea if the client encoding > is different when selecting than it was when storing there is still a chance > that your data will be come out different to how it should. > If you are using libpq in your own C program, one way around these issues (for retrieval anyway) is to use a binary cursor. Joe
On Thursday 20 March 2003 01:56, Stéphane Pinel wrote: > My main need is storing pictures (like Jpegs etc) or small blobs. Doc > says that (2) is more > appropriate but I need to work with blobs in memory, not from files. If you need to work with it in memory, you're better off using the bytea (same thing as a varbinary). LOBs are generally for when the data is so large that the performance of your application would be comprimised if you allocated memory for the contents. LOBs allow you to do streaming so that you don't need to allocate memory. Only the lo_export/lo_import functions require a file. All of the other LOB functions only require a stream that's chunked into byte arrays.