Обсуждение: Large objects, why not use the filesystem?

Greetings,

I always see posts of people trying to get their large binary and text
objects into and out of the database somehow.  I was wondering if there is
some reason why just storing a filename in the table would be a bad thing?
This way you can let the file system worry about storing the data (since
that is what the file system is good at.)  I understand that you probably
could not access the data via ODBC, but if you are writing your frontend in
C or Perl, etc. then you would simply use the filename stored in the table
to access the data.

Comments?

Also, could someone explain varchar?  I have never found a *really* clear
explanation and I'm not sure I fully understand its potential, use, and/or
shortcomings.

Thank you,
Matthew Hagerty

On Sun, Jan 31, 1999 at 03:26:15PM -0500, Matthew Hagerty wrote:
> Greetings,
>
> I always see posts of people trying to get their large binary and text
> objects into and out of the database somehow.  I was wondering if there is
> some reason why just storing a filename in the table would be a bad thing?
> This way you can let the file system worry about storing the data (since
> that is what the file system is good at.)  I understand that you probably
> could not access the data via ODBC, but if you are writing your frontend in
> C or Perl, etc. then you would simply use the filename stored in the table
> to access the data.

    Because the database is in a different room then the client.

Matthew Hagerty <matthew@wolfepub.com> writes:
> I always see posts of people trying to get their large binary and text
> objects into and out of the database somehow.  I was wondering if there is
> some reason why just storing a filename in the table would be a bad thing?

Quite a number of people do exactly that.

It falls down though if you are trying to use Postgres in a multiple-
machine environment: the client applications don't necessarily have
access to the filesystem on the database machine.  Even if they do,
there are security issues to think about: the access permissions for
the filesystem may be tighter or looser than your access permissions
for the database installation, and will certainly be *different*.

But if it gets the job done for you, go right ahead...


> Also, could someone explain varchar?

AFAIK varchar is just a variable-length string type with a specific
length limit set on a per-column basis.  This is distinct from plain
char(n), which is a fixed-length string, and from text, which is a
variable-length string with no specific upper limit (short of the
implementation limit on total size of a tuple, that is).

There are probably a few small differences among the three types in
terms of the operations available, but this would be more in the nature
of an oversight (ie, someone forgot to write the operator routine) than
a fundamental property of the type.

varchar and text are essentially equivalent in terms of implementation
efficiency (space cost), too.  I believe char(n) occupies the same
amount of space as a varchar or text that happens to be n characters
--- ie, there is a 4-byte overhead, even though it's not logically
necessary for char(n).  I've been griping about that, but I doubt that
anything is likely to happen soon... too many other things on the
to-do list.

            regards, tom lane

> > I always see posts of people trying to get their large binary and text
> > objects into and out of the database somehow.  I was wondering if there is
> > some reason why just storing a filename in the table would be a bad thing?
> > This way you can let the file system worry about storing the data (since
> > that is what the file system is good at.)  I understand that you probably
> > could not access the data via ODBC, but if you are writing your frontend in
> > C or Perl, etc. then you would simply use the filename stored in the table
> > to access the data.
>
>     Because the database is in a different room then the client.
>
There are lots of easier ways to pass files from that room (http, etc.)

--Gene

On Sun, 31 Jan 1999, Matthew Hagerty wrote:

> Greetings,
>
> I always see posts of people trying to get their large binary and text
> objects into and out of the database somehow.  I was wondering if there is
> some reason why just storing a filename in the table would be a bad thing?
> This way you can let the file system worry about storing the data (since
> that is what the file system is good at.)  I understand that you probably
> could not access the data via ODBC, but if you are writing your frontend in
> C or Perl, etc. then you would simply use the filename stored in the table
> to access the data.
>
> Comments?

It depends on you application. I noticed in another post that you are
using it for .cgi. In that case you could store the filename into the
database, then access it directly. I've used this method on a couple of
occasions in the past.

However, this doesn't work if you have, say a server in a different
continent to the user. Then you could have a URL stored in the database,
and use another protocol to serve the objects. This however, leads to the
problem of having to keep an httpd server running, and also - depending on
the application, could have security implications. Having a separate
protocol to serve the large objects may not be viable if you want to
limit access to them.

Storing them in the database has it's benefits. You are certain that the
objects are going to a client that's actually connected to the database.
Also, Corba objects may depend on using Large Objects for storing data too
large to fit in normal tables.

Peter

--
       Peter T Mount peter@retep.org.uk
      Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
 Java PDF Generator: http://www.retep.org.uk/pdf