Dear Friends,

Apologize.. this posting may not relevance enough to this group.
I am very much a newbie to posgreSQL database programming and would like
assistance. I am finding an example how to use the database (postgreSQL) 
to store MRI/CT image data using C language interface. I heard something 
pronounced like: Content-Based Image Retrieval.. but I still do not have
any idea for a HowTo do on postgreSQL database.

If possible, could someone describe the skeleton for such database pro-
gramming.. nor if there are examples or some references/tutorial/books
concering this kind database programming, could someone give me pointers 
to both the site online or book store. 

Sorry if this is the incorrect place for this post, but I do need helps 
and figured to start with. Thank you for your helps.

santosa budi.-


9 Apr 2003 20:53:13 -0700
jp_santosa@yahoo.com (Santosa Budi)
medical image on postgreSQL?
On 9 Apr 2003, Santosa Budi wrote:

> Dear Friends,
> 
> Apologize.. this posting may not relevance enough to this group.
> I am very much a newbie to posgreSQL database programming and would like
> assistance. I am finding an example how to use the database (postgreSQL) 
> to store MRI/CT image data using C language interface. I heard something 
> pronounced like: Content-Based Image Retrieval.. but I still do not have
> any idea for a HowTo do on postgreSQL database.
> 
> If possible, could someone describe the skeleton for such database pro-
> gramming.. nor if there are examples or some references/tutorial/books
> concering this kind database programming, could someone give me pointers 
> to both the site online or book store. 
> 
> Sorry if this is the incorrect place for this post, but I do need helps 
> and figured to start with. Thank you for your helps.

Good of a place as any :-)

If you want to store a binary file in postgresql, you can either use the 
semi-non-standard large object interface (I'd recommend against it 
personally) or you can encode your data and store it in the database.  
while there is a bytea type that works ok, but my preferred method is more 
transportable:  base-64 encode the data, then store that in a text field.  
Since postgresql compresses text fields automagically, you don't have to 
worry about base-64 being too bloaty, since the only bloat will be when 
you're actually moving the file into / out of the database.

pseudo code:

$file = "/somedir/somefile.gif";
$fp = fopen($file,"r");
$img=fread($fp,filesize($file));
$b64 = base64encode($img);
$b64=pg_escape($b64);
$query = "insert into table (bigfield) values ('$b64');


Just reverse the process to get it back.


Fri, 11 Apr 2003 14:15:04 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: medical image on postgreSQL?
> Apologize.. this posting may not relevance enough to this group.  I
> am very much a newbie to posgreSQL database programming and would
> like assistance. I am finding an example how to use the database
> (postgreSQL) to store MRI/CT image data using C language
> interface. I heard something pronounced like: Content-Based Image
> Retrieval.. but I still do not have any idea for a HowTo do on
> postgreSQL database.

This is the 3rd such case that I've heard someone using PostgreSQL to
store MRI images.  While it's non-portable (works on FreeBSD/Linux),
sendfile() and recvfile() would be huge wins for folks in that crowd
(or anyone storing/fetching large files).  There'd be some large
#ifdef sections (~100 lines?) in the backend, possibly in the fe
('nother ~100 lines), but would patches for this be accepted into the
tree if I did the legwork given sendfile()'s non-universal portability
(which seems to be, IMHO, an overly important criteria)?  -sc

-- 
Sean Chittenden


Fri, 11 Apr 2003 13:36:03 -0700
Sean Chittenden <sean@chittenden.org>
Re: [GENERAL] medical image on postgreSQL?
On Fri, 11 Apr 2003, Sean Chittenden wrote:

> > Apologize.. this posting may not relevance enough to this group.  I
> > am very much a newbie to posgreSQL database programming and would
> > like assistance. I am finding an example how to use the database
> > (postgreSQL) to store MRI/CT image data using C language
> > interface. I heard something pronounced like: Content-Based Image
> > Retrieval.. but I still do not have any idea for a HowTo do on
> > postgreSQL database.
> 
> This is the 3rd such case that I've heard someone using PostgreSQL to
> store MRI images.  While it's non-portable (works on FreeBSD/Linux),
> sendfile() and recvfile() would be huge wins for folks in that crowd
> (or anyone storing/fetching large files).  There'd be some large
> #ifdef sections (~100 lines?) in the backend, possibly in the fe
> ('nother ~100 lines), but would patches for this be accepted into the
> tree if I did the legwork given sendfile()'s non-universal portability
> (which seems to be, IMHO, an overly important criteria)?  -sc

I'm not familiar with sendfile() recfile().  Are they in contrib 
somewhere?


Fri, 11 Apr 2003 14:53:49 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: [GENERAL] medical image on postgreSQL?
> > > Apologize.. this posting may not relevance enough to this group.
> > > I am very much a newbie to posgreSQL database programming and
> > > would like assistance. I am finding an example how to use the
> > > database (postgreSQL) to store MRI/CT image data using C
> > > language interface. I heard something pronounced like:
> > > Content-Based Image Retrieval.. but I still do not have any idea
> > > for a HowTo do on postgreSQL database.
> > 
> > This is the 3rd such case that I've heard someone using PostgreSQL
> > to store MRI images.  While it's non-portable (works on
> > FreeBSD/Linux), sendfile() and recvfile() would be huge wins for
> > folks in that crowd (or anyone storing/fetching large files).
> > There'd be some large #ifdef sections (~100 lines?) in the
> > backend, possibly in the fe ('nother ~100 lines), but would
> > patches for this be accepted into the tree if I did the legwork
> > given sendfile()'s non-universal portability (which seems to be,
> > IMHO, an overly important criteria)?  -sc
> 
> I'm not familiar with sendfile() recfile().  Are they in contrib
> somewhere?

:) They're system calls, like write() && read() except they're zero
copy socket operations.  If the OS has sendfile(), the OS basically
DMA's the data from the disk read directly to the network card
averting copying the data to the userland and back to the kernel.  I
actually think it'd be interesting to get this in use for general
reads.  sendfile() lets you send headers/trailers to the data being
sent, similar to writev().  Other zero copy socket operations are
mmap() + write(), but last I heard, that was a FreeBSD only
thing... for now.  man 2 sendfile

err... there is no recvfile though... I thought there was an fd based
call similar to recvfrom called recvfile... I wonder where I saw
that... hrm, I think I may add that to -CURRENT for orthogonality's
sake. -sc

-- 
Sean Chittenden


Fri, 11 Apr 2003 14:16:17 -0700
Sean Chittenden <sean@chittenden.org>
Re: [GENERAL] medical image on postgreSQL?

Sean Chittenden <sean@chittenden.org> writes:

> Other zero copy socket operations are mmap() + write(), but last I heard,
> that was a FreeBSD only thing... for now. man 2 sendfile

There's a lot of resistance to the optimizating mmap+write in the linux camp.
It isn't just a matter of time, the developers there actively think this is a
bad idea. In fact the code has been written several times and is never
accepted. They think developers should be encouraged to use sendfile and the
common code path for write shouldn't be wasting cycles checking for  special
cases in the page table.

Note that there are some protocol requirements for sendfile to be feasible.
There has to be zero alterations made to the data in flight. No escaping,
decompression, etc. And there has to be no cases when the program would want
to stop transmitting partway through. I think you can send a portion of a file
but you would have to know the size of the chunk up front and the best
performance would be if the chunk is very large.

--
greg


11 Apr 2003 19:06:03 -0400
Greg Stark <gsstark@mit.edu>
Re: [GENERAL] medical image on postgreSQL?
> > Other zero copy socket operations are mmap() + write(), but last I
> > heard, that was a FreeBSD only thing... for now. man 2 sendfile
> 
> There's a lot of resistance to the optimizating mmap+write in the
> linux camp.  It isn't just a matter of time, the developers there
> actively think this is a bad idea. In fact the code has been written
> several times and is never accepted. They think developers should be
> encouraged to use sendfile and the common code path for write
> shouldn't be wasting cycles checking for special cases in the page
> table.

Well, I won't go into how well/poorly Linux's VM is written... that
said, I suppose I sympathize with the developers in the linux camp
that want to avoid this issue...  this isn't easy to do
right/elegantly and it took BSD quite a while to get right, iirc.

> Note that there are some protocol requirements for sendfile to be
> feasible.  There has to be zero alterations made to the data in
> flight. No escaping, decompression, etc. And there has to be no
> cases when the program would want to stop transmitting partway
> through. I think you can send a portion of a file but you would have
> to know the size of the chunk up front and the best performance
> would be if the chunk is very large.

I can speak from personal experience under huge loads (60K+
connections to a single webserver) that for small files, it is
advantageous to use mmap() + write() instead of sendfile().
sendfile() has a pretty funky API that isn't the cleanest out there
and requires a small state machine per file being sent and is more
complex for nonblocking IO, but it's still better.  As for
performance, mmap() + write() is _faster_ than sendfile() for small
files that can be cached by the FS cache layer.  What's odd, however,
is that I found it only marginally faster (1-3ms?) and I'm not
convinced that the speed up wasn't from sending data from the local
box (mmap()) instead of being pulled over NFS (sendfile()).
sendfile() is pretty slick and I'd recommend its use anywhere over
read() + write().

FWIW, cache coherency isn't an issue for well written VMs though
(*rub*).  The data can change under sendfile()'s feet and that's okay,
BSD handles this correctly (nevermind MVCC prevents this from being a
problem).  Writing data to a page that's mmap()'ed is also sync'ed and
cache coherency isn't an issue for so long as the page is shared and
sync'ed with disk periodically.

-sc

-- 
Sean Chittenden


Fri, 11 Apr 2003 16:43:56 -0700
Sean Chittenden <sean@chittenden.org>
Re: [GENERAL] medical image on postgreSQL?