Re: Status of server side Large Object support?

Joe Conway wrote:
> Thomas Hallgren wrote:
>
>> Peter Eisentraut wrote:
>>
>>> Am Sonntag, 28. November 2004 12:33 schrieb Thomas Hallgren:
>>>
>>>> Hmm, ok. But there's no way to stream them in and out from disk. From
>>>> what I can see, you have to bring all of it into memory. Not so ideal
>>>> perhaps if you want to provide streaming media for thousands of users.
>>>
>>>
>>> You can use the substring function to read the pieces you need.
>>>
>> Won't the substring function bring the whole thing into memory in the
>> backend before it pass you the piece you need? Let's assume I want to
>> stream 4k at a time of a 40mb image, that's a whole lot of byte
>> swapping if that's the case.
>
>
> Not if the column is storage type EXTERNAL. See a past discussion here:
> http://archives.postgresql.org/pgsql-general/2003-07/msg01447.php
>
>
>> How do you handle writes without first creating the whole image in
>> memory?
>
>
> You can't currently, but it would be a nice addition ;-)
>
> I agree with Peter -- I think effort is better spent improving bytea.
>
> BTW, someone on GENERAL just started a very similar thread
> ("implementing a lo_truncate operation, and possibly a lo_size"). I've
> cc'd him here.
>
Seems to me both solutions have things (and people) speaking for them.

My goal is to provide a handle to a large amount of data. In Java, this
is a java.sql.Blob/Clob and it maps to an SQL locator. This object
supports random access so you can do the equivalent of seek, tell, read,
write, and truncate. A Blob/Clob object must be valid for the duration
of a transaction.

Mapping this object to a LargeObjectDesc and get full functionality
seems to be fairly easy. The only thing missing is the "truncate" since
a "size" function can use seek with SEEK_END on a temporary descriptor copy.

Another fairly significant advantage using large objects is that the
client JDBC driver is using it. I'd like to keep my driver as similar as
possible.

Mapping a Blob to a bytea appears to be much more difficult and the
result is more limited (half the size).

I understand from Peter and Joe that they consider bytea to be the
future. One thing to really consider for the future is the current 1 GB
limit. I'd like to see a future where bytea remains more or less as it
is today, i.e. optimal for sizes 1 GB and less, and LargeObjectDesc is
rewritten to use 64 bit quantities for seek, size, and tell, thus
allowing for much larger objects to be stored.

What is the quality of the large object solution today. Does it have
known flaws that nobody cares about since it's discontinued or is it
considered a maintained and worthy part of the overall solution?

Regards,
Thomas Hallgren