Re: BLOB support - Mailing list pgsql-hackers

Dne 2.6.2011 15:49, Pavel Stehule napsal(a):
> 2011/6/2 Pavel Golub <pavel@microolap.com>:
>> Hello, Pavel.
>>
>> You wrote:
>>
>> PS> 2011/6/2 Peter Eisentraut <peter_e@gmx.net>:
>>>> On ons, 2011-06-01 at 22:00 +0200, Radosław Smogura wrote:
>>>>> I partialy implemented following missing LOBs types. Requirement for this was
>>>>> to give ability to create (B/C)LOB columns and add casting functionality e.g.
>>>>> SET my_clob = 'My long text'.
>>>>>
>>>>> Idea is as follow:
>>>>> 0. Blob is two state object: 1st in memory contains just bytea, serialized
>>>>> contains Oid of large object.
>>>>> 1. Each type has additional boolean haslobs, which is set recursivly.
>>>>> 2. Relation has same bool haslobs (used to speed up tables without LOBs)
>>>>> 3. When data are inserted/updated then "special" function is called and tuple
>>>>> is modified in this way all LOBs are serialized to (old) LOB table and just
>>>>> Oid is stored.
>>>>> 4. When removed LOB is removed from (old) LOB table.
>>>>
>>>> Superficially, this looks like a reimplementation of TOAST.  What
>>>> functionality exactly do you envision that the BLOB and CLOB types would
>>>> need to have that would warrant treating them different from, say, bytea
>>>> and text?
>>>>
>>
>> PS> a streaming for bytea could be nice. A very large bytea are limited by
>> PS> query size - processing long query needs too RAM,
>>
>> LO (oid) solves this, doesn't it?
> 
> partially
> 
> There is a few disadvantages LO against bytea, so there are requests
> for "smarter" API for bytea.
> 
> Significant problem is different implementation of LO for people who
> have to port application to PostgreSQL from Oracle, DB2. There are
> some JDBC issues too.
> 
> For me - main disadvantage of LO in one space for all. Bytea removes
> this disadvantage, but it is slower for lengths > 20 MB. It could be
> really very practical have a possibility insert some large fields in
> second NON SQL stream. Same situation is when large bytea is read.

Yes, being able to do this (without the need to use LOs as they have
their own set of problems - no FKs, etc.) would help a lot of people who
want/need to keep memory usage low.

What I'd like to see is the ability to stream BYTEA columns in both
directions - let's not reinvent the API, other databases already support
this. E.g. with Oracle you can do this using PDO (PHP):

<?php

$db = new PDO('oci:', 'scott', 'tiger');
$stmt = $db->prepare("insert into images (imagedata)".       "VALUES (EMPTY_BLOB())");

$fp = fopen('./myfile.data', 'rb');

$stmt->bindParam(1, $fp, PDO::PARAM_LOB);
$stmt->execute();

?>

If we could make it work in a similar way, that would be great. There
are some more details at http://cz2.php.net/manual/en/pdo.lobs.php.

Tomas