Re: BLOB performance test FYI - Mailing list pgsql-jdbc

Its an interesting topic... when I use BLOB's its generally for storage of large things, documents, images, data files
etc.Thats one view though; other uses like small binary data or encrypted stuff would be common too, so 'bytea' is an
importanttype, but used in moderation (with the current JDBC driver) like anything I guess ;) 

I care about all the things that PostgreSQL does, including access control, referential integrity and whatnot, but I
thinkthe important thing is I'm not particularly concerned about speed of access (when dealing with whole binary data,
certainlydon't need it indexed): what I mean by that is the binary file could happily reside on the local file system
andbe streamed to the client... maybe a 'softlink' to the data (aka the Large Object manager with oids almost), but I
guessthis is old ground, I'm thinking a new datatype that still has ACL's but is only a string pointing to the file on
thelocal filesystem. I should probably be thinking of all the complexity this might add to the parser, optimisations
andstuff. I'm not - as a result of aforementioned hop water ;) 

So a LO/link datatype that obeys ACL's, can be integrated into the BE/FE protocol, would be rw-only by the postmaster
userand stored in the db data directory perhaps, that fits in with pg_dump|restore, that could be streamed and
referencedin result sets, that would be deleted on deletion of the link, and that doesn't break any existing
functionality,perhaps with extensions to the SQL standard. That's all I'm after :) :) 

No, I've no idea what all the issues are yet, I'm sure there's heaps - I think I need to burn some rosemary and
sacrificea goat to the Tom Lane alter ;) Maybe a new datatype 'bytefs', rejig the parser and all the rest. Or, is large
binarydata really the province of an RDBMS? 

Cheers,
Joe

PS. Someone raised the PG-Java procedural language the other day. When that idea comes to fruition it will be an
IncrediblyGreat Thing(TM).... but then I thought about about the separation of data and logic, and thought that it's
overkillfor the database to do that. Thoughts? I was thinking maybe a pseudo 

PPS. We need an RFC or a good MIME type that stops digital signatures interfering with automatic appended footers.
Outhouseis causing me grief with this list. 

> Joe, Anders,
>
> Please keep this discussion online. The more people we have looking at a
> possible solution, the better.
>
> Dave
[snip]
> > > Instead of streaming large data into the
> > > backend, the client reads them into memory before sending them.
> > > It seems the LO parts of the driver has a different solution, that
> > > streams the data directly.
> > > It would take some major reworking of the JDBC driver to fix
> this. I've
> > > looked at it, and concluded that it is possible, but that it
> was too big
> > > for me to fix.