Horton

Thread

  1. BLOB support

    Radosław Smogura <rsmogura@softperience.eu> — 2011-06-01T20:00:31Z

    Hello,

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.

I have implemented:
- 0.
- 1. and 2. without altering relations and types.
- 3. Inserts only, Updates need polishing. No recursion (no support for BLOBs 
in composites or arrays).

Current patch is here (many changes to pg_types.h - new column haslobs added), 
it's in _early_ stage for those who wants to look inside it.

Any ideas or suggestions?

Regards,
Radek

P. S. 
I'm during removal, and I have limited access to Internet.

  2. Re: BLOB support

    Peter Eisentraut <peter_e@gmx.net> — 2011-06-02T12:39:39Z

    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?

  3. Re: BLOB support

    Pavel Stehule <pavel.stehule@gmail.com> — 2011-06-02T12:58:52Z

    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?
>

a streaming for bytea could be nice. A very large bytea are limited by
query size - processing long query needs too RAM,

Pavel

>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

  4. Re: BLOB support

    Kenneth Marshall <ktm@rice.edu> — 2011-06-02T13:18:35Z

    On Thu, Jun 02, 2011 at 02:58:52PM +0200, Pavel Stehule wrote:
> 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?
> >
> 
> a streaming for bytea could be nice. A very large bytea are limited by
> query size - processing long query needs too RAM,
> 
> Pavel
> 

+1 for a streaming interface to bytea/text. I do agree that there is no need
to reinvent the TOAST architecture with another name, just improve the existing
implementation.

Regards,
Ken

  5. Re: BLOB support

    Pavel Golub <pavel@microolap.com> — 2011-06-02T13:27:07Z

    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?

PS> Pavel

>>
>>
>> --
>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers
>>




-- 
With best wishes,
 Pavel                          mailto:pavel@gf.microolap.com

  6. Re: BLOB support

    Radosław Smogura <rsmogura@softperience.eu> — 2011-06-02T13:29:02Z

     On Thu, 02 Jun 2011 15:39:39 +0300, Peter Eisentraut wrote:
> 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.
 May look similar, but Datums doesn't support more then 32bit length and 
 size of any field is limited to 1GB, am I right? Serializations is only 
 for casting simple values < 1GB, and simple operations, to do not 
 overhead creation of hundreds LOBs.

> 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?

 Actually I thought about less sophisticated support of LOBs, supporting 
 casting and copying data, as well known form other databases idea that 
 LOBs are not "downloaded" during normal query execution (just ids are 
 taken). Currently, e.g. LOBs are not connected with tables, so deleting 
 rows doesn't delete LOB, table actually holds Oid of large objects, no 
 support for casting to/from LOB, no support for CLOBS. Some drivers try 
 to emulate BLOBs/CLOBs, but it is not perfect, mainly from above 
 reasons.

 Regards,
 Radek

  7. Re: BLOB support

    Pavel Stehule <pavel.stehule@gmail.com> — 2011-06-02T13:49:20Z

    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.

Pavel


>
> PS> Pavel
>
>>>
>>>
>>> --
>>> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-hackers
>>>
>
>
>
>
> --
> With best wishes,
>  Pavel                          mailto:pavel@gf.microolap.com
>
>

  8. Re: BLOB support

    Robert Haas <robertmhaas@gmail.com> — 2011-06-02T13:59:42Z

    On Thu, Jun 2, 2011 at 9:29 AM, Radosław Smogura
<rsmogura@softperience.eu> wrote:
>> 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?
>
> Actually I thought about less sophisticated support of LOBs, supporting
> casting and copying data, as well known form other databases idea that LOBs
> are not "downloaded" during normal query execution (just ids are taken).
> Currently, e.g. LOBs are not connected with tables, so deleting rows doesn't
> delete LOB, table actually holds Oid of large objects, no support for
> casting to/from LOB, no support for CLOBS. Some drivers try to emulate
> BLOBs/CLOBs, but it is not perfect, mainly from above reasons.

But these problems can be fixed without inventing a completely new
system, I think.  Or at least we should try.  I can see the point of a
data type that is really a pointer to a LOB, and the LOB gets deleted
when the pointer is removed, but I don't think that should require
far-reaching changes all over the system (like relhaslobs) to make it
work efficiently.  I think you need to start with a problem statement,
get agreement that it is a problem and on what the solution should be,
and then go write the code to implement that solution.  This is a
classic example of writing the code first and then working backwards
toward the problem you're trying to solve, and that rarely works out
well for the reasons that you're now finding out: people may not agree
with your proposed solution, they may want things done differently,
and now you're stuck reworking code that you've already written.  It's
much easier to change a design document than it is to rewrite code.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  9. Re: BLOB support

    Tom Lane <tgl@sss.pgh.pa.us> — 2011-06-02T14:42:42Z

    Robert Haas <robertmhaas@gmail.com> writes:
> But these problems can be fixed without inventing a completely new
> system, I think.  Or at least we should try.  I can see the point of a
> data type that is really a pointer to a LOB, and the LOB gets deleted
> when the pointer is removed, but I don't think that should require
> far-reaching changes all over the system (like relhaslobs) to make it
> work efficiently.  I think you need to start with a problem statement,
> get agreement that it is a problem and on what the solution should be,
> and then go write the code to implement that solution.

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.)

For primitive types like text or bytea it seems pretty obvious what
"streaming access" should entail, but it might be interesting to
consider what it should mean for structured types.  For instance, if I
have an array field with umpteen zillion elements, it might be nice to
fetch them one at a time using the streaming access mechanism.  I don't
say that that has to be in the first version, but it'd be a good idea to
keep that in the back of your head so you don't design a dead-end
solution that can't be extended in that direction.

			regards, tom lane

  10. Re: BLOB support

    Pavel Stehule <pavel.stehule@gmail.com> — 2011-06-02T15:12:08Z

    2011/6/2 Tom Lane <tgl@sss.pgh.pa.us>:
> Robert Haas <robertmhaas@gmail.com> writes:
>> But these problems can be fixed without inventing a completely new
>> system, I think.  Or at least we should try.  I can see the point of a
>> data type that is really a pointer to a LOB, and the LOB gets deleted
>> when the pointer is removed, but I don't think that should require
>> far-reaching changes all over the system (like relhaslobs) to make it
>> work efficiently.  I think you need to start with a problem statement,
>> get agreement that it is a problem and on what the solution should be,
>> and then go write the code to implement that solution.
>
> 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.)
>
> For primitive types like text or bytea it seems pretty obvious what
> "streaming access" should entail, but it might be interesting to
> consider what it should mean for structured types.  For instance, if I
> have an array field with umpteen zillion elements, it might be nice to
> fetch them one at a time using the streaming access mechanism.  I don't
> say that that has to be in the first version, but it'd be a good idea to
> keep that in the back of your head so you don't design a dead-end
> solution that can't be extended in that direction.

+1

Pavel

>
>                        regards, tom lane
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

  11. Re: BLOB support

    Radosław Smogura <rsmogura@softperience.eu> — 2011-06-02T16:53:52Z

    Tom Lane <tgl@sss.pgh.pa.us> Thursday 02 of June 2011 16:42:42
> Robert Haas <robertmhaas@gmail.com> writes:
> > But these problems can be fixed without inventing a completely new
> > system, I think.  Or at least we should try.  I can see the point of a
> > data type that is really a pointer to a LOB, and the LOB gets deleted
> > when the pointer is removed, but I don't think that should require
> > far-reaching changes all over the system (like relhaslobs) to make it
> > work efficiently.  I think you need to start with a problem statement,
> > get agreement that it is a problem and on what the solution should be,
> > and then go write the code to implement that solution.
> 
> 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.)
> 
> For primitive types like text or bytea it seems pretty obvious what
> "streaming access" should entail, but it might be interesting to
> consider what it should mean for structured types.  For instance, if I
> have an array field with umpteen zillion elements, it might be nice to
> fetch them one at a time using the streaming access mechanism.  I don't
> say that that has to be in the first version, but it'd be a good idea to
> keep that in the back of your head so you don't design a dead-end
> solution that can't be extended in that direction.
> 
> 			regards, tom lane

In context of LOBs streaming is resolved... I use current LO functionallity 
(so driver may be able to read LOBs as psql \lo_export does it or using COPY 
subprotocol) and client should get just LO's id. BLOBs in this implementation, 
like Robert wanted are just wrapper for core LO, with some extensions for 
special situations.... Adding of relhaslob in this impl is quite importnat to 
do not examine tupledesc for each table operation, but this value may be 
deduced during relation open (with performance penatly). I saw simillar is 
made few lines above when triggers are fired, and few lines below when indices 
are updated. 

Currently BLOBs may be emulated using core LO (JDBC driver does it), but among 
everything else, other problems are, if you look from point of view of 
application developing:

1. No tracking of unused LO (you store just id of such object). You may leak 
LO after row remove/update. User may write triggers for this, but it is not 
argument - BLOB type is popular, and it's simplicity of use is quite 
important. When I create app this is worst thing.

2. No support for casting in UPDATE/INSERT. So there is no way to simple 
migrate data (e.g. from too long varchars). Or to copy BLOBs.

3. Limitation of field size to 1GB.

Other solution, I was think about, is to introduce system triggers (such 
triggers can't be disabled or removed). So there will be new flag in triggers 
table.

Now I think, we should try to mix both aproches, as system triggers may give 
interesting API for other developers.

Other databases (may) store LOBs, Arrays, and Composites in external tables, 
so user get's just id of such object.

I think about two weaks about streaming, I have some concepts about this, but 
from point of view of memory consumption and performance. I will send concept 
later, I want to think a little bit about it once more, and search what can be 
actually done.

Regards,
Radek

  12. Re: BLOB support

    Tom Lane <tgl@sss.pgh.pa.us> — 2011-06-02T17:43:16Z

    =?utf-8?q?Rados=C5=82aw_Smogura?= <rsmogura@softperience.eu> writes:
> Tom Lane <tgl@sss.pgh.pa.us> Thursday 02 of June 2011 16:42:42
>> 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.)

> In context of LOBs streaming is resolved... I use current LO functionallity 
> (so driver may be able to read LOBs as psql \lo_export does it or using COPY 
> subprotocol) and client should get just LO's id.

Just to be clear: I do not want to expose a concept of object IDs for
field values in the first place.  All of the problems you enumerate stem
from the idea that LOBs ought to be a distinct kind of field, and I
don't buy that.

			regards, tom lane

  13. Re: BLOB support

    Radosław Smogura <rsmogura@softperience.eu> — 2011-06-02T19:26:29Z

    Tom Lane <tgl@sss.pgh.pa.us> Thursday 02 of June 2011 19:43:16
> =?utf-8?q?Rados=C5=82aw_Smogura?= <rsmogura@softperience.eu> writes:
> > Tom Lane <tgl@sss.pgh.pa.us> Thursday 02 of June 2011 16:42:42
> > 
> >> 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.)
> > 
> > In context of LOBs streaming is resolved... I use current LO
> > functionallity (so driver may be able to read LOBs as psql \lo_export
> > does it or using COPY subprotocol) and client should get just LO's id.
> 
> Just to be clear: I do not want to expose a concept of object IDs for
> field values in the first place.  All of the problems you enumerate stem
> from the idea that LOBs ought to be a distinct kind of field, and I
> don't buy that.
> 
> 			regards, tom lane

So do I understand good should We think about create bettered TOAST to support 
larger values then 30-bit length? I like this much more, but without Objects 
ID quering relation with lobs will require to lock relation for some time, as 
client will need to reference LOB in some way, I think using TID or some 
derivative of TID, am I right?

Regards,
Radek

  14. Re: BLOB support

    Tomas Vondra <tv@fuzzy.cz> — 2011-06-02T20:21:08Z

    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

  15. Re: BLOB support

    Tomas Vondra <tv@fuzzy.cz> — 2011-06-02T20:43:21Z

    Dne 2.6.2011 15:18, ktm@rice.edu napsal(a):
> On Thu, Jun 02, 2011 at 02:58:52PM +0200, Pavel Stehule wrote:
>> 2011/6/2 Peter Eisentraut <peter_e@gmx.net>:
>>> 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?
>>>
>>
>> a streaming for bytea could be nice. A very large bytea are limited by
>> query size - processing long query needs too RAM,
>>
>> Pavel
>>
> 
> +1 for a streaming interface to bytea/text. I do agree that there is no need
> to reinvent the TOAST architecture with another name, just improve the existing
> implementation.

Building a "parallel" architecture that mimics TOAST is obviously a bad
idea.

But I do have a curious question - the current LO approach is based on
splitting the data into small chunks (2kB) and storing those chunks in a
bytea column of the pg_largeobject table.

How much overhead does all this mean? What if there is a special kind of
blocks for binary data, that limits the amount of chunks and TOAST?
Actually this probably would not need a special type of block, but when
writing a block there would be a single row with as much data as
possible (and some metadata). I.e. there would be almost 8kB of
compressed data.

This would probably bring some restrictions (e.g. inability to update
the data, but I don't think that's possible with the current LO anyway.
Has anyone thought about this?

regards
Tomas

  16. Re: BLOB support

    Ross Reedstrom <reedstrm@rice.edu> — 2011-06-02T20:54:53Z

    On Thu, Jun 02, 2011 at 01:43:16PM -0400, Tom Lane wrote:
> =?utf-8?q?Rados=C5=82aw_Smogura?= <rsmogura@softperience.eu> writes:
> > Tom Lane <tgl@sss.pgh.pa.us> Thursday 02 of June 2011 16:42:42
> >> 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.)
> 
> > In context of LOBs streaming is resolved... I use current LO functionallity 
> > (so driver may be able to read LOBs as psql \lo_export does it or using COPY 
> > subprotocol) and client should get just LO's id.
> 
> Just to be clear: I do not want to expose a concept of object IDs for
> field values in the first place.  All of the problems you enumerate stem
> from the idea that LOBs ought to be a distinct kind of field, and I
> don't buy that.
> 

I think you're saying no OIDs exposed to the SQL i.e. actually stored in
a field and returned by a SELECT? (Which seems to be the proposal).

As I mentioned recently on another list, I've wrapped a block-oriented
"streaming" interface over bytea in python for a web app, specifically
to deal with the latency and memory footprint issues of storing
'largish' files directly in the db.  I find that with a 64K blocksize,
latency is 'good enough' and substr() seems to be constant time for a
given size, no matter what part of the bytea value I'm fetching: toast
does a fine job of random access.


I was musing about providing a way to use the existing client lo
streaming interface (rather than the backend bits) for this type of
access. The thing called an OID in the client interface is really just a
nonce to tell the backend what data to send. With a single generator
function: 

SELECT CASE WHEN is_lo THEN my_loid ELSE make_lo_oid(my_bytea) END 
  FROM my_file_table WHERE id = 34534;

Then plugging that back into the lo interface from the client side,
would let me use bytea as I currently do for files under 1GB, lo for
larger, and gain client side streaming that is transparent to the
storage of that particular value. Admittedly, application software would
still need to know how to _store_ different values, and manage large
objects, with all the pain that entails. But there's some gain in
unifying the reading part.

Hard to not call it an oid, since that's what the client libraries
already document it as (at least, python does)

Ross
-- 
Ross Reedstrom, Ph.D.                                 reedstrm@rice.edu
Systems Engineer & Admin, Research Scientist        phone: 713-348-6166
Connexions                  http://cnx.org            fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E  F888 D3AE 810E 88F0 BEDE

  17. Re: BLOB support

    Alvaro Herrera <alvherre@commandprompt.com> — 2011-06-03T14:35:37Z

    Excerpts from Radosław Smogura's message of jue jun 02 15:26:29 -0400 2011:

> So do I understand good should We think about create bettered TOAST to support 
> larger values then 30-bit length? I like this much more,

Good :-)

(BTW while it'd be good to have longer-than-30 bit length words for
varlena, I'm not sure we have room for that.)

> but without Objects ID quering relation with lobs will require to lock
> relation for some time,

Why?  The tuples are not going away due to MVCC anyway.

-- 
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

  18. Re: BLOB support

    Tom Lane <tgl@sss.pgh.pa.us> — 2011-06-03T14:44:13Z

    Alvaro Herrera <alvherre@commandprompt.com> writes:
> Excerpts from Radosaw Smogura's message of jue jun 02 15:26:29 -0400 2011:
>> So do I understand good should We think about create bettered TOAST to support 
>> larger values then 30-bit length? I like this much more,

> Good :-)

> (BTW while it'd be good to have longer-than-30 bit length words for
> varlena, I'm not sure we have room for that.)

You wouldn't want to push such values around as whole values anyway.
Possibly what would work here is a variant form of TOAST pointer for
which we'd simply throw error if you tried to fetch the entire value
at once.

			regards, tom lane

  19. Re: BLOB support

    Robert Haas <robertmhaas@gmail.com> — 2011-06-03T16:03:40Z

    On Thu, Jun 2, 2011 at 12:53 PM, Radosław Smogura
<rsmogura@softperience.eu> wrote:
> 1. No tracking of unused LO (you store just id of such object). You may leak
> LO after row remove/update. User may write triggers for this, but it is not
> argument - BLOB type is popular, and it's simplicity of use is quite
> important. When I create app this is worst thing.
>
> 2. No support for casting in UPDATE/INSERT. So there is no way to simple
> migrate data (e.g. from too long varchars). Or to copy BLOBs.
>
> 3. Limitation of field size to 1GB.

As a general point, it would probably be a good idea to address each
of these issues separately, and to have a separate discussion about
each one.

As to #1 specifically, if you use a text or bytea field rather than a
large object per se, then this issue goes away.  But then you lose the
streaming functionality.  So at least some people here are saying that
we should try to fix that by adding the streaming functionality to
text/bytea rather than by doing anything to the large object facility.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

  20. Re: BLOB support

    Tom Lane <tgl@sss.pgh.pa.us> — 2011-06-03T16:08:56Z

    Robert Haas <robertmhaas@gmail.com> writes:
> On Thu, Jun 2, 2011 at 12:53 PM, Radosaw Smogura
> <rsmogura@softperience.eu> wrote:
>> 1. No tracking of unused LO (you store just id of such object). You may leak
>> LO after row remove/update. User may write triggers for this, but it is not
>> argument - BLOB type is popular, and it's simplicity of use is quite
>> important. When I create app this is worst thing.
>> 
>> 2. No support for casting in UPDATE/INSERT. So there is no way to simple
>> migrate data (e.g. from too long varchars). Or to copy BLOBs.
>> 
>> 3. Limitation of field size to 1GB.

> As a general point, it would probably be a good idea to address each
> of these issues separately, and to have a separate discussion about
> each one.

> As to #1 specifically, if you use a text or bytea field rather than a
> large object per se, then this issue goes away.  But then you lose the
> streaming functionality.  So at least some people here are saying that
> we should try to fix that by adding the streaming functionality to
> text/bytea rather than by doing anything to the large object facility.

#2 is also a problem that only becomes a problem if you insist that LOBs
have to be a distinct kind of value.

			regards, tom lane

  21. Re: BLOB support

    Radosław Smogura <rsmogura@softperience.eu> — 2011-06-03T16:09:48Z

    Tom Lane <tgl@sss.pgh.pa.us> Friday 03 of June 2011 16:44:13
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Excerpts from Radosław Smogura's message of jue jun 02 15:26:29 -0400 
2011:
> >> So do I understand good should We think about create bettered TOAST to
> >> support larger values then 30-bit length? I like this much more,
> > 
> > Good :-)
> > 
> > (BTW while it'd be good to have longer-than-30 bit length words for
> > varlena, I'm not sure we have room for that.)
> 
> You wouldn't want to push such values around as whole values anyway.
> Possibly what would work here is a variant form of TOAST pointer for
> which we'd simply throw error if you tried to fetch the entire value
> at once.
> 
> 			regards, tom lane

Ok, now it's more clear about this, what You have talked about, but I still 
need to pass constant ID to client.

Actually, this variant must be passed to client.

Form other side, as BLOB may be created before statement invoke or if it's 
called. This will require to create tempolary BLOBs, and introducing v3.1 
protocol, which will allow to stream values greater then 4GB, by passing -2 
size in length fields, and introducing stream_in/out in pg_type (this is from 
my concept of streaming protocol).

So I think better will be to introduce 1st streaming protocol, as it is on top 
LOBs. I will send thread for this in a moment.

>> Why?  The tuples are not going away due to MVCC anyway.
Vaccum / autovacumm + no lock may be enaugh, I think. Constant ID is required.

Regards,
Radek

  22. Re: BLOB support

    Radosław Smogura <rsmogura@softperience.eu> — 2011-06-03T16:50:21Z

    Tom Lane <tgl@sss.pgh.pa.us> Friday 03 of June 2011 18:08:56
> Robert Haas <robertmhaas@gmail.com> writes:
> > On Thu, Jun 2, 2011 at 12:53 PM, Radosław Smogura
> > 
> > <rsmogura@softperience.eu> wrote:
> >> 1. No tracking of unused LO (you store just id of such object). You may
> >> leak LO after row remove/update. User may write triggers for this, but
> >> it is not argument - BLOB type is popular, and it's simplicity of use
> >> is quite important. When I create app this is worst thing.
> >> 
> >> 2. No support for casting in UPDATE/INSERT. So there is no way to simple
> >> migrate data (e.g. from too long varchars). Or to copy BLOBs.
> >> 
> >> 3. Limitation of field size to 1GB.
> > 
> > As a general point, it would probably be a good idea to address each
> > of these issues separately, and to have a separate discussion about
> > each one.
> > 
> > As to #1 specifically, if you use a text or bytea field rather than a
> > large object per se, then this issue goes away.  But then you lose the
> > streaming functionality.  So at least some people here are saying that
> > we should try to fix that by adding the streaming functionality to
> > text/bytea rather than by doing anything to the large object facility.
> 
> #2 is also a problem that only becomes a problem if you insist that LOBs
> have to be a distinct kind of value.
> 
> 			regards, tom lane

And one more topic to discuss. Should blob be referencable, e.g. I create in 
JDBC new Blob, I set stream for it what should happen if I will call
UPDATE t set b = ? where 1=1
?

This is not about copy on write.

Regards,
Radek

  23. Re: BLOB support

    Radosław Smogura <rsmogura@softperience.eu> — 2011-06-04T09:16:31Z

    Tom Lane <tgl@sss.pgh.pa.us> Friday 03 of June 2011 16:44:13
> Alvaro Herrera <alvherre@commandprompt.com> writes:
> > Excerpts from Radosław Smogura's message of jue jun 02 15:26:29 -0400 
2011:
> >> So do I understand good should We think about create bettered TOAST to
> >> support larger values then 30-bit length? I like this much more,
> > 
> > Good :-)
> > 
> > (BTW while it'd be good to have longer-than-30 bit length words for
> > varlena, I'm not sure we have room for that.)
> 
> You wouldn't want to push such values around as whole values anyway.
> Possibly what would work here is a variant form of TOAST pointer for
> which we'd simply throw error if you tried to fetch the entire value
> at once.
> 
> 			regards, tom lane

I mentoined about JDBC call. 
1. 
b = conn.createBlob();
ps = conn.preparesStatement("INSRT INTO t blob = ?");
ps.setBlob(1, b);
ps.executeQuery();

2.
Statements could be as well too
"SELECT blob_index_of(?, ?)";
where 1st ? is blob, 2nd one is some text/sequence

This call must be supported to make BLOB as far as possible simillar to other 
types, this actually disallow to put LOB in TOAST as there is no relation (2.) 
or relation is not known (1.) during reading LOB - in any case you can't skip 
bytes from protocol stream, so possible solutions are:

1. Create temporaly LOB in file or memory depending of it size.
2. Use existing Large Object interface.
3. Client will not send LOB, just it's faked ID and during call Server will 
ask client to serialize this LOB, by faked id.
4. Any other propositions?

I vote for 2.

For pg_type/class changes I think about removing haslobs, and put this as 
attstorage or somewhere else for example ('l' may stand for lobs or -3 
length), but currently TOAST composites doesn't support recursion, and those 
types are toasted as whole. I may add recursion for those types, and support 
special maintain for LOBs. In any case handling this will be better in 
toasting code, not in nodeModifyTable.

Any ideas about this?

Reagrds,
Radek

  24. Re: BLOB support

    Dimitri Fontaine <dimitri@2ndquadrant.fr> — 2011-06-05T20:16:41Z

    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,
-- 
Dimitri Fontaine
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support

  25. Re: BLOB support

    Radosław Smogura <rsmogura@softperience.eu> — 2011-06-06T08:11:21Z

     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 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

  26. Re: BLOB support

    Pavel Stehule <pavel.stehule@gmail.com> — 2011-06-06T08:38:39Z

    2011/6/6 Radosław Smogura <rsmogura@softperience.eu>:
> 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 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.

@1 is useles for multiuser applications. This is a problem of current
implemementation for large TOAST values. You can hold around
"work_mem" bytes in mem, but any larger content should to be forwarded
to file.

Pavel

>
> So please give some ideas how to resolve this, or may be it has low
> priority?
>
> Regards,
> Radek
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers
>

  27. Re: BLOB support

    Tom Lane <tgl@sss.pgh.pa.us> — 2011-06-06T14:13:26Z

    =?UTF-8?Q?Rados=C5=82aw_Smogura?= <rsmogura@softperience.eu> writes:
>  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,

Yes, if you insist upon designing the API like that, then you come to
the conclusion that you need global LOB identifiers.

However, there are many ways to design this that don't work that way.
One idea to think about is

	insert into someonetubevideos values('')
	returning open_for_write(videocolumn)

which gives you back some kind of writable stream ID (this is a
transient, within-session ID, not global) for the target field in the
row you just inserted.

BTW, as was noted upthread by Dimitri, this whole subject has been
discussed before on pgsql-hackers.  You really ought to go re-read the
previous threads.

			regards, tom lane

  28. Re: BLOB support

    Radosław Smogura <rsmogura@softperience.eu> — 2011-06-06T15:23:08Z

    Tom Lane <tgl@sss.pgh.pa.us> Monday 06 of June 2011 16:13:26
> =?UTF-8?Q?Rados=C5=82aw_Smogura?= <rsmogura@softperience.eu> writes:
> >  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,
> 
> Yes, if you insist upon designing the API like that, then you come to
> the conclusion that you need global LOB identifiers.
> 
> However, there are many ways to design this that don't work that way.
> One idea to think about is
> 
> 	insert into someonetubevideos values('')
> 	returning open_for_write(videocolumn)
> 
> which gives you back some kind of writable stream ID (this is a
> transient, within-session ID, not global) for the target field in the
> row you just inserted.
I know, but this is a little bit old-fashioned bahaviour.

> BTW, as was noted upthread by Dimitri, this whole subject has been
> discussed before on pgsql-hackers.  You really ought to go re-read the
> previous threads.
> 
> 			regards, tom lane

I read this, but it may be rethinked again. Actaully changes to TOAST (I mean 
streaming will be just for LOBs, I had written all found disadvantages for 
TOAST for LOB and it's looks like only performance of above is some kind of 
disadvantage, as well this prevent some less usefull concepts of Copy on Write 
for LOBs.

Introducing streaming for TOAST is little useless, sorry just for cite from 
my, mentoined document:

(This is generally about on demand stream of TOASTed value, in 
context of LOBs is acceptable, as long not transactional aware LOBs are 
acceptable). If we will add streaming of TOASTed values, so caller will 
get some reference to this value, we need to ensure that pointed data 
will not be changed, nor deleted - I think this will require caller to 
add FOR UPDATE (or silently to add this by server) for each statement 
returning pointers to TOASTed, as client may do transactional query, and 
other client just after (1st) may remove record, commit, and call 
VACUUM. In this situation when 1st will try to read data form given row, 
it will get error. This may be accpetable for LOBs (commonly LOBs may be not 
transaction aware, but I will be angry if this will happen with VARCHAR)

If this is acceptable I will do following changes.

Add 
- server_max_in_memory_lob_size - GUC server start-only config to describe 
maximum value of client session parameter max_in_memory_lob.

- max_in_memory_lob - session GUC describing how huge LOBs may be keept in 
memory before backing up to file

- rescursivly toasting, detoasting during insert/update/remove for searching 
for LOBs (we need this for arrays and complex types) - this is for last stage 
(error disallowing LOBs in composites/arrays may be quite enaugh, for 
begining) - I want LOBs to be starting point for LOBing other types (e.g. some 
big arrays may be LOBbed).

- during toasting, lob will be toasted and in place of LOB, the reference to 
it will be putted, and encoded in LOB datum.

- api for LOB manipulation (few changes to current implementation) in way that 
BLOB is field type and uninon of in_memory, in_file, in_toast. This few bytes 
for LOB will not affect size of datum looking at size of LOB.

- api for maintaing temoraly lob files, we need this as per session list of id 
-> file desc, to prevent prevent stealing of lobs by different connections 
(security)

- streaming api for TOASTED values (based on COPY protocol, or changed COPY 
protocol) or at least function calls - I havent looked at this in context of 
TOASTed LOBs.

Is it good?

Regards,
Radek

  29. Re: BLOB support

    Tom Lane <tgl@sss.pgh.pa.us> — 2011-06-06T15:41:11Z

    =?utf-8?q?Rados=C5=82aw_Smogura?= <rsmogura@softperience.eu> writes:
> Introducing streaming for TOAST is little useless, sorry just for cite from 
> my, mentoined document:

> (This is generally about on demand stream of TOASTed value, in 
> context of LOBs is acceptable, as long not transactional aware LOBs are 
> acceptable). If we will add streaming of TOASTed values, so caller will 
> get some reference to this value, we need to ensure that pointed data 
> will not be changed, nor deleted - I think this will require caller to 
> add FOR UPDATE (or silently to add this by server) for each statement 
> returning pointers to TOASTed, as client may do transactional query,

It's already been explained to you that that's not the case.

> If this is acceptable I will do following changes.

> Add 
> - server_max_in_memory_lob_size - GUC server start-only config to describe 
> maximum value of client session parameter max_in_memory_lob.

> - max_in_memory_lob - session GUC describing how huge LOBs may be keept in 
> memory before backing up to file

> - rescursivly toasting, detoasting during insert/update/remove for searching 
> for LOBs (we need this for arrays and complex types) - this is for last stage 
> (error disallowing LOBs in composites/arrays may be quite enaugh, for 
> begining) - I want LOBs to be starting point for LOBing other types (e.g. some 
> big arrays may be LOBbed).

> - during toasting, lob will be toasted and in place of LOB, the reference to 
> it will be putted, and encoded in LOB datum.

> - api for LOB manipulation (few changes to current implementation) in way that 
> BLOB is field type and uninon of in_memory, in_file, in_toast. This few bytes 
> for LOB will not affect size of datum looking at size of LOB.

> - api for maintaing temoraly lob files, we need this as per session list of id 
> -> file desc, to prevent prevent stealing of lobs by different connections 
> (security)

> - streaming api for TOASTED values (based on COPY protocol, or changed COPY 
> protocol) or at least function calls - I havent looked at this in context of 
> TOASTed LOBs.

> Is it good?

This all looks like you decided on a solution first and then started to
look for a problem to apply it to.  I don't want to see us inventing a
pile of GUCs for this, and I don't think there is a need to make any
fundamental changes in the TOAST mechanism either.  What we do need is a
streaming access protocol to read or write wide field values *without*
forcing people to change the basic structure of their tables.  You keep
on wanting to invent weird, IMO unnecessary language features instead.
Try to make the feature as transparent as possible, not as visible as
possible.

			regards, tom lane

  30. Re: BLOB support

    Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> — 2011-06-06T15:48:42Z

    On 06.06.2011 17:13, Tom Lane wrote:
> =?UTF-8?Q?Rados=C5=82aw_Smogura?=<rsmogura@softperience.eu>  writes:
>>   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,
>
> Yes, if you insist upon designing the API like that, then you come to
> the conclusion that you need global LOB identifiers.

That's what the JDBC api looks like, but it doesn't mean you need global 
LOB identifiers. When you create the Blob object (myWeddingDvd), the 
driver can just keep a reference to the given stream (myWeddingStream) 
to the Blob object. When you execute the INSERT statement, the driver 
can read the stream and stream the data to the server.

The protocol changes I think Tom and I and others are envisioning would 
work just fine with that.

-- 
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com