Re: BLOB support

On Sun, 05 Jun 2011 22:16:41 +0200, Dimitri Fontaine wrote:
> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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 construct
Blob myWeddingDvd = conn.createBlob(myWeddingStream, size); //A bit
outdated we have BlueRay
conn.prepareStatemnt("INSERT INTO someonetubevideos values (?)")
where 1st parameter is myWeddingDvd,
or if someone doesn't like Java he/she/it may wish to put C++ istream,
or C FILE.

I think (with respect to below consideration), this implicite requires
that LOBs should be stored in one, centralized place doesn't matter if
this will be file system or special table, or something else, but when
statement is processed there is no idea with which table LOB will be
associated, if we want to TOAST, where TOAST it, what will be if
insertion will by 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 create LOB. I think, as
well constructs that insert 1st, small LOB into table to get some driver
depending API are little bit 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 TOAST table, 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