I was going through the Todo list looking at the items that are planned 
for 7.2 (i.e. those starting with a '-').  I was doing this to see if 
any might impact the jdbc driver.  The only one that I thought might 
have an impact on the jdbc code is the item:

*  -Make binary/file in/out interface for TOAST columns (base64)

I looked through the 7.2 docs and I couldn't find any reference to this 
new functionality, so I am assuming that it isn't completed yet. If this 
is going to be done for 7.2, I would like to get a better understanding 
of what functionality is going to be provided.  That way I can decide 
how best to expose that functionality through the jdbc interface.

thanks,
--Barry


Sat, 04 Aug 2001 16:41:59 -0700
Barry Lind <barry@xythos.com>
Question about todo item
> I was going through the Todo list looking at the items that are planned 
> for 7.2 (i.e. those starting with a '-').  I was doing this to see if 
> any might impact the jdbc driver.  The only one that I thought might 
> have an impact on the jdbc code is the item:
> 
> *  -Make binary/file in/out interface for TOAST columns (base64)

Marked items are done, not planned for 7.2.

> I looked through the 7.2 docs and I couldn't find any reference to this 
> new functionality, so I am assuming that it isn't completed yet. If this 
> is going to be done for 7.2, I would like to get a better understanding 
> of what functionality is going to be provided.  That way I can decide 
> how best to expose that functionality through the jdbc interface.

Not sure on the docs issue, but it is a set of function uuencode,
uudecode, etc that allow binary data to be uuencoded, then loaded into a
bytea field as binary.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sat, 4 Aug 2001 21:14:03 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Question about todo item
OK.  Those functions are in the docs.  I didn't relate those functions 
and this todo item together.

By 'in/out interface for TOAST columns' I thought this item dealt with 
adding large object like functions to read/write/append to TOAST column 
data.  I know that has been talked about in the past on hackers.  But I 
don't see it on the todo list.  Has that been done?

thanks,
--Barry

Bruce Momjian wrote:
>>I was going through the Todo list looking at the items that are planned 
>>for 7.2 (i.e. those starting with a '-').  I was doing this to see if 
>>any might impact the jdbc driver.  The only one that I thought might 
>>have an impact on the jdbc code is the item:
>>
>>*  -Make binary/file in/out interface for TOAST columns (base64)
>>
> 
> Marked items are done, not planned for 7.2.
> 
> 
>>I looked through the 7.2 docs and I couldn't find any reference to this 
>>new functionality, so I am assuming that it isn't completed yet. If this 
>>is going to be done for 7.2, I would like to get a better understanding 
>>of what functionality is going to be provided.  That way I can decide 
>>how best to expose that functionality through the jdbc interface.
>>
> 
> Not sure on the docs issue, but it is a set of function uuencode,
> uudecode, etc that allow binary data to be uuencoded, then loaded into a
> bytea field as binary.
> 
> 



Sat, 04 Aug 2001 18:35:15 -0700
Barry Lind <barry@xythos.com>
Re: Question about todo item
> OK.  Those functions are in the docs.  I didn't relate those functions 
> and this todo item together.
> 
> By 'in/out interface for TOAST columns' I thought this item dealt with 
> adding large object like functions to read/write/append to TOAST column 
> data.  I know that has been talked about in the past on hackers.  But I 
> don't see it on the todo list.  Has that been done?

Only large objects allow that kind of access.  I don't think we will do
that for TOAST columns.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sat, 4 Aug 2001 21:51:28 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Question about todo item
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> By 'in/out interface for TOAST columns' I thought this item dealt with 
>> adding large object like functions to read/write/append to TOAST column 
>> data.  I know that has been talked about in the past on hackers.  But I 
>> don't see it on the todo list.  Has that been done?

> Only large objects allow that kind of access.  I don't think we will do
> that for TOAST columns.

Barry's right --- that *has* been talked about, and I thought the
consensus was that we needed such functions.  You don't necessarily
want to read or write a multi-megabyte TOASTed value all in one go.
If it's not on TODO then it should be.  (But I suspect if you check
the archives, you'll discover that this is exactly what the TODO
item was really about.)

			regards, tom lane

Sat, 04 Aug 2001 22:33:17 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Question about todo item 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> >> By 'in/out interface for TOAST columns' I thought this item dealt with 
> >> adding large object like functions to read/write/append to TOAST column 
> >> data.  I know that has been talked about in the past on hackers.  But I 
> >> don't see it on the todo list.  Has that been done?
> 
> > Only large objects allow that kind of access.  I don't think we will do
> > that for TOAST columns.
> 
> Barry's right --- that *has* been talked about, and I thought the
> consensus was that we needed such functions.  You don't necessarily
> want to read or write a multi-megabyte TOASTed value all in one go.
> If it's not on TODO then it should be.  (But I suspect if you check
> the archives, you'll discover that this is exactly what the TODO
> item was really about.)

Yes, I kept talking about it, but no one was interested, saying large
objects are better for that kind of access.  When the uuencode idea came
around, I though the read/write binary toast idea was dead.

I agree we should have it, but I thought the problem was that we
couldn't come up with an API that worked.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sat, 4 Aug 2001 22:35:59 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Question about todo item
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I agree we should have it, but I thought the problem was that we
> couldn't come up with an API that worked.

AFAIR, no one's really tried yet.  I do not recall any proposals
getting shot down ...

			regards, tom lane

Sat, 04 Aug 2001 22:38:40 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Question about todo item 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I agree we should have it, but I thought the problem was that we
> > couldn't come up with an API that worked.
> 
> AFAIR, no one's really tried yet.  I do not recall any proposals
> getting shot down ...

I keep bugging Jan about it, since pre-7.1 and no one has come up with
an idea.  I think the lack of any proposal or anyone even mentioning
they liked the idea made me give up, especially when uuencode at least
gave us binary in/out.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sat, 4 Aug 2001 22:41:14 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Question about todo item
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I keep bugging Jan about it, since pre-7.1 and no one has come up with
> an idea.

Well, if you want an idea:

	BEGIN;

	SELECT open_toast_object(toastable_column) FROM tab WHERE ...;

	-- app checks that it got exactly one result back

	-- app lo_reads and/or lo_writes using ID returned by SELECT

	END;

Implementation is left as an exercise for the reader ;-).

Offhand this seems like it would be doable for a column-value that
was actually moved out-of-line by TOAST, since the open_toast_object
function could see and return the TOAST pointer, and then the read/
write operations just hack on rows in pg_largeobject.  The hard part
is how to provide equivalent functionality (transparent to the client
of course) when the particular value you select has *not* been moved
out-of-line.  Ideas anyone?

			regards, tom lane

Sat, 04 Aug 2001 23:11:39 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Question about todo item 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I keep bugging Jan about it, since pre-7.1 and no one has come up with
> > an idea.
> 
> Well, if you want an idea:
> 
> 	BEGIN;
> 
> 	SELECT open_toast_object(toastable_column) FROM tab WHERE ...;
> 
> 	-- app checks that it got exactly one result back
> 
> 	-- app lo_reads and/or lo_writes using ID returned by SELECT
> 
> 	END;
> 
> Implementation is left as an exercise for the reader ;-).
> 
> Offhand this seems like it would be doable for a column-value that
> was actually moved out-of-line by TOAST, since the open_toast_object
> function could see and return the TOAST pointer, and then the read/
> write operations just hack on rows in pg_largeobject.  The hard part

I am confused how pg_largeobject is involved?

> is how to provide equivalent functionality (transparent to the client
> of course) when the particular value you select has *not* been moved
> out-of-line.  Ideas anyone?

Don't forget compression of TOAST columns.  How do you fseek/read/write
in there?

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Sat, 4 Aug 2001 23:34:13 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Question about todo item
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Offhand this seems like it would be doable for a column-value that
>> was actually moved out-of-line by TOAST, since the open_toast_object
>> function could see and return the TOAST pointer, and then the read/
>> write operations just hack on rows in pg_largeobject.  The hard part

> I am confused how pg_largeobject is involved?

s/pg_largeobject/toast_table_for_relation/ ... sorry about that ...

> Don't forget compression of TOAST columns.  How do you fseek/read/write
> in there?

Well, you can *do* it, just don't expect it to be fast.  The
implementation would have to read or write most of the value, not just
the segment you wanted.  A person who actually expected to use this
stuff would likely want to disable compression on a column he wanted
random access within.

Hmm ... that provides an idea.  We could easily add some additional
'attstorage' settings that say *all* values of a column must be forced
out-of-line (with or without allowing compression), regardless of size.
Then, open_toast_object would work reliably on such a column.  One
possible user API to such an infrastructure is to invent BLOB and CLOB
datatypes, which are just like bytea and text except that they force the
appropriate attstorage value.  Ugly as sin, ain't it ... but I bet it
could be made to work.

Okay, there's your idea.  Now, who can do better?

			regards, tom lane

Sat, 04 Aug 2001 23:55:24 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Question about todo item 
Bruce Momjian wrote:
> 
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > > I agree we should have it, but I thought the problem was that we
> > > couldn't come up with an API that worked.
> >
> > AFAIR, no one's really tried yet.  I do not recall any proposals
> > getting shot down ...
> 
> I keep bugging Jan about it, since pre-7.1 and no one has come up with
> an idea.  I think the lack of any proposal or anyone even mentioning
> they liked the idea made me give up, especially when uuencode at least
> gave us binary in/out.

Can anyone recall, why was uuencode chosen over base64 encoding ?

-----------------
Hannu

Sun, 05 Aug 2001 10:48:44 +0500
Hannu Krosing <hannu@tm.ee>
Re: Question about todo item
Tom Lane wrote:
> 
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I keep bugging Jan about it, since pre-7.1 and no one has come up with
> > an idea.
> 
> Well, if you want an idea:
> 
>         BEGIN;
> 
>         SELECT open_toast_object(toastable_column) FROM tab WHERE ...;
> 
>         -- app checks that it got exactly one result back
> 
>         -- app lo_reads and/or lo_writes using ID returned by SELECT
> 
>         END;
> 
> Implementation is left as an exercise for the reader ;-).
> 
> Offhand this seems like it would be doable for a column-value that
> was actually moved out-of-line by TOAST, since the open_toast_object
> function could see and return the TOAST pointer, and then the read/
> write operations just hack on rows in pg_largeobject.  The hard part
> is how to provide equivalent functionality (transparent to the client
> of course) when the particular value you select has *not* been moved
> out-of-line.  Ideas anyone?

I'd propose the folllowing - 

      BEGIN;

      DECLARE toastaccesscursor 
      CURSOR FOR  
      SELECT open_toast_object_handle(toastable_column) as
toast_object_handle FROM tab WHERE ...;

      -- while you get any rows

          FETCH 1 IN toastaccesscursor;
 
          -- app lo_reads and/or lo_writes using toast_object_handle
returned by SELECT
 
      END;


If we really wanted to have lo_xxx functionality on any toastable column
it should be doable by 
creating a fake toast-handle and manipulating the column value directly,
preferrably automatically 
moving the lo_written column to toast. Faking the handle should be easy
as it has to live only while 
cursor is positioned on affected row .

But your another idea of creating special [B|C]LOB types that are
allways saved to toast seems nicer

CREATE TABLE breakfast (
    main    eggs_and_bacon WITH TOAST = 'always,nocompress'
);

and just raise an error or do a silent conversion if a section is
lo_written in a compressed 
or non-toasted column.

As TOAST is a general purpose feature of postgres I think that providing
the WITH options is more 
desirable than special types for only a few of them. 

CLOB and BLOB could still be provided as shorthand names similar to
SERIAL.

---------------
Hannu

Sun, 05 Aug 2001 11:13:36 +0500
Hannu Krosing <hannu@tm.ee>
Re: Question about todo item
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I agree we should have it, but I thought the problem was that we
> > couldn't come up with an API that worked.
>
> AFAIR, no one's really tried yet.  I do not recall any proposals
> getting shot down ...

    One  of  the problems I saw, and that's probably why we don't
    have a proposal yet,  is,  that  the  size  of  the  data  is
    recorded  in  the  toast reference held in the main tuple. If
    you later open the toast value for writing, you'll change the
    size,  but  you'd  need  to  change it in the main tuple too,
    what'd require a regular update on the  main  tuple,  what  I
    don't think we want to have here.

    The  other  problem  is,  if  you insert a tuple containing a
    small value (e.g.  empty string), it'll not get  toasted  and
    you  can't force it to get. Later you open it for writing and
    pump a CD-image into. How do we convert  the  existing  empty
    text datum into a toast reference in the main tuple?


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Mon, 6 Aug 2001 10:35:00 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: Question about todo item
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > I keep bugging Jan about it, since pre-7.1 and no one has come up with
> > an idea.
>
> Well, if you want an idea:
>
>    BEGIN;
>
>    SELECT open_toast_object(toastable_column) FROM tab WHERE ...;
>
>    -- app checks that it got exactly one result back
>
>    -- app lo_reads and/or lo_writes using ID returned by SELECT
>
>    END;
>
> Implementation is left as an exercise for the reader ;-).
>
> Offhand this seems like it would be doable for a column-value that
> was actually moved out-of-line by TOAST, since the open_toast_object
> function could see and return the TOAST pointer, and then the read/
> write operations just hack on rows in pg_largeobject.  The hard part
> is how to provide equivalent functionality (transparent to the client
> of course) when the particular value you select has *not* been moved
> out-of-line.  Ideas anyone?

    TOAST  values aren't stored in pg_largeobject. And how do you
    seek to a position in a compressed and then sliced object? We
    need  a  way  to  force the object over a streaming interface
    into uncompressed toast slices first. Let me think  about  it
    for two days, Okay?

    The  interface  lacks  imho a mode (r/w/rw/a) argument. Other
    than that I'd like this part.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Mon, 6 Aug 2001 10:40:18 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: Question about todo item
Jan Wieck <JanWieck@yahoo.com> writes:
>     One  of  the problems I saw, and that's probably why we don't
>     have a proposal yet,  is,  that  the  size  of  the  data  is
>     recorded  in  the  toast reference held in the main tuple. If
>     you later open the toast value for writing, you'll change the
>     size,  but  you'd  need  to  change it in the main tuple too,
>     what'd require a regular update on the  main  tuple,  what  I
>     don't think we want to have here.

Well, in fact, maybe we *should*.  I was thinking last night about
the fact that large objects as they stand are broken from a
permissions-checking point of view: anyone who knows an LO's OID
can read or write it.  A LO-style interface for toasted columns must
not be so brain-dead.  This says that a SELECT open_toast_object()
should deliver a read-only object reference, and that if you want
to update, you should have to do an UPDATE.

Now a read-only TOAST LO reference strikes me as no problem.  If the
open() function finds that it's been handed a not-toasted value, it
can just save the value verbatim in the open-LO-reference table.
The value is not large, by definition, so this will work fine.

As for the update side of things, the best idea I can come up with
is a multi-phase operation: open the value with a select, read/write
the reference, store the updated reference with UPDATE.  Something
like:

1. SELECT writable_toast_reference(column) FROM table WHERE ...;

(Actually, SELECT FOR UPDATE would be the more common idiom.)

2. Read and/or write the LO reference returned by SELECT.  Note that
this must be defined to read/write a temporary work area --- if the
transaction aborts in this part, or commits without doing UPDATE,
nothing has happened to the stored value referenced by the main table
row.  (I think this happens automatically if we are hacking rows in
a toast table.  If we are hacking an in-line value stored in the
LO-reference table, we might at some point decide we need to shove it
out to disk.)

3. UPDATE table SET column = write_toast_reference(objectref) WHERE ...;

write_toast_reference extracts the toastable column's data or reference
from the LO table, closes the open LO reference (so you can't continue
hacking the data afterwards), and proceeds with a normal UPDATE.

It would also be pretty straightforward to extend this to the INSERT
case: we just need an "open" function that creates a new, empty object
of a TOASTable type in the LO reference table.  Write on this, and
finally invoke write_toast_reference() in the INSERT.


Kinda grotty, but implementable, and it doesn't require a whole new set
of permissions concepts.  Can anyone improve on this?

			regards, tom lane

Mon, 06 Aug 2001 10:53:20 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Question about todo item 
Tom Lane wrote:
> 
> Jan Wieck <JanWieck@yahoo.com> writes:
> >     One  of  the problems I saw, and that's probably why we don't
> >     have a proposal yet,  is,  that  the  size  of  the  data  is
> >     recorded  in  the  toast reference held in the main tuple. If
> >     you later open the toast value for writing, you'll change the
> >     size,  but  you'd  need  to  change it in the main tuple too,
> >     what'd require a regular update on the  main  tuple,  what  I
> >     don't think we want to have here.
> 
> Well, in fact, maybe we *should*.  

I think so too, as we shouldnt do in-place modification in the toast 
table anyway but give changed pages new trx ids, i.e UPDATE them.

it could be somewhat tricky to change just a few pages if there are 
some inter page pointers in toast-table. If its all done with regular
index only then this should pose no problem.

> I was thinking last night about
> the fact that large objects as they stand are broken from a
> permissions-checking point of view: anyone who knows an LO's OID
> can read or write it.  A LO-style interface for toasted columns must
> not be so brain-dead.  This says that a SELECT open_toast_object()
> should deliver a read-only object reference, and that if you want
> to update, you should have to do an UPDATE.
> 
> Now a read-only TOAST LO reference strikes me as no problem.  If the
> open() function finds that it's been handed a not-toasted value, it
> can just save the value verbatim in the open-LO-reference table.
> The value is not large, by definition, so this will work fine.
> 
> As for the update side of things, the best idea I can come up with
> is a multi-phase operation: open the value with a select, read/write
> the reference, store the updated reference with UPDATE.  Something
> like:
> 
> 1. SELECT writable_toast_reference(column) FROM table WHERE ...;
> 
> (Actually, SELECT FOR UPDATE would be the more common idiom.)
> 
> 2. Read and/or write the LO reference returned by SELECT.  Note that
> this must be defined to read/write a temporary work area --- if the
> transaction aborts in this part, or commits without doing UPDATE,
> nothing has happened to the stored value referenced by the main table
> row.  (I think this happens automatically if we are hacking rows in
> a toast table.  If we are hacking an in-line value stored in the
> LO-reference table, we might at some point decide we need to shove it
> out to disk.)

but in both inline and toast-table modified pages should have new 
transaction id's like regular tuples and thus be handled by regular 
transaction commit/abort mechanics, at least this seema as a postgres 
way to do it .

> 3. UPDATE table SET column = write_toast_reference(objectref) WHERE ...;
> 
> write_toast_reference extracts the toastable column's data or reference
> from the LO table, closes the open LO reference (so you can't continue
> hacking the data afterwards), and proceeds with a normal UPDATE.
> 
> It would also be pretty straightforward to extend this to the INSERT
> case: we just need an "open" function that creates a new, empty object
> of a TOASTable type in the LO reference table.  Write on this, and
> finally invoke write_toast_reference() in the INSERT.
> 
> Kinda grotty, but implementable, and it doesn't require a whole new set
> of permissions concepts.  Can anyone improve on this?

If toast table has the same permissions as the main table and lo_write 
honours these then we should be ok.

---------------
Hannu

Mon, 06 Aug 2001 18:00:05 +0200
Hannu Krosing <hannu@tm.ee>
Re: Question about todo item
Can this be added to the TODO list? (actually put back on the TODO list) 
Along with this email thread?

I feel that it is very important to have BLOB support in postgres that 
is similar to what the commercial databases provide.  This could either 
mean fixing the current implementation or adding additional capabilities 
to toasted columns.

The major problem with the current LargeObject implementation is that 
when the row containing the LargeObject is deleted the LargeObject 
isn't.  This can be a useful feature under some circumstances, but it 
isn't how other databases handle BLOBs.  Thus porting code from other 
databases is a challenge.  While it is true that this can be worked 
around through triggers, I don't like the manual nature of the workarounds.

thanks,
--Barry

Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> 
>>>Offhand this seems like it would be doable for a column-value that
>>>was actually moved out-of-line by TOAST, since the open_toast_object
>>>function could see and return the TOAST pointer, and then the read/
>>>write operations just hack on rows in pg_largeobject.  The hard part
>>>
> 
>>I am confused how pg_largeobject is involved?
>>
> 
> s/pg_largeobject/toast_table_for_relation/ ... sorry about that ...
> 
> 
>>Don't forget compression of TOAST columns.  How do you fseek/read/write
>>in there?
>>
> 
> Well, you can *do* it, just don't expect it to be fast.  The
> implementation would have to read or write most of the value, not just
> the segment you wanted.  A person who actually expected to use this
> stuff would likely want to disable compression on a column he wanted
> random access within.
> 
> Hmm ... that provides an idea.  We could easily add some additional
> 'attstorage' settings that say *all* values of a column must be forced
> out-of-line (with or without allowing compression), regardless of size.
> Then, open_toast_object would work reliably on such a column.  One
> possible user API to such an infrastructure is to invent BLOB and CLOB
> datatypes, which are just like bytea and text except that they force the
> appropriate attstorage value.  Ugly as sin, ain't it ... but I bet it
> could be made to work.
> 
> Okay, there's your idea.  Now, who can do better?
> 
> 			regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://www.postgresql.org/search.mpl
> 
> 



Thu, 16 Aug 2001 09:18:50 -0700
Barry Lind <barry@xythos.com>
Re: Question about todo item
I have added to TODO:

BINARY DATA
        o -Add non-large-object binary field (already exists -- bytea)
        o -Make binary interface for TOAST columns (base64)
        o Improve vacuum of large objects (/contrib/vacuumlo)
        o Add security checking for large objects
        o Make file in/out interface for TOAST columns, similar to large object
          interface (force out-of-line storage and no compression)
        o Auto-delete large objects when referencing row is deleted

> Can this be added to the TODO list? (actually put back on the TODO list) 
> Along with this email thread?
> 
> I feel that it is very important to have BLOB support in postgres that 
> is similar to what the commercial databases provide.  This could either 
> mean fixing the current implementation or adding additional capabilities 
> to toasted columns.
> 
> The major problem with the current LargeObject implementation is that 
> when the row containing the LargeObject is deleted the LargeObject 
> isn't.  This can be a useful feature under some circumstances, but it 
> isn't how other databases handle BLOBs.  Thus porting code from other 
> databases is a challenge.  While it is true that this can be worked 
> around through triggers, I don't like the manual nature of the workarounds.
> 
> thanks,
> --Barry
> 
> Tom Lane wrote:
> > Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > 
> >>>Offhand this seems like it would be doable for a column-value that
> >>>was actually moved out-of-line by TOAST, since the open_toast_object
> >>>function could see and return the TOAST pointer, and then the read/
> >>>write operations just hack on rows in pg_largeobject.  The hard part
> >>>
> > 
> >>I am confused how pg_largeobject is involved?
> >>
> > 
> > s/pg_largeobject/toast_table_for_relation/ ... sorry about that ...
> > 
> > 
> >>Don't forget compression of TOAST columns.  How do you fseek/read/write
> >>in there?
> >>
> > 
> > Well, you can *do* it, just don't expect it to be fast.  The
> > implementation would have to read or write most of the value, not just
> > the segment you wanted.  A person who actually expected to use this
> > stuff would likely want to disable compression on a column he wanted
> > random access within.
> > 
> > Hmm ... that provides an idea.  We could easily add some additional
> > 'attstorage' settings that say *all* values of a column must be forced
> > out-of-line (with or without allowing compression), regardless of size.
> > Then, open_toast_object would work reliably on such a column.  One
> > possible user API to such an infrastructure is to invent BLOB and CLOB
> > datatypes, which are just like bytea and text except that they force the
> > appropriate attstorage value.  Ugly as sin, ain't it ... but I bet it
> > could be made to work.
> > 
> > Okay, there's your idea.  Now, who can do better?
> > 
> > 			regards, tom lane
> > 
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> > 
> > http://www.postgresql.org/search.mpl
> > 
> > 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
> 

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Thu, 6 Sep 2001 16:36:52 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Question about todo item