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

Peter,

Your help is greatly appreciated. As far as information goes. The
postgres site has a developers section. I presume you already know how
to get the code, if not, let me know and I can direct you appropriately

Please, feel free to contribute

Dave
On Thu, 2002-04-18 at 12:15, Peter V Cooper wrote:
> This sounds reasonable and like something I might use as I am doing this myself
> manually already in Postgres & Oracle. I only have one install of postgres
> and many w/ Oracle. We manage Objects like large images manually. It would
> be nice to have this in postgres. I would use it now as I have to use the
> os filesystem
> and synchronize using a sequence already. Security and especially
> non-cleanup on
> delete are real problems. Just one user opinion.
>
> BTW, I have never used a DBMS for this in the past because I was
> comfortable with
> having a sequence in the DB/hashed set of dirs and binary files in those
> dirs. The
> reason for hashed directories is twofold one to keep the number of directory
> entries lower when working with millions of rows/files and two when you overrun
> the filesystem then it is possible to symlink some of the dirs to another
> file system.
> This could create problems if you lost some of the files to a drive failure
> but it
> would be temporary as the system admin would replace/repair and recover.  My
> next task is to give my FE Java class multiple filesystem places to
> automatically
> manage - this would be cool as well.
>
> PS, I have done development since 1979 at OS(UNIX) DB (Informally Helped Oracle
> in 1983) and Application levels. I have never added code/my help to an Open
> Source
> project. Where do I find info on this? I might be inclined to work on something
> like this if anyone would want me to. I have a lot of source which does much of
> what I have spoken of. Any suggestions or should I 'butt out' so to speak.
>
> At 02:49 PM 4/18/2002 +1000, Joe Shevland wrote:
> >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 important type, 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 think the important
> >thing is I'm not particularly concerned about speed of access (when
> >dealing with whole binary data, certainly don't need it indexed): what I
> >mean by that is the binary file could happily reside on the local file
> >system and be streamed to the client... maybe a 'softlink' to the data
> >(aka the Large Object manager with oids almost), but I guess this is old
> >ground, I'm thinking a new datatype that still has ACL's but is only a
> >string pointing to the file on the local filesystem. I should probably be
> >thinking of all the complexity this might add to the parser, optimisations
> >and stuff. 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 user and stored in the db
> >data directory perhaps, that fits in with pg_dump|restore, that could be
> >streamed and referenced in 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 sacrifice a goat to the Tom Lane
> >alter ;) Maybe a new datatype 'bytefs', rejig the parser and all the rest.
> >Or, is large binary data 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 Incredibly Great Thing(TM)....
> >but then I thought about about the separation of data and logic, and
> >thought that it's overkill for 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. Outhouse is 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.
> >
> >
> >---------------------------(end of broadcast)---------------------------
> >TIP 5: Have you checked our extensive FAQ?
> >
> >http://www.postgresql.org/users-lounge/docs/faq.html
>
>