Lately I've been planning work on a patch to postgres and linux on my
system to allow access to BLOBs as a virtual filesystem, so I can see
any file I put in there even through network shares while avoiding
duplication / broken link issues.  Does this sound like something worth
doing / is there a better way to safely reference files from both inside
and outside postgres?

Wed, 23 Jun 2004 11:21:45 -0400
Peter Martini <PeterM@processflo.com>
BLOBs and a virtual file system
Peter Martini <PeterM@processflo.com> writes:
> Lately I've been planning work on a patch to postgres and linux on my
> system to allow access to BLOBs as a virtual filesystem, so I can see
> any file I put in there even through network shares while avoiding
> duplication / broken link issues.  Does this sound like something worth
> doing / is there a better way to safely reference files from both inside
> and outside postgres?

How would you do that without breaking transactional integrity for
blobs?  There'd be no way to deal with multiple row versions in such
a representation.

			regards, tom lane

Wed, 23 Jun 2004 11:48:57 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: BLOBs and a virtual file system 
Peter Martini writes: 

> Lately I've been planning work on a patch to postgres and linux on my
> system to allow access to BLOBs as a virtual filesystem, so I can see
> any file I put in there even through network shares while avoiding
> duplication / broken link issues.  Does this sound like something worth
> doing / is there a better way to safely reference files from both inside
> and outside postgres? 
> 

I have been looking into using the Tcl VFS features for this kind of thing. 
I work with AOLserver and PostgreSQL so I have Tcl and PostgreSQL available 
to me. 

http://nnsa.dl.ac.uk/MIDAS/manual/ActiveTcl8.4.4.0-html/tclvfs/doc/vfs.html 

I haven't thought about accessing the VFS from inside PostgreSQL though. 

Dave Bauer
dave@thedesignexperience.org 


> ---------------------------(end of broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose an index scan if your
>       joining column's datatypes do not match
 


Wed, 23 Jun 2004 11:55:08 -0400
"Dave Bauer" <dave@thedesignexperience.org>
Re: BLOBs and a virtual file system
I'm not sure what you mean by breaking blobs transactional integrity. 
Do you mean by allowing filesystem type access, the blobs won't be
properly locked and updated during a transaction?  If so, that's exactly
what I'm trying to achieve - a compromise between forcing the files to
be stored solely in the database (maintaining integrity but sacrificing
functionality) or referencing their names to gain functionality, but at
a greater cost.
More to the point, wouldn't file locking mechanisms, and the possibility
of limiting file visibility through the virtual filesystem, allow
transactional integrity to be maintained?  If not, could you explain
where the problem is so I can look further into it?

Thanks,
Peter

On Wed, 2004-06-23 at 11:48, Tom Lane wrote:
> Peter Martini <PeterM@processflo.com> writes:
> > Lately I've been planning work on a patch to postgres and linux on my
> > system to allow access to BLOBs as a virtual filesystem, so I can see
> > any file I put in there even through network shares while avoiding
> > duplication / broken link issues.  Does this sound like something worth
> > doing / is there a better way to safely reference files from both inside
> > and outside postgres?
> 
> How would you do that without breaking transactional integrity for
> blobs?  There'd be no way to deal with multiple row versions in such
> a representation.
> 
> 			regards, tom lane


Wed, 23 Jun 2004 15:07:42 -0400
Peter Martini <peterm@processflo.com>
Re: BLOBs and a virtual file system
On Wed, 23 Jun 2004 15:07:42 -0400
Peter Martini <peterm@processflo.com> wrote:

> I'm not sure what you mean by breaking blobs transactional integrity. 
> Do you mean by allowing filesystem type access, the blobs won't be
> properly locked and updated during a transaction?  If so, that's
> exactly what I'm trying to achieve - a compromise between forcing the
> files to be stored solely in the database (maintaining integrity but
> sacrificing functionality) or referencing their names to gain
> functionality, but at a greater cost.
> More to the point, wouldn't file locking mechanisms, and the
> possibility of limiting file visibility through the virtual
> filesystem, allow transactional integrity to be maintained?  If not,
> could you explain where the problem is so I can look further into it?

  I believe the problem is that the blob could be in several different
  states inside of several different transactions. How do you 
  determine which you show in the filesystem? 

  Even if the file system is read only you still have this problem of
  "which" of the many possible BLOBs to reveal.  

 ---------------------------------
   Frank Wiles <frank@wiles.org>
   http://frank.wiles.org
 ---------------------------------


Wed, 23 Jun 2004 14:16:51 -0500
Frank Wiles <frank@wiles.org>
Re: BLOBs and a virtual file system