Re: Question about todo item - Mailing list pgsql-hackers

I have added to TODO:

BINARY DATA       o -Add non-large-object binary field (already exists -- bytea)       o -Make binary interface for
TOASTcolumns (base64)       o Improve vacuum of large objects (/contrib/vacuumlo)       o Add security checking for
largeobjects       o Make file in/out interface for TOAST columns, similar to large object         interface (force
out-of-linestorage 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,
Pennsylvania19026