Thread

1. BLOBs

   Thomas Swan <tswan@olemiss.edu> — 2001-06-05T21:31:05Z

   I know that BLOBs are on the TODO list, but I had an idea.
   
   I think the storage of a BLOB outside of the table is an elegant 
   solution and keeps table sizes down without the bloat of the stored 
   object.   Granted, if you are searching with a regular expression or 
   using like or ilike clauses, you're likely to be a little slower but it 
   shouldn't be by much.  More than likely, you won't be searching for 
   patterns in the BLOB but rather the fields in the table associated with 
   the BLOB.
   
   Wouldn't it be wonderful if you used the methods you had already 
   implemented and instead create a behavoir similar to the following.
   
   on an insert
       take the data that was to be the blob...
       create your externally "to be referenced" file
       save the data to the file
       store the reference to that file
   
   on an update
       take the data that was to be the blob...
       create your externally "to be referenced" file
       save the data to the file
       store the reference to that file
       delete the old referenced file
   
   on a delete
       delete the reference to your file
       delete the external file
   
   I was thinking that the BLOB column type might be a trigger for a macro 
   that could handle the lo_import, lo_export juggling...
   
   I know it seems overly simplified, but having fought with MySQL and then 
   trying to wrestle with postgresql and importing,exporting BLOBs, it 
   seemed there might be a little more room for discussion, although I 
   doubt this may have added anything to it...
   
   I'd love to see something done with BLOB support during 7.2.x *hint* :)
   
   Besides, if someone could give me some pointers as to where I might be 
   able to start, I might try to contribute something myself.
   
   Thomas
   
   
   
   

2. Re: BLOBs

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-06-05T22:58:22Z

   Thomas Swan <tswan@olemiss.edu> writes:
   > I know that BLOBs are on the TODO list, but I had an idea.
   
   I think you just rediscovered TOAST.
   
   			regards, tom lane
   
   

3. Re: BLOBs

   Bruce Momjian <pgman@candle.pha.pa.us> — 2001-06-11T03:15:42Z

   > Thomas Swan <tswan@olemiss.edu> writes:
   > > I know that BLOBs are on the TODO list, but I had an idea.
   > 
   > I think you just rediscovered TOAST.
   
   We have TOAST and people want to keep large objects for performance.  I
   think we could us an API that allows TOAST binary access and large
   object access using the same API, and hopefully an improved one.
   
   -- 
     Bruce Momjian                        |  http://candle.pha.pa.us
     pgman@candle.pha.pa.us               |  (610) 853-3000
     +  If your life is a hard drive,     |  830 Blythe Avenue
     +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026
   
   

4. Re: BLOBs

   Thomas Swan <tswan@olemiss.edu> — 2001-06-12T22:19:46Z

   Bruce Momjian wrote:
   
   >>Thomas Swan <tswan@olemiss.edu> writes:
   >>
   >>>I know that BLOBs are on the TODO list, but I had an idea.
   >>>
   >>I think you just rediscovered TOAST.
   >>
   >
   >We have TOAST and people want to keep large objects for performance.  I
   >think we could us an API that allows TOAST binary access and large
   >object access using the same API, and hopefully an improved one.
   >
   I think I missed what I was trying to say in my original statement.  I 
   think there's a way to use the existing API with performance benefits 
   left intact.
   
   Take for example the table :
   create table foo {
       foo_id serial,
       foo_name varchar(32),
       foo_object BLOB,
   );
   
   On the insert statement "insert into foo (foo_name,foo_object) values 
   ('My Object','{some escaped arbitrary string of binary data}');", flush 
   the {some escaped arbitrary string of binary data} to disk as a 
   temporary file.  Then do the lo_import operation transparent to the user.
   
   On a select, do the same thing (transparently) and return the data back 
   to user.
   
   Personally, I like LO's being stored separately from the actual table.
   
   
   
   

5. Re: BLOBs

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-06-12T23:02:09Z

   Thomas Swan <tswan@olemiss.edu> writes:
   > I think I missed what I was trying to say in my original statement.  I 
   > think there's a way to use the existing API with performance benefits 
   > left intact.
   
   > Take for example the table :
   > create table foo {
   >     foo_id serial,
   >     foo_name varchar(32),
   >     foo_object BLOB,
   > );
   
   > On the insert statement "insert into foo (foo_name,foo_object) values 
   > ('My Object','{some escaped arbitrary string of binary data}');", flush 
   > the {some escaped arbitrary string of binary data} to disk as a 
   > temporary file.  Then do the lo_import operation transparent to the user.
   
   > On a select, do the same thing (transparently) and return the data back 
   > to user.
   
   > Personally, I like LO's being stored separately from the actual table.
   
   I still think you've rediscovered TOAST.  How is this better than (or
   even significantly different from) foo_object being a toastable bytea
   column?
   
   			regards, tom lane