>>> Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> I was kinda wondering about something closer to the TOAST model,
where
> a blob is only referenceable from a value that's in a table field;
> and that value encapsulates the "name" of the blob in some way that
> needn't even be user-visible. This'd greatly simplify the
> cleanup-dead-objects problem, and we could avoid addressing the
> permissions problem at all, since regular SQL permissions on the
table
> would serve fine. But it's not clear what regular SQL fetch and
update
> behaviors should be like for such a thing. (Fetching or storing the
> whole blob value is right out, IMHO.) ISTR hearing of concepts
roughly
> like this in other DBs --- does it ring a bell for anyone?
It'd probably be good to have methods parallel to the JDBC API within
the implementation.
http://java.sun.com/javase/6/docs/api/java/sql/Blob.html
http://java.sun.com/javase/6/docs/api/java/sql/Clob.html
http://java.sun.com/javase/6/docs/api/java/sql/NClob.html
http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#getBlob(int)
http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#getClob(int)
http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#getNClob(int)
http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#updateBlob(int,%20java.sql.Blob)
http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#updateBlob(int,%20java.io.InputStream)
http://java.sun.com/javase/6/docs/api/java/sql/ResultSet.html#updateBlob(int,%20java.io.InputStream,%20long)
(similar for CLOB & NCLOB)
Reading a blob value gives you an object which lets you perform the
stream-based operations against the blob. To set a blob value on an
insert, you prepare a statement and then link the stream to the blob
-- the insertRow method sucks the data from the stream. To set a blob
on an update, you use an updateable cursor (or maybe a prepared
statement) to do the same. You can set a lob from another lob
directly in SQL I assume we'd want to support streams directly inline
in the protocol, as well as support functions to convert between
datums and streams (for, say, tiny little 2MB or 10MB values), and
files and streams (kinda like COPY).
-Kevin