Re: BLOB support - Mailing list pgsql-hackers

On Sun, 05 Jun 2011 22:16:41 +0200, Dimitri Fontaine wrote:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> Yes.  I think the appropriate problem statement is "provide 
>> streaming
>> access to large field values, as an alternative to just 
>> fetching/storing
>> the entire value at once".  I see no good reason to import the 
>> entire
>> messy notion of LOBS/CLOBS.  (The fact that other databases have 
>> done it
>> is not a good reason.)
>
> Spent some time in the archive to confirm a certain “déjà vu”
> impression.  Couldn't find it.  Had to manually search in closed 
> commit
> fests… but here we are, I think:
>
>   https://commitfest.postgresql.org/action/patch_view?id=70
>   
> http://archives.postgresql.org/message-id/17891.1246301879@sss.pgh.pa.us
>   
> http://archives.postgresql.org/message-id/4A4BF87E.7010107@ak.jp.nec.com
>
> Regards,
I think more about this with contrast to sent references, but I still have in my mind constructBlob myWeddingDvd =
conn.createBlob(myWeddingStream,size); //A bit outdated we have BlueRayconn.prepareStatemnt("INSERT INTO
someonetubevideosvalues (?)")where 1st parameter is myWeddingDvd,or if someone doesn't like Java he/she/it may wish to
putC++ istream, or C FILE.
 
I think (with respect to below consideration), this implicite requires that LOBs should be stored in one, centralized
placedoesn't matter if this will be file system or special table, or something else, but when statement is processed
thereis no idea with which table LOB will be associated, if we want to TOAST, where TOAST it, what will be if insertion
willby SQL function, which choose table depending on BLOB content?
 
Quite interesting idea from cited patch was about string identifying LOB, but with above it close road to for JDBC
createLOB. I think, as well constructs that insert 1st, small LOB into table to get some driver depending API are
littlebit old fashioned.
 
Possible solutions, if we don't want centralized storage, may be:1. Keep BLOB in memory, but this may, depending on
implementation,reduce size of initial BLOB.2. Temporally backup blob in file, then when values are stored copy file to
TOASTtable, but still some changes are required to support LOBs for complex types and arrays.
 
So please give some ideas how to resolve this, or may be it has low priority?
Regards,Radek