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